SQL Server 2012 BI: The Good, The Bad, and The Ugly

Recently I implemented a BI solution for a client using SQL Server 2012 and wanted to share some thoughts on that.

Reporting Layer

The Microsoft BI presentation layer includes several reporting and analysis tools including: Reporting Services, Report Builder, Power View, Excel, PowerPivot (for Excel and SharePoint), Excel Services, PerformancePoint Services and Visio Services.

Many of these tools have overlapping capabilities that can easily confuse developers and designers at first glance. For instance for ad-hoc functionalities you can use Report Builder, Excel, PowerPivot or Power View.

Number of this tools are only available on SharePoint Enterprise Edition. So if you don’t already have it and don’t want to spend on this costly licence you will miss many of them such as: Power View, PowerPivot for SharePoint, Excel Services, PerformancePoint Services and Visio Services.

By introducing the Power View, it seems like Microsoft was trying to add more self-service functionality to the stack, but to me this good looking and flashy tool won’t be able to compete with the beloved Excel to get adopted by business users, and it’s certainly not envisioned to be used by IT people either. The other big limitation is that only Tabular databases can be used as the source for Power View reports.

BI Layer

With the new Business Intelligence Semantic Model (BISM), the objective was to have one model for all user experiences: reporting, analytics, and custom applications, in other words “BI for all” based on the same model. Though a big difference is that in SQL Server 2012, Analysis Services is available in two types: Tabular and Multidimensional.

The Tabular model provides the advantage of the table-based approach, DAX queries, and the VertiPaq (xVelocity) which is an in-memory engine. In other words, a server version of PowerPivot without the need of SharePoint or Excel.

The Multidimensional model (cubes, dimensions & hierarchies) is what we had before with UDM, storing aggregated data on disk and using MDX language to query OLAP storage. It doesn’t have major changes since SQL 2008 except for a few fixes and optimizations and improvements.

The problem with that is unlike what Microsoft claimed, there is no such a thing as a unified BI model: BISM models cannot live together on the same SSAS instance and they speak in 2 different languages! Tabular database can be queried with old MDX too, but the point is developers spent so much time to understand and learn MDX (with not much luck!) and now they have to learn a new language and still keep learning the old one too!

Also there are not many tools that can use DAX to query database, currently Reporting Services and Excel 2010 can query the BISM tabular using MDX and PowerPivot and Power View can use DAX, so there is inconsistency in terms of the way you can talk to BISM, however Microsoft claims that this limitation would soon be removed and the developers could use either of the query languages to query data from both multidimensional or tabular types.

As Marco Russo stated in his blog "...there are opportunities in Tabular thanks to the flexibility, but these are very early days and we lack of new client tools able to take advantage of the new model. Power View is just one, but there is space for more."

Do I recommend using Tabular mode? Well, as it’s always the case: it depends. If you are implementing an enterprise BI solution, given the limitations of the Tabular mode I don’t recommend it at all, but if you don’t have a huge data size and you want to build a flexible system fairly quickly, then Tabular is the answer, otherwise use the traditional Mmltidimensional mode.

ETL and data Layer

SSIS had some improvements. Not only it looks nicer and tidier than previous versions, but also there are number of new controls and features in SSIS, such as Change Data Capture (CDC) tasks, scripting improvements and new expressions like LEFT, TOKEN and REPLACENULL, easier troubleshooting & logging, data taps, PowerShell support, etc. I was also very impressed with the data quality components in SSIS and DQS in general. 

Conclusion:

So in overall there are many new BI features and improvements in SQL Server 2012, but what seems to be lacked is a clear and well-defined vision on the BI roadmap. As mentioned, many of their tools are based on Enterprise Edition of SharePoint which is not affordable for small-mid size companies, and some of their tools are also not designed to satisfy enterprise clients. So to me it’s not very clear on where Microsoft is heading to and what type of market they are targeting with these products.



Update: Luke mentioned that Power View is now included as part of Excel 2013, I haven’t got chance to check it yet but am happy that Microsoft is coming to realize that not every company is interested in using SharePoint 2010 Enterprise, so perhaps they need to consider Office clients as the main delivery tool for BI and enrich the functionality of the good old friend Excel in order to have a better market share in BI space.