T
his past month I have been developing a dashboard solution for a Fortune Global 500 company using Microsoft Business Intelligence technologies. I know all the Microsoft buzz is about Power BI and Excel these days, but customers need real-world, professional business intelligence solutions that meet their complex real-world requirements today. In this article, I will share what technologies were used and why to meet common dashboard requirements. Some of the decisions may surprise you and the lessons learned are sure to be of value.
Related: Microsoft Power BI First Public Preview
Unlike the world I used to live in at Microsoft, showcasing all of the latest and greatest, often leading-edge business intelligence technologies, this enterprise customer had older Excel 2007, Excel 2010, SQL Server 2008 and SharePoint 2010 technologies that are quite common within large companies right now. Although they have seen the latest Azure Cloud, Office 2013 Power View enhancements, and upcoming Office 365 Power BI, they will not upgrade to Office 2013 in the next few years. They also refuse to put their data on the Azure Cloud. For an optimal dashboard project front-end user experience, the enterprise customer was open to upgrading SharePoint and SQL Server. They also openly shared that they were not allowed to use any third-party technologies regardless of how much time or money would be saved. It was an interesting engagement that got me thinking about all of the other groups out there struggling with these same issues.
Current Microsoft Business Intelligence Offerings
After reviewing the dashboard project objectives, constraints, specifications, and success measures, the next thing we did was review the Microsoft Business Intelligence offerings to choose appropriate dashboard technologies. Since all cloud technologies were eliminated by organizational standards and directives, these choices are shaded in grey.
The group had been evaluating SQL Server Analysis Services Tabular for a while and thought the dashboard may be the perfect project to pitch a SQL Server 2012 upgrade. They were excited about all they had heard and seen in MVP articles, SQL Server User Group presentations, conferences and Microsoft marketing. They wanted the easier, rapid development and in-memory reporting speed benefits. After reviewing Analysis Services Multidimensional vs. Analysis Services Tabular, the current serial processing of Analysis Services Tabular fact table partitions and a few other limitations was a show stopper since they have a 10+ TB data warehouse with large fact tables. This group also has at least 20 or more Analysis Services databases and was not ready to introduce a new Analysis Services instance or process into larger cross organizational, operational workflows unless the reasons were compelling to do so. Unable to find a public list of all the enhancements made to Multidimensional Analysis Services 2012 to sway the executive sponsorship votes, the underlying dashboard data source was required to be a SQL Server 2008 Analysis Services database. With a data source chosen, we looked at front-end dashboard options.
DAX MD for Power View allows Power View to be used with traditional SQL Server Analysis Services Multidimensional data sources. It was a fantastic release in May 2013, with SQL Server 2012 SP1 Cumulative Update 4. This feature will help many of larger enterprise customers that have existing investments in Analysis Services. Power View is a Microsoft Business Intelligence star offering and a fan favorite. This group absolutely loved the smooth bar chart animations. The sponsor desire was to use Power View if at all possible. However, in this case we were unable to use Power View and DAX MD because the dashboard data source was SQL Server Analysis Services 2008. DAX MD for Power View requires both Analysis Services and Reporting Services to be at version 2012 SP1 CU4+. We considered pitching the SQL Server Analysis Services upgrade to the powers in charge for Power View, but as soon as we cross-validated Power View with the enterprise dashboard requirements it was eliminated.
Common Enterprise Dashboard Requirements
The customer's enterprise dashboard contained:
- custom branding for colors
- specific placement of elements
- cascading filters
- drill to details and other links
- export and print
- save personalized filter settings
- dimensional security
- advanced parameter logic
- conditional color coding of table cells and fonts
- combination charts with multiple data sets at differing grains
- charts with appended columns and tables that had non-roll up, line-by-line logic
It sounds complicated, but actually, many of these are common enterprise level dashboard requirements. Dashboard queries would have to be written in MDX not DAX or T-SQL elevating complexity a bit for combining data sets and consuming parameters. Scenarios like UNIONS require CROSSJOINS or other MDX design patterns. Hide and show tricks are needed to achieve single combination chart views that contain differing grain data points. Although we might have been able to use the fantastic new Power View Pinned Filter and URL parameter features to achieve some personalization—most of the other requirements were impossible in Power View today.
So what about Excel and Excel Services? This group currently builds dashboards in Excel and it would be pretty easy to link that to SQL Server Analysis Services 2008 to automate it, publish it to SharePoint, and call it a day—BUT, there is a stigma about using Excel for dashboards! This group spent a long time rallying to get funding to automate their manual Excel dashboards, add professional filtering, security controls and finally got approval. If they delivered Excel Services dashboards in the end solution, there would be widespread disappointment and political issues. A lot of people forget about the emotional aspects and long history of using Excel for enterprise dashboards. Many groups love it, many groups hate it and some will not allow it at all. Excel is sometimes not perceived as a true enterprise dashboard option today, even if it is capable of fulfilling that role when published to SharePoint. Pure Excel Services by far has the best Mobile Business Intelligence experience in SharePoint 2013 today. Excel Services now is interactive, can be drilled down, sliced/diced, pivot table and pivot chart reports can be changed from a web browser or mobile device browser. Excel Services is also very future Microsoft Business Intelligence Roadmap friendly. Assuming we could have overcome the human emotional side of the equation, the limited Excel Services parameterization in SharePoint 2013 would have quickly eliminated it as a viable option.
Where does that leave us for front-end dashboard options? Visio Services is inappropriate, so only PerformancePoint and SQL Server Reporting Services remain standing. Again, going through the requirements, immediately PerformancePoint is ruled out because of the multiple data set combination charts, custom colors for branding and detailed programmatic control needs. On the other hand, PerformancePoint is the only option that can easily save personalized filter selections and has much nicer looking parameter displays in SharePoint than SQL Server Reporting Services parameters.
Tried and True SQL Server Reporting Services
SQL Server Reporting Services has historically been the go-to for anything complex or requiring customization because of the deeply rich feature set and customization capability that no other Microsoft Business Intelligence front-end tool offers. The downside of using Reporting Services is that it is not the newest, coolest, shiniest tool in the box. It is not animated, and the green rendering circles are getting old after 10 years of seeing them. The MDX parameterized query features, multiple data set combinations with LookUps, functions and many other fine-grained, controllable features are wonderful for complex, real world dashboard development. By far, Reporting Services was the best option for this specific dashboard project in a pure Microsoft world.
One of the beauties of using SharePoint for delivering dashboards is having the ability to combine the best business intelligence front-end tools within PerformancePoint’s parent dashboard containers or using SharePoint Web Parts. These tools in SharePoint allow passing of filter values and parameters via connections in PerformancePoint or SharePoint Web Part filter connections. That is a powerful concept and opens up a world of dashboard integration possibilities. Thus, we landed on using PerformancePoint for the filtering and personalization capabilities along with SQL Server Reporting Services for all the complex user interface charting and table logic requirements.
SharePoint 2013 Business Intelligence Tip
After building out a new SharePoint 2013 Business Intelligence Farm with the latest SQL Server 2012 SP1 CU 4+, dashboard development started. Everything was going well until we hit a PerformancePoint Designer bug in SharePoint 2013 that prevented adding Reporting Services reports. The bug was caused by the August SharePoint 2013 CU. A call into Microsoft Customer Services and Support (CSS) provided insight that this bug would not get resolved anytime soon and a workaround was presented. The workaround is to only develop the cascading PerformancePoint filters in PerformancePoint Designer and the rest of the solution should get pieced together inside a SharePoint page using the Web Part connections features. The workaround is a more time consuming and tedious process to do, but it also allows for more control of dashboard content placement on the SharePoint page.
So, there you have it—building a real world Microsoft enterprise dashboard today in the fall of 2013 still requires some of the same tools that you used 10 years ago. Although the new Power BI Excel, Power View and Power Map solutions are shown all the time for dashboards, only the sophisticated SQL Server Reporting Services and neglected PerformancePoint can actually deliver the most common enterprise dashboard requirements today. SQL Server Reporting Services and PerformancePoint also happen to have some on premise Mobile Business Intelligence capability using browser-based options on your favorite mobile tablet devices.