SQL Centre of Excellence

Ok, now that we have scaled the data warehouse (AdventureWorksDW) to about 120 million rows (see http://blogs.prodata.ie/post/Scaling-AdventureWorksDW-for-BI-Testing-(1-of-2).aspx) we now need to modify the Analysis Services project to actually use out new “big” tables for products and Internet Sales.

We also need to fix up a few issues that break with scale in the default AdventureWorks cube:

  • The products Dimension will break the 2GB string store limit as it has a photo in it (horrible idea to put blobs into Analysis Services!)
  • The Internet Sales has an Fact Dimension with the same grain as the base fact. This isn’t going to scale too well so I simply delete the “Internet Sales Reason” and related components.
  • The default Internet Sales is partitioned by year. I have switched it to be partitioned by Month and also added another "Measure Group which is internet sales with only a single partition.
  • 95% of cube we work with have some sort of date intelligence, so I have shamefully borrowed and added into Adventure Works Marco Russo’s “DateTool” available here http://www.sqlbi.com/tools/datetool-dimension/

To save you doing this manually I have attached a zip file of the final solution. Note the cube processing time is now around 30 min’s or so on a laptop. So don’t go processing cubes in a demo!

download:

AdventureWorksDW

blog comments powered by Disqus

Page List

Page List