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.

To PowerPivot, or not to PowerPivot?

We’ve all seen many presentations about PowerPivot and its great features like in-memory processing which enables Excel to handle millions of records. But the question is when to use PowerPivot as opposed to using a SSAS cube?

Here are some points you need to consider when choosing the tool:

Pros:

 

• PowerPivot is a powerful tool to gather data from different data sources. It can be used to create POCs or demos in a quick (and possibly dirty) way.
• It can be used to answer some ad-hoc questions or to play with data (data analysis).

Cons:

• PowerPivot doesn't support data level security. Whoever gets access to the file can see all data inside it. With SSAS you can implement security at data level.
• Complicated dimension types (like parent-child) are not supported in PowerPivot
• Using PowerPivot workbooks as data source for reports may cause version management issues.
• PowerPivot runs in memory, when you use PowerPivot for all your enterprise data you would need a huge RAM
• Saving PowerPivot files in SharePoint may cause some issues when the size is too large.
• SSAS allows you to fine tune your cube but with PowerPivot you can’t.
• Data can easily be out of control as soon as it is saved on workstation. There also might be huge security risks involved. Even with the SharePoint implementation users have to download data to workstation first, and then publish it to SharePoint.
• PowerPivot for SharePoint requires Enterprise license and SQL Server 2008R2 EE license. The cost (which is very high) may affect the SharePoint implementations of PowerPivot. By using PowerPivot without SharePoint (Excel add-in which is free) we may end up having even bigger Excel hell!
• Source data is not always in high quality. We may need to validate and do data cleansing and also more often we need to apply some business rules which can be handled better in SSAS or at ETL level.
• Working with data from different data sources may require high technical knowledge. Some data isn't that easy to join. A key isn't always available that lets you join data from different sources. It will make it hard for a non-technical person to work with the tool without any IT support.

  

From what I've seen so far, PowerPivot is not a replacement for Analysis Services, but it certainly has a role for tactical solutions. It also can be used by power users to quickly analyse data, or perhaps to build prototypes of larger scale SSAS implementations.

So I think PowerPivot is great for certain scenarios, but for Enterprise level data warehouse implementation SSAS is the way to go.

PowerPivot & Visio Services for SharePoint 2010

Gemini has got a new name: PowerPivot

Microsoft revealed that it officially branded Gemini as Microsoft PowerPivot for Excel 2010.

“With PowerPivot, users and IT departments can: Integrate massive amounts of data on the desktop from virtually any source; Perform lightning-fast calculations and analysis on large data volumes; Share and collaborate on user generated analysis through integration with SharePoint; Monitor user generated applications,” the SQL Server team representative noted.

Couple of points to consider:

• PowerPivot for Excel only works with Excel 2010.
• PowerPivot for SharePoint will require SQL Server 2008 R2 and SharePoint 2010.

For more information about "Managed Self Service BI" and PowerPivot see:

http://www.powerpivot.com

http://www.microsoft.com/sqlserver/2008/en/us/powerpivot.aspx

Visio Services: a great visualization tool which allows real time data to be shared on a SharePoint site for those without the Visio client.

Diagrams can connect to an OBDC source such as a SharePoint list, Excel spreadsheet, or SQL Server database. However it can not be used with SSAS or PowerPivot (which is a bit disappointing for BI developers). That's a great feature and I think we may be able to build fancy looking BI dashboards using Visio in near future.