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 currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>