Power BI Training in BTM Layout

The future of Microsoft’s Business Intelligence: Power BI

• Industry analyst Gartner’s most recent report on Business Intelligence and Analytics Platforms place Microsoft as a Leader in their Magic Quadrant. They have been a Leader for nine consecutive years, but for the first time they are considered to have the most Completeness Of Vision. Impressive stuff.
• Power BI is a key element of their vision for the future. As the world slowly moves away from on-premises, and into the cloud, Power BI, Microsoft hope, will become an important component of how an organisation will develop, deploy and consume Business Intelligence reporting in the future.
• This is all well and good, but we suspect the reality for many organisations is that the idea of Power BI is simply a ‘pie-in-the-sky’ idea of where we may or may not be in the future. When we read that with Power BI we can Craft Data Stories, our eyes glaze over and we hunt out cynical images to email to our colleagues to get a cheap giggle.
• When Power BI was launched we did actually email a picture of a bottle of snake oil to our colleagues, commenting on the grand claims that Power BI seemed to be making for itself, that users would simply be able to use their data to Tell Your Story.
• This wasn’t a dig at Microsoft, but more a British reaction to hyperbole based on our current understanding of the state of play at that time. However, we now know a little more than we did then, and Power BI’s functionality has been constantly evolving, so we think it’s time to atone for our snake oil jibe.

Well Organised Data

We still maintain that without well organised data available to you, it’s going to be very difficult to generate any kind of meaningful reporting.

The Data Model

• The magic data can use to create their stunning Power BI reports would probably come from a Tabular Data Model hosted in a SQL Server Analysis Services installation.
• The Tabular Data Model is an in-memory version of the pre-prepared reporting data (in the data warehouse) that allows users to navigate and explore the data themselves.
• Self-Service BI. Tables, graphs, charts and other data visualisations representing that data are all then available to the users via Pivot Tables, Power BI dashboards and reports, or Reporting Services reports.

This is the important point:

The Tabular Data Model is essentially the same as a Power Pivot Data Model in Excel. It is the same as a Power BI Data Model in the cloud.

All three use the same Data Analysis Expressions (DAX) to extract further value from the raw data. DAX can give you running cumulative totals for example. If you have a Date table, you can slice your data up by your organisation’s Financial Quarters or perhaps by Semesters or Terms. You can have Year On Year analysis, rolling changes by Previous Period, etc.

If you can load up your data up into an Excel Power Pivot Data Model, and have your Pivot Tables work just as you’d like, you’re basically there. (You will need Well Organised Data for this to be the case).

• Mocking up a Data Model in Excel is a great idea.
• From there you can:
• Export it into SQL Server Analysis Services. It becomes a Tabular Data model, and so can be formalised into a more managed environment. All your organisation’s users can access the model via Reporting Services or Power BI.
  Export it up into Power BI. A new Power BI Data Model will be created for you.
• Power BI dashboards and reports created against that data model can be shared with anybody, including people without an Active Directory entry.
• Power BI Data Models can be ‘Analysed in Excel’. So once you export your model, other Excel users can pull it back down from the cloud to perform their own separate analysis of the data.
• Of course you can also create Power BI Data Models directly with the Power BI Desktop application, and Tabular Data Models directly with Visual Studio.

The ETL process

So what do we mean by ‘Well Organised Data’?

• There are a few elements to this, and most of what is traditionally achieved in a SQL Server’s ETL process can be achieved in Power BI and Excel through the use of Power Query.
• Power Query is like a Macro on steroids. Each query consists of a series of steps. When a query is run or refreshed, the same steps are repeated each time.

With Power Query you can:

• Connect to a whole raft of different data sources
• Pull in all your data, transforming it in many ways, like:
• Column renaming and exclusion
  
• Pivot/Un-pivot
• Change data types
• Fix errors
• Populate missing values
• Combine query results
• Split datasets
• Manipulate to your hearts content
• Load that processed data into the Data Model

What are we achieving here? What is Well Organised Data?

• Consolidated data sources – If you have a Customer’s data in more than one place, bring it all together in Power Query.
• One version of the truth, Clean Data, Master Data – The Customer may have a different Id in each system. The name may have been recorded slightly differently. The sales area they live in may be referred to with a different code in each system. Tidy all the discrepancies up in Power Query.
• Data organised into a reporting data structure – The main data set(s) you want to report on, the data you want the numbers about, are prepared in Power Query. The data you want to use to slice up the reportable data, the lookup data sets, are ideally split out and prepared with Power Query here too.

Finally, you define the relationships between the prepared data sets in your Data Model, create any extra DAX calculations that you’d like, and you should be in a good position to start thinking about what kind of reports and dashboards would be most value for your organisation.

Phew! Amazingly this is only scratching the surface. Business Intelligence is a critical function to get right. We can’t underemphasise that – it should be the life-blood for decision making.