Building Real-World Microsoft BI Dashboards Today

RSS

TPowerPivot App screenshothis 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.

Microsoft buisness intelligence options in Fall 2013

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.

Discuss this Blog Entry 8

on Oct 21, 2013

Excellent article Jen, thanks.

on Oct 21, 2013

Also note that we also ran into very difficult to work around PerformancePoint bugs in SharePoint 2013 August CU related parameter string length issues with MDX and a SSAS Cube source. MDX uses very long member names and the reports had quite a few ~8 to 9 MDX parameters. This is a known Microsoft Support issue but currently no known fix.

Bottom line on PerformancePoint with SharePoint 2013 =
it is risky and filled with big bugs. Try to avoid PerformancePoint IF you can.

So that only leaves, old faithful SSRS. I sure hope the professional SharePoint dashboard situation gets much better soon!

Jen

on Oct 22, 2013

One more huge tip! If you do need PerformancePoint to fill the voids today, deploy on SharePoint 2010. All the parameters issues mentioned above work fine in the older version of SharePoint. SharePoint 2013 has big issues with it...

on Oct 27, 2013

Hi Jen.

We build a similar solution in 2010 for a company in Denmark I worked for. We used SSRS to build all the charts and PerformancePoint for scorecards and filters. We then combined it all in PerformancePoint where we could pass selection in scorecards over to the SSRS reports. It worked brilliantly and looked good. We had a custom master page made to brand it specifically.

I very much agree that for complex dashboarding using MS technology, PPS and SSRS are the best tools available.

on Nov 7, 2013

Good article and it helped us a lot.

on Nov 7, 2013

Super article. Just what I needed. Thanks Jen

on Nov 7, 2013

Hi Jen - this was a good read and something I'll share with clients who catch the latest buzz from MS, but still need complex dashboards/scorecards. Also - I wouldn't hold my breath on getting changes/fixes for PPS. I don't think there's much activity on that front - but you probably already know that.

Thanks again - Paul Goldy

on Jul 25, 2014

Hi Jen,

That is a great article and it is very similar to my experience. There are some companies that get very excited (I like excitement) about new possibilities just to later learn about key limitations and abandon the idea.

I feel like your experience is very true for larger organization or those with who have to comply with regulations (Finance or in UK NHS) which limits it to smaller organizations who may struggle to get people who can fully utilize the technologies (Design/Data Quality etc), which further limits it to smaller organizations who can find people who are really into it.

I see a lot of potential but Microsoft really have to put more effort into dedicating themselves to making updates that people are after.... Having PowerView and not being able to put on a chart's X axis year and then month is just silly. Having Excel Pivot Table (or PowerVIew) connected to cube and not being able to do a search for a field you are after makes it really time consuming.... I remember beta of PowerPivot that had search in pivot table... why has Microsoft removed it?

SSRS is old and at one point I abandoned it but after using alternatives tools I came back to it and I hope Microsoft will re-write it in a way to leave good things (design flexibility) and improve bad things (filters, unnecessary refresh of page, and comply with data visualization best practices... Tufte, Few). There are still plenty of people who will not do self-service BI and plenty of companies with reasonable standards and a tool like SSRS is really needed.

Take care
Emil

Please or Register to post comments.

What's SQL Server BI Blog?

Derek Comingore’s, Mark Kromer's, and Jen Underwood's candid look at SQL Server’s Business Intelligence features.

Contributors

Mark Kromer

Mark Kromer has been a technical product manager & solution architect in business intelligence, data warheouse and Big Data world for over 20 years for Microsoft, Oracle and AT&T, currently...

Jen Underwood

Jen Underwood, Founder and Principal Consultant at Impact Analytix, has almost 20 years of experience in data warehousing, business intelligence, and predictive analytics. She is a former Global...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×