Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

manowar — Sun, 29 May 2011 13:44:59 GMT

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.

Is Microsoft serious about BI?

sqlbi — Sat, 26 May 2007 11:17:17 GMT

The short answer is yes. The long answer is that Microsoft should still improve in many ways. I thought about this many times before writing this post, because I want to be constructive and I hope that my words will not be misunderstood.

My consideration start from the lack of a BI client. If Microsoft want BI for the masses, a low-cost end-user front-end should be available to navigate data contained in Olap cubes. Excel 2007 is a great product to achieve this goal, but its limit is that it is not "only" a BI client, it is a very big product with a lot of dependencies. This is the only explanation for the situation created by the Service Pack 2 of Analysis Services 2005. This SP breaks a function that worked well with Service Pack 1 and Excel 2007 (see the issue here, that has many votes and comments of other people in the same situation). Well, bugs happen and it is not too strange to me that a SP can break something. It would be better to catch these kind of issues during the beta process... however, it happens.

Real issues started when I tried to fix the bug.

SQL Server 2005 SP2 cannot be uninstalled. A complete reinstallation of SQL Server 2005 is necessary to restore a working state (or at least Analysis Services 2005 - but do you trust an AS2005 SP1 with SQL2005 SP2? Some of my customers didn't like to be a beta tester).
I opened an incident to Microsoft Product Support Services. I opened the incident as a Microsoft Partner to simplify the life to my customer, who wouldn't be able to handle a technical conversation with the PSS. Unfortunately, the PSS closed the case answering that the observed behavior is "by design". David Gainer (partially) explained the change in this post.
I wasn't so happy of this answer and I asked to the PSS to give a solution to the customer - even as a workaround just to restore the previous behavior. The answer was that asking for an Excel hotfix (because they said that it depends from Excel) required a Premiere contract and I hadn't this level of contract. But my customer HAS a Premiere contract and I opened the same incident through him.
After more than 2 months from the start of this story, I still haven't received a definitive answer from Microsoft.

This apparent little feature is blocking the upgrade to SP2 on many sites. Any cube that uses Time Intelligence Wizard does not expose a fully navigable cube to Excel 2007. Not to mention all other models built with techniques similar to those used by Time Intelligence Wizard (making use of calculated members on non-measure dimensions).

I completely understand the (technical) impact of changes on a product big and widely used like Excel 2007. At the same time, I completely understand the disappoint of one of my customers that don't have a premiere contract and receive a "it's by design" answer to their complaints (it has to be seen if the premiere customers will receive a different answer...).

I see an issue that is more about customer relationship than technical. If you publicize the "BI for the masses" and "democratize BI" mantras but then you split your customers between first and second class, are you still reliable?

A BI solution, today, must be supported by someone. It is not a "install, click, click, click and go" product, like Word or Excel itself. You need the design and implementation of ETL packages and cubes. These solution are built by Microsoft Partners or other System Integrators. They are the most important contributors to Microsoft success in BI, because without them you would have empty boxes without data. Imagine their feelings when they are unable to solve these issues. At this point there are more chances that partners will adopt products like Dundas Olap Services or Report Portal to deliver data to their customer (especially in the mid-market), for a lot of reasons (customer service, control of user actions and so on). However, I like the improvements made by Excel 2007 in the PivotTable feature - but they are a "little" feature of a big product, thus we are at risk of further design changes in the future just for a Service Pack (of another product)?

The other part of the story is (should be) well known. There are too many different ways to create a KPIs. There is ProClarity that will be included in Performance Point Server, removing the option to get a desktop product alternative to Excel 2007 (maybe I'm in error for this - correct me if you have better information). There is no more PivotTable Web Component included in Office 2007.

A customer who want to start a simple BI project with a tight budget will need to rely only on Excel 2007 and Reporting Services (or he will look at third-party tools). I hope a Small Business Edition of Performance Point Server will be available at the time the product will be released: this could be a good move to introduce the complete Microsoft BI pipeline even in companies who are new to BI and are probably small-medium sized: they will invest more only if they can see a ROI. If the entry level is too high, they see an higher risk to play the game and prefer to wait for other solutions. Today these companies still produces tons of Reporting Services (or Crystal Report) static reports, obtained querying the relational database (often the OLTP one).

My conclusion: Microsoft is technologically serious about BI. But Microsoft could have a better BI technology adoption today just with simple support, customer-care and licensing decisions.

Search results matching tags 'SSAS', 'Integration Services', and 'SSIS'

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

Is Microsoft serious about BI?