Processing Multidimensional cubes

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    Hi all

     

    We currently have a multidimensional database that contains 5 cubes.

    We want to process these via an SSIS task (for flow control) but we need to do them individually (it's part of our loading strategy for our data warehouse) but we're struggling to get things processing correctly.

     

    At the moment, we load all the relvant tables and then process all the cubes at the same time by just processing the entire database (if that makes sense).

     

    We've tried to do the cubes individually but it keeps coming with with errors along the lines of :-

    [Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'Dim_Uncoded_Spell_Measures', Column: 'SpellKey', Value: '86649241'. The attribute is 'Spell Key'.

    [Analysis Services Execute DDL Task] Error: Errors in the OLAP storage engine: An error occurred while processing the 'Dim Patient Measures' partition of the 'Patient Measures' measure group for the 'APC Activity Cube' cube from the DataWarehouseCubes database.

     

    (The above are just two out of a list).

     

    Things we've tried so far:-

    • Process the cube on it's own
    • Process the DIMs, process the cube
    • Process the DIMs, process the measures, process the cube

    All of the above give us a similar error to the one's list below.

     

    I've made sure that the value mentioned above is in the correct table (and it is) but it just doesn't want to recognise it for some reason.

     

    We could process the entire database (i.e. all 5 cubes in one go) but that would defeat the object of what we're trying to do.

     

    We're using an "Analysis Service Processing Task" object in SSIS to do what we want to do (and we've had it working in the past when processing everything at once).

     

    I'm fairly certain I will have missed some info that someone will need so feel free to ask for anything else.

     

    Has anyone any idea on what we're missing?

     

    TIA

     

    Richard

  • Martin Schoombee

    SSCoach

    Points: 19033

    Processing all the dimensions first and then the cubes should work. If it doesn't, it might be because you're not doing a "full" process on the dimensions or you may have ignored those errors in the processing task.

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    I've just checked and I've got all the dimensions set to "full" and the cube itself is set to the same.

    We do have measures that I haven't set to process (I turned that bit off temporarily) but I was under the impression that these would be done when the cube itself was processed.

  • Martin Schoombee

    SSCoach

    Points: 19033

    If the cube is processed "full", then all associated measure groups in the cube will also be processed. You may have to dive into the error a little more. For some reason, the dimension member with that key attribute is in your fact table but not your dimension. Could be a data source issue (i.e. incorrect query, bad data, etc.)

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    That's what I thought (although I thought it processed all the necessary DIMs as well).

     

    I've just tried to process it again and it's thrown a wobbly with one of the measure groups (and I've got the code it's running).

    I've run the code againt my SQL database for the key value it said was mising and it's appeared (hopefully that bit makes sense).

     

    For me, that leaves two options:-

    1. That key value is not being recognised in the DIM for some reason
    2. That key value is a red herring and the issue is elsewhere

     

    Now I'm really tearing my hair out.

  • Martin Schoombee

    SSCoach

    Points: 19033

    In the world of multidimensional cubes, the value it's complaining about is usually the culprit (or part of it). I would do the following:

    • Fully process the dimension in question
    • Try to find the attribute key in the dimension after it was processed. If it is, process the measure group again and see if it works. If the measure group fails, it may be because of a bad relationship or attribute relationship between the dimension and fact table.
    • If the attribute key is not in the dimension, look at your data source view and make sure there aren't any filters which may cause the exclusion of this row.
  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    Looks like I'm going blind and/or stupid again.

     

    In desperation, I ticked all of the DIMs to be processed (full) and let it run through.

    That gave me an error on a DIM that's completely unrelated to the cube in question so I went through and removed all DIMs that I knew were unnecessary.

    That got the measure I was struggling with to process which, in turn, allowed the cube to process.

    I've now removed the measure processing but left everything else in place (I've only disabled the bits in the SSIS package so I can easily turn them back on again if necessary).

     

    Everything is now running again (just so I can check it before I deploy the updated packages) but it's all looking good.

     

    Thanks for the help.

  • Martin Schoombee

    SSCoach

    Points: 19033

    No worries. The dimension that was failing was most likely causing your other dimensions down the list not to process, and SSIS wasn't throwing the error like it should have. One of the reasons I don't like to process cubes using the built-in SSIS tasks :-/

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    That was the first of two cubes that have been failing but I'm fairly certain the root cause will be the same for both (so the same fix should apply).

     

    Just out of curiosity, what do you use to process cubes if you don't use the SSIS tasks?

  • Martin Schoombee

    SSCoach

    Points: 19033

    I like to use XMLA directly in the SQL Agent jobs. At least in my experience, the built-in SSAS processing task is a little clunky sometimes.

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    Thanks for that, I'll have a look and see if it offers any improvements.

  • richardmgreen1

    SSCrazy Eights

    Points: 9857

    I'm back again.

     

    I've changed to running the XMLA in a "Analysis Services Execute DDL Task" in SSIS (I grabbed the generated XMLA from SSMS and pasted that in.

     

    I've now got the following error:-

    [Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Dim_Patient_Indicators', Column: 'pkCurrentPatientKey', Value: '5500092'; Table: 'dbo_Dim_Patient_Indicators', Column: 'pkPatientIndicatorLocalID', Value: 'Y00e4'; Table: 'dbo_Dim_Patient_Indicators', Column: 'pkSYSSourceSystem', Value: 'SystmOne'. The attribute is 'Pk Current Patient Key'.

     

    This record is in my DIM table but not in the relevant FACT table so I'm now sure why it's throwing it out.

     

    Anyone any ideas?

     

    ::edit::

     

    Just reset all the DIMS and the one FACT table to reload everything and now I'm getting a different error:-

    [Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_Dim_All_APC_Diagnoses', Column: 'pkEpisodeKey', Value: '87920058'; Table: 'dbo_Dim_All_APC_Diagnoses', Column: 'pkOrderOfDiagnosis', Value: '1'. The attribute is 'Pk Episode Key'.

     

    I've checked all my tables and that record (i.e. EpisodeKey) appears in all the necessary places for this cube to process.

     

    There doesn't seem to be any rhyme or reason with this.

    • This reply was modified 1 month, 1 week ago by  richardmgreen1. Reason: Extra info

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply