February 18, 2020 at 10:43 am
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:-
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
February 18, 2020 at 11:34 am
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.
February 18, 2020 at 11:43 am
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.
February 18, 2020 at 12:23 pm
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.)
February 18, 2020 at 2:54 pm
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:-
Now I'm really tearing my hair out.
February 18, 2020 at 3:01 pm
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:
February 18, 2020 at 3:50 pm
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.
February 18, 2020 at 3:52 pm
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 :-/
February 18, 2020 at 4:03 pm
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?
February 18, 2020 at 4:27 pm
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.
February 18, 2020 at 4:31 pm
Thanks for that, I'll have a look and see if it offers any improvements.
February 27, 2020 at 3:16 pm
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply