Printed 2014/04/17 02:23PM

SSAS development quick tip


When creating a new cube in SSAS, you are frequently making changes that cause the whole cube to reprocess when you use the “Deploy” option (such as structure changes or aggregation design changes).  This means a lot of sitting around and waiting for the cube to process if you have a large fact table.  What I usually do to avoid this problem is to go to the Data Source View that the cube is using, right-click the fact table, choose “Replace Table With New Named Query” and modify the resulting SQL SELECT statement to use a “TOP 1000″ or create a WHERE clause that filters by a particular day or month.

This works great, and processing becomes very quick as I build out the cube.  Then when the cube is far enough along that I want to check it out with all the data, I revert back to the unmodified SELECT SQL statement.  A word of warning: a few times I created a new named query and filtered the results, only to forget about the filter and I winded up wasting my time trying to figure out why my cube is not pulling in all the data.  So don’t make the mistake I made!

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.