Β 
Search
  • Mara Pereira

Power BI Report Development 101 - Part 2

Last month I wrote the first part of this blog post, which is intended for users that are in the beginning of their Power BI journey.


After a long wait, I finally had some time to finish the second part of the post!


If you don't remember what we discussed in the first post, have a look in the link below:

Power BI Report Development 101 - Part 1 (datapears.com)


Review of the Part 1 finished? Amazing!!


So, let's go back to our diagram:

Colours legend:

Blue Squares - These steps are usually mandatory in most reporting projects

Orange Squares - These are optional steps for advanced visualisation features

Pink Squares - These are optional steps for Q&A Setup

Green Squares - These are optional steps for Row Level Security

Purple Squares - This is a mandatory step for On-Premises data sources



In my diagram, I divided the report development process into four main steps:

1) Analyse data and build your data model

2) Report Design

3) Prepare to share your report

4) Share your report


In the Part 1 of this post, we went through the steps 1) and 2). In this post, we are going to focus on the remaining steps: Prepare to share your report and Share your report


You probably noticed I have a small section on the right called Power BI Integrations.

I decided to add this section because I think it's important to also know what integrations with other tools are there, and how this can be useful in your use case.

I won't be talking about the Power BI Integrations in this post though.





3) Prepare to share your report


πŸž‚ Row Level Security (Optional):

This step comes after you've finished all your work with your report development in Power BI Desktop (data model and report design).


Row-level security (RLS) can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. This means that, if you are using RLS in your report, when a user opens it he/she will be able to see only the data for the role he was assigned to (e.g. you define a RLS role for UK users. You defined a rule that will filter the dataset/report for this group users, meaning they will be able to see only sales data for the UK region).


Once you have your roles defined and saved, you can then test those roles in Power BI Desktop using the View as option:



As you guessed by the title, this step is optional. Not all reports need Row Level Security, so unless you want to restrict the access to your data for certain individuals or groups of users, you don't really need to use RLS, so you can skip this step in that case.


I had a few questions before about my flowchart, asking why I have Row Level Security twice, both in the Prepare to share your report and the Share your report steps. The reason is because, for you to implement Row Level Security, you first need to define the security roles you're going to need for your report while still in Power BI Desktop, and then, after the report is published, you need to add the correspondent members to those roles in Power BI Service. We will talk about this last option in the steps ahead.


Where to start:

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs


Learning paths:

Implement row-level security - Learn | Microsoft Docs


By the end of this step, you should have:

Defined the rules you want to apply to your data, and the Row Level Security roles created in Power BI Desktop.



πŸž‚ Q&A Setup (Optional):

The Q&A feature in Power BI lets you explore your data in your own words by allowing you to ask natural language questions and then providing you with answers to those questions.



Q&A is also optional, I've seen companies/users that use the Q&A feature a lot, some others don't use it as much, but it is an amazing feature and even if you're not planning to use it, there are a few considerations you should still have in this step.


Because Q&A uses natural language queries, you need to spend a bit more time on your data model, particularly on the naming and organisation of your columns/measures.


Some of the things you should consider:

> Naming convention of your measures/columns: users will use natural language to ask their questions, so you need to be really careful with the namings you have in your data model. Imagine you have a dataset with sales. Your users will probably be typing something like "Show me the sales for last year". If your measure is called "CONTOSO_SLS", the user will probably never find the answer he is looking for...

> Hide auxiliary and redundant measures/columns: again, let's use the same example. If your user wants to know sales for last year, but you have two measures named "Sales1" and "Sales2", that won't be very helpful for your user right? Using the right naming convention also makes the model way more readable in general.

> Define synonyms for your columns/measures: You can define synonyms to help your end users find the correct answers on your data. For example, one user might search for "number of customers last year", another user might write "number of clients last year". So having a synonym for customers makes sense in these cases.

> Suggest questions: when your users enter Q&A, some predefined questions will show. You might want to think about defining this questions, as this helps guiding your end user on the type of questions he might ask.


There are many more things we should consider when using Q&A. This would be content for an entire blog post, so I will leave you with one of the things I find really useful when using Q&A.

You can Teach Q&A: you can review the questions asked, and teach Q&A on new questions and terms (Teach Q&A to understand questions and terms in Power BI Q&A - Power BI | Microsoft Docs)


Where to start:

Use Power BI Q&A to explore and create visuals - Power BI | Microsoft Docs

Q&A for Power BI business users - Power BI | Microsoft Docs


Learning paths:

Q&A for Power BI business users - Power BI | Microsoft Docs


By the end of this step, you should have:

Hidden all your auxiliary and redundant measures/columns.

Defined and implemented a naming convention on your measures and columns.

Defined synonyms for your measures/columns.

Added a few suggested questions to help guide your users on the type of questions they can ask using Q&A.



πŸž‚ Create Workspace:

Even though I have marked this step as mandatory, this is not exactly always the case.

Sometimes you already have access to a Power BI Workspace you can use to publish and share your reports with others. In that case, you should just publish it to that workspace.


If you need to create a new workspace, you should also think about the name you're going to use for it.


And lastly, if you're still wondering "Why do I need a workspace for??". Well, think about a workspace as your Power BI folder, but online. This is the place where you have all your artefacts including reports, dashboards, datasets, dataflows, you can even have Excel Workbooks in your workspace!




Note: You should not use the My Workspace workspace to publish your reports!!! This workspace has a lot of limitations, and you should only use it for testing reports, not for collaboration.


Where to start:

Organize work in the new workspaces in Power BI - Power BI | Microsoft Docs


Learning paths:

Create and manage workspaces in Power BI - Learn | Microsoft Docs


By the end of this step, you should have:

A Power BI Workspace you can use (either an existing one or a newly created one) to publish your report to.



πŸž‚ Publish your report:

We are finally at the end of Step 3! What a journey to get here!

So, we are pretty much ready to leave Power BI Desktop, and focus on Power BI Service now.


The only thing missing is publishing our report, so that we can set up the scheduled refresh, finish our Row Level Security setup, and finally share it with our colleagues.



Note: You should not use the My Workspace workspace to publish your reports!!! This workspace has a lot of limitations, and you should only use it for testing reports, not for collaboration.


Where to start:

Publish from Power BI Desktop - Power BI | Microsoft Docs


Learning paths:

Publish and share in Power BI - Learn | Microsoft Docs


By the end of this step, you should have:

Your report published to a Power BI Service Workspace.




4) Share your report


πŸž‚ Setup Data Gateway

The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data (data that isn't in the cloud) and several Microsoft cloud services.

This means that, if all your data sources are in the Microsoft cloud, you don't need to use a data gateway.




There are three different types of gateways, each for a different scenario:

> On-premises data gateway (Enterprise Mode): allows multiple users to connect to multiple on-premises data sources. This gateway is well-suited to complex scenarios with multiple people accessing multiple data sources. In an enterprise scenario, you should use this type of gateway.


> On-premises data gateway (Personal Mode): This gateway is well-suited to scenarios where you’re the only person who creates reports, and you don't need to share any data sources with others. In enterprise scenarios, it is not recommended to use this gateway.


> Virtual network data gateway: This type of gateway is very similar to the Enterprise Mode gateway. The main difference is that there is no installation required as this is a Microsoft managed service.


Ideally, you will have an Enterprise Data Gateway or a Virtual Network Gateway in your organisation already. You will just need to know who is the gateway admin, and ask him to add the on premises data sources that you're using in your report to the data gateway.


Why do I need a data gateway anyway?

As I've mentioned before, a data gateway acts as a secure bridge between the cloud and your on premises data sources. Without a data gateway, you won't be able to set up a scheduled refresh for your report.


Where to start:

On-premises data gateway - Power BI | Microsoft Docs

The Power BI Gateway; All You Need to Know - RADACAD


Learning paths:

Use a Power BI gateway to connect to on-premises data sources - Learn | Microsoft Docs



By the end of this step, you should have:

Access to either an Enterprise Data Gateway or a Virtual Network Gateway. You should have added your on premises data sources to your data gateway.



πŸž‚ Schedule data refresh frequency

Everyone wants to see up to date data in their reports right?

With the scheduled refresh in Power BI Service, you can schedule the refresh of your dataset, so you don't have to worry anymore about looking at outdated data ever again! And bonus, this will be done for you automatically for now on!


It's worth mentioning that, as I explained before, if you have on premises data sources (not in the cloud), it's crucial to have your data sources added to a data gateway first. Without this, you won't be able to successfully finish this step.



Depending on if your report is published to a Pro Workspace or a Premium Workspace, you can set the refresh frequency up to 8x a day (for Pro Workspaces) or 48x a day (for Premium Workspaces).


Note: This step assumes you are importing your data, and not using Direct Query or a Live Connection to connect to your data source. If you are using either Direct Query or a Live Connection, you can skip this step as the data will always be "real time data", so you don't need to setup a scheduled refresh.


Where to start:

Data refresh in Power BI - Power BI | Microsoft Docs


Learning paths:

Configure a dataset scheduled refresh - Learn | Microsoft Docs


By the end of this step, you should have:

Your dataset refresh scheduled at your defined frequency on Power BI Service.



πŸž‚ Row Level Security (Optional)

As with the previous Row Level Security step, if you are not using RLS in your report, you can skip this step straight away.


Since we already defined the roles we want for our Row Level Security, now we just need to add our end users to those roles.



If you didn't test the roles while in Power BI Desktop, you can test them on Power BI Service using the Test as role option:


Testing the roles allows you to check if the rules you defined are correct, as you will be seeing the report as if you were in that specific role you chose.



Where to start:

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

What Do You Need to Implement Dynamic Row-Level Security in Power BI? - RADACAD

By the end of this step, you should have:

Add your end users to the correspondent Row Level Security roles in Power BI Service.




πŸž‚ Configure and publish Power BI App & Share your App with the end users

We are almost there!

We've come all the way from connecting to our data sources, to building our data model, building our report, publishing it, and now it's finally time to share it with our colleagues.


There are multiple ways you can use to share content in Power BI:

> Invite colleagues to collaborate in your workspace - you can add your colleagues as Contributors or Viewers of your workspace. This is specially useful if you have e.g. a workspace that will be destined for your team's collaboration, and everyone can see all the content stored that workspace. Remember, workspaces work like folders!

Note: I don't really recommend this approach, as it might get really hard to keep track on who as access to which reports in your workspace.

> Share the report with the end users - if, for some reason, you have e.g. two reports in one workspace, and you need to share them with different audiences, you might want to think about sharing the report directly instead of building an app or giving the users permissions to see all the content in your workspace.

> Create an App - this third option allows you to select a subset of reports in your workspace, organise them and distribute it using an App. Apps are great for sharing content, but you need to take into account that you can only have one App per workspace, so if you have multiple reports and want to share them with different audiences, you might want to think about other alternatives (multiple workspaces, sharing the report directly etc).


In this case we are going to focus on sharing our content through Apps.


An app is a Power BI content type that combines related dashboards and reports, all in one place. An app can have one or more dashboards and one or more reports, all bundled together. Having this, Apps are a great way to share content with your end users.