Kasper de Jonge PowerPivot Blog

Today I heard that the Kimball Group  recently released the 2^nd edition of their book, The Microsoft Data Warehouse Toolkit. This is the must read for all datawarehouse developers /architects. The cool thing is that is now featuring SQL Server 2008 R2 !

For those of you who don’t posses the first edition or don’t know it by heart here is what to book is about:

Best practices and invaluable advice from world-renowned data warehouse expertsIn this book, leading data warehouse experts from the Kimball Group share best practices for using the upcoming “Business Intelligence release” of SQL Server, referred to as SQL Server 2008 R2. In this new edition, the authors explain how SQL Server 2008 R2 provides a collection of powerful new tools that extend the power of its BI toolset to Excel and SharePoint users and they show how to use SQL Server to build a successful data warehouse that supports the business intelligence requirements that are common to most organizations. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, as well as Microsoft Office, the authors walk you through a full project lifecycle, including design, development, deployment and maintenance.

• Features more than 50 percent new and revised material that covers the rich new feature set of the SQL Server 2008 R2 release, as well as the Office 2010 release
• Includes brand new content that focuses on PowerPivot for Excel and SharePoint, Master Data Services, and discusses updated capabilities of SQL Server Analysis, Integration, and Reporting Services
• Shares detailed case examples that clearly illustrate how to best apply the techniques described in the book
• The accompanying Web site contains all code samples as well as the sample database used throughout the case studies

The Microsoft Data Warehouse Toolkit, Second Edition provides you with the knowledge of how and when to use BI tools such as Analysis Services and Integration Services to accomplish your most essential data warehousing tasks.

go go preorder ! I did it as well

I had a question about how to do Slowly changing dimensions in PowerPivot yesterday on my ask a questions page and decided to share two excellent solutions. First a quick refresher on what actually is a slowly changing dimension ? It probably is something we all came across one time or another but didn’t recognize it as such. Wikipedia gives a pretty good description:

Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information. Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

For example, you may have a dimension in your database that tracks the sales records of your company’s salespeople. Creating sales reports seems simple enough, until a salesperson is transferred from one regional office to another. How do you record such a change in your sales dimension?

You could sum or average the sales by salesperson, but if you use that to compare the performance of salesmen, that might give misleading information. If the salesperson that was transferred used to work in a hot market where sales were easy, and now works in a market where sales are infrequent, her totals will look much stronger than the other salespeople in her new region, even if they are just as good. Or you could create a second salesperson record and treat the transferred person as a new sales person, but that creates problems also.

There is more to it like different types and such which you can read all about in PowerPivot. I could make and example myself but there are two sources that already did it for me, one is Marco Russo and Alberto Ferrari’s book  PowerPivot for Excel 2010: Give Your Data Meaning (chapter 7) or see this other solution on this blog post: http://bennyaustin.wordpress.com/2010/08/08/powerpivot-dax-expression-for-type-2-scd-lookup/

So as you can see it is possible using PowerPivot, but you need a proficient amount of knowledge of DAX to pull it off.

SQLCAT has created a great guid on how to  Assigning surrogate keys to early arriving facts using Integration Services, in data warehouses, it is quite common that fact records arrive with a source system key that has not yet been loaded in the dimension tables. This phenomena is known as “late arriving dimensions” or “early arriving facts” in Kimball terminology.

Read the entire article here:  http://sqlcat.com/msdnmirror/archive/2009/05/13/assigning-surrogate-keys-to-early-arriving-facts-using-integration-services.aspx

• Per terabyte pricing
• Linear and predictable performance
• 1.6-32 terabytes capacity
• A very competitive $/terabyte offering starting at around $13K (32 TB capacity) and ending at $25K (4 TB capacity)

Read more at Peter Koller:
http://peterkol.spaces.live.com/Blog/cns!68755AEAC31F9A6C!1022.entry