Category: Power BI

Reza Rad dives into aggregations in Power BI.  Part one introduces the topic:

Imagine a Fact table with 250 millions of rows. Such a fact table is big enough to be considered as a good candidate for DirectQuery connection. You don’t want to load such a big table into the memory, and most probably, the Power BI file size exceeds the 1GB limitation. Now, think about your reporting solution for a second. Do you always query this fact table at the finest or minimum granular level? I mean do you always look at every single transaction in this table when you do report on it?

The answer is No. In most of the times, you are querying the data by other fields or columns. As an example; you query the Sales value in the fact table, by Year. Some other times, you query the fact table’s values by Customer’s education category. Some other times, you query the values in the fact table, by each product. When you look at real-world scenarios, most of the time, you are querying the fact table by aggregations of dimension tables.

Then, Reza starts building an aggregation table:

Aggregation tables are the fast performing solution for huge DirectQuery tables in Power BI. In the previous blog post, I explained what is an aggregation, and why it is an important part of a Power BI implementation. Aggregations are part of the Composite model in the Power BI. For the aggregation set up, your first step is to create an aggregated table. In this blog post, I’ll explain how that step can be done. If you want to learn more about Power BI, read the Power BI book, from Rookie to Rock Star.

There’s a lot of detail packed into the first posts in this series, so it looks like a good one to watch.

Matt Allington shows us how to move a query from Power BI desktop into a Power BI Dataflow:

Power Query is a user friendly ETL tool (Extract, Transform and Load).  Traditionally ETL has been done using more complicated tools (such as SQL Server Integration Services – SSIS) and the resulting data is stored in a data mart or data warehouse for consumption by anyone that needs a standard view of the data.  Power BI Desktop can consume tables directly from a data warehouse and simply load the table into Power BI – dead easy.  But Power Query is also a powerful ETL tool in its own right, and it can be used to transform and reshape the source data directly inside Power BI Desktop (and then PowerBI.com).  This is very useful if :

1. You don’t have a data warehouse and/or
2. You need some variation of what is in your data warehouse.
3. You have other data sources that are not in a data warehouse but are still important to you.

Taking this approach (manipulate in Power Query) is perfectly fine if you have a single workbook, but what if you have 10 similar workbooks all needing the same transformation?  Worse still, what if you are one of many people in a company all doing the same thing with multiple workbooks?

Read on for the solution.

Kasper de Jonge shows off a few Power BI dynamic management views:

Today a quick one that I came across while writing a different blog post that I will blog later. I know we have talked about it again and again but a good best practice is to remove any high carnality columns you don’t necessary need. This trick is not new and has been blogged about before in different places but I wanted to emphasize it again due to the importance.

I was finishing up my next blog post and wanted to upload the sample file. While doing that I noticed the file was 150MB large. That is rather large for such a simple file. The largest table has 500,000 rows and none of them are unique. What is going on?

There are some interesting DMVs in Kasper’s post, including one which shows cardinality by column.

Alexander Arvidsson shares an interesting use case for Power BI Dataflows:

There are several more use cases for a dataflow, but one that is very useful is the ability to share a dataset between apps. Previously we had to duplicate the dataset to each and every app that needed to use it, increasing the risk that one dataset was ignored, not refreshed properly or otherwise out of sync with reality. By using dataflows we can have several apps rely on the same dataflow (via a dataset), and thus it is quite possible to have a “master dataset”.

Click through for a walkthrough, as well as an understanding of the process’s limitations.

Teo Lachev gives us the lowdown on Dataflows in Power BI:

There is a lot to like about dataflows. I can think of two primary self-service scenarios that can benefit from dataflows:

• Data staging – Many organizations implement operational data stores (ODS) and staging databases before the data is processed and loaded in a data warehouse. As a business user, you can use data-flows for a similar purpose. For example, one of our clients is a large insurance company that uses Microsoft Dynamics 365 for customer relationship management. Various data analysts create data models from the same CRM data, but they find that refreshing the CRM data is time consuming. Instead, they can create a dataflow to stage some CRM entities before importing them in Power BI Desktop. Even better, you could import the staged CRM data into a single dataset or in an organizational semantic model to multiple data copies and duplicating business logic.

• Certified datasets – One way to improve data quality and promote better self-service BI is to prepare a set of certified common entities, such as Organization, Product, and Vendor. A data steward can be responsible for designing and managing these entities. Once in place, data analysts can import the certified entities in their data models.

Read on for some more positives and negatives.

Eugene Meidinger delineates what is free versus paid with respect to Power BI:

The Power BI Service, think powerbi.com, allows for free users. These free users can create reports and upload them, but with a significant number of limitations. The biggest is you only have one way of sharing content to others. Specifically with Publish to Web, which essentially makes your entire report free to the public.

You also only have one way of privately consuming other people’s reports, and that’s if someone places content in Power BI Premium. Otherwise, other users can’t share their reports directly with you. Power BI Free users are truly and island to themselves.

One other thing worth nothing is that you can’t sign up with a personal email. David Eldersveld has a good blog post on the issue. As of this writing, the uservoice request to change this has 2,800 votes.

See here for some more limitations of the free version of Power BI.

Read the whole thing.