Press "Enter" to skip to content

Category: Power BI

Installing Power BI Gateway

Published 2019-11-18 by Kevin Feasel

Paulina Nowinska shows how to install the Power BI Gateway in its two separate modes:

This On-premises was created for a multi-developer environment. Here multiple people can work on the same Data Gateway if the administrator authorized them before. With this Data Gateway, you will have much more fun than Personal Mode. Why? Because of on-premises support not only Power BI just like his brother Personal Mode. Here you can provide quick and secure data transfer between data which is not in the cloud and Microsoft cloud services: PowerApps, Microsoft Flow, Azure Analysis Services, and Logic Apps and of course Power BI. Depending on your needs, you can choose one of them.

Click through for step-by-step instructions on both techniques.

Leave a Comment

Your Power BI Administrator’s Privileges

Published 2019-11-18 by Kevin Feasel

Melissa Coates goes into exactly what it is that a Power BI admin can see and do:

I wrote about (and updated) this topic previously, but this is so important that it warrants revisiting. So let’s have a quick chat about what privileges a Power BI administrator has with respect to accessing data throughout the Power BI tenant.

All metadata throughout the tenant is available to the Power BI administrator (ex: if they want to enumerate a list of workspaces, reports, dashboards, etc using the APIs). So, metadata is easily discoverable but — technically speaking — a Power BI administrator cannot access datasets in Power BI unless they have permission to that workspace. However…

Read the whole thing.

Leave a Comment

Using Azure DevOps for Power BI CI/CD

Published 2019-11-14 by Kevin Feasel

Marc Lelijveld and Ton Swart look at today’s CI/CD options for Power BI:

As a developer we might be used to working with Git repositories, especially in order to have release management in place. Git is well known as a modern version control system. By using Git, you will have a local copy of the code on your machine as well. Based on these local copies, you can continue developing. After you’re finished with your work, you can easily push your local repository to merge with the online (shared) repository. By doing this, only the changes will be pushed and saved in the online repository. In fact, only for the new code there will be a new version created. 

Versioning of Power BI files is a whole different story. Since pbix files are binary files, there is no way of checking-in only the code changes. The process of pushing changes identifies the pbix file as one object which has a new version.

Read on for the state of the art. To be honest, I don’t like the state of the art that much, but that has nothing to do with Marc and Ton’s great article.

Leave a Comment

Star Schemas and Power BI

Published 2019-11-14 by Kevin Feasel

Alberto Ferrari explains why star schemas are so important to Power BI:

A common question among data modeling newbies is whether it is better to use a completely flattened data model with only one table, or to invest time in building a proper star schema (you can find a description of star schemas in Introduction to Data Modeling). As coined by Koen Verbeeck, the motto of a seasoned modeler should be “Star Schema all The Things!”

The goal is to demonstrate that a report using a flattened table returns inaccurate numbers, whereas using a star schema turns it into a sound analytical system.

Read on for the example.

Leave a Comment

Power BI Paginated Reports on the Cheap

Published 2019-11-14 by Kevin Feasel

John White shows how you can get paginated reports in Power BI at a lower cost:

In our scenario, we have a paginated report that uses a published Power BI dataset as a data source. as of this writing, there is no API call available to render a paginated report on demand, so we will rely on the scheduled subscription capability. in order to minimize the cost of the solution, we want the dedicated capacity to run as little as possible.

The solution will consist of an Azure logic app, and Power BI paginated report scheduling. An Azure logic app uses the same set of actions that a Flow in Power automate does, but is a little more flexible in its permissions model.

Read on for the step-by-step instructions.

Leave a Comment

Thoughts on Large Datasets in Power BI

Published 2019-11-12 by Kevin Feasel

Teo Lachev has some early thoughts on large datasets in Power BI:

At Ignite 2019 Microsoft announced the public preview of large datasets in Power BI Premium. This is a significant milestone as now datasets can grow up to the capacity’s maximum memory (previously, the max size was 10 GB with P3 plan), thus opening the possibility of deploying organizational semantic models to Power BI. I consider this feature mostly suitable for organizational BI as I don’t imagine business users dealing with such large data volumes. I tested large datasets during its private preview, and I’d like to share some notes.

Teo has some open questions, and I’d like to see this shifted down to SSAS too.

Leave a Comment

Improving Post-Join Aggregation Performance in Power Query

Published 2019-10-29 by Kevin Feasel

Imke Feldmann finds some nice performance improvements with aggregating data after a join using Power Query:

When you join a table to another table in Power Query, the UI gives you the option to either expand the columns (default) or aggregate the contents of the joint tables. That’s useful if multiple rows are returned for the rows of the table that has been joined to (left table):

But this method is extremely slow. Compared to “simply” expanding all values to new rows (which took around 5 seconds), the aggregation took around 50 seconds. The automatically generated code uses the “Table.AggregateTableColumn”-function.

Read on to see two separate attempts to speed things up.

Leave a Comment

Speeding Up Excel Pivot Table Performance

Published 2019-10-28 by Kevin Feasel

Chris Webb shows how you can improve performance of Excel pivot tables hitting Analysis Services Multidimensional models:

Back in 2016 I wrote the following blog post about changes to the way Excel 365 generated MDX queries for PivotTables connected to Analysis Services, Power Pivot/the Excel Data Model and Power BI datasets:

https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/

I know it sounds boring and not something you need to worry about but trust me, this is important – these changes solved the vast majority of Excel PivotTable performance problems that I encountered when I was a consultant so you should read the above post before continuing.

Unfortunately, earlier this year these changes had to be partially rolled back because in some rare cases the queries generated returned incorrect results; this means that you may find that values for subtotals and grand totals are again being returned even when they aren’t being displayed. The good news is that you should still be able to get the improved performance with a few minor tweaks.

Read on to see what those tweaks are.

Comments closed

Head-to-Head Comparisons with Power BI

Published 2019-10-24 by Kevin Feasel

Rob Collie walks us through building a visual which provides head-to-head comparison using Power BI:

Yes, I know that NONE of the infographics above is a scientifically “good” comparison tool.  Too noisy, too flashy, not clean…  but every now and then you DO need to cater to your audience.  Engagement is the first step in the comprehension funnel, and in this particular example, yep, I’m trying to capture the eyeballs of an audience that likes this sort of thing.  The style of #4 is a decent compromise in this case.  Know your audience.

Rob takes us through an interesting journey. I don’t think I’d want to use that style too often, but to be fair, Rob talks about that in the snippet I clipped.

Comments closed