Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Integration Services - Issues when using the Analysis Services Processing task. It doesnt seen to be processing the dimensions Expand / Collapse
Author
Message
Posted Friday, June 15, 2012 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
Points: 20, Visits: 47
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?
Post #1316600
Posted Friday, June 15, 2012 7:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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
Post #1316605
Posted Friday, June 15, 2012 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
Points: 20, Visits: 47
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.
Post #1316613
Posted Friday, June 15, 2012 7:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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?
Post #1316621
Posted Friday, June 15, 2012 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
Points: 20, Visits: 47
Did you execute in ssis bids?

See the attachement. You need to look at the warnings tab in ssis to get more info.


  Post Attachments 
ssiswarnings.bmp (7 views, 213.35 KB)
Post #1316626
Posted Friday, June 15, 2012 8:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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.......






  Post Attachments 
Error1.JPG (6 views, 53.42 KB)
Post #1316686
Posted Friday, June 15, 2012 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 15, 2012 2:26 AM
Points: 20, Visits: 47
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.
Post #1316699
Posted Friday, June 15, 2012 9:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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
Post #1316735
Posted Friday, June 15, 2012 1:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
Can you send a screenshot of the package and of the configuration of the tasks? Grey out all sensitive data.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1316878
Posted Thursday, June 21, 2012 4:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 6:35 AM
Points: 426, Visits: 639
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?
Post #1319201
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse