Best of the September 2018 Power BI Release
The following is part of an ongoing series on Power BI from Shawn Alpay. Shawn is a Business Intelligence Architect at Senturus who has spent the last 15 years designing and implementing Microsoft-centric BI solutions for clients.
Hi there! Shawn Alpay here with a roundup of the most interesting features from the September 2018 release of Power BI. As with my previous blog entry, I'm not going to give you a point-by-point rundown of every new feature, as some of them each month are just filler; I'll only be discussing the items that elicit a strong opinion from me, either good or bad.
Undoubtedly, the ability to define aggregations is the biggest new feature in this month's release. It's very much built on top of the composite model framework that was introduced in the July 2018 release; that feature allows a single model to carry both Import and DirectQuery tables.
Imagine that your Power BI semantic model is sitting on top of a big data dataset in Hadoop, where your largest fact table is on the order of billions (or trillions!) of rows. At that size, it is not feasible to set such a table to Import mode, as it will far exceed Power BI's 10 GB data size limit. Instead, we are forced to use the DirectQuery storage mode. However, it also means that even the highest-level queries issued to your model will have to perform calculations against that table at run-time, which could take a very long time indeed. What if we were able to create an aggregation table for that detail table which is only perhaps millions of rows large and which could easily be set to Import mode…?
This is where the Aggregations feature in this month's release comes in! We can set our model to carry two tables for a single subject area
1) A detail fact, with storage mode set to DirectQuery
2) An aggregation fact, with storage mode set to Import
We can then link these two tables together by defining which fields in the aggregation table will be used by the corresponding fields in the detail table when the queries issued to the model are performed at those higher levels.
Consider the following example, where we have a Sales table and a Sales Agg table. The Sales Agg table carries values at the grain of Order Date, Customer and Product Subcategory, whereas the Sales table carries a much richer grain of Order Date, Ship Date, Customer, Product and so on.
The Sales table is set to DirectQuery mode, whereas the Sales Agg table is set to Import mode. We can tell the model to use the agg table in specific relevant cases by using the following interface to link the two tables:
Then, when the model fields a query that exclusively uses the dimensions carried in the agg table, it will automatically leverage that table, resulting in much faster performance at those levels.
This idea was actually introduced in SQL Server Analysis Services (SSAS) Tabular by Marco Russo and Alberto Ferarri some time ago. Their solution requires the user to handcraft DAX to use the proper table for every relevant measure, whereas all of that work is now performed behind the scenes in Power BI.
I really think the ability to define aggregations is a game-changer for organizations when deciding between Power BI and SSAS Tabular. Teams no longer have to choose between slow response times or losing detail when using Power BI alone (and grappling with its 10 GB data size limit). Further, I’d say that this ability is an interesting example of a semantic model feature that is now natively available in Power BI and not SSAS. I’m starting to wonder if Power BI might soon supplant SSAS Tabular, the same way that SSAS Tabular supplanted SSAS Multidimensional as Microsoft’s enterprise semantic model platform of choice. Time will tell!
CATEGORICAL X-AXIS VALUES ON SCATTER CHARTS
Prior to this month’s update, we were only able to put continuous (i.e. numeric) fields on the x-axis of scatter charts. This meant we couldn't use categorical (e.g. text or date) fields there, like we can with a bar chart. That restriction has now been lifted, allowing us to create so-called "dot plots" with multiple y-axis values per category. Pretty neat!
COPY/PASTE FROM TABLE/MATRIX
I think this feature is pretty huge, and has been a long time coming: users now have the ability to copy either a particular value or selection from a table or matrix visual, which they can then proceed to paste anywhere (although I assume they're most likely going to Excel!). Sure, a user can leverage Analyze in Excel to fetch model data via PivotTables, but this feature is a step in the right direction towards breaking down the divide of use case between Power BI and Analyze in Excel. And once Microsoft adds the ability to expand and collapse matrix visuals in Power BI, I expect this divide to crumble further.
The following screenshots show the tabular format of the data that gets saved to the clipboard when the user chooses Copy selection.
As is commonly the case with new Power BI features, this new copy/paste functionality is frustratingly limited, as you cannot yet copy the entire table. I expect Microsoft to flesh this feature out soon, and I hope that they'll expand its functionality to custom visuals.
PDF DATA SOURCE
This one's interesting given that the user base was clamoring for it so heavily: we now have the ability to use a PDF as a data source, the same way we might use an Excel spreadsheet or a CSV.
Here's my hot take: I think this feature reinforces bad behavior when leveraging data in semantic models. And if a team can't fetch upstream data in a better format than PDF, then it suggests that other issues are afoot, either technical, personal, political or otherwise. I look forward to making it through the rest of my career without ever having to use this feature – though I reserve the right to eat crow in a future article, if ever I find that it was the one thing that saved a project.
It's always a good thing when your development environment can auto-complete your code, and that's now the case within the Power BI Advanced Query Editor. Helpful dialogs will now appear when writing M code.
Personally, I'm not a huge M user, as I prefer to do as much data prep as possible in my upstream data source(s) — but I absolutely expect to leverage this feature in cases where I don't have the ability or license to prepare upstream.
It sounds like the ability to copy/paste visuals from one PBIX file to another is coming soon, and I think that'll be huge. Currently, if Developer A comes up with a great design for a particular visual in their PBIX, Developer B can't use it in their PBIX without designing the visual from scratch.
That's it for now! Check back here after the October 2018 release drops and I'll have another feature rundown for you.
If you’d like to hear more from Shawn and learn more about Microsoft Power BI, check out his webinar recording Power BI: Beyond the Buzz. Shawn explains how the product fits into Microsoft’s overall Business Intelligence framework, reviews what it does well (and does poorly) and demos some of its more prominent features. Or read his workaround tip for printing reports in Power BI.