Thursday, May 27, 2010

Simple Ways to Increase Cube Performance

There are Few Simple Steps to Increase the Performance of a Cube:
To Decrease the Cube Processing Time :
1>The Default Member of the Cube (usually First Member of the First Measure Group)in the Cube has Large Data Spanning to Millions of Records and Your Client is Using Excel ,Reporting Services as Client Tool then Set the Null or 0 as per Experts as Default Member and Once you Pull Your Dimension Member it will not be Stuck for a Long time ! :)
2>Set the Attribute Hierchy Enabled,Is Aggregatable to Fasle as it will lead to Unnecassry Calculation Build up as it will never be used.
For ex:If you have Employee Address as Property ..and u have a report Showing to the CEO he will Never analyse by Email ID...And if you set this to true You are Tried to Set a All Level for a Default Measure...Waste of Processing Time...So Be careful and Be Smart on Chosing this Option !
To Increase Query Performance of the Cube:
1>Create Partitions on Your Fact Table...Maro Goli If you have Fact Table less then 10 million Rows if its more try making Partitions.
2>Fine Tune Your MDX Quieries inside Your Cube.
3>Design Aggregations for Each Partition in Your Cube(dont Forget Never Design Aggregations to Leaf Members as your Cube Processing Time Will Increase Rapidly)
4>If You have Reports Which Show the Same Data (Like a Pie,Bar chart or some Tablix) where Quieries are Written in your Report and Reporting Services will in turn Ask your Analysis Query Engine then do Use User based Optimization in Your Cube by logging Your Quieries !!!!
5>Have Your Members Unique Name to be displayed Which will optimise your Dimensions.

I Will Be coming Back With More Updates !....Keep Waiting :)

No comments:

Post a Comment