Making Microsoft Power BI terrific in an enterprise

By Chris du Toit, Chief Technology Officer at Stellar

Microsoft Power BI is typically sold as a tool which empowers end users to create self-service analytical models. These models can then be self-published to the Power BI Service where other users can create visualisations, reports and dashboards. Power BI even solves the “Cloud BI data refresh problem” with its personal and enterprise data gateways.

It’s almost impossible not to get excited about Power BI. In fact, Stellar has deployed solutions where it is the only presentation tool, and the customers are having a terrific experience.

So, what’s the downside to using Power BI?

Power BI is so user-friendly that enterprises may find they soon have an enormous number of Power BI datasets, reports and dashboards hosted in the cloud – and this raises a few concerns about how Power BI is being sold or used:

• Anyone can publish anything
  Power BI does not distinguish between those who consume and those who publish data. It is entirely possible for the Excel spread-mart issue we had for the past 20 years to continue in the cloud. This is essentially not that far removed from hosting a whole bunch of Excel files on SharePoint and calling it a self-service BI solution. One of Stellar’s customer recently remarked: “How’s this any different than the Access database nightmare we use to have?”.
• No clear path to making models scale
  When you create a Power BI model by importing and modelling data, you might be unaware of certain caveats. Models containing all history will take longer and longer to load, and even longer to publish. Of course it is possible to place a sliding-window filter on your import queries and run the Enterprise Gateway to assist with scheduled refreshes, but isn’t access to historical data one of the reasons why BI solutions exist?
• Redundant effort and point-to-point solutions
  Power BI demonstrators are happy to show how end users can access all kinds of on-premises databases, mash up data, create extravagant models, or publish and share data. It is even possible to provide cloud connectivity back to on-premises sources with the Enterprise Data Gateway. Now imagine hundreds of users doing this, and the inefficiencies and risk it introduces. It is hard to think of an organisation where such proliferated access to databases is considered routine.
• The Power BI freemium model
  Power BI Desktop is free, without any limitations. The Power BI Service, however, is only “free” to load datasets, create dashboards and share dashboards with colleagues. (This is changing on 1 June 2017 with the introduction of the Power BI Premium tier.) It stops being free as soon as you want to connect back to on-premises data sources, apply row-level security or manage data in workspaces instead of user logons. (The recent Power BI Premium announcement re-iterated this by stating that Power BI Free is “intended for personal use”). These are all Pro features, which sound affordable at US$10 per user per month, but as soon as you need Pro functionality (and you will), then all your Power BI users need to be licensed as Pro users. This could be tough when IT budgets are getting tighter and tighter. Trying to manage a mix of free and Pro users would be very difficult and create a lot of unnecessary effort.

What can you do?

1. 1. Power BI over Analysis Services is amazing!
      Power BI shines when the data is ready and accessible for end users. If your organisation has already invested in exposing business information through SQL Server Analysis Services models, make this data accessible through the Power BI Enterprise Gateway.

      Once a cube is exposed through the gateway, users can access all the information through the Power BI Service, enabling row-level security, active directory authentication and access to essentially limitless data sizes.

      All this without having to worry about refreshing data, figuring out who’s allowed to see the data and having to model the data in the first place. It provides the right level of data governance while giving users freedom to access and explore the data.

   2. You don’t have to build a data warehouse
      If you haven’t invested in SSAS, don’t fret, you can ramp-up very quickly using Analysis Services Tabular Models. There’s no need to invest in a multi-month data warehouse project. With the right mix of skills, technology and subject expertise the first iteration of a tabular model can be delivered in a few days.

      Stellar did exactly this when it used SSIS and SQL Server to bring several multi-million-row transactional datasets from seven locations into a single Tabular Model. The customer now has reliable, up-to-date information about their operations, and users are chomping at the bit to gain access and insights using Power BI.

      Using Tabular Models is not that different from using Power BI. In fact, they both use the same underlying VertiPaq engine and use the DAX language for querying information. Instead of using Power BI Desktop, you will be using SQL Server Data Tools in Visual Studio.

   3. You can still enable Power BI champions
      Many fall in love with the power and freedom that Power BI Desktop provides. The ability to gather, integrate and model your data is very powerful and is certainly something you wouldn’t want to take away from users. But how do you make this work without creating a governance nightmare?

      SQL Server 2016 makes it possible to transition Power BI models to full-fledged tabular models which can be loaded on any schedule, can be altered for incremental loading, can hold a lot more historical data, and do not need to be uploaded to the Power BI Service. Once the Tabular Model is created, it is simply a matter of using the Enterprise Data Gateway to connect to the on-premises Tabular Model. If the Tabular Model needs to change, then there are a few workflow options that can be taken:

      1. Make the change in the .pbix file and transition to SQL Server 2016 Tabular Model again
      2. Make the change directly in the Tabular Model

      This approach combines the ease of Power BI and the power of SQL Server 2016. The benefits include:

      • Data is kept securely on-premises
      • Data models are centralised in SQL Server 2016
      • Power BI Desktop can now be used as a legitimate reporting tool using live connections and sharing of .pbix files
      • Proper version control can be applied
      • Other end user tools can be used
      • Data discovery and understanding can be made easier with Azure Data Catalogue

      Note: SQL Server Analysis Services 2017 will make this even easier with direct support for M queries

So, is Power BI terrific again?

Let’s have a quick look at the concerns we were trying to address:

1. 1. • Anyone can publish anything
        While Power BI still allows this to happen, we now have a clear path as to how this should be used. Publishing of models to the Power BI service can easily be monitored and the Power BI champions who want to build and model can be taken on the SQL Server 2016 Tabular Model journey. It cannot be overstated how important it is to provide a central data source for most end users.
      • No clear path to making models scale
        Centralising the Power BI Models into Tabular Models makes it possible to create large models well past the current 1GB Power BI Pro limit, load them incrementally using partitions, avoid having to refresh datasets using the somewhat limited scheduling mechanisms of the Power BI Service and only move data to the Cloud when it is required to satisfy end users’ queries.
      • The Power BI freemium model
        If your organisation is committed to having everyone on the Power BI Service, then you should work with Microsoft to get the best deal that fits the scale of your organisation. However, centralising the data in Tabular Models does make it possible to use Power BI Desktop as a reporting tool by providing users with access to .pbix files with reports. It also works nicely with other Microsoft BI tools such as Excel, Reporting Services Report Builder and Reporting Service Mobile Reports.

Conclusion

All data and technology in an enterprise need governance. The key, however, is to provide the correct levels of governance. Stellar’s customers who are using Power BI, or starting to adopt Power BI, have realised this, and we have worked closely with them to create scalable and sustainable deployments.

I am sure this post will raise a lot more questions, so feel free to reach out to us so we can work together to make Power BI terrific in your organisation.

Main image: Microsoft