June 15, 2012 at 3:54 am
Oh whoops,
I didnt realise I was chatting to the same person about the issue on both forumns
Ill come off this one then
June 15, 2012 at 7:08 am
When you do a 'Process FULL' on a dimension; your cube structure becomes invalidated and SSAS needs to drop and recreate the whole cube (it says all objects depending on the dimension but in reality the whole cube is processed). Thus your cube is offline and unavailable for the duration of the process.
With Process 'Update', your cubes remain online and available for query always. This lets you perform data refreshes on your production cube during office hours and you don't get business users screaming at you as to why the cube is offline and your scheduled reports do not fail or return empty data sets.
June 15, 2012 at 7:10 am
|Thats what I thought but process update wasnt working.
I was told its best to go for a full Process, its still not working through but neither is process update
June 15, 2012 at 7:16 am
Did you change the data structure or dimension structure before getting the processing errors?
Can you recover your cube to the last time it processed successfully? If you can recover the cube/dimension to that last state and also recover the data back to that state then by process of elimination you can check whether the problem is with the dimension structure or the data.
Personally i've been using SSIS to process my cubes (update and full) for over 4 years and i've not had any major hang-ups that i ascribe to SSIS specifically. if there are any errors (and there are) it is mostly due to the new data that is being processed.
June 15, 2012 at 7:19 am
This is the thing,
It errors in IS.
I then go into Analysis Services and it processes fine (On Full and on Updates)
I then go back to the IS package and it works
Then the over night one failes again (Not the dimensions, just the cubes)
I go back into Analysis Services.....and there is the constant circle......
Its driving me up the wall at the moment. Ive had to tell my manager I can only update the cube manually. doest look good but I am at a loss
Debbie
June 15, 2012 at 7:27 am
I checked the error message you posted above and that is not very helpful. I've observed that sometimes the 'warnings' that preceed the 'errors' in the 'error list' tab in IS gives the pointer to the cause of the error.
So can you run the task in SSIS bids and if it error outs, copy the 'warnings' produced now instead of the errors?
June 15, 2012 at 7:30 am
Ill see if I can do that,
Those errors come from a report that the bda team have created for me during the nightly import.
Are they not grabbing the right thing for the report?
When Its failed for me running the IS package Ive bever seen any other types of messages but the ones given, so Im obviously not doing something that I could be doing.
I have no clue what though
June 15, 2012 at 7:35 am
The warnings drive the errors. SQL jobs will only capture the errors produced so the dba's will not be any help here. So you need to debug the pacakge itself by executing in ssis bids and look at which warnings are being flagged.
June 15, 2012 at 7:39 am
akin.akinwumi (6/15/2012)
The warnings drive the errors. SQL jobs will only capture the errors produced so the dba's will not be any help here. So you need to debug the pacakge itself by executing in ssis bids and look at which warnings are being flagged.
OK so I execute it and it fails. Then I look at the execution results?
basically I dont now how to look at the warnings and not the errors?
June 15, 2012 at 7:44 am
Did you execute in ssis bids?
See the attachement. You need to look at the warnings tab in ssis to get more info.
June 15, 2012 at 8:28 am
Ah a little more of idea than I did have,
Right I have processed in IS and the cubes have failed.
Ive gone to the bottom of the screen and there are no errors warnings or messages .
But there are some warnings in Progress
[Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'fact_SEN_Fact', Column: 'DW_Prov_ID', Value: '368613'. The attribute is 'DW Prov Child ID'.
[Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute DW Prov Child ID of Dimension: Provision from Database: CAYA_DataWarehouse_Dev, Cube: SEN, Measure Group: COP Stage Pupil Business Measures, Partition: SEN Fact, Record: 146611.
[Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'fact_SEN_Workflow_Steps', Column: 'DW_Prov_ID', Value: '368606'. The attribute is 'DW Prov Child ID'.
[Analysis Services Execute DDL Task] Warning: Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute DW Prov Child ID of Dimension: Provision from Database: CAYA_DataWarehouse_Dev, Cube: SEN Workflow Steps, Measure Group: SEN Workflow Steps, Partition: SEN Workflow Steps, Record: 796998.
Which does make me think, something is wrong that can be dealt with. A data issue....
So I go into Analysis Services and process the dimensions and its fine
I then run the cubes on full and..... it processes successfully,
Which leads me right back to the start of the problem again. How can I trouble shoot this when this is happening.
I now go back and run again in IS and re run and its fine
So I go back to quare 1 again.
Tonight it will fail. Ill run it in IS and get warnings.
Ill process in Analysis Services and it will work
I go back to IS and run it and its fine.......
:crying:
June 15, 2012 at 8:43 am
Ahh... we are getting there.
You did say that after running a dimension process folowed by a full process in Analysis services, you then reran the SSIS task successfully?
And from the new warning/error messages posted; you are missing attribute keys. Thats probably because your fact table contains dimension keys that do not exist in the dimension at the time you process the measures.
Or are you processing the dimension & cube in the same task? Do not do this.
Add a Process Analysis task to process the 'Provision' dimension BEFORE the task that processes the ''fact_SEN_Fact'' measures. Do this in different Processing tasks using a 'Success' constraint from the dimension process to the measures process.
Also ensure that your fact tables are not being modified once you've started the dimension processing. If you have 'early arriving facts' then this problem will continue. you can check if you have 'early arriving facts by performing a lookup between your 'Provision' dimension table and ''fact_SEN_Fact'' table.
That'll come at a later stage. Try seperating your processing tasks and creating a process dimension before process measure task.
Let me know how you get on. 🙂
June 15, 2012 at 9:28 am
akin.akinwumi (6/15/2012)
Ahh... we are getting there.You did say that after running a dimension process folowed by a full process in Analysis services, you then reran the SSIS task successfully?
I run the dimensions on Full in IS.
Then in the next task the cubes on full in IS which failed
Then I went to SSIS and everything was fine. So its like when Im processing the dimensions and then the cubes, the dimensions arent actually processing or something.
And from the new warning/error messages posted; you are missing attribute keys. Thats probably because your fact table contains dimension keys that do not exist in the dimension at the time you process the measures.
Yes, but it shouldnt because I had processed the dimensions
Or are you processing the dimension & cube in the same task? Do not do this.
Im not processing them in ther same task no. Dimensions go first.
Add a Process Analysis task to process the 'Provision' dimension BEFORE the task that processes the ''fact_SEN_Fact'' measures. Do this in different Processing tasks using a 'Success' constraint from the dimension process to the measures process.
This is what i have already
Also ensure that your fact tables are not being modified once you've started the dimension processing. If you have 'early arriving facts' then this problem will continue. you can check if you have 'early arriving facts by performing a lookup between your 'Provision' dimension table and ''fact_SEN_Fact'' table.
The fact table is definitely not being processed while this is happening
June 15, 2012 at 1:57 pm
Can you send a screenshot of the package and of the configuration of the tasks? Grey out all sensitive data.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 21, 2012 at 4:21 am
Koen Verbeeck (6/15/2012)
Can you send a screenshot of the package and of the configuration of the tasks? Grey out all sensitive data.
I have 2 threads open on this site and the Microsoft forums so I thought I had better update them both
Ive been pulled over to something else this week but I still have the exact same problem
The Analysis Services section of the IS package fails every night, I have to run it manaully on Full which is fine.
My user tried to open a report this morning and got the error
An error occured during local report processing
Query execution failed for data set......
The Sen cube either does not exist or has not been processed
When I have finished what Im doing at the moment I can get cracking again and try and get it sorted but Im worried that its going to be beyond my expertise which is ridiculous sicne it should be easy to set up.
Again Im not sure what you mean by screenshotting the package and task configuration?
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply