Press "Enter" to skip to content

Category: Power BI

Stress Testing Power BI Embedded

Kristyna Hughes puts Power BI to the test:

For example, one instance may have a very large data model that takes a lot of memory and CPU time to refresh, 20 users at peak viewing times, hourly refreshes, and the queries are all very simple and allow for query folding. Another business may have six smaller data models, 950 users at peak viewing times, daily refreshes, and the queries populating the data model are all very very complex. All of these elements impact the usage at any given time, making predicting overall CPU needs nearly impossible. Thankfully, stress testing your capacity gives us an option that is not purely reactionary.

This blog will walk through how to stress test your capacity, the elements of capacity planning, and how to understand the results of the stress test.

Read on to see how, using a step-by-step guide.

Comments closed

Using KQL to Extract JSON Data in Power BI

Dany Hoter shreds some JSON:

In Kusto (aka Azure Data Explorer aka ADX) you can have columns in a table that contain JSON structures.

In KQL it is very easy to extract elements from these columns and use them as regular columns.

It requires more resources but overall, it is standard.

An example can be found in the table TransformedMetrics in the SampleMetrics databases in the help cluster.

Click through for that process.

Comments closed

Scripting and Automating Power BI Backups

Marc Lelijveld backs that thing up:

Basically for every data model, but in particular for large and enterprise-grade data models, it might be wishful to perform backups. Power BI Premium offers a way to perform backup and restore operations for Power BI datasets. This feature, coming from Azure Analysis Services, helps you to take a backup of your dataset logic as well as the data itself and the ability to restore in case your dataset gets corrupted or backend processes have failed. Furthermore, this backup and restore process is also a way to migrate your datasets from Analysis Services to Power BI Premium.

In this blog, I describe what is needed for Power BI backup operations, how you can perform them and how to script / automate them.

Read on for the process.

Comments closed

Table-Valued Functions and Dynamic M Parameters

Chris Webb uses dynamic M parameters:

My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.

Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.

Leaving aside thoughts on table-valued functions in general, dynamic M parameters looks like a really nice feature and as Chris notes, it also works for things like stored procedures.

Comments closed

Goals in Power BI

Gogula Aryalingam takes us through Power BI goals:

The feature is currently in preview, introduced some 8 months ago, and has quite a lot of promise. For me, it is particularly exciting since I am working with a large customer, who is a perfect candidate to implement goals for. So, what is Goals in Power BI?

Let us take a quick scenario first: Organizations, regularly (if not frequently) monitor indicators of their business performance to ensure their goals and aspirations are met. Sometimes these aspirations are difficult to keep track of due to various complexities. Consider a goal called Reduce employee turnover and increase satisfaction (something that I picked up from here). To effectively understand and track its progress, the organization would probably have a few key performance indicators (KPIs) that make it easy to look at reducing employee turnover and increasing satisfaction objectively. One such KPI could be a low human capital Turnover Rate while another could be a high Employee Satisfaction Indicator. Collectively these KPIs will help determine the achievement of the goal within a stipulated period (such as a calendar year). Similarly an organization will have many goals that are aligned to organizational KPIs or metrics. Sometimes, certain KPIs/metrics may cascade down the organization’s departments, where each department’s performance determine the overall organizational performance.

Read on to see how Goals work and one use case involving KPIs.

Comments closed

Multi-Column Transformations in Power Query

Imke Feldmann has the need for speed:

In this article I’m going to present a method about transforming multiple columns at once in a fast way. This method also allows you to reference columns that exist in your table already. As I have described in a previous article, this cannot be done using the native Table.TransformColumns function that will be applied if you do column transformations using the UI in Power Query. The function I am sharing here allows you to enter a list of column names to be transformed and a function that defines the transformation itself. So you have to be familiar with defining custom functions to use this approach.

Click through for Imke’s function and explanation but also check out the comments for another take on the problem.

Comments closed

Plotting Multiple Columns on a Legend in Power BI

Jason Cockington has a workaround:

At a recent training course, one of the students asked if it was possible to add two different columns on the legend of a line chart, so that when a selection is made on a second slicer the chart splits to reveal multiple lines.

Given others in the class showed interest in the subsequent conversation, I decided to create a short blog so that everyone could benefit.

The short answer is “no” but the longer answer is more interesting.

Comments closed

Power BI Implementation Planning Guidance

Melissa Coates has a plan:

I’m really excited to share with you that we’re working on a new set of Power BI guidance called “Power BI Implementation Planning.”

The first pieces of content are some of the most common Power BI usage scenarios. In terms of scope of content, we’re just getting started – there will be LOTS more content to come. We’ll be iteratively publishing additions to this set of content over the next several months.

There are a lot of smart people working on this project.

Comments closed