DEV Community: Jon Stjernegaard Vöge

Guide: How to add Write Back capabilities to your Power BI reports with Power Apps — Part 3

Jon Stjernegaard Vöge — Wed, 25 Sep 2024 07:00:00 +0000

Guide: How to add Write Back capabilities to your Power BI reports with Power Apps — Part 3

Introduction

This article is part of a series on integrations between Power BI and Power Apps.

Part 1 of the series showed how to get started with creating and embedding a basic Power App in a Power BI report for data write-back. Find the article here: Link to Part 1!

Part 2, taught you how to choose an appropriate Data Source for your Writeback needs, and how to display the data live in Power BI with a DirectQuery connection. Find the article here: Link to Part 2!

In this Part 3 of the series, I’ll teach you a few quick tips & tricks for layouting your embedded Power Apps in Power BI, and overcome the limitations of the Phone Layout that is forced upon Power Apps created from Power BI.

In short, this article will teach you:

How to change the sizing dimensions of Power Apps with Phone Layouts.
How to identify the right sizing dimensions for your embedded Power App

1. Manipulating the size of the canvas in Power Apps made for Phone Layout

If you have followed the first Parts of this Article Series, or have on your own tried creating a new Power App from within the Power Apps for Power BI visualization type in Power BI (which is necessary to enable the PowerBIIntegration.Refresh() function described in Part 2), you will inevitably realize that your Power App will not have dimension properties available in the Power App settings. Rather, you will only be able to choose between ‘Portrait’ or ‘Landscape’ orientation…:

… as opposed to the full dimension settings of a Power App with Tablet Layout:

The inability to alter the sizing of the app may result in the dreaded “grey borders” that often haunt the design of Power Apps embedded in Power BI reports:

However, there is a workaround. instead of resizing your Power App from within the App Settings, you may simply change the Height and Width properties of your Screens in the Power App, to whatever fits your needs.

Before:

After changing the Screen Height and Width:

A bit of rearranging, and we’re left with a Power App much better fitting the dimensions of our visualisation in Power BI:

You should however use this feature with caution, as it may produce unexpected end-user experiences. You want to ensure that all of your screens are sized equally, and you want to make sure the Power App is only used in a setting where the chosen sizing makes sense.

2. Identifying the proper sizing dimensions of your embedded Power Apps

But what size should you then choose for your Screen Height and Width?

Trial and error can be cumbersome, and another tip in terms of identifying the correct Height and Width dimensions, is to open your Power BI report, and resize the Power Apps for Power BI visualisation to your required size. Within the formatting options of the visualization, make note of the height and width dimensions. Use these dimensions as the size dimensions inside of your Power App as well, to make the Power App fit snugly within the frame in Power BI.

My personal experience, is that everything inside embedded Power Apps often appear slightly too large. If you experience this too, a bonus tip is to scale up the dimensions of the Power Apps size dimensions to make everything appear smaller, without having to resize each individual element. Just make sure to keep the correct height to width ratio as you are adjusting (e.g. to make elements in your 1000x500 Power App appear smaller, try adjusting the size of the App to 1200*600 within Power Apps).

Here i made small adjustments to make the content appear slightly smaller:

Summary

Power Apps created from within Power BI will always be in Phone Layout. Use the Screen.Width and Screen.Height properties in your Power App to manually alter the Height and Width of your Power App screens. For sizing guidance, use the dimensions of the Power Apps visualization in Power BI, to get a starting point.

Guide: How to add Write-Back capabilities to Power BI reports with Power Apps — Part 2

Jon Stjernegaard Vöge — Tue, 24 Sep 2024 07:00:00 +0000

Guide: How to add Write-Back capabilities to Power BI reports with Power Apps — Part 2

Introduction

This article is part of a series on integrations between Power BI and Power Apps.

Part 1 showed how to get started with creating and embedding a basic Power App in a Power BI report for data write-back.

Part 2, this article, will teach you:

How to pick a data source for your Power Apps output data
How to output data to the selected source
How to make the data appear Live in your Power BI report, without the user having to refresh anything.

1. Choosing the right Data Source for your Power Apps output

Before we can show any kind of data created from Power Apps in a Power BI report, we need to store it somewhere. Choosing the right Data Source for hosting your data is an important question. Most often, the choice is between Sharepoint, a SQL server and Dataverse.

Two main tradeoffs may influence your choice:

Timeliness of data (update frequency) vs. License Costs
Security & Governance vs. Availability

First, you need to ask yourself (or your users), how frequently data needs to be updated. If the answer is less than 8 times a day, you may be just fine using Sharepoint as a Data Source. If you need more than that, perhaps even live data, you will need either a SQL Server or Dataverse Connection. The kicker however, is that a Power Apps using Dataverse or SQL Server Data Connections, will require Power Apps Premium Licensing, as opposed to the standard license required for Sharepoint. Refer to Microsofts own website for guidance on licenses: Link to Power Apps Licensing

Second, you may want to consider how sensitive your data is, and how accessible it should be. Sharepoint is easy to get started with and accessible for users, but also has limited governance and security options. Dataverse and SQL Server allows much more comprehensive security setups. If you are faced with picking between the latter options, it will often be a matter of preference, and it might simply be the easiest to piggyback off of any existing solution you may have in your organization.

In short:

Need low cost, low security, infrequent data refresh or high accesibility? Pick Sharepoint.

Need high security or frequent/live data refreshes? Pick Dataverse or SQL Server.

2. Outputting data to your data source

After selecting your data source, prepare an empty table at the destination with the correct columns and data types you wish to input. I am going to input Sales Budgets by Product Category, and have created a table in an SQL database with three columns. Make sure that you define a Primary Key on the table if you are using a SQL Server, as that is required to update records in the table from Power Apps:

Now, open up your Power BI report with your embedded Power App. The best way to work with embedded Power Apps, is to open the App from inside Power BI, as that will send data from Power BI over to Power Apps for you to work with. To do so, open your report from app.powerbi.com, enter the Edit Mode for the report, open the menu for the Power Apps visualization, and select “Edit” here. This should prompt your Power App to open in edit mode in another window in your browser, with data from your Power BI report available.

With the Power Apps studio open, find the Data tab on the left hand side, add a new data connection to your data source of choice, and connect to the table you created.

Now, you need to create a screen in your App for submitting data to your new data connection. I have created a simple input interface with drop downs and text input controls allowing users to input budget amounts on Product Category, as well as a button control which submits the data to the data source:

Note that I am not using a Form control here, but instead built my own form with simple controls as seen in the Tree View. While Forms may seem simpler to setup initially, they are difficult to customize, and are very bad at submitting multiple rows of data, if you ever run into that requirement. Instead, I configured my Submit Budget button with the following code:

With
    {
        BudgetExists: LookUp(
            SalesBudget,
            ProductCategory = dd_Category.Selected.Category
        )
    },
    If(
        IsBlank(BudgetExists),
        Patch(
            SalesBudget,
            Defaults(SalesBudget),
            {
                InputID: Last(SalesBudget).InputID + 1,
                ProductCategory: dd_Category.Selected.Category,
                SalesAmount: Value(ti_BudgetValue.Text)
            }
        ),
        Patch(
            SalesBudget,
            BudgetExists,
            {
                SalesAmount: Value(ti_BudgetValue.Text)
            }
        )
    )
);

The code uses two of my favourite functions: With(), to lookup if an input already exists for the selected ProductCategory, and use the result repeatedly throughout the code for efficiency, as well as Patch(), to write new records and update existing records in the data source, instead of using a Form.

Further, i decided to populate the Text Input control with the default value of the selected ProductCategory, if a value already exists:

LookUp(SalesBudget, ProductCategory = dd_Category.Selected.Category).SalesAmount

With your custom form setup, save and publish your Power App, reload your Power BI Report, and Test that you are able to create rows of data inside your data source, using the embedded Power App.

3. Retrieving the data with Power BI, and displaying it live for users, without the need for refreshes

Now wouldn’t it be nice if those values we input with our App just show up in the same report? I would say so!

In Power BI, add a new Data Source to your report, and connect to the Data Source and Data Table in which you outputted your Power Apps data. During this setup, you have an important choice to make: To DirectQuery or not to Direct Query.

If you went with the low cost/low security route of Sharepoint, you are unfortunately limited to Import mode, which will make your data update only when your Power BI dataset is being refreshed.

If you are using SQL Server or Dataverse however, choosing DirectQuery will enable you to query data from the data source live, and new/updated data coming from the Power App may be reflected immediately in your report. However, DirectQuery comes with a number of limitations you should be aware of. See the Microsoft documentation for more details: Link to documentation.

I will select DirectQuery, as I wish to provide my users with live feedback on the impact of their inputs. After setting up the new Data Source, create any relevant relationships to other tables in your Data Model. You can now build a visualization using the new data coming from the embedded Power App, or you may use the data in existing visualisations as well! Here I have chosen to show the Budget Amounts inputted in my Power App, together with the Sales Amounts from my original dataset.

If you save and test your report and app now, you will most likely disappointedly notice that when you Submit Data in your Power App, nothing happens in Power BI until you refresh your browser. What is actually happening, is that DirectQuery is not truly “live”, but only occurs every time a visual refresh takes place. Luckily, we can force this to happen automatically!

Return to Power Apps Studio, and find the button control that submits your data to the data source. You simply need to put the funciton called PowerBIIntegration.Refresh() at the end of the code of your button’s OnSelect property, to trigger a visual refresh of the entire Power BI report after you are done patching data to the data source. This effectively makes the data show up immediately in the Power BI report.

There is one catch to the PowerBIIntegration.Refresh function: It is only available within Power Apps which have originally been created from within the Power Apps for Power BI visualisation type in Power BI, by clicking on “Create New” inside the visualisation. The function is not available in a Power App created ‘normally’ from make.powerapps.com, and connected to a Power BI report at a later stage. See Part 1 of this article series for further details.

https://www.linkedin.com/embeds/publishingEmbed.html?articleId=8322741095634091622&li_theme=light

Congratulations! You are now able to write data back to your data source and display it live in your report!

Summary

In this article you were taught how to make your Power Apps data write-back entries show up in Power BI, and how to trigger visual refreshes of your Power BI reports with Power Apps to display DirectQuery data live without the need for any refreshes.

Do you have any interesting use cases for this feature, or ideas you want sparring on? Feel free to reach out.

Guide: How to add Write-Back capabilities to your Power BI reports with Power Apps - Part 1

Jon Stjernegaard Vöge — Mon, 23 Sep 2024 07:00:00 +0000

Guide: How to add Write-Back capabilities to your Power BI reports with Power Apps — Part 1

Introduction

Power Apps work great as a standalone tool for replacing your old spreadsheet with a more structured and cloud native data entry solution. However, what if I told you that:

Power Apps can be embedded into your Power BI Reports
Power Apps can display, and let user interact with, data coming from your Power BI report
Power Apps integrated in Power BI reports can be filtered by your users’ selections in Power BI
Power Apps can write data back to your Power BI data source, and create, edit and delete rows of data. Changes which can be shown instantly in the report!

In this article, I will cover how to get started with embedding Power Apps into your Power BI Reports, as well as provide a few examples of common use cases.

Getting Started with Embedded Power Apps

To get started with Embedded Power Apps, create a new Power BI Report in Power BI Desktop, or on the Power BI Service. Connect the report to your dataset, and create a simple visualisation. In this case, I’ve created a simple table.

TIP: If you started in Power BI Desktop, now is the time to Publish the file to your workspace, and open it in Edit Mode in the Power BI Service, as that will make it easier to author your Power Apps.

Next, insert a new Power Apps for Power BI visualization. This is a standard visualization type in Power BI. In order to send data from Power BI to Power Apps, drag any relevant fields from your Data Source onto the Fields list of your Power Apps visualization, just like you would with any other visualization in Power BI. You may choose to include Fields that you want to display and interact with, or any fields that you wish to be able to filter the App by. New fields can be added at any point, so don’t worry about forgetting any at this stage.

Once you start adding Fields to the visualisation, it should change design, and give you two options:

We are creating a new embedded Power App, and will as such select the “Create New” option. This prompts your browser to open up a new tab for the Power Apps editor, and it should show a simple app displaying a gallery with some of the data from your Power BI report displayed. This data is available in the App due to the PowerBIIntegration element that you can see in the Tree View of the app. This element can be called and treated like any other data source you would use in your Power Apps:

To test that all the building blocks are working as intended, create a new screen in your app. You can use a template, or create a blank screen. Just ensure that your new screen has a Gallery, and ensure that the ‘Items’ property of the gallery is populated with ‘PowerBIIntegration.Data’ (without the single quotes around it). This is the formula used to call and use the data coming from Power BI. Hence, try to add a new Text Label to your Gallery, and use ThisItem.YourFieldName as the Text property, using one of the Fields you added to the PowerApps visualization inside Power BI, in place of ‘YourFieldName’.

Finally, to test our very basic app, save and Publish the Power Apps, and jump back into Power BI. If you created your Report in the Power BI Service, make sure to save the report, before refreshing your browser page, otherwise you will need to recreate the report and/or Power Apps visualization as it will not save your changes. If you created your report in Power BI Desktop, you should publish the report to your workspace, to make testing of the Power App more seamless. After saving and publishing both the Power App and your Power BI Report, try refreshing the report page, and you should see your Power App render inside the report. Try out the interaction between Power BI and the Power App, by selecting a data point in your initial visualization, and check if the Power App is being filtered correctly.

Now you have all the basic building blocks for building Embedded Power Apps which interact with your Power BI Report. From here, you might modify your App to write new data to a data source, or even play around with having your App edit the data that your Power BI Report is based on.

Continue reading below for a few examples of Embedded Power Apps in practice.

Embedded Power Apps to power up report comment functionality

The Power BI Service does include comment functionality, but they are limited in functionality, and the comment data can’t be extracted and used elsewhere. A very simple Power App can be configured to write new comments to a data source, taking into account the filter context of your Power BI report, in order to better filter, display and use the comments for future analysis and decisions.

In this example, the Power App is used for creating, editing and displaying comments on Financial Statements, which are filtered by Department and YearMonth, to allow more granular commenting and analysis:

Embedded Power Apps with Direct Query connection to Power BI for live feedback

Another common use case is having Power Apps write data back to a data source like Dataverse or SQL databases, which allows Direct Query connection to Power BI. This makes it possible to visualize the data inputted with the embedded Power Apps live in the same Power BI report, increasing the potential for interactive reporting solutions.

Use case examples include Budget Engines, in which analysts may dynamically adjust input values to be picked up by complex calculations in Power BI, in order to test different forecasting scenarios. Or checklist scenarios, in which analysts can use the direct query functionality to check off items or assignments as they go along, dramatically enhancing the interactive potential of Power BI.

Summary

Data Entry requirements are notoriously difficult to satisfy with Self Service BI tools alone, but Power Apps integrated in Power BI reports provides a powerful and yet approachable way to get started. If you have questions or comments, you are always welcome to reach out.

Ingesting API data to Microsoft Fabric with low-code Dataflows: A Practical Example using the Strava API

Jon Stjernegaard Vöge — Wed, 18 Sep 2024 07:00:00 +0000

Ingesting API data to Microsoft Fabric with low-code Dataflows: A Practical Example using the Strava API

Introduction

Working with API data is often a task reserved for pro-coders. But in Microsoft Fabric, we can leverage the low-code Dataflows Gen2 to connect to any API and output the data to our Fabric Data Lakehouse or Data Warehouse.

The article below takes you through the entire process, step by step, of connecting to Stravas API, setting up automatic refresh of Authorization Tokens, Downloading and transforming all Athlete Activities, and outputting them to a Microsoft Fabric data destination.

Strava prerequisites

For this guide, I am using the Strava API, as it contains data that I as a hobby cyclist have a personal interest in extracting and visualizing. However, many of the principles described will apply to other APIs from applications you use. I may do a Spotify example in a future blogpost.

To get started with the Strava API, simply register an account or login to Strava.com. Then, go to Strava | API and create an App. Then extract the following pieces of information and keep them somewhere safe:

Client ID
Client Secret

Don’t bother with the Authorization Token and Refresh Token as we will get these programatically.

User Authorization

Next, you’ll need to Authorize your newly created app to access the data of one or more Strava users.

This is done by sharing the following link with the person(s) you wish to extract data from, and have them login using their Strava Account, and press Authorize:

https://www.strava.com/oauth/authorize?client_id={InsertYourClientIDHere}&response_type=code&redirect_uri=http://localhost/exchange_token&approval_prompt=force&scope=read,activity:read

After clicking ‘Authorize’, you will be redirected to a localhost URL, and you should now copy the ‘Code’ part of the URL displayed:

Example: http://localhost/exchange_token?state=&code=f97ce75c0113eba8926629369e5843242cc8e9be&scope=read,activity:read

Copy this part: f97ce75c0113eba8926629369e5843242cc8e9be

Note that this code is shortlived, and will need to be used within a few hours for proper API authorization. Luckily, we will do that right away with Postman.

Initial Token Exchange using Postman

Next, we need to do the initial Token Exchange by using the code we just retrieved. I prefer doing this with Postman, but you can use whichever tool you wish.

In Postman, create a new POST request, adding “https://www.strava.com/oauth/token" to the URL. Then add the following four parameters:

client_id: Add the Client ID you retrieved from your Strava API app
client_secret: Add the Client Secret you retrieved from your Strava API app
code: Add the code you got from the user authorization URL in the previous step
grant_type: Add the value “authorization_code”

In Postman, these parameters will automatically be added to your POST request URL.

With all the details inputted, press Send, and you should receive back a Status Code 200 “OK” response, with a JSON response that includes a Bearer + Refresh Token valid for the User in question.

Below you can see my sample request + result, retrieving a Bearer + Refresh token for my own Strava User Profile:

Write down the Refresh_Token and Access_Token in the same place as your Client ID and Secret. We will need it in the following step.

ALSO write down the Athlede ID, as we need that as well.

Creating our Dataflow + Setting up automatic Token Refresh

Now its finally time to jump into Microsoft Fabric.

First, find a suitable Workspace, and create a Warehouse or Lakehouse to be used as Data Destination for the Strava Data. Then, create a new Dataflow Gen2 to be used for the Strava API:

Next, we need to create a query in our Dataflow to refresh our Access Token, so that we don’t accidentally try to download data with an expired token.

In your Dataflow create a new blank query, and paste the following into the Advanced Editor:

    let
      URL = "https://www.strava.com/api/v3/oauth/token",
      Headers = [#"Content-Type"="application/json"],
      Body = "{""client_id"": ""InsertYourClientIDHere"", ""client_secret"": ""InsertYourClientSecretHere"", ""grant_type"": ""refresh_token"", ""refresh_token"": ""InsertYourRefreshTokenHere""}",

      Source = Json.Document(Web.Contents(URL, [Headers = Headers, Content = Text.ToBinary(Body)])),
      #"Converted to table" = Record.ToTable(Source),
      #"Filtered rows" = Table.SelectRows(#"Converted to table", each ([Name] = "access_token")),
      #"Drill down" = #"Filtered rows"{0}[Value]

      in
      #"Drill down"

The code above creates a Query to make a POST Request to the https://www.strava.com/api/v3/oauth/token URL just like we did in Postman, but this type the grant_type is “Refresh Token”, meaning we wish to refresh our access and get a new Access Token.

POST requests are notoriously difficult to work with in Power Query / Dataflows, but a few pointers if you find yourself working with them:

The difference between a GET and POST request in Dataflows / Power Query, is that a POST request will include a [Content = Text.ToBinary(InsertBodyHere)] parameter.
The Body of the Content parameter almost always needs to be wrapped in a Text.ToBinary() formula to be sent correctly to the API.
The Body should be formatted in JSON, which is also difficult to do. For this request, the syntax used is: “{“”parameterkey1"”: “”parametervalue1"”, “”parameterkey2"”: “”parametervalue2"”}”. Notice that all keys and values are wrapped in double quotes, and that the full curly bracket is also encased in quotes.. This is not a mistake, but very intentional, as this is the only format which will work

If you did everything correctly, the result should be a query which returns a new Refresh and Access Token, exactly like the Postman request. The difference is, that we no longer need that User Authorization URL code! Now we just need the refresh token, to get new access tokens.

Hence, we can call this query every time we wish to extract new data, to ensure we have the latest token:

Extracting Strava Data

It is finally time to extract some data from the API.

In my case, I want to retrieve Activity data for my user profile, as documented in the Strava API: Strava Developers

Downloading all my data involves some level of complexity, as the API includes pagination, allowing me to only retrieve 100 activities per API call. Hence, I first need to create one query with an API call to identify the number of Activities to download, and then another query to call each Page of Items to be downloaded from the API.

Number of Pages to download

First create a New Text Parameter, “AthleteID” and set the default value equal to the ID of the Athlete you authenticated earlier, which you wrote down after the initial token exchange.

Then, create a new blank query, “fxGetNumberOfPages”, and paste the following code:

    (Token as text) =>

    let
        RelativePath = "/" & AthleteID & "/stats",
        Payload = [RelativePath = RelativePath, Headers=[Authorization = Token]],
        Source = Web.Contents("https://www.strava.com/api/v3/athletes", Payload),
        JSON = Json.Document(Source)

        in
        JSON

This query is a reusable Function which will take an Authorization Token as input, and we will use that in another query.

Create another new query, “GetPages”, and paste the following code:


    let
      Source = fxGetNumberOfPages("Bearer " & GetRefreshToken),
      #"Converted to table" = Record.ToTable(Source),
      #"Filtered rows" = Table.SelectRows(#"Converted to table", each ([Name] = "all_ride_totals" or [Name] = "all_run_totals" or [Name] = "all_swim_totals")),
      #"Expanded Value" = Table.ExpandRecordColumn(#"Filtered rows", "Value", {"count"}, {"Value.count"}),
      #"Changed column type" = Table.TransformColumnTypes(#"Expanded Value", {{"Value.count", Int64.Type}}),
      #"Calculated sum" = List.Sum(#"Changed column type"[Value.count]),
      #"Divided value" = #"Calculated sum" / 100,
      #"Rounded up" = Number.RoundUp(#"Divided value"),
      #"Converted to table 1" = #table(1, {{#"Rounded up"}}),
      #"Added custom" = Table.AddColumn(#"Converted to table 1", "StartPage", each 1),
      #"Added custom 1" = Table.AddColumn(#"Added custom", "Pages", each {[StartPage]..[Column1]}),
      #"Expanded Custom" = Table.ExpandListColumn(#"Added custom 1", "Pages"),
      #"Removed columns" = Table.RemoveColumns(#"Expanded Custom", {"Column1", "StartPage"}),
      #"Changed column type 1" = Table.TransformColumnTypes(#"Removed columns", {{"Pages", Text.Type}}) in
    #"Changed column type 1"

This code will run the function we just defined, using the refreshed Access Token from ‘GetRefreshToken’ as the token input. The result is a JSON response from the API, which the subsequent steps in the query formats into a simple one column table, with one row for each page of items to be downloaded:

Downloading the pages

Create a new blank query, and paste in the following code:


    let
      Source = GetPages,
      #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(Source, "Token", each "Bearer " & GetRefreshToken), {{"Token", type text}}),
      #"Added custom 1" = Table.AddColumn(#"Added custom", "RelativePath", each "/activities?page=" & [Pages] & "&per_page=100"),
      #"Changed column type" = Table.TransformColumnTypes(#"Added custom 1", {{"RelativePath", type text}}),
      #"Added custom 3" = Table.AddColumn(#"Changed column type", "Custom", each Web.Contents("https://www.strava.com/api/v3/athlete", [RelativePath=[RelativePath], Headers=[Authorization = [Token]]])),
      CombineBinaries = Table.AddColumn(#"Added custom 3", "Custom2", each Json.Document([Custom]))
      in
    #"Added custom 3"

The query references the “GetPages” query to start from a one column table of pages to download. Then we add a column for the Access Token as well as a Column which constructs the Relative part of the URL.

Finally, the API GET request is performed by using Web.Contents(), and the contents are combined in one table:

I struggled quite a bit getting this to work well in Fabric. I was often able to get something working inside the Dataflow Editor, but most of the time the automatic refresh would fail. However, the above code, with separation of URL and Relative Path seems to work for me.

After a bit of transformation and filtering, you end up with a final table of all the athletes activities, and you can add your data destination of choice (in my case a Warehouse) before publishing your dataflow:

Summary

My Strava data now lives in my Fabric Data Warehouse, is automatically updated on a schedule, and can be used for any number of things. I can use it in my Semantic Models, create new Views and Queries, and use the data in Power BI reports as I please.

Working with APIs in Dataflows is not the easiest, and due to the editor sometimes being able to produce in-view results which are not achievable with a scheduled flow, they can even be hard to debug.

However, with a bit of persistence, trial and error, it is possible to use Dataflows to get data from any API into your Microsoft Fabric Data Platform.

4 ways to access data from Microsoft Fabric OneLake in Dataverse / Power Platform solutions

Jon Stjernegaard Vöge — Mon, 16 Sep 2024 07:00:00 +0000

4 ways to access data from Microsoft Fabric OneLake in Dataverse / Power Platform solutions

Introduction

I have written about accessing Dataverse Data from Microsoft Fabric, outlining how it is possible using:

Azure Synapse Link for Dataverse
Dataverse Link for Fabric
Dataflows / Dataflows Gen2
Power BI semantic models created in Power BI Desktop

But what if we wanted to go the other way around? What if we wanted to use data stored in OneLake as part of a Power Platform Solution?

There are several potential solutions to this, and this article will outline four different options, each with their pros and cons:

Using Virtual Tables in Dataverse to reference OneLake data
Using Dataflows in Dataverse to copy OneLake data
Using Data Pipelines in Microsoft Fabric to copy OneLake data to Dataverse
Using direct connectors in Power Apps / Power Automate to access OneLake data

Using Virtual Tables in Dataverse to copy OneLake data

The first option for bringing Fabric / OneLake data into Dataverse and your Power Platform solutions, is to reference the data using Dataverse External Tables, to query Fabric Lakehouses.

First, go to the Tables section of the Dataverse Environment that should access the data:

Create a New Table, select Virtual Table, and create a new Microsoft Fabric connection if you have not used the connection before (it will ask you to authenticate with your Windows Credentials):

Then select from the list of available workspaces in Fabric, and point to the Lakehouse that holds your tables:

Select the table you wish to query:

Configure Primary Key column (your table must have a unique column that can be used for this), and save the table:

The result is a Virtual Table in Dataverse which can be imported to your Power Platform solutions like any other Dataverse table:

Pros:

Data is not replicated, but queried.

Cons:

Warehouses are not supported.
Tables must have a unique column to be used as Primary Key.
A general list of Virtual Table considerations and limitations can be found in the Microsoft Documentation: Limitations and troubleshooting virtual tables with Power Apps — Power Apps | Microsoft Learn

Using Dataflows in Dataverse to copy OneLake data

Another option is to use Dataflows in Dataverse to ingest data from Fabric Lakehouses and Warehouses.

First create a new dataflow:

Choose SQL Server as your data source, and input the Connection String of your Fabric Warehouse or Lakehouse in the Server details (you can get this from inside Microsoft Fabric), and write the exact name of the Warehouse or Lakehouse as the Database name:

Now select the table you wish to import, apply any transformations and map the output to the destination table in Dataverse:

Publish the dataflow, and way for the refresh to go through. Now you can use the data in your Power Platform solutions just like any other Dataverse table!

Pros:

Allows transformation of data before landing in Dataverse
Supports both Warehouses and Lakehouses in Fabric

Cons:

Data is replicated and will take up storage in both Fabric and Dataverse
Data is not live and depends on refresh schedule

Using Data Pipelines in Microsoft Fabric to copy OneLake data to Dataverse

The third way is to use a Copy Activity in a Microsoft Fabric Data Pipeline to copy data from a Lakehouse or Warehouse in Fabric into a table in Dataverse.

In order for this integration to work, you need to first setup a Service Principal, and grant it access to your Dataverse Environment, so that your Data Pipeline can use it for authentication.

Step 1: Creating the Service Principal in Azure

First, go to portal.azure.com, search for ‘App Registrations’, and create a new Registration (this is your Service Principal / SPN).

Provide it a name, just stick with the default Account Type, and hit create.

After creation, go to the Overview Page of your App Registration, and note down the Application/Client ID and the Tenant ID. You will need those later.

Then jump to the API Permissions page, and add a new Permission.

Select / Search for Dynamics CRM, select the ‘user_impersonation’ permission and add it to your permissions.

Now go to Certificates and Secrets, and create a new Secret. Note down the Key in the same place as your Application ID and Tenant ID, as you will need this later as well. Note that once you exit the screen with the Key displayed, you will not be able to view the key again, and will have to create a new one to continue setup.

Step 2: Adding the Service Principal to your Power Platform Environment

Next, we need to grant the Service Principal access to the Power Platform Environment that we need it to authenticate against.

Go to the Power Platform admin center (microsoft.com), and under Environments, select the one you intend to use. From this screen, copy the Environment URL and store it with the IDs and Keys you copied from Azure. You will also need this later.

Under Access, select the button under ‘Users’, and on the Users page find the button to go to the App Users List.

In here, create a new app user, and select the Service Principal you created in Azure.

Assign it the required Security Roles. You may use custom roles, or for testing purposes you may select the System Customizer role, as it will ensure it has the necessary privileges.

Step 3: Creating the Pipeline and setting up the Copy Activity

Finally its time to setup the actual data copy. Create a new Data Pipeline in a workspace of your choice.

Add a Copy data Activity, and configure its source to the table you wish to copy into Dataverse.

Setup the destination of the Activity to point to a Dataverse Destination by choosing External, and selecting Dataverse.

While creating the connection, you will need all the four values we copied during Steps 1 and 2.

Insert your Environment URL as the Environment Domain.
Insert your Tenant ID as the Tenant ID
Insert the Application / Client ID as the Service Principal Client ID
Insert the Secret Key as the Service Principal Key

Select the destination table in Dataverse you wish to write to. If it is not created already, you will need to jump into Dataverse and create that now.

After pointing to the table, feel free to Preview the Data and test the connection, as well as selecting which Write Behaviour you wish, under Advanced options.

Finally, we need to map the Source Columns against the Destination Columns, under the Mapping tab.

Selecting Import Schema will attempt to automatically do this for you, but you may remove any columns you do not wish to include in the copy, as well as do any manual adjustments.

Note, that in case your table includes complex columns like LookUp columns, you may need to do some additional steps. In this case, I recommend following Scott Sewells excellent video on the topic: Fabric Pipelines for Dataverse Part 5: Populate the Lookups (youtube.com)

After mapping the activity, you can Validate, Save and Run your pipeline, and hopefully it succeeds:

With the data showing up in your Dataverse Table, as having been Created/Modified by your SPN:

Pros:

May be easily included with the rest of your Fabric Data Pipelines.
Good Performance.

Cons:

Requires you to enter Azure to configure SPN — which not everybody has access to.
May be difficult to setup if your table includes complex columns, e.g. LookUp columns.

Using direct connectors in Power Apps / Power Automate to access OneLake data

The last option is to skip the step of populating a Table in Dataverse which the previous methods take advantage of, and instead connect directly to the SQL endpoint of your Fabric Warehouse and Lakehouse from within Power Apps / Power Automate.

In Power Apps, simply add a SQL Source Connection:

And point it towards the same SQL Connection string + database name as mentioned in the Dataflows option above, before choosing the tables you wish to add:

The data can now be used in Power Apps controls, but do note that Power Apps does not allow you to write data directly to a Fabric Data Warehouse, despite the endpoint being both Read and Write enabled. This is due to the lack of enforced primary keys in the Warehouse. (Read more in my blog about write-back to Microsoft Fabric: https://downhill-data.com/2024/05/07/live-data-write-back-to-fabric-data-warehouse-from-power-bi-w-power-apps-and-power-automate/):

In Power Automate its a similar story. Simply add an action that leverages the SQL connector, and connect using the Server connection string and Database name. Here I am using my Power Automate write-back workaround by using the SQL connector in Power Automate against a Fabric Warehouse:

Pros:

Data is always queried live from the Fabric data source.
Data is not replicated.
Supports both Warehouses and Lakehouses in Fabric

Cons:

Power Apps functionality is limited due to lack of enforced Primary Keys (while Power Automate has no issues with that).
Data never lands in Dataverse, making you unable to leverage Dataverse for any additional functionality. You have to build any transformations/logic locally in the solution you are developing.

Summary

I hope this post has given you inspiration for different ways to access data from Fabric and OneLake in your Power Platform solutions.

Here I outlined four possibilities, and I am sure that there are more out there. Do you think I missed an obvious one? Please drop me a comment or message — I would love to hear from you.

5 ways to get your Dataverse Data into Microsoft Fabric / OneLake

Jon Stjernegaard Vöge — Fri, 13 Sep 2024 07:00:00 +0000

5 ways to get your Dataverse Data into Microsoft Fabric / OneLake

Introduction

Microsoft Fabric is top of mind everywhere, and its promises for improving the analytic capabilities of Power BI users are huge. However, integrations and compatibility between Fabric and the wider Power Platform (e.g. Dataverse, Power Apps) is not seeing the focus it warrants.

For starters:* How can I leverage data currently stored in Dataverse, in my Microsoft Fabric setup?***

When Fabric released in 2023, in terms of automatically syncing data, the existing ‘Azure Synapse Link’ was our only solution for this challenge. Later in 2023 however, came the simpler ‘Dataverse Link to Fabric’. and most recently in 2024, it was made possible to create ‘Shortcuts to Dataverse’ from Lakehouses in Fabric.

In addition, we have approaches such as using Dataflow Gen2 to do scheduled loads of data from Dataverse to a Fabric Destination. And finally the Semantic Model Integration to OneLake which I covered last week on this blog, also allows for a scheduled load from Dataverse to OneLake.

But what are the strengths and weaknesses of each approach?

This article shares and compares the step-by-step process for each of the five approaches to, hopefully aiding you in selecting the right method for your case.

Note, that each section pretty lengthy and full of screenshots. Feel free to jump to the final section for a quick comparison between all five.

Solution 1: Azure Synapse Link

Dataverse has long supported Azure Synapse Link for Dataverse. However, by leveraging the new Fabric OneLake Shortcuts, we can use the old Synapse Link for Dataverse to transport data into an Azure Data Lake Gen2, and subsequently the OneLake shortcut for ADLS Gen2 to make said data available in OneLake / Fabric.

The steps we need to take to make this happen are as follow:

Setup the necessary Azure Resources for the Synapse Link (Subscription, Resource Group, Storage Account/ADLS Gen2)
Prepare Dataverse Tables and establish Azure Synapse Link between the Tables and ADLS Gen2
Create Shortcut in your Fabric/OneLake Data Lakehouse to the Dataverse Tables

The following sections will take you through each step.

Step 1: Azure Resources

In this step we will create the following Azure Resources, and note down a couple of important links and values:

A Subscription
A Resource Group
A Storage Account (ADLS Gen2) including its URL Endpoint.
An App Registration with correct roles and permissions on the Storage Account (+ a Client ID & Secret for authentication).

Follow the steps below to create the resources:

First head to portal.azure.com, and if you do not have a subscription already, create one and connect to a billing account.
Second, create a Resource Group, linking it to your Subscription.
Third, create a Storage Account, linking this to your Subscription and Storage Account. Also make sure to find the Advanced Options, and toggle on ‘Enable Hierarchical Namespace’. This ensure that the Data Lake being created is Gen 2!

Fourth, go to your Azure Active Directory, open App Registrations, and create a new registration, making sure “Accounts in this organizational directory only” is selected as supported account type.

After creating the App Registration, open it, and from the Overview Page, write down the Directory (tenant) ID and the Application (client) ID for later use.

Then go to Certificates & Secrets of the App Registration to create a new Secret for authentication. Write down the Secret Value, for use later on.
Now go back to your Storage Account, and into ‘Access Control (IAM)’. Here, add a new Role Assignment for your newly created App Registration, giving it the Roles “Storage Blob Data Contributor” and “Storage Blob Data Owner”.
And finally, open up the “Endpoints” menu of your Storage Account, and write down the Primary Endpoint for later use.

Step 2: Prepare Dataverse Tables and Create Azure Synapse Link

After creating all the necessary Azure Resources, we can prepare our Dataverse tables and setup the Azure Synapse Link.

First go to make.powerapps.com and identify the Tables which you want to sync to your Data Lake.

For each table, open up the table settings, go to properties, expand the Advanced Options, and make sure “Track Changes” has been enabled. If you do not do this, your table will not show up in the Synapse Link.

Now go to the Azure Synapse Link page, by selecting it from the navigation panel on the left hand side. If the option is not there, select More > Discover All and find the option in the bottom right side of the page under Data Management.

Create a New Link, and select the Subscription, Resource Group and Storage Account that you created during Step 1.

Next, Add the Tables which you want to sync to your Data Lake. Note that only tables which have “Track Changes” enabled will show up here.

Also pay attention to the “Advanced” tab, in which you can customize the frequency of refresh taking place, down to a minimum of 15 minutes.

And you should now be able to see an overview of your Synapse Link and the tables being synced to the Data Lake:

Similarly, you should be able to go to your Storage Account in Azure, find the Storage Browser, and see that the tables are now being synced and are available within your Data Lake.

As a final act in Step 2, please write down the string of the subfolder in which your Dataverse Tables have been uploaded. In my case, it is the “dataverse-60xtthdefault-unq…….” string.

Step 3: Create Shortcut in your Fabric/OneLake Data Lakehouse to the Dataverse files in the Data Lake

Our final step on the journey, is to create a shortcut from our OneLake Data Lakehouse to the ADLS Gen2 folder containing our Dataverse data.

First, go to app.powerbi.com, and if you haven’t already, create a Fabric-Enabled workspace, and create a Data Lakehouse within the Workspace. Microsoft has published an excellent guide to get you started on this.

Now open up the Data Lakehouse and click Files > New Shortcut

From here, choose Azure Data Lake Storage Gen2

In the Connection Settings, point the URL to the ADLS Gen2 ‘Primary Endpoint’, which you wrote down as part of step 1.

For Connection Credentials, you may, depending on your settings, be able to connect with your Organizational Account, but otherwise select Service Principal as ‘Authentication Kind’, and fill in Tenant ID, Client ID and then your Secret Value as the Service Principal Key, all of which are values you wrote down during Step 1.

Finally, give the Shortcut an appropriate name, and then provide it with the Subfolder string which you wrote down as the very last thing in Step 2.

You should now be able to use the Shortcut from the navigation menu of your Lakehouse, and query the copies of your Dataverse data directly from within your Notebooks.

Solution 2: Dataverse Link to Fabric

Prerequisites

The first thing you need to do, is to check the following off your list:

You must have the System Administrator security role in the Dataverse environment.
You must have a Fabric Capacity, Fabric Trial Capacity or a Power BI Premium Capacity available.
You must be an administrator of the Power BI workspace you want to link to, and the workspace should be allocated to one of the capacity types mentioned above.

Then, in addition, you may or may not need to be aware of some Azure Region details regarding your Fabric and Dataverse Envronments.

Up until a few weeks ago, it was a hard requirement that the regions of your Fabric Workspace and Dataverse environments were the same, else the integration would not work. However, rumour has it that this restriction has been lifted:

If for some reason the feature has not yet been rolled out for your region yet, I would be patient and wait a few weeks.

Setting up Dataverse Link for Fabric

With the prerequisites out of the way, it is time to setup the actual Dataverse Link for Fabric.

First, go to the Tables area of your Dataverse environment, click Analyze at the top, and Link to Microsoft Fabric:

Set up your Connection by inputting your credentials, and also choose the workspace (or create a new) that you wish to use as destination for the Lakehouse which will be created.

After finalizing the setup, your Lakehouse and/or Workspace will be created and opened for you:

And the Dataverse Link for Fabric will be accessible from the Azure Synapse Link page in your Dataverse Environment:

At first the Lakehouse will have to perform an initial load:

If even after the initial load, some or all of your tables end up in a weird “Unidentified” folder, you may open the Dataverse Link for Fabric in the Azure Synapse Link page, and click “Refresh Fabric Tables”, which will refresh the metadata in the Link. On this page, you can also monitor the status of the tables being synced:

Finally, with a bit of wait time, you should be able to see actual delta tables appear in your Lakehouse, which you can then use in Fabric as you would any other Lakehouse table. Use it in a Semantic Model. Shortcut it into other Lakehouses. Run a notebook. Whatever you please!:

Note that what ahs happened under the hood, is that new Delta Tables have been created in a Dataverse Managed Lake, which reference the original Dataverse data. That means that this solution consumes Dataverse Storage as well!

Solution 3: Shortcut to Dataverse

The Mad March of 2024 brought the announcement that Lakehouses in Fabric would be able to create shortcuts directly to Dataverse: Analyze Dataverse tables from Microsoft Fabric | Microsoft Fabric Blog | Microsoft Fabric

However, to my disappointment, this seems to be more or less a small additional functionality that builds on top of Solution 2, more so than offering a true alternative.

What is new, is that from the Lakehouse, once selecting Shortcut, you are now able to select Dataverse as an option:

However, as described in the documentation, in order for you to create Shortcuts to Dataverse from a Lakehouse in Fabric, you need to:

Have System Administrator privileges on a Dataverse Environment that resides in the same Azure geographic region as your Fabric Capacity Workspace
Enable Link to Fabric from said Dataverse Environment

But wait… Wasn’t that essentially the same as Solution 2? Yes, indeed.

And won’t that process already automatically populate a Lakehouse in Fabric with all my Dataverse tables, making them available for me to Shortcut into other Fabric Lakehouses? Yes. Indeed.

The new thing here is really, that after setting up the Link to Fabric on an environment, we can now access the tables from the underlying Dataverse Managed Lake directly through the Shortcut wizard in the Lakehouse, and we can do that freely from any Lakehouse in any Workspace.

Instead of having to work with that auto generated Lakehouse from the ‘Link to Fabric’ functionality, we can consider that setup as only an administrative task. After the initial setup is done, any new Shortcuts you make to Dataverse can be made much cleaner than previously possible. You can pick and choose which column to select from your environments:

And you can even Shortcut multiple tables from separate environments into the same Lakehouse:

AND you can even cross-query between these shortcutted tables with ease:

In short, Shortcuts to Dataverse doesn’t make the initial setup any easier than before, but after setting up, it’s smooth sailing from there, and very easy to perform cross Dataverse environment analysis in Fabric.

Solution 4: Dataflow Gen2

Dataflow Gen2 is the low-code tool of choice for Data Ingestion in Microsoft Fabric. The process for ingesting Dataverse data with Dataflows is straightforward:

You first create a new dataflow, and use the Dataverse Connector to load data from your Environment, selecting which tables to load:

If you wish, you may transform the data, before selecting a data destination to output the data to, with both Fabric Lakehouses and Warehouses being among the options. You are able to both Append and Overwrite destination tables, which are persisted as Delta Tables in OneLake:

Finally, you may setup a refresh schedule on the Dataflow to automate the process:

Solution 5: Semantic Model Integration to Fabric

The process below is the same that I walked through in my blog from last week. You first need to enable two Tenant Settings in your Fabric Admin Portal:

Next, you can connect to a Dataverse table from Power BI desktop, and if you wish, you can use the Power Query Editor to transform the incoming data. After you are done, you publish the model to your Fabric Enabled workspace:

Opening the Model from the Workspace, you go into the settings, and enable the OneLake Integration setting:

After applying the new setting, data will be automatically ingested into your OneLake when your Semantic Model is either refreshed manually or by a schedule.

The data can subsequently be accessed by creating a Shortcut within a Lakehouse:

Or even by browsing the files using the OneLake explorer on your desktop:

If I go ahead and alter a few values in the table in Dataverse (in this case, deleting a value, and editing a value):

The updated values show when I query the shortcutted Lakehouse Table, but only after a refresh of the Semantic Model has occurred:

Under the hood, the update is simply performed by creating new Delta Tables, and deleting the old ones. There is no time traveling and retention of old files with this integration:

The last thing you need to do to automate the process, is to schedule the automatic refresh of your Semantic Model, which is done from the Semantic Model settings, after which your OneLake data will be overwritten each time the model is refreshed.

A super simple and clean setup for those who are used to working with Power BI and Semantic Models.

Comparison remarks and considerations

Storage and Cost:

Solution 2 and 3 does not move any data from Dataverse into Microsoft Fabric. Instead what happens, is that Delta Tables are created *in *a Dataverse Managed Lake, which counts towards Dataverse Storage.

This also means that you will pay Dataverse Storage costs for these additional Delta Tables, which can be a costly affair. If this is a concern (e.g. you wish to move large quantities of data), you should consider the Azure Synapse Link for Dataverse approach instead, which instead moves your data to a Data Lake Gen 2 in Azure that you have to manage yourself. Here you have more control over all costs incurred during the process.

For the Dataflow and Semantic Model, you will “pay” in terms of Capacity Units for your consumption, which I dive into the details of in last weeks blog. In general though, with my limited testing, Dataflows seem more expensive than Semantic Models in terms of Capacity Units.

Flexibility of process:

With regards to how flexible the process is, Azure Synapse Link, Dataflow Gen2 and Semantic Model Integration provides the greatest amount of flexibility. You can choose for yourself which data to sync, and even perform transformations along the way if you desire.

On the opposite side, you are not able to have any say in which tables to Sync from Dataverse to Fabric in Solution 2 and 3.

Update Frequency:

The Dataverse Link for Fabric has no option for customizing refresh frequency, and instead seems to run on a fixed 1 hour schedule, meaning that you will only achieve near real time data sync to Fabric/OneLake with Solution 2 and 3. For Dataflows and Semantic Models, you can schedule a refresh up to 48 times a day, bringing that down to a delay of 30 minutes.

Now this is obviously enough for many reporting scenarios, but it will not work as a way to push data Live from Power Apps / Automate to Fabric. In these cases, you will need to write data directly to Fabric instead.

If you need even more frequent refreshes than every hour, again consider the Azure Synapse Link for Dataverse in Solution 1, which through advanced options may be scheduled to run every 15 minutes.

Complexity:

Not all of these approaches are equally straight forward.

The high Flexibility of Azure Synapse Link come at the expense of a complex setup, if you want full control over your sync. You’ll need to dive into the world of Azure — and that can be a complex place to be. To a lesser extent the same is true of Dataflows and Semantic Models, which require some set up, maintenance and administration, and familiarity with Power BI/Query and the Service.

Conversely, the Dataverse Link and Shortcut to Dataverse is painless, after you get the few requirements out of the way. You click, click, click and you’re done, and able to see data being synced hourly.

Summary

And with that overview, I would like to close this way too long blog post, which has been a long time in the making, as the Fabric team kept inventing new ways to integrate Dataverse and OneLake. Let’s see how long it takes before this blogpost is outdated.

Did you like the tutorial & comparison? Do you have any other cool ideas for integrating Power Platform and Fabric? Please reach out in the comments, or directly to me.

My Experience and top tips for the Fabric Analytics Engineer Associate Certification (DP-600)

Jon Stjernegaard Vöge — Wed, 11 Sep 2024 07:00:00 +0000

My Experience and top tips for the Fabric Analytics Engineer Associate Certification (DP-600)

If you want to showcase your Microsoft Fabric prowess, DP-600 is the certification you are looking for. With a syllabus covering data analytics, data engineering, platform management and enterprise development best practices, it is indeed a test worthy of Analytics Engineers.

Personally, I felt this was the hardest Microsoft Exam I’ve taken to date, due to the wide range of topics covered, but with a few good learning resources and tips & tricks, I managed to walk out of the exam with a score of 921 out of 1000. Plenty more than the 700 needed to pass.

Below, I’ve put together a small cheat sheet of learning resources that I used, tips & tricks for the exam, as well as some of the topics which I was surprised to face.

Learning Resources before the Exam

Learning Path — Your point of departure should be the official Microsoft Learning Path. It is especially great at introducing general concepts of Microsoft Fabric, while it is perhaps less good at guiding you towards specific knowledge that may help you answer exam questions: Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric — Certifications | Microsoft Learn
Microsoft Lab Exercises — Nothing beats Hands On experience. And while I’d always recommend true project experience over any other resource, the online Lab Exercises created by Microsoft are frankly a great substitute. They are scattered around the Learning Path, but worth visiting on their own. Check them out here: mslearn-fabric (microsoftlearning.github.io)
Microsoft Practice Assessment — Microsoft have their own practice test which allows you to try your best on 50 sample questions (which are not 1:1 equal to actual exam questions, but come pretty close!). Also, the pool of sample questions is greater than 50, so be sure to retake the test once or twice to see more of them. Check it out here: Practice Assessment | Microsoft Learn
Pragmatic Works — I am by no means affiliated with Pragmatic Works but found that their CertXP platform had an incredible range of questions which were very similar to the ones on the exam itself. However, the most impressive part of the platform is the thorough explanations that follow each answer. I can only recommend you try it yourself.

During the exam

Look for patterns in the answers — Multiple Choice tests will always possess some degree of predictability, and that goes for DP-600 too. In many cases, especially those that ask you to pick the correct line of code, I found that of the four different choices, there would usually be two options which would be almost identical, a third which was quite different, and a fourth which was completely different. In my experience, the correct answer was always one of the two options which were almost identical.
Use Microsoft Learn during the exam! — As of September last year, all role based exams have access to Microsoft Learn from the exam environment. I used it to search for definitions I had no clue about (DMVs — I’m looking at you!), I used it to validate answers where I was only 80% certain, and I used it to disqualify some answer options for those questions where I knew I had to submit a complete guess anyway.
Read Case Questions before reading the Case — The exam, like all other role based exams from Microsoft, includes a case study that requires you to read quite a bit of text material. However, only a fraction of the text in the case is actually related to the questions that you receive (the pool of questions is much larger than the 6–8 questions you will see). Read the questions first, and then scan the case for the relevant information afterwards.
Questions are not fully up to date — Updates to Microsoft Fabric are coming out faster than a horse can run (as we say in Danish). And some of the questions on the exam are already outdated. They may not be plain wrong, but may be missing an otherwise obvious choice as the answer. As an example, the recent additions to allow RLS editing directly in the semantic model web authoring experience, was not a possible answer option during RLS questions on the test.

Topics which took up a larger part of the exam than I expected

DMVs (Dynamic Management Views) and how each view could be used.
Query Folding, and how to tell which steps are folding and which are not. Especially using the Dataflow web experience, not the Power Query desktop experience.
Data Profiling tools in dataflows (again using the web experience UI, not the desktop Power Query UI), with very specific distinctions between Column Quality, Distribution and Profile.
External tools, including Tabular Editor, ALM Toolkit, DAX Studio, Best Practice Analyzer and Vertipaq Analyzer.
PySpark code snippets. Both “fill in the blanks” type questions, and ‘pick the correct syntax from these examples’ type questions. Especially surprised to see that not only data transformation but also data exploration with PySpark saw quite a bit of emphasis during the test.

Armed with the above, and a bit of studying, I’m sure the certification is within reach for you as well!

Do you have any other tips & tricks to share with fellow Microsoft Data Professionals? Feel free to share them in the comments.

Row Level Security in Microsoft Fabric — Update: No need for Tabular Editor!

Jon Stjernegaard Vöge — Mon, 09 Sep 2024 07:00:00 +0000

Row Level Security in Microsoft Fabric — Update: No need for Tabular Editor!

Introduction

In March, I wrote about How to setup RLS in Microsoft Fabric / Power BI without breaking DirectLake functionality — Downhill Data (downhill-data.com)

Back then, RLS was not possible to edit through the Web Authoring experience of Semantic Models in Fabric. Instead, you had to go the roundabout way of using Tabular Edit to add RLS rules to your model.

However, just a few weeks after my post, an update to the Web Authoring experience was added, which now adds the missing RLS functionality, eliminating the need for Tabular Editor in the solution.

Therefore, I have revamped the RLS guide to leverage the new interface. Check it out below!

Creating a Semantic Model with an RLS Table

The first step is to create a new Semantic Model on top of your Data Lakehouse or Data Warehouse, in order to have a Semantic Model that uses Direct Lake.

Below I have done just that, on top of some dummy Taxi data. First I created a Data Warehouse, then I imported the Taxi Data Sample, and finally I created a Usertable with a SQL statement, populating it with a few rows of data to use in RLS:

Subsequently, I created a new Semantic Model, including all the tables:

Notice how the “Manage Roles” button is no longer greyed out at the top? That is the recent addition to the web authoring experience that makes setting up RLS easier.

But before we get that far, we need an additional step.

Setting up the Fixed Identity

Before setting up RLS rules, we need to set up a Fixed Identity to be used instead of the end-users credentials, when authenticating against the data model.

Open the settings of your newly created Semantic Model:

Find the Gateway and cloud connections tab, and create a new connection for your data source:

Create the cloud connection (the Server and Database should be filled out automatically), and select OAuth 2.0 as authentication method:

Back in the semantic model settings, map the data source to the newly created connection:

Congratulations! You have mapped your semantic model to a fixed identity, which will now be used when users access reports built on your model!

However… This Fixed Identity, using your OAuth credentials, will have access to all of the data in your model that you have access to. Hence, we need to setup our RLS rules.

Setting up RLS on the Semantic Model

Once again, open up your data model in the web authoring experience, and this time, select the “Manage Roles” button at the top. This brings out an interface familiar to any Power BI Desktop user:

Simply use this interface to setup your Security Roles, adding any filters to the Tables required. If you prefer the old DAX editor in place of the modern conditional data filtering interface, it is easy to swap between the two.

After adding your Roles, you can further go to the ‘Assign’ tab at the top, to add users and AD groups to your roles:

Press the Save button, and you’re done! That’s all there is to it! No more need for Tabular Editor for this case.

If you wish to, you can still open the Security Settings of your Semantic Model to test the rules you’ve made. Just right click on the role in the menu, and select Test As Role, to test if you set up everything correctly:

And in my case, my report went from this:

To a filter only showing data for Pennsylvania:

I hope you enjoyed this small update. I for one welcome any implementation that makes development in the browser easier.

Exploring the new Dynamic Subscriptions for Power BI Reports

Jon Stjernegaard Vöge — Fri, 06 Sep 2024 07:00:00 +0000

Exploring the new Dynamic Subscriptions for Power BI Reports

Introduction

Dynamic Subscriptions for Power BI were recently released as a Preview Feature in the Power BI Service, allowing for distribution of personalized per recipient reports.

The feature is explained in some depth in the official Documentation & Power BI Blog, but when playing around with the feature I came across both a few easily overlooked features, as well as some potential use cases that I failed to setup correctly.

The rest of the article below summarizes my exploration of the feature, both in terms of what works and what does not work in the current Preview state.

Setting up Dynamic Subscriptions

Dynamic Subscriptions are set up from the same place as the regular subscriptions — by opening a Power BI Report either from within its Workspace, or from its Workspace App, and clicking the “Subscribe to Report” button:

Opening Dynamic per Recipient allows you to connect to a published Semantic Model, from which you can selects fields to be used for dynamic content. Currently, you can dynamically control:

Email Address of the recipients
Email Subject for each recipient
Report Filters for each recipient
Attachment Filetype for each recipient

This means, that you for example can distribute the same report to 10 different recipient emails, each with customized Report Filters, and potentially letting some recipients receive PDFs and others .pptx files. Note that there is no way to manipulate the data that you pull into the Dynamic Report, and everything need to be modeled beforehand. You are however not limited to connecting to the same Semantic Model as the one used for your report.

In my example I’ve populated a barebones UserTable in my DataWarehouse, with two recipients each belonging to a “State” value in the Geography Dimension of my data model, but this could of course be your regular User or dynamic RLS table:

Afterwards, you setup the basic email details, mapping the recipient to the email field you imported:

As well as adding your dynamic filters and setting up a schedule for the subscription. Here I am filtering so that each User will only see data relevant to the State that they have access to in the Usertable:

After reviewing, the Subscription is easily edited, activated/deactivated, or tested from the subscription overview:

An overview of Subscriptions may also be found in your personal settings in the Service:

Testing the Subscription shows how emails and attachments may look:

And in this case, only Pennsylvania data is shown to the recipient of the report.

Exploring Filter possibilities and limitations

So that was the basics out of the way. Next up, I set out to do a couple of tests, trying to test the limits of the filter functionality.

Measure Filters

First I tried creating a Measure in my Semantic Model, and try to use that as a filter condition. I first tried creating a measure for Today() to dynamically filter by today’s date:

Unfortunately my test failed. I subsequently tried creating a Measure to filter for only “Friday” records, and also tried a numeric measure filter, both tests which failed as well.

It appears as if it not currently possible to use Measures in filters, despite the ability to select them in the data selection menu. If you have figured out a way to do so, please do drop me a message! I suspect they may be possible to use in dynamic headers in the subscriptions, or something similar.

Column Filters

Column filters on the other hand. Now they work great! In my test, I tried both creating a date column in the Data Warehouse for Today’s date, as well as a Text column to filter for State. These tests succeeded, and the result is reports which filter correctly for the supplied values:

Measure Filters

Applying Report filters was also a success. In this example I added a Page Level filter for Month = October as the only filter, and the results were indeed only October values:

Visual Filters

And finally, it is possible to use the visual interaction between visualisations and apply those to the subscription. Meaning that filters set in slicers, or values highlighted in visuals, will trigger a new option ‘Pre-Existing filters’ being available in the ‘Map your data’ menu:

Limitations and Oddities

The feature is in preview still, and there are some limitations to the functionality still. The Measures as Filters issue described above is one. Another is the current cap of 50 separate recipients, listed in the official documentation.

Hence, if you need more than 50 recipients, the good old Power Automate Export To File is still your only option: Export and email a report with Power Automate — Power BI | Microsoft Learn

Another weird bug I encountered is that some of my Dynamic Subscriptions, started sending themselves twice at the same time, seemingly without consistency. I couldn’t quite isolate when it would happen:

Summary

Dynamic Subscriptions for Power BI reports are to me a welcome feature, and may in time (once recipient limitations are liberated) be a worthy substitute for current Power Automate subscription flows.

The ability to allow the reports to propagate visual level filters to the attachment reports in the subscription is a cool feature, which may help highlight specific insights in key reports.

I’m curious to see if people brighter than me will be able to make even better use of the dynamic filtering options, and potentially find a way to leverage Measures as well.

How to setup RLS in Microsoft Fabric / Power BI without breaking DirectLake functionality

Jon Stjernegaard Vöge — Wed, 04 Sep 2024 07:00:00 +0000

How to setup RLS in Microsoft Fabric / Power BI without breaking DirectLake functionality

Introduction

RLS in Microsoft Fabric is a convoluted topic which at the time of writing, is not very clearly spelled out for users.

Most documentation refers to setting up RLS on Data Warehouses or Data Lakehouses by applying permissions directly on the tables: Row-level security in Fabric data warehousing — Microsoft Fabric | Microsoft Learn

However, to the frustration of many Power BI developers, this breaks the coveted DirectLake functionality, making the storage mode fallback to DirectQuery, leaving us unable to leverage those sweet performance gains.

However, what if I told you that there is a way to implement RLS without breaking DirectLake? Actually, you just need to do it the old fashioned way: Apply RLS directly on the Semantic Model, but with a twist involving fixed identities. A process which can only be done using external tools.

Read on to learn how!

Creating a Semantic Model with an RLS Table

The first step is to create a new Semantic Model on top of your Data Lakehouse or Data Warehouse, in order to have a Semantic Model that uses Direct Lake.

Subsequently, I created a new Semantic Model, including all the tables:

Notice how the “Manage Roles” button is greyed out at the top? That is the cardinal issue that most people face when setting RLS up on Semantic Models in Fabric.. They simply get to this point and then stop.

But there is a way!

Setting up the Fixed Identity

The first thing we need to do, is to set up a Fixed Identity to be used instead of the end-users credentials, when authenticating against the data model.

Open the settings of your newly created Semantic Model:

Find the Gateway and cloud connections tab, and create a new connection for your data source:

Create the cloud connection (the Server and Database should be filled out automatically), and select OAuth 2.0 as authentication method:

Back in the semantic model settings, map the data source to the newly created connection:

Congratulations! You have mapped your semantic model to a fixed identity, which will now be used when users access reports built on your model!

However… This Fixed Identity, using your OAuth credentials, will have access to all of the data in your model that you have access to. Hence, we need to setup our RLS rules.

Setting up RLS on the Semantic Model

As you saw previously, we are unable to create security roles directly in the browser. And unfortunately, creating a report in the browser based on the semantic model, downloading the .pbix and trying to setup roles in there, does not offer better results:

Instead, what we need to do is use External Tools against the XMLA endpoint of the workspace, to manipulate the semantic model. In this case, we will use the free external tool, Tabular Editor 2: Tabular Editor 2.x | TabularEditor

Go to the Premium tab of your workspace settings, and retrieve the connection string shown there. This is your XMLA endpoint for the workspace in question:

Next, Open Tabular Editor, and connect to a tabular server, providing the copied workspace connection as the server:

After authenticating, select your semantic model:

And finally, we can set up our RLS roles. Right click the Roles folder, and create a new role. Scroll down to Row Level Security, and apply your RLS rule of choice. In this case, I am implementing a simple DAX filter on my Geography dimension:

After setting up your rules, scroll further down and make sure to change the Model Permission value from None to Read. Else, you will not be able to publish your changes to the semantic model.

Another issue you may encounter when saving your changes, is that it complaints about Read/Write permissions on your tenant. In order to save and publish the changes to the model, you need to ensure that your Capacity Settings in your Fabric Tenant Settings allow both Read AND Write to XMLA Endpoints. In my case, I had to go and make this adjustment:

Now you can finally save and publish your model changes to the service. The next step is to open the Security Settings of your semantic model, and add members to your newly created roles:

Right click on the role in the menu, and select Test As Role, to test if you set up everything correctly:

And in my case, my report went from this:

To a filter only showing data for Pennsylvania:

And that’s all there is to it!

With this RLS setup, your report will always use your credentials as a fixed identity for accessing the Lakehouse/Warehouse underneath your semantic model. This in turn warrants you to set up RLS via Tabular Editor, to ensure that no security issues occur, but has the added benefit that DirectLake functionality is intact when consuming the report.

How to create Measure Tables in Microsoft Fabric

Jon Stjernegaard Vöge — Mon, 02 Sep 2024 07:00:00 +0000

How to create Measure Tables in Microsoft Fabric

Creating Measure Tables in Microsoft Fabric Semantic Models

Measure Tables have long been used to store Measures in Power BI Semantic Models.

However, in Microsoft Fabric, where most of the data modeling takes place in the browser, at least if we wish to reap the benefits of the DirectLake storage mode, the process of creating Measure Tables are slightly different.

Step 1: Creating an empty table

First you need to create an empty table. Or rather, a table with one column in it (a table can’t have zero columns in Fabric), but you should not add any data to this table.

If the Data Lakehouse is your weapon of choice, you may use a Notebook to do so with Python:

data = [(1, 'MeasureTable')]
columns = ['ID', 'Col1']

measure_df = spark.createDataFrame(data, columns)
measure_df.show()

spark.sql("DROP TABLE IF EXISTS MeasureTable")
measure_df.write.format("delta").saveAsTable('MeasureTable')

If you are using the Lakehouse SQL Endpoint you can create an empty view, or if you are using Data Warehouse, create a table with SQL:

Step 2: Pulling the table into your Semantic Model

The next step is to create a Semantic Model on top of your Warehouse or Lakehouse:

And subsequently, add a couple of measures, before finally hiding the only column present in the table, to convert it into a Measure Table:

And there you have it! It is that simple to create centralized measure tables in your Microsoft Fabric semantic models. Go build!

What is using all of my Microsoft Fabric Capacity? A quick guide to installing and using the Fabric Capacity Metrics App

Jon Stjernegaard Vöge — Sun, 01 Sep 2024 20:33:02 +0000

What is using all of my Microsoft Fabric Capacity? A quick guide to installing and using the Fabric Capacity Metrics App

Introduction

Are you experiencing throttling or perhaps even outages in your Microsoft Fabric Capacity? Are you confused why your Capacity is maxed out, or perhaps wish to explore your usage deeper?

Then you have come to the right place.

This brief article will show you how to install the Fabric Capacity Metrics App, and give you some quick pointers for navigating it and understanding the insights it provides.

Installing the Fabric Capacity Metrics App

To install the Capacity Metrics App, you need to follow a few steps:

Install the App by going to app.powerbi.com, find the Admin Portal and the Capacity Settings, and click “See usage report”:

From the App page, select ‘Get It Now’:

After installing, open the report and click ‘Connect’:

Find the Capacity ID in your Capacity Settings, and input it in the Connection menu:

Finally, add data source credentials to the installed app’s data model, and let the report refresh:

Using the Capacity Metrics App

With the app installed, its time to have a look at the insights it might assist you with. The App is split into multiple pages. From the main navigation, You’ll have access to the Compute and Storage pages, but I find that some of the most impactful insights are the ones hidden in the drill through pages. Read on below for a small tour.

Compute Page

The Overview page provides an overwhelming amount of information. The way I use the page is as follows:

Am I leveraging the full extent of my Capacity, and what types of processes are driving the usage?

The top right ‘CU over time’ chart in this case shows that I am only using about 1% of my Trial F64 SKU. I even had to switch the diagram to Logarithmic Y-Axis scaling to see anything in the visualisation.

The chart also clues me in on whether my usage has been driven by interactive or background processes. In my case, mostly background (no wonder, seeing as this is my developer account tenant, used for testing purposes).
Had my usage been higher, the Throttling and Overages views would have shown more significant insights, allowing me to see Delays, Rejections and Overages. In my case, there are only very limited delays:

Which artifact types and specific items are spending most of my Capacity?

The Ribbon chart allows for a quick overview of the most demanding artifact types are eating my capacity. I find that I only really use the CU option here, as that is also the billing metric. In my case, I clearly have something going on with some expensive Warehouse artifacts:

Don’t forget to click a data point to filter the other visuals. In my case, it is the DataflowsStagingWarehouse, as shown in the filtered Items table:

For more details on the Compute page, have a look at the official documentation: Understand the metrics app compute page — Microsoft Fabric | Microsoft Learn

Timepoint Details Page

To me, the hidden gem of the Fabric Capacity Metrics App is the Timepoint Details page, which many people don’t event realize exists.

To find it, right click or select a specific time point in the Top Right visualisation, and drill through to the Timepoint Details Page:

Which specific actions are driving my usage?

On the TimePoint Details page I can see that there are both Interactive and Background operations going on during the selected 30 second Timepoint. Many of which are indeed related to the Warehouse identified before.

Notice how the Interactive Operations show timepoints that start right around the selected Timepoint? This is because interactive operations are smoothed over a 5 minute time interval, in terms of how the usage affects your capacity.
Notice how the Background Operations display activities which are not taking place in the selected TimePoint? This is because background operations are smoothed over 24 hours!
Nonetheless, I can see that at the selected point in time, my capacity was used interactively for some write-back to the Warehouse using the XMLA endpoint (more about live data write-back in this blogpost: Link to blog about Write-Back to Fabric), as well as some background acitivities which due to smoothing was also consuming CUs at this time.

Are there any specific Users or Operations which stand out?

Another thing I like to do, is to examine whether any specific Users or actions stand out in the report. In this case, I can see that it is myself, using the jonAdmin account, which is driving most of the usage.
Especially if you are diving into an overage scenario, it is most helpful to know if specific users are misusing Fabric Items, in order for you to help them finding a better solution.

As another example, in a different tenant, I was curious about how expensive it was to perform Copilot queries, and I could observe the following in the Timepoint Details page:

In which I can see that Copilot Queries are most definitely not “free” to execute in terms of Capacity Units. If you are curious specifically about CU consumption of Copilot queries, have a look at Chris Webb’s blog: How much does Copilot cost in Microsoft Fabric? (crossjoin.co.uk)

For more information about the Timepoints Detail page, here is a link to the official documentation: Understand the metrics app timepoint page — Microsoft Fabric | Microsoft Learn

OneLake Storage page

Finally, we have the OneLake Storage page:

In Microsoft Fabric, we have to pay for storage, in addition to our Capacity, hence another relevant question for any Fabric Administrator might be:

How much billable storage are we using, are what are the primary contributers?

Luckily, this page is easy to interpret. My Demo workspace by far takes 1st place in billable storage, and by clicking it I get a filtered view of the Workspace specific Total Storage and Billable Storage.

Clicking the Export Data button in the top right, further lets you download this storage data if you wish:

More details about the storage page can be found on this documentation page: Understand the metrics app storage page — Microsoft Fabric | Microsoft Learn

Summary

The Capacity Metrics App is an excellent tool for understanding your Capacity Usage and Storage, as well as pinpointing exact operations which might be the cause of overages.

Use the app to monitor your whole capacity and/or specific workspaces, and to understand if the users of your tenant are leveraging their Fabric Capabilities correctly.

I hope you learned a thing or two from the sample Questions and Approaches to answering them. What are you using the Metrics App for? Please let me know if you have other cool use cases!