Unlocking the secrets of Microsoft Power BI

Key Takeaways

Ease of deployment across multiple platforms, efficient data-driven insights, ease of use and support for a data-driven corporate culture is what defines an ideal Business Intelligence solution for enterprises.

Power BI leads in self-service BI because it’s the first Software as a Service (SaaS) platform to offer ‘End User BI’ in which anyone, not just a business analyst, can leverage powerful tools to obtain greater value from data.

Microsoft Power BI has been identified as a leader in Gartner’s Magic Quadrant for BI and Analytics platforms, and provides a visually rich and easy to access interface that modern business users require.

You can isolate report authoring from dataset development in Power BI, or quickly scale up or down a Power BI dataset as per your needs.

Power BI is much more than just a tool for reports and dashboards. With a thorough understanding of the query and analytical engines of Power BI, users can customize more powerful and sustainable BI solutions.

Part Two: Interview Excerpts - Power BI from a Worm’s Eye View

How long have you been a Microsoft Power BI user? How have you been using Power BI on a day-to-day basis? What other tools do you generally end up using alongside Power BI for your work?

I’ve been using Power BI from the beginning when it was merely an add-in for Excel 2010. Back then, there was no cloud service and Microsoft BI was significantly tethered to SharePoint but the fundamentals of the Tabular data modelling engine and programming language of DAX was available in Excel to build personal and team solutions.

On a day-to-day basis I regularly work with Power BI datasets – that is, the analytical data models inside of Power BI Desktop files. I also work with Power BI report authoring and visualization features and with various data sources for Power BI such as SQL Server.

From Learning to Mastering Power BI

For someone just starting out using Power BI, what would your recommended learning plan be? For existing users, what does the road to mastering Microsoft Power BI look like?

When you’re just starting out I’d recommend learning the essentials of the Power BI architecture and how the components (Power BI service, Power BI Desktop, On-Premises Data Gateway, Power BI Mobile, etc) work together. A sound knowledge on the differences between datasets, reports, and dashboards is essential and an understanding of app workspaces and apps is strongly recommended as this is the future of Power BI content management and distribution.

In terms of a learning path you should consider what your role will be on Power BI projects – will you be administering Power BI, creating reports and dashboards, or building and managing datasets? Each of these roles has their own skills, technologies and processes to learn. For example, if you’re going to be designing datasets, a solid understanding of the DAX language and filter context is essential and knowledge of M queries and data access is very important as well.

The road to mastering Power BI, in my view, involves a deep understanding of both the M and DAX languages in addition to knowledge of Power BI’s content management, delivery, and administration processes and features. You need to be able to contribute to the full lifecycle of Power BI projects and help guide the adoption of Power BI across an organization.

The most difficult or ‘tricky’ aspect of Power BI is thinking of M and DAX functions and patterns in the context of DirectQuery and Import mode datasets. For example, certain code or design patterns which are perfectly appropriate for import models are not suitable for DirectQuery models. A deep understanding of the tradeoffs and use cases for DirectQuery versus default Import (in-memory) mode and the ability to design datasets accordingly is a top characteristic of a Power BI master.

5+ interesting things (you probably didn’t know) about Power BI

What are some things that users may not have known about Power BI or what it could do? Can readers look forward to learning to do some of them from your upcoming book: Microsoft Power BI Cookbook?

The great majority of learning tutorials and documentation on Power BI involves the graphical interfaces that help you get started with Power BI. Likewise, when most people think of Power BI they almost exclusively think of data visualizations in reports and dashboards – they don’t think of the data layer. While these features are great and professional Power BI developers can take advantage of them, the more powerful and sustainable Power BI solutions require some level of customization and can only be delivered via knowledge of the query and analytical engines of Power BI.

Readers of the Power BI Cookbook can look forward to a broad mix of relatively simple to implement tips on usability such as providing an intuitive Fields list for users to more complex yet powerful examples of data transformations, embedded analytics, and dynamic filter behaviours such as with Row-level security models. Each chapter contains granular details on core Power BI features but also highlights synergies available by integrating features within a solution such as taking advantage of an M query expression, a SQL statement, or a DAX metric in the context of a report or dashboard.

What are the 3 most striking features that make you love to work with Power BI? What are 3 aspects you would like improved?

The most striking feature for me is the ability to isolate report authoring from dataset development. With Power BI you can easily implement a change to a dataset such as a new metric and many report authors can then leverage that change in their visualizations and dashboards as their reports are connected to the published version of the dataset in the Power BI service.

A second striking feature is the ‘Query Folding’ of the M query engine. I can write or enhance an M query such that a SQL statement is generated to take advantage of the data source system’s query processing resources.

A third striking feature is the ability to quickly scale up or down a Power BI dataset via the dedicated hardware available with Power BI Premium. With Power BI Premium, free users (users without a Pro License) are now able to access Power BI reports and dashboards.

The three aspects I’d like to see improved include the following:

1. Currently we don’t have IntelliSense and other common development features when writing M queries.
2. Currently we don’t have display folders for Power BI datasets thus we have to work around this with larger, more complex datasets to maintain a simple user interface.
3. Currently we don’t have Perspectives, a feature of SSAS, that would allow us to define a view of a Power BI dataset such that users don’t see other parts of a data model not relevant to their needs.

Is the latest Microsoft Power BI update a significant improvement over the previous version? Any specific new features you’d like to highlight?

Absolutely. The September update included a Drillthrough feature that, if configured correctly, enables users to quickly access the crucial details associated with values on their reports such as an individual vendor or a product. Additionally, there was a significant update to Report Themes which provides organizations with more control to define standard, consistent report formatting.

Drillthrough is so important that an example of this feature was added to the Power BI Cookbook. Additionally, Power BI usage reporting including the identity of the individual user accessing Power BI content was recently released and this too was included in the Power BI Cookbook. Finally, I believe the new Ribbon Chart will be used extensively as a superior alternative to stacked column charts.

Can you tell us a little about the new 'time storyteller custom visual' feature in Power BI?

The Timeline Storyteller custom visual was developed by the Storytelling with Data group within Microsoft Research. Though it’s available for inclusion in Power BI reports via the Office Store like other custom visuals, it’s more like a storytelling design environment than a single visual given its extensive configuration options for timeline representations, scales, layouts, filtering and annotations.

Like the inherent advantages of geospatial visuals, the linking of Visio diagrams with related Power BI datasets can intuitively call out bottlenecks and otherwise difficult-to-detect relationships within processes.

7 reasons to choose Power BI for building enterprise BI solutions

Where does Power BI fall within Microsoft's mission to empower every person and every organization on the planet to achieve more of 1. Bringing people together 2. Living smarter 3. Friction free creativity 4. Fluid mobility?

Power BI Desktop is available for free and is enhanced each month with features that empower the user to do more and which remove technical obstacles. Similarly, with no knowledge whatsoever of the underlying technology or solution, a business user can access a Power BI app on their phone or PC and easily view and interact with data relevant to their role.

Importantly for business analysts and information workers, Power BI acknowledges the scarcity of BI and analytics resources (ie data scientists, BI developers) and thus provides both graphical interfaces as well as full programming capabilities right into Power BI Desktop. This makes it feasible and often painless to quickly create a working, valuable solution with relatively little experience with the product.

We can expect Power BI to support 10GB (and then larger) datasets soon as well as improve its ‘data storytelling’ capabilities with a feature called Bookmarks. In effect, Bookmarks will allow Power BI reports to become like PowerPoint presentations with animation.

Organizations will also have greater control over how they utilize the v-Cores they purchase as part of Power BI Premium. This will make scaling Power BI deployments easier and more flexible.

I’m personally most interested in the incremental refresh feature identified on the Power BI Premium Roadmap. Currently an entire Power BI dataset (in import mode) is refreshed and this is a primary barrier to deploying larger Power BI datasets.

Additionally (though not exclusively by any means), the ability to ‘write’ from Power BI to source applications is also a highly anticipated feature on the Power BI Roadmap.

How does your book, Microsoft Power BI Cookbook, prepare its readers to be industry ready? What are the key takeaways for readers from this book?

Power BI is built with proven, industry leading BI technologies and architectures such as in-memory, columnar compressed data stores and functional query and analytical programming languages. Readers of the Power BI Cookbook will likely be able to quickly deliver fresh solutions or propose ideas for enhancements to existing Power BI projects. Additionally, particularly for BI developers, the skills and techniques demonstrated in the Power BI Cookbook will generally be applicable across the Microsoft BI stack such as in SQL Server Analysis Services Tabular projects and the Power BI Report Server.

A primary takeaway from this book is that Power BI is much more than a report authoring or visualization tool. The data transformation and modelling capabilities of Power BI, particularly combined with Power BI Premium capacity and licensing considerations, are robust and scalable.

Readers will quickly learn that though certain Power BI features are available in Excel and though Excel can be an important part of Power BI solutions from a BI consumption standpoint, there are massive advantages of Power BI relative to Excel. Therefore, almost all PowerPivot and Power Query for Excel content can and should be migrated to Power BI Desktop.

An additional takeaway is the breadth of project types and scenarios that Power BI can support. You can design a corporate BI solution with a Power BI dataset to support hundreds of users across multiple teams but you can also build a tightly focused solution such as monitoring system resources or documenting the contents of a dataset.

If you enjoyed this interview, check out Brett’s latest book, Microsoft Power BI Cookbook. Also, read part one of the interview here to see how and where Power BI fits into the BI landscape and what it takes to stay successful in this industry.