Why am I Processing Index Separately?

Someone asked me why am I processing index separately in my earlier post. Why didn't I do it inside the first measure group foreach loop.

Yes, it would have worked.

There is a reason for splitting it up, is because that we have a measure group with billions of rows. Building aggregations on that measure group will take some time. I do not want to hold rest of the measure group partition processing.

Therefore I process the cubes first and make them available to users as quickly as possible and then build the aggregations. I have informed the users that some of the reports might not run as fast as you would expect straight after refreshing the data, until the aggregations are rebuilt.

Thanks
Sutha

Processing Dimensions, Partions and Re-Building Aggregations

This package is very similar what Jamie has already posted. I have written a simple package which process all dimensions and process 3 specific partitions (current fiscal period, current fiscal period -1 and current fiscal period -2).

Once I have processed all the dimensions and 3 partitions in selected measure groups, I rebuild aggregations (using Process Index) for each measure group.



I have used XMLA and Execute DDL statement tasks to achieve this.

You can download the sample package from here. Please note that I have set DelayValidation = TRUE, to avoid any errors.

Thanks
Sutha

Gotcha When You Process Update Dimensions

We can process cube related members via DDL statements. Jamie posted a simple package which process all the dimensions and measure groups.

Jamie followed it with a question in the forum and did a post. I got interested in that and followed the thread and got it confirmed the following:

If your do process update on dimensions you are likely to lose aggregation on the cube.

You have to re-build aggregations using "Process Index" option.

I will post a simple package later on today which process all dimensions, specific partitions in a given measure group and finally it will build the aggregations for the cube.

Thanks
Sutha

64 Bit Performance Issue Resolved

With the hotfix from Microsoft we have resolved this issue. It was a bug, which was fixed with hotfix. If anyone has performance issue contact Microsoft and get hotfix.

Thanks
Sutha

Continuing on 64 bit SSIS issues - Hopefully Resolved

Using the example I have posted on my previous post, Microsoft have reproduced the issue I have had on a 64 bit platform.

Infact some of the issues were identified before. Microsoft have been working on the solution for last couple of months (service pack 2). Microsoft released a hot fix (which is not available for public yet, I dont know when it will be available either) for us to test.

After initial testing the hotfix seems to have resolved the issue on a 64 bit box. I will be doing an extensive testing on Monday and will post my findings.

Thanks
Sutha

Sample Project / Package to Re-Pro 64 Bit Issue

Microsoft said, they couldn't reproduce the problem from Scratch, but I have. I have sent it to MS as well. I have attached the whole project (with all the packages) and configuration files.

Master Package
[1] Creates a simple table in tempdb database if it doesn't exist else it will truncate the table.
[2] It will populate 25 records into that table.
[3] SQL will get those 25 records and assign them to a Object variable.
[4] Foreach Loop (ADO) and set the values of each row to couple of variables. Inside that foreach I have an execute package task, which will call another package (in this case Pkg_Sample25.dtsx)

I have got 52 packages in this project. All the packages except Pkg_Master is exactly same. They all have one single Execute SQL Task, which has a SQL statement as "SELECT 1".

Every package has connections for all 51 packages and 55 flat files. All these are configured in the configuration file (64BitConfig.dtsConfig) which is located in a directory called "C:\RePro\Config". Please note you would need to change servername in the configuration file before you execute the package. You will need to change flat file directory and packages directory if you put them anywhere other than "C:\RePro".

If I run a package called "Pkg_Master" on my laptop (32 bit), it executes under 50 seconds. If I execute same package on a 64bit machine it executes just under 7 minutes (6min 42 Secs).

If anyone wish to download the sample project and try it on there 64 bit box, you can do it from here.

Thanks
Sutha