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

Adam Machanic posted a great article on making the AdventureWorks OLTP database bigger
http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx

What about those of us working on testing larger scale data warehousing or Analysis Services project. The current AdventureWorksDW size is just not sufficient for most interesting events like load testing.The main problem is that the main fact tables just don’t have enough volume when looking at performance or scalability.

In this post I’ll show you how to blow up adventure works to 120 million rows for Internet Sales, that’s about 17GB, and the products dimension from 600 rows to 1.2 million rows (10GB). This can be used to create about an 8GB cube. Ok, not “massive”, many of our customers have 100GB+ cubes and SQLCAT play around with 5+TB cubes, but large enough to use for testing and demos.

image

 

The next post will show how to then modify the AdventureWork Analysis Services Project to account for these larger tables.

Attached is a simple stored proc called “[dbo].[usp_make_big] “that you can add into AdventureWorksDW to scale create larger tables.

usp_make_big.sql

SQL 2012 RTM Big news

by Bob Duffy 6. March 2012 12:04

Ok, more announcements within the last hour or so than you can shake a stick at #sql2012

  • SQL 2012 has released to manufacturing (RTM). The evaluation is not quite available yet, but maybe within the next 8 hours or so the evaluation edition will be here
  • The SQL 2012 Virtual Launch event is tomorrow with lots of great live web casts. I’ve registered and the link is here
  • SQL Server Data Tools (SSDT) has been launched as well. The link to download is here

Just in time for me to rebuild our demo and BI environments  with the new bits Winking smile

Page List

Page List