The Ultimate CDS Power BI Explorer Part 1 – From Zero to Hero

[RoryBI]

Introduction

Power BI has been around for a number of years now, however, it’s only in the last couple of years that it’s started to gain heavy use in organizations across the world.  Many of us now have access to many reports in a large number of workspaces, and there are a number of challenges that remain at this time. These are as follows:

• Dashboards can only surface tiles from the same workspace
• It’s not easy to refresh a dataset
• It’s relatively hard to create a simple navigation experience especially when moving between reports in different workspaces
• There is no report preview option
• Bookmarking isn’t particularly easy to manage or use and the bookmarks relevant to one month aren’t necessarily the ones that are relevant to all periods.
• You can only have ONE app per workspace, however you may not wish all users to see every report and indeed they may not wish to see them either.  

This is an end-to-end tutorial that aims to address some of these points and includes a Model Driven App, Canvas App and a Power Automate Flow (to update your Power BI datasets).  1 Power App tile could theoretically surface tens of pages from 1000 reports ACROSS MULTIPLE WORKSPACES with even more filters dynamically applied.  Don’t be too concerned if you’re not familiar with this technology as we go through every stage step by step.

Whilst this is a useful tutorial, it isn’t strictly speaking an end point, and I would invite people to dive further into this structure to deliver value to themselves and their teams.

You can dive to a full version of the tutorial by using the video link below, but I'll be taking you through the steps via this post.

The Prize (1 minute 20)

The prize is a relatively simple structure for surfacing Dynamic Power BI reports.  We pick the video up and go directly into a demonstration below. 

Solution Ingredients

This is a typical workspace – many reports, some of which are used, and some of which that aren’t.  Indeed, as you can see, some are favourited, but once you have 10 or so favourites life starts to get complicated.

After asking myself the question “Is there a better way to approach this?” I identified a number of technical ingredients that I thought might be able to provide me with the basis of the solution that we are going to explore.

• The Power Apps Power BI tile isn’t just for dashboards. If you use the TileURL property you can put in whatever report you like. 
• URLs for Power BI can use parameters to specify the report page
• URLs for Power BI can specify also how we wish to filter the report.  ALL in a SINGLE URL!!  In the demonstration we use just one filter, however it is possible to have multiple table filters.
• You MUST use an EMBEDDED LINK to the report as the URL for the TileURL property.  This is much more straightforward than it sounds and is shown below:-
  

Just be aware that the only way I've found of getting to this URL is by ensuring that we're in the "new look" for the report.

• You can pass report parameters into the Embedded Link
• Power Automate (Flow) now has an action that allows you to refresh a Power BI dataset. This action will take parameters too.
• The Common Data Service provides a superb methodology for creating relationships between entities, and in this case I mean Power BI Reports and Power BI Report Pages and Report filters.  Don't be put off by this use of CDS if it's not your favoured data source.  It would work equally well with SharePoint, SQL Server or any other data source you care to mention, however it is significantly more portable by using CDS.

If you’ve got this far then here’s what we’re going to build.

• A Power Apps Environment
• A Power Platform Solution as a wrapper for our Flow, Entities and Apps
• A Flow to update a Power BI dataset using PowerApps
• 3 related CDS entities – PBIDataset, PBIPage, PBIFilter plus their forms and views
• A Canvas app that uses the above entities to surface a number of Power BI reports
  • I cheat a little bit here and move to one I prepared earlier, but you can download this using the attachments below
  • ---Link to resources to be provided
• A Model driven app to aid the creation of the data

So, let’s get started.

Step 1 – Exploring the data model (5 minutes 40)

In the next few minutes we take a look at the structure of the entities (tables) used by the app that we will be building and how the entities relate to each other.  Essentially there are 3 tables.  PBIDataset, PBIPages, PBIFilters

Step 2 – Building a new Power Apps Environment (8 mins 30)
If you're familiar with Power Apps you can skip over this session, but it's important in the context of building a full product.

Environments are containers for your Apps, Flows, Entities, Option Sets - essentially everything that you need to create an app.  Note that they don't contain anything to do with Power BI.  Although as we shall see the products work well together, they are only loosely joined.  We pick this up in the canvas app in step

Step 3 – Creating a Solution (12 minutes 21)
A solution is a wrapper for your Apps, Flows, Entities and a good many other things.  They are also a unit of import/export so it's a very good idea to make use of solutions when building over the Common Data Service.  Trust me 😉.

Step 4 – Creating the Power Automate Flow to refresh the Power BI Dataset (15 minutes 48)
We pick up the process of creating a Flow that will allow the refresh of a Power BI at the click of a button.  Just be aware that this uses up one of your 8 scheduled refreshes per day, so you will need to be careful with how much you make use of this.  We associate this with the canvas app in Step 9.

Step 5 - Building the Entities, Forms, Views and Relationships (22 minutes 28)

So, this is quite a chunky session, but it reflects the very heart of the Power of the Power Platform at least from the perspective of the Common Data Service.

• Entities are effectively data tables
• Forms are what they sound like.  They present the fields that we are allowing users to input data into.
• Views are tabular presentations of the entities - essentially a bit like a spreadsheet.
• Relationships are the glue that pulls everything together and fundamentally allow the navigation between entities, usually between parents and child records.  They are absolutely essential for creating the simplicity of the PowerApp that we will be building.

Step 6 - Building a Model Driven App (40 minutes 36)

A model-driven app is essentially an App that is constructed from the Entities, Forms, Views and Relationships in the step above.  They are actually quite powerful as the user interface is pre-built and the experience is more around configuration.  It's easier to think of them by comparison to Canvas Apps, which are "Pixel Perfect" apps which you have a significant amount of control with regards to the precise positioning, colour etc of your objects.  We build one of these later on.

Step 7 – Using the Model Driven App (54 minutes 43)

Now the fun starts.  This is a short demo of how to use your model driven app.  What we find here is that we need to do a number of corrections to the earlier steps, not because the actions were incorrect, but they just haven't been saved.  In many respects it shows the power of the system to be easily and quickly updated.  

Building Power BI URLs - Some technical guidance

One aspect of the model that is quite fiddly is understanding how to create a URL that is capable of filtering the data according to conditions associated with a specific field within a specific table in addition to identifying the correct Power BI pageName (or section)
The structure of the URL required is as follows:-

URLEmbedded 
&"&pageName="&[ReportSectionID]
&"$filter="&[TableField]&" eq '"&[EntryfromDropdown]&"'"

Just to be clear you need to be EXTREMELY precise as to the syntax above and TableField.  It is case sensitive and MUST be structured as follows:-

TableName/FieldName and in the example below that would translate to dimProduct/ProductName  In our case we add a text field called FilterPipedOptions with the | delimiter (to the left of z on my keyboard) in order to deliver a range of options such as year etc (for example 2017|2018|2019).  There are other ways of doing this, but it seems quite a flexible approach to me.

Patrick from @GuyInACube has a good video on the filter piece, but just remember that he isn't using an embedded URL, so the syntax will be slightly different in our app later on.  

In order to get to the URLEmbedded, you need to navigate as follows, and you'll be provided with the correct URL for our task.

It’s by no means a requirement to follow through with this part, as far as filtering is concerned, but it does illustrate the depth of potential views that you could leverage using Power BI hyperlinks.  In this case, we are looking to filter the entire dataset by Gender or by Year.  In the future, I will extend this demonstration to show how to have multiple filters available and how to turn them on and off as required.

Step 8 – Building the Canvas app (1 hour 11 mins 55 seconds)   
Now that we understand a bit about Power BI URLs, we've finally arrived at the point where we can start building a canvas app that uses the data contained within our Common Data Service entities.  Whilst it's needs to be carefully prepared, it isn't a terribly complex app, and there is significant room for improvement of the user interface.

I do a cheat here by copying and pasting controls from another app (I was a bit tired at this point - needed more tea!), to speed up the creation process, but we'll do a recap later on.  I've attached a very simple Power BI report that aligns to the video for you to use at your leisure.

Some key formulas to be aware of are associated with how to create a url to an embedded report and then enhancing it with additional filters and navigation to different pages.

The basic structure for the Power BI object within Power Apps is as follows

TileUrl = [URLEmbedded]
&"&pageName="&[ReportSectionID]
&"$filter="&[TableField]&" eq '"&[EntryfromDropdown]&"'"

However, if you just need to get to a report you can just use to following

TileUrl = [URLEmbedded]

In the App the full string is as follows:-

GalPowerBIReports.Selected.URLEmbedded
&"&pageName="&galPowerBIReportPages.Selected.admin_reportsectionid &"&"&"$filter="&galReportFilter.Selected.FilterTableField &" eq '"&ddPBIFilterSelected.Selected.Result&"'"

You should be able to just copy and paste the above if you have precisely the same object names in your app.

It is admittedly a mouthful, but it does work, and an example of the string is as follows:-

https://app.powerbi.com/groups/9b607fd3-ff92-40ab-b338-03bf1b1a31bb/reports/ee2a7adb-d398-40a9-8796-...  eq ‘2018’

Step 9 – Associating the Flow with the Canvas App to refresh the dataset (1h 29 Minutes 04)

In order to associate the flow we've made to the canvas app in step 4, we are going to need to pull in the workspace and dataset

This includes enhancing the model (1h 30:26) to make the process easier.
In this part we add the WorkspaceID and the DatasetID which are essential for the flow that refreshes our dataset to work.

Step 10 - A recap on the TileURL construction (1 hour 39 minutes 31)

Step 11 - App enhancements (1 hour 40 minutes 51)

This is a little demonstration of how I have enhanced my app, but I don't go into detailed instructions as to how to achieve this.  You are welcome to let your imagination run wild at this point, and remember - this is just the beginning - not the end!

Finally - A recap of what we've built (1 hour 41 mins 47)

If you've come this far, then you've probably come a long way.  Every time I look at this way of using the Power Platform and the practical problems that it solves, I am very encouraged about the extent to which it can be used and extended.  I'm very excited to look forward to building upon this in the weeks and months to follow. 

Best regards, and remember to like, subscribe and click the notify button to get first sight of new material as it comes out. 

PowerBITrainingSPSynch.pbix