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!

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Quick Tip, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

One Response to SSAS development quick tip