SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Hi all,

I have an SSAS cube with 4 measure groups and some 15 dimension tables, of which i created 2 partitions for one of the measure groups based on the year(2009,2010),initially i deployed and processed the cube and got correct results in the cube as both partitions gets processed there.Later i created a package in SSIS 2005 to process the cube, and in that i have selected only the current year partition(as last year partition has huge data i ommitted that from selection),all dimension tables,the remaining three measure groups and executed the task.But this time i got data only for the current partition,last year's data is gone from the cube.So how to get both partitions data even though we select only one partition.

Thanks
Sandeep

Hi Sandeep,

Just to confirm - I am assuming that you are using SSIS "Analysis Services Processing Task". What objects do you include in the processing list? Are you including dimensions? If so what are Process option for dimensions? "Process Full" or "Process Update"? If you do "Process Full" for dimensions then all data for related cubes will be invalidated.

When you selected to process partitions, in the object list do you see just correct list of partitions processed? Do you see type "Partition" and Process option "Process Full"?

If you have process dimensions and process partitions in the same list, could you please separate list, then try to do process dimensions first and see if there is any impact on your data. If after process partitions data looks OK, execute SSIS task to process one partition and see if data is OK.

Try that and report back if you still have any problems.

Regards,

Vidas,

I am delightful with your valuable reply on this, so far i am executing the package by putting the dimension tables in one task(Analysis Services Processing Task), and the Partitions in another task.But every time i used to put the processing option for all dimension tables as 'Process Full'.And i see the correct list of partitions(selected ones only) along with other measure groups in the Objects list of the task.Partitions have 'Process Full' option only. Now i will execute the package in the way you mentioned above for a change.

Thanks,
Sandeep

Thanks a lot Vidas.....

Yes, i changed the processing type for dimensions to 'Process Update' and just included one partition out of two, and executed the tasks in the SSIS package, now i am getting correct results of both partitions even though i executed only the current Year Partition.

Thanks,
Sandeep

Vidas,

So far it seems good to use 'Process Update' for the dimensions, but Daily some records gets added to my dimension tables, in that case is it correct to use the processing option 'Process Update' instead of 'Process Full' to process these dimensions, and sometimes(not daily) there will be changes to the existing records in the dimension tables, so in that case we anyway have to put 'Process Full' option only, for these dimensions.

Thanks,
Sandeep

Sandeep - "ProcessUpdate" will add new dimension members and change attribute values for existing members. Name "ProcessUpdate" is a bit misleading as it inserts new and updates existing at the same time.

When I do my cube reprocessings I do "Process Full" for dimensions just when I want to completely re-load database in the cubes. After that I allways use "ProcessUpdate" for dimensions.

Regards,

Vidas Matelis

Vidas,

Our Cube is usually scheduled for full process twice a week, and everday we will do Process Update on all dimensions and Process full on current Month's Partition which makes the cube to process only this month's data which seems fine so far, but is it good to opt for 'Process Update' on the dimensions for 5days a week, and 'Process Full' on all dimensions for the remaining 2days in the same week.

Thanks
Sandeep

Sandeep,

If you do "Process Update", then doing from time to time "Process Full" for all dimensions and then partitions is a good practice. This is because "Process update" kind of defragments data. Question is - how often you need to do "Process Full". It really depends on your specific case, but if you have time window to do "Process Full" once a week, then that is good option for you.

Important Note. When you do "Process Update", if there are any dimension attribute changes, then !!!!! all !!!!! partition aggregations and indexes might be dropped. So make sure that your partition ProcessMode is set to "LazyAggregations". This way indexes and aggregations will be rebuild automatically in the background. Or another option is when doing "Process Update" on dimension choose option to ProcessAffectedObjects that will updated affected indexes/aggregations.
To see partition processMode go into SSMS and select partition, then chooose Properties.

Hello Vidas,

I got a new issue in my cube, when i put a backup of my cube from Management Studio 2005, the backed up file shows an Archive type, and data seems to be truncated in that file, could you please tell me the reason for this.

Thanks
Sandeep