THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

I’m very happy to announce that my proposal for the Spotlight session I’ve been invited to deliver at PASS 2011 has been accepted!

Temporal Snapshot Fact Table

You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day.

If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.

In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.

The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.

The topic is very interesting and – I bet – very intriguing for many people working in BI and specially with Analysis Services, since it lacks the support of “time intervals” to define the validity period of a fact row. With my SolidQ Italian collegues we’ve been able to find a way to overcome this limitation, allowing the storage of daily snapshots of data with a very high efficency and performance.

In this session I’m going to share everything we discovered with you. It will be really interesting, I can tell you! Probably one of the most advanced – yes simple - usage of SSAS and Many-To-Many relationship you’re going to see.

Published Sunday, May 29, 2011 4:44 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



dugi said:


May 29, 2011 9:52 AM

Nigel Sammy said:

Congrats! Will see you there in October

May 29, 2011 2:06 PM

Steve P said:

Damn. I'm stuck on the wrong continent. Any chance one of your Italian colleagues will be showing us Europeans this trick?

I'm struggling with this very issue at the moment. Though its weekly rather than daily. But we are storing about 200m elements a week, for 26 weeks. My solution works, though I'm unsure of how its going to scale on our full production system. And the many to many revolution paper By Marco Russo has been heavily thumbed here over the last few weeks to get this working. I'm also contending with an issue where these snap shot facts are not at the required granularity so I'm introducing extra granularity using some calculated measures.

May 31, 2011 4:27 AM

Davide Mauri said:

Hi Steve!

We're going to organize a SolidQ tour in Eastern Europe in September, just before the PASS Summit, where we're probably going to deliver that session in a dedicated BI workshop.

EMail me privately for more deatils :)

May 31, 2011 12:05 PM

Davide Mauri said:

Spotlight sessions and pre-conference sessions has been revelaed, and you can read the complete list

June 4, 2011 4:27 AM

Mark Morris said:

Damn, I am desperate for this solution, however, I cannot attend a pass.  Can you share the solution with me possibly?  mmorris at savantec dot com.  I have a similar problem involving account balances for hundreds of millions of customers and the bank requires the snapshot at the daily grain and want to compare balances in pre-defined buckets (ranges).  UNfortunately, a transactional snapshot is doable but obviously not performant, a regular periodic snapshot causes many billions of rows accross (like yours) 10 years of historical data.

June 17, 2011 12:26 AM

Davide Mauri said:

Hi Mark you may be interested in the workshop we'll deliver in EU in September: :)

June 17, 2011 9:39 AM

Gene Furibondo said:


This is awesome.  I had a question about the date intervals.  It looks like in your example, you do not have a date interval spanning year end (like 2014-05-01 - 2015-06-30).  It looks like this would become two records in your fact table.  one for 2014-05-01 - 2015-01-01 and a second for 2015-01-01 - 2015-06-30).  Is that accurate?  Also, I'm toying with the idea of building out my date interval table with all combinations.  Any experience with this?    

June 2, 2016 1:36 PM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement