Business Intelligence in SQL Server Denali & Beyond

BI for the Masses (including Excel & DBA Pros) Continues…

Before I begin I would like to thank the Microsoft SSAS development team for all of their help and support to the Microsoft MVP community. Without their relentless answering of my questions and help this post would not be possible. Also, I will be clear on matters that are still to be determined as well.

When PowerPivot was released with Office 14 and SQL Server 2008 R2 we experienced a massive lowering of the entry point for Microsoft BI. With PowerPivot, Microsoft self-service BI had become a reality and even in its first incarnation the product was better than most (arguably all) similar products in the market for personal insights. In addition to PowerPivot's usage for personal insights it was deeply rooted in SSAS architecture which traditionally serves some of the team and corporate workloads.

Speaking for the community we knew that Vertipaq (the PowerPivot powerhouse in-memory engine) would come to power the higher end workloads of BI (namely corporate workloads). What we did not know was:

1.  How Vertipaq would be implemented to power corporate workloads
2.  What the transition strategy would look like?
3.  What workloads would qualify for the new vertipaq corporate solution?
4.  What would qualify for the traditional UDM data model and what would the future of UDM be?

After reading this large post you will know the answers to these questions plus hopefully a bit more. With the recent announcements made at PASS last week we are now about to see the 'BI for the masses’ mission advance yet again with SQL Server 2012 (formerly code-named "Denali").

The Microsoft BI Continuum Today

Before I go into details about the new announcements it helps to review what we call the BI Continuum and how the SQL Server 2008 R2 technologies map to the continuum.

Models: Relational Vs. Multidimensional Vs. Semantic

Before I go any further it will help you if you understand the big differences between the 3 kinds of models. Relational models you are probably all familiar with (this is a key driver for How you model in BISM); relational models are built using concepts such as tables, relationships, rows, and columns. Multidimensional models are built with concepts such as dimensions, cubes, and hierarchies. And last but not least Semantic models are logical abstractions from an underlying physical model that present information from a user’s perspective.

Terminology

A few more prerequisites that are good to cover include:

1. Traditional SSAS models are built with OLAP concepts are referred to as SSAS UDM (Universal Data Model). MDX is the query language used to query and extend these models.
2. The SQL Server Denali BISM models (which I will describe in detail within this post) are referred to as SSAS BISM (Business Intelligence Semantic Model). DAX is the query language used to query and extend these models.
3. Column-oriented in this post’s context refers to how data is stored via memory or disk. As opposed to storing data by rows, data is stores by columns and their unique values. Vertipaq (the engine that powers PowerPivot workbooks & the upcoming SSAS BISM) stores its data using column-orientation which helps immensely with things such as compression. The relational database engine will also begin its usage of column-orientation storage with Project ‘Apollo’ (more to come on Apollo shortly).

PowerPivot in Denali

PowerPivot for Excel will be receiving enhancements. Probably my personal favorite is the support of KPIs and drill through! Beyond KPIs, PowerPivot will gain additional DAX functions and blob (images) support. The blob support should prove useful for supporting the rich Silverlight interfaces that will be found in Project Crescent (see below for more info on Crescent). Excel will continue to be the analyst tool of choice for PowerPivot, BISM, and UDM data models.

SSAS BISM Migration

OK, so PowerPivot for self-service is getting even better but there may be scenarios that cause you the need to upgrade from PowerPivot to BISM. A few of the reasons to upgrade to BISM from PowerPivot include:

Now for the best news regarding PowerPivot to BISM migration, with a single-click you can migrate your PowerPivot relational models to BISM relational model! So it’s just that easy, with one-click you go from having a very efficient self-service solution to a rich Departmental-Corporate BI solution.

BISM in Denali

First off what is BISM? BISM is UDM 2.0, everything UDM (i.e. Universal) was meant to be, will be the overall mission for BISM. BISM will merge the past two distinct worlds of reporting and analytics together with a best-in-class semantic model. Do I think BISM will be perfect in its first incarnation? No. Do I think BISM will be an outstanding v1 semantic model? Yes I do.

So a few more technical details, you might want to read the awesome content I mentioned earlier with specific focus on the BISM details found at http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx . There will be three layers to the BISM as described by TK and even cooler there will be two ‘modes’ of BISM: Vertipaq & Real-time. The Vertipaq mode will store (process) data but will NOT have concepts such as pre-aggregations and indexes. I *believe* the real-time mode will essentially be ROLAP mode pointing to an underlying SQL Server Denali relational database that can use ‘Apollo’ columnar relational indexes. Finally, what’s even cooler about BISM is it will support support both client interfaces: Pivot Tables and Tabular reporting for different UI clients! In fact it will also support 3rd party BI applications.

SSAS UDM Migration

If you started with a BISM model and need the extensive capabilities found in the UDM and you believe performance using traditional MOLAP and/or using ROLAP with ‘Apollo’ (column store relational indexes) will suffice then you might want to migrate to SSAS UDM. A few examples of reasons why you might (final BISM capabilities are to be determined) want to migrate to UDM include:

It is to be determined regarding how manual versus automated the migration will be including possible tools.

UDM in Denali

The SSAS UDM is not without its own set of enhancements in Denali. In addition, after the intense discussions between the community and the SSAS development team I am optimistic that Denali UDM may incur additional features and enhancements. Some of the noted new UDM features include: (UDM IS NOT GOING AWAY ANYTIME SOON)

Migrating UDM to BISM

If your UDM data models do not need the extensive capabilities found in the SSAS UDM and you would like the model to receive a ‘performance boost’ it might make sense to migrate the UDM solution to BISM. It is to be determined regarding how manual versus automated the migration will be including possible tools.

The Microsoft BI Continuum Tomorrow

Now let’s map the upcoming Denali Bi technologies to the BI Continuum.

Other Notable Denali BI Features

In addition to PowerPivot enhancements, the new BISM, and UDM enhancements there are other new BI (or synergic BI) features.

Project 'Crescent'

Crescent is a new set of technologies from the SSRS team that will usher in a new era of ad-hoc reporting and visualization for both PowerPivot and BISM. Crescent is a thin-client experience that leverages the power of Microsoft Silverlight. Support of the UDM as a data source is to be determined and we expect to see an official announcement from the SSAS development team over the coming weeks. Excel will remain the analytical tool of choice for PowerPivot, BISM, and the UDM.A good overview video by the one and only Amir Netz can be found at http://www.youtube.com/watch?v=FfRpfCav9hg .

Project ‘Apollo’: Column-based Query Accelerator

Apollo will be the first instance of taking the knowledge gained from PowerPivot and pushing it down into the relational database engine. You will be able to create a new non-clustered index type called column store. Apollo will be a key relational engine feature to power ROLAP for both BISM (real-time) mode as well as SSAS UDM ROLAP mode.

Project ‘Juneau’: SQL Server Developer Tools

Juneau is the consolidated SQL Server development tools found in Microsoft Visual Studio 2010. Juneau will include support for BIDS project types. So, no more having a separate tool just for BI development! More information on ‘Juneau’ can be found at http://msdn.microsoft.com/en-us/data/tools.aspx

Data Quality Services

Data Quality Services is the result of the Zoomix acquisition in 2008. Data Quality Services will enable knowledge-based data cleansing. If I am perceiving this feature-set correctly it will essentially be centralized data cleansing that ‘gets smarter’ about how it cleans data based upon the data it has cleansed already.

Additional Resources

As I mentioned earlier this post is a result of a phenomenal amount of collaboration between the Microsoft SSAS team and the MVP community. A few of my peers similar blogs can be found:

There are also a few miscellaneous resources you might find of value:

Good for everyone!

Microsoft is working hard on making SQL Server Denali the best release for corporate BI yet. They have a lot of tough decisions to make and I know they will do what’s best regarding some of the items that are to be determined. The enhancements found in SQL Server Denali for BI are good for everyone including: customers, partners, excel pros, BI pros, and DBAs/Developers. For customers, corporate BI solutions will be delivered faster. For partners, the rate of value they can provide will increase. For Excel pros, you can now ‘graduate’ to BISM capabilities if you so choose. For BI pros, I want to list a few points out:

1.  Like the partner reason we can deliver value faster
2.  For those of us who decided to learn DAX early on that investment of time will further proof wise
3.  We will be some of the best overall professionals to help guide customers in their decision to choose SSAS UDM vs. BISM for their unique business requirements

Finally, for DBAs and developers this will now open up corporate BI solutions to you without as much learning overhead!

Last but not least is the fact that data marts and warehouses are still 100 percent needed for corporate solutions. All the same reasons here still apply including centralized data cleansing, corporate agreement on one version of measurements and dimensions, and agreed upon correlation. SQL Server Denali CTP 2 should be the first release to ship that has some of the experience mentioned in this post.

Again, I want to thank both the SSAS development team as well as my SQL/BI MVP peers. If you have any questions or comments please feel free to leave them here at my blog or email me direct at [email protected].