M Archives - Page 2 of 9

Content Endorsement in Power BI, Part 1, The Basics

As you may already know, Power BI is not a report-authoring tool only. Indeed, it is much more than that. Power BI is an all-around data platform supporting many aspects you’d expect from such a platform. You can ingest the data from various data sources, transform it, model it, visualise and share it with others. Read more about what Power BI is here.

One of the key aspects of users’ experience in Power BI is their ability to collaborate in creating and sharing artifacts, making it an easy-to-use and convenient platform. But the convenience comes with the cost of having a lot of shared artifacts in large organisations raising concerns about the artifact’s quality and trustworthiness. It would be hard, if not impossible, to identify the quality of the artifacts without a mechanism to identify the quality of the artifacts. Endorsement is the answer to this.

In this series of blog posts, I answer the following questions:

What does content mean in Power BI? (this post)
What is endorsement? (this post)
Which artifacts support endorsement? (this post)
Who can endorse the supported artifacts? (this post)
How to endorse the supported artifacts?
What are endorsement processes?

But before we start, we need to know what content means in Power BI.

What does Content Mean in Power BI?

Update:
Microsoft lately updated the “Content” terminology, which is slightly different from when I wrote this blog. So I replaced content with artifact that is a more generic term. While the term content is not relevant to the topic anymore, I decided to keep this section explaining what content means in Power BI.

When we use the term Content in the context of Power BI, we refer to the artifacts related to visuals in Power BI Service. We currently have the following artifacts in Power BI:

From those artifacts, the Reports, Dashboards and Apps are Contents.

Continue reading →

In September 2014, I wrote a blog post on dynamically passing parameters from PowerPivot to a SQL Server stored procedure using VBA. Back then, VBA was a real lifesaver. It perhaps still is for many of us. But frankly, I even forgot how to write VBA. Maybe it is time to look at it again. I also wrote a quick tip in August 2020 about doing a similar thing in Power BI using Query Parameters. Check it out if you’re keen to know how it works in Power BI.

Eight years later, one of my weblog readers asked how to do the same thing in later versions of Excel; he is specifically asking for Excel 2019. I thought it would be good to cover this topic after 8 years and see how it works now. So, here it is, a new blog post.

The Problem

From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?

Prerequisites

For this blog post, I use SQL Server 2019 and Microsoft’s famous sample database, AdventureWorks2019. You can find Microsoft’s other sample databases here. I also use Excel 365, it should work the same way in Excel 2019, though.

The Solution

I discuss two approaches to overcome the challenge. Both approaches use Power Query slightly differently. In both approaches, we parameterise the SQL Statement of the SQL Server connector, passing the values to the parameters from an Excel table. One approach requires ignoring the Privacy Levels in Power Query, while the other does not. Both approaches work, but, depending on your preferences, you may prefer one over the other.

As mentioned, I use the AdventureWorks2019 sample database that contains a couple of stored procedures. I use the dbo.uspGetBillOfMaterials stored procedure accepting two parameters, @StartProductID and @CheckDate.

Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levels

Follow these steps to pass the parameters’ values from an Excel sheet to the stored procedure and get the results in Excel:

In Excel, navigate to the Data tab
Click the Get Data dropdown
Hover over the From Database option and click the From SQL Server Database
Enter the Server
Enter the Database
Expand the Advanced options
Type EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 727, @CheckDate = N'2013-01-01' in the SQL statement textbox
Click OK

Using SQL Statement in Power Query for Excel

Click the dropdown on the Load button
Click Load to

Load to Options to Load the Results of Power Query query into an Excel Sheet or PowerPivot Model

From here, we have some options to load the results either into an Excel sheet or the PowerPivot data model. We want to load the data into the PowerPivot data model in this example.

Select Only Create Connection
Check the Add this data to the Data Model option
Click OK

Loading the Power Query Data into PowerPivot in Excel

Continue reading →

I explained what SCD means in a Business Intelligence solution in my previous post. We also discussed that while we do not expect to handle SCD2 in a Power BI implementation, we can handle scenarios similar to SCD1. In this post, I explain how to do so.

Scenario

We have a retail company selling products. The company releases the list of products in Excel format, including list price and dealer price, every year. The product list is released on the first day of July when the financial year starts. We have to implement a Power BI solution that keeps the latest product data to analyse the sales transactions. The following image shows the Product list for 2013:

So each year, we receive a similar Excel file to the above image. The files are stored on a SharePoint Online site.

Scenario Explained

As the previous post explains, an SCD1 always keeps the current data by updating the old data with the new data. So an ETL process reads the data from the source, identifies the existing data in the destination table, inserts the new rows to the destination, updates the existing rows, and deletes the removed rows.

Here is why our scenario is similar to SCD1, with one exception:

We do not actually update the data in the Excel files and do not create an ETL process to read the data from the Excel files, identify the changes and apply the changes to an intermediary Excel file
We must read the data from the source Excel files, keep the latest data while filtering out the old ones and load the data into the data model.

As you see, while we are taking a very different implementation approach, the results are very similar with an exception: we do not delete any rows.

Implementation

Here is what we should do to achieve the goal:

We get the data in Power Query Editor using the SharePoint Folder connector
We combite the files
We use the ProductNumber column to identify the duplicated products
We use the Reporting Date column to identify the latest dates
We only keep the latest rows

Getting Data from SharePoint Online Folder

As we get the data from multiple files stored on SharePoint Online, we have to use the SharePoint Folder connector. Follow these steps:

Login to SharePoint Online and navigate to the site holding the Product list Excel files and copy the site URL from the browser

From the Get Data in the Power BI Desktop, select the SharePoint Folder connector
Click Connect

Connecting to SharePoint Online Folder from Power BI

Paste the Site URL copied on step 1
Click OK

Connecting to SharePoint Online Folder from Power BI using the SharePoint Folder connector

Click Transform Data

Continue reading →

I previously explained in a blog post what thin reports are and why we should care about them. I also explained Report Level Measures in another blog post. In this post, I try to raise some real-world challenges we face when developing thin reports. I also provide a solution to those challenges.

Report Level Measure Related Challenges

Creating and using Report Level Measures is relatively easy, but there are some challenges that we face from time to time, such as:

Distinguishing Report Level Measures from Dataset Level Measures
Report Level Measure dependencies

Determining Report Level Measures from Dataset Level Measures

One of the challenges that Power BI Developers face is creating many report level measures. Unfortunately, Power BI Desktop currently uses the same iconography for both types of measures, making it hard to distinguish the actual measures created within the dataset from the report level measures. It gets even more challenging if we need to write technical documentation for an existing thin report. We have to open the PBIX file of the thin report in the Power BI Desktop and click every single measure. If the expression bar appears, the selected measure is a report level measure; otherwise, it is a dataset level measure.

So unless we use third-party tools, which I explain in this post, we must go through the manual process.

Report Level Measure dependencies

Another pain point related to the previous challenge is finding the dependencies between the report level measures. It is crucial to be aware of the interdependencies when doing impact analysis. We need to understand how a change in a report level measure impacts other report level measures. Again, Power BI Desktop does not currently have any options supporting that, so we have to click every measure and read through the DAX expressions to identify the dependencies or use the third-party tools to save development time.

Dataset and Thin Reports Dependency Challenges

The other challenges are even more difficult to overcome relate to interdependencies between datasets and thin reports. Power BI Service provides a lineage view that shows the dependencies between a dataset and its connected thin reports. But the challenges can get more complex to overcome manually. The following are some real-world examples of more complex situations:

What if we need to analyse the impact of changes in a dataset measure on all report level measures of the connected thin reports?

How do we analyse the impact of changes on a dataset measure on all connected thin reports, including the visuals, filters, etc…?

What if we need to tune the performance and we want to find a list of all unused tables or unused fields?

As you can see, the situation can get pretty complex, so manual operations are virtually impossible.

But there is a third party tool we can use which provides heaps of capabilities with a couple of clicks.

Continue reading →

Tag: M

Endorsement in Power BI, Part 1, The Basics

What does Content Mean in Power BI?

Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query

The Problem

Prerequisites

The Solution

Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levels

Slowly Changing Dimension (SCD) in Power BI, Part 2, Implementing SCD 1

Scenario

Scenario Explained

Implementation

Getting Data from SharePoint Online Folder

Thin Reports, Real-world Challenges

Report Level Measure Related Challenges

Determining Report Level Measures from Dataset Level Measures

Report Level Measure dependencies

Dataset and Thin Reports Dependency Challenges

What does Content Mean in Power BI?

Share this:

The Problem

Prerequisites

The Solution

Approach 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privacy Levels

Share this:

Scenario

Scenario Explained

Implementation

Getting Data from SharePoint Online Folder

Share this:

Report Level Measure Related Challenges

Determining Report Level Measures from Dataset Level Measures

Report Level Measure dependencies

Dataset and Thin Reports Dependency Challenges

Share this: