Marthe Moengen

Gal in a Cube

Category: Power BI

What are Dataflows Gen 2 in Fabric? — 16. Jun 2023

What are Dataflows Gen 2 in Fabric?

/Marthe Moengen/Leave a comment

I have previously written a post on Power BI Dataflows explaining what it is, how you can set it up when you should use it, and why you should use it. I am a big fan of the Gen 1 Power BI Dataflows. So now, with the new introduction of Dataflows Gen 2 in Fabric, I had to take a deeper look.

In this article, we will look at the new features that separate Dataflows Gen 2 from Dataflows Gen 1. Then we’ll have a look at how you can set it up inside Fabric before I try to answer the when and why to use it. What new possibilities do we have with dataflows Gen 2?

After digging into the new dataflows Gen 2, there are still unanswered questions. Hopefully, in the weeks to come, new documentation and viewpoints will be available to answer some of these.

To learn the basics of a dataflow you can have a look at my previous article regarding dataflows gen 1.

What, How, When and Why on Power BI Dataflows [Hill Sprint]
  1. What are Dataflows Gen 2 in Fabric?
  2. What is the difference between Dataflows Gen 1 and Gen2 in Fabric?
    1. Output destination
    2. Integration with datapipeline
    3. Improved monitoring and refresh history
    4. Auto-save and background publishing
    5. High-scale compute
  3. How can you set up Dataflows Gen 2 in Fabric?
  4. When should you use Dataflows Gen 2 in Fabric?
    1. Limitations
  5. Why should you use Dataflows Gen 2?

What are Dataflows Gen 2 in Fabric?

To start, Dataflows Gen 2 in Fabric is a development from the original Power BI Dataflows Gen 1. It is still Power Query Online that provides a self-service data integration tool.

As previously, you can create reusable transformation logic and build tables that multiple reports can take advantage of.

What is the difference between Dataflows Gen 1 and Gen2 in Fabric?

So, what is new with Dataflows Gen 2 in Fabric?

There are a set of differences and new features listed in the Microsoft documentation here. They provide the following table.

FeatureDataflow Gen2Dataflow Gen1
Author dataflows with Power Query✓✓
Shorter authoring flow✓
Auto-Save and background publishing✓
Output destinations✓
Improved monitoring and refresh history✓
Integration with data pipelines✓
High-scale compute✓
Get Data via Dataflows connector✓✓
Direct Query via Dataflows connector✓
Incremental refresh✓
AI Insights support✓

But what is different, and what does that mean? I would say the features output destination and integration with data pipelines are the most existing changes and improvements from Gen 1. Let’s have a look.

Output destination

You can now set an output destination for your tables inside your dataflow. That is, for each table, you can decide if by running that dataflow, the data should be loaded into a new destination. Previously, the only destination for a dataflow would be a power bi report or another dataflow.

The current output destinations available are:

  • Azure SQL database
  • Lakehouse
  • Azure Data Explorer
  • Azure Synapse Analytics

And Microsoft says “many more are coming soon”.

Integration with datapipeline

Another big change is that you can now use your dataflow as an activity in a datapipeline.  This can be useful when you need to perform additional operations on the transformed data, and also opens up for reusability of transformation logic you have set up in a dataflow.

 

Improved monitoring and refresh history

In Gen 1 the refresh history is quite plain and basic as seen from the screenshot below.

In Gen 2, there have been some upgrades on the visual representations, as well as the level of detail you can look into.

Now you can easier see what refreshes succeeded and which ones failed with the green and red icons.

In addition, you can go one step deeper and look at each refresh separately. Here you get details on request ID, Session ID and Dataflow ID as well as seeing for the separate tables if they succeeded or not. This makes debugging easier.

Auto-save and background publishing

Now, Fabric will autosave your dataflow. This is a nice feature if you for whatever reason suddenly close your dataflow. The new dataflow will be saved with a generic name “Dataflow x” that you can later change.

High-scale compute

I have not found much documentation on this, but in short dataflow Gen 2 also got an enhanced compute engine to improve performance similar to Gen 1. Dataflow Gen 2 will create both Lakehouse and Warehouse items in your workspace and uses these to store and access data to improve performance for your dataflows.

How can you set up Dataflows Gen 2 in Fabric?

You can create a Dataflow Gen2 inside Data Factory inside of Fabric. Either through the workspace and “New”, or the start page for Data Factory in Fabric.

Here you can choose what source you want to get data from, if you want to build on an existing data flow, or if you want to import a Power Query Template.

If you have existing dataflows you want to use, you can choose to export them as a template and upload it as a starting point for your dataflow.

When should you use Dataflows Gen 2 in Fabric?

In general, the dataflows gen 2 can be used for the same purpose as dataflows Gen 1. But what is special about Dataflows Gen 2?

The new data destination feature combined with the integration to datapipeline provide some new opportunities:

  •  You can use the dataflow to extract the data and then transform the data. After that, you now have two options:
    • The dataflow can be used as a curated dataset for data analysts to develop reports.
    •  You can choose a destination for your transformed tables for consumption from that destination.
  •  You can use your dataflow as a step in your datapipeline. Here there are multiple options, but one could be
    • Use a dataflow to both extract and transform/clean your data. Then, invoked by your datapipeline, use your preferred coding language for more advanced modelling and to build business logic.

The same use cases that we had for dataflows Gen 1 also apply to dataflows Gen 2:

Dataflows are particularly great if you are dealing with tables that you know will be reused a lot in your organization, e.g. dimension tables, master data tables or reference tables.

If you want to take advantage of Azure Machine Learning and Azure Cognitive Services in Power BI this is available to you through Power BI Dataflows. Power BI Dataflows integrates with these services and offers an easy self-service drag-and-drop solution for non-technical users. You do not need an Azure subscription to use this but requires a Premium license. Read more about ML and Cognitive Services in Power BI Dataflows here.

In addition, Power BI Dataflows provides the possibility to incrementally refresh your data based on parameters to specify a date range. This is great if you are working with large datasets that are consuming all your memory – but you need a premium license to use this feature.

Limitations

But, there are also some limitations with dataflows Gen 2 stated by Microsoft:

  •  Not a replacement for a data warehouse.
  •  Row-level security isn’t supported.
  •  Fabric capacity workspace is required.

Why should you use Dataflows Gen 2?

As for the Gen 1 dataflows, Gen 2 can help us solve a range of challenges with self-service BI.

  • Improved access control
  • One source of truth for business logic and definitions
  • Provides a tool for standardization on the ETL process
  • Enables self-service BI for non-technical users
  • Enables reusability

But there are still some unanswered questions…

Even though the new additions to Dataflows Gen 2 are exciting, there’s still some questions that remain unanswered.

As I read more documentation and get more time to play around with the tool, I hope to be able to update this article with answers.

  • What about version control? If you edit a dataflow as a transformation activity in your data pipeline it is important to be able to back track changes and be able to roll back to previous versions. How would that work?
  • What are the best practices? Is it best to use Power BI dataflows as the main ETL tool now, or should we use pipelines. Should dataflows be mainly used for simple transformations as cleansing, or should we perform as much transformation and logic development as possible?
    • To mainly use dataflows for simple clean up transformations and then use a notebook in a pipeline for more advanced transformations would be my first guess. But then the question on what provides best performance come up.

So, to conclude, the new dataflow Gen 2 features are awesome. It opens up some very exciting new opportunities for your ETL process. The question now is when those opportunities are something you should take advantage of, and when you should not.

Power BI Pro or Power BI Premium – what license should you choose? — 15. Feb 2023

Power BI Pro or Power BI Premium – what license should you choose?

/Marthe Moengen/Leave a comment

So, what should you choose when looking at the different licences in Power BI? Do you really need to pay for Premium? Or is Premium in fact cheaper for your organization? What features could you take advantage of for the different licenses? And what considerations should you take when evaluating this?

Let’s have a look!

  1. What Power BI licenses are available?
    1. Free
    2. Power BI Pro
    3. Power BI Premium per User
    4. Power BI Premium per Capacity
  2. What should you consider when deciding on a Power BI license?
    1. What flexibility do we need when it comes to changing the licence in the future?
    2. Do you have any technical deal-breaker requirements?
  3. So, what should you choose?

What Power BI licenses are available?

There are four Power BI licenses to choose from. Free, Pro, Premium per Capacity (PPC) or Premium Per User (PPU).

Ordinary Workspace/AppWorkspace/App PPUWorkspace/App PPC
Free licenseNot able to accessNot able to accessGot access
Pro licenseGot accessNot able to accessGot access
PPU licenceGot accessGot accessGot access
Premium per Capacity vs Premium per User

Free

Without a license (or with the free license), you can still take advantage of Power BI Desktop. Still, you cannot share your content with others. The free license is a great place to start learning Power BI if you are curious, but not in a position to purchase a license.

If you are a report consumer and the content you want to consume is placed in a workspace connected to a Premium per Capacity, you do not need any other license than the free one.

Power BI Pro

With a Pro license, you get full developer functionality (with some exceptions that are listed in the next chapter). You can share your content with others.

If you are a report consumer, and you want to consume reports that are inside a workspace that is NOT linked to a premium per capacity license, you also need a Pro license to consume that content.

Power BI Premium per User

With a Premium per User (PPU) license you get full functionality as a developer. Essentially, you get all the Premium features on a per-user basis. You do not need an additional Pro license if you have a PPU license, as all Pro license capabilities are included.

However, if you are a report consumer you also need a Premium Per User license to be able to consume the content within a workspace that is linked to a Premium Per User license.

Power BI Premium per Capacity

With a Premium per Capacity (PPC) license you get full premium functionality. Still, as a report developer, you need a Pro or PPU license to share your reports.

If you are a report consumer, you only need the Free license to consume content that is linked to a Premium per Capacity license.

What do you get with the different licenses?

So, what are the differences between the Pro, Premium per User and Premium per Capacity licenses?

Microsoft got a great overview page where you can compare the licenses and their features HERE.

Below I have listed the differences that in my experience are the most important when considering what license to choose.

PROPremium (Per user)
$ 9.99 monthly price per user$ 4.995 monthly price per dedicated cloud computing and storage resource with an annual subscription.
($ 20 per month per user)
1 GB model size limit.
Your .pbix file cannot be larger than 1 GB		400 GB model size limit
(100 GB model size limit)
8 daily refreshes on dataset in Power BI Service48 daily refreshes on dataset in Power BI Service
–Deployment Pipelines available (Application lifecycle management)
Read more on deployment pipelines in my article
Dataflows (minus the dataflow premium features)Dataflows premium features:
– The enhanced compute engine (running on Power BI Premium capacity / parallel execution of transforms)
– DirectQuery connection to dataflow
– AI capabilities in Power BI
– Linked entities
– Computed entities (in-storage transformations using M)
– Incremental refresh
Read more on dataflows in my article
–Datamarts available
Read more on datamarts in my article
–Embed Power BI visuals into apps
–Advanced AI (text analytics, image detection, automated machine learning)
– XMLA endpoint read/write connectivity
–Configure Multi-Geo support (Only PPC)

What should you consider when deciding on a Power BI license?

Choosing what license fits best for your organization is not easy, and depends on individual requirements. Still, let’s see if there are any questions and considerations you could take into account when trying to decide what license you need.

What flexibility do we need when it comes to changing the licence in the future?

Deciding between the licences can for sure be a difficult decision. The great thing is that you do not have to choose and stick to that solution forever. Many start out with a Pro license, and then as the Power BI usage and adoption within the organization grows, they move over to Premium.

It is however a bit harder to move back to a Pro license if you have started developing reports and datasets that exceed the size limit or have started to take advantage of deployment pipelines, datamarts or premium features in dataflows.
Another important aspect is that you commit to the Premium per Capacity for a year, even though it is billed monthly. This also makes it difficult to move back to Pro.

Still, if you have started taking advantage of these premium features, you probably see the value of keeping the premium capacity.

How many report consumers do you have?

Price wise there is a sweet spot to evaluate here. When you have a premium capacity, you connect your workspaces to that premium capacity. That means that all the reports you publish to an app from that workspace are visible to anyone. They do not need their own pro licence to be able to consume the reports you put in these premium workspaces/apps.

So, some quick math gives us a number of report consumers where the premium feature pays off.

500 report consumers. If you know that you have that many report consumers today or expect to reach that number soon as your company grows and the adoption of Power BI increases, the Premium per Capacity license is a good choice.

Are you using Power BI on an enterprise level?

Or how large is Power BI in your organization? Are there multiple workspaces, apps, reports, data domains and business areas?

How complex is your report development process? Are your report development teams organized in different business domains, but still collaborate on content?

Do you see the need to take advantage of Deployment pipelines to improve the lifecycle management of your content, or do you want to implement source control using an XMLA endpoint?

If you are considering starting with Power BI and know that your setup requires some level of complexity, these premium features can really help you out with large enterprise deployments and workloads.

How large are your reports?

First of all – try and reduce the size of your report. Microsoft got an article listing the techniques you could consider:

Now, if you are not able to reduce the size of your reports below 1 GB, or that does not make any sense to you, the Premium per Capacity or Premium per User license sounds like a solution for you.

Do you have any technical deal-breaker requirements?

When evaluating this question you should collect the technical requirements for your organization. Based on that list, you might see some deal-breakers when it comes to choosing the Pro license.

For instance, you might need an SQL endpoint for your datamarts, or an XMLA endpoint to automate deployment that requires premium features.

You might have some data residency requirements that can only be achieved through a Premium Per Capacity license.

You will be working with datasets that are above 1 GB.

Or you want to take advantage of an incremental refresh for real-time data using DirectQuery. This is only supported for premium licenses.

Getting an overview of these requirements, and evaluating if they require Premium features is a good starting point.

Do you need some of that additional premium features, but the premium per capacity is too much?

After having evaluated all of these questions above, you might still be in need of some of the premium features but are not in a position to choose Premium per Capacity as that might be too expensive. Then Premium per User could be the solution for you if you:

  • Want some of your Power BI Developers to learn or investigate the premium features?
  • Take advantage of the advanced AI features?
  • Want to take advantage of the Deployment Pipelines to improve the lifecycle management of your content?
  • Are working with large datasets that you cannot reduce the size of?
  • Want to set up a solution for source control taking advantage of the XMLA endpoint? Read my article on source control and your options HERE.
  • Want to centralize your BI solution in Power BI Service by building re-usable dataflows and datamarts, and reducing some of the development load on your data warehouse?
  • Do not have a proper data warehouse solution in your organization and want to take advantage of the datamart feature in Power BI Service?

Still, remember: If you go with a PPU license, all consumers of that conent also need a PPU license.

So, what should you choose?

The considerations listed above are probably not covering everything you need to consider if you are in a position where you need to decide between licenses, I am sure.

Still, they might give you a starting point in your evaluation.

The decision each organization falls on depends on the requirement that exists within the individual organization.

Let’s try to sum up some key take aways:

  • If you do not see the need for the premium features to start with –> Consider starting with Pro licenses
  • If you have more than 500 report consumers –> Consider Premium Per Capacity
  • If you are a smaller organization, but still need the premium features –> Consider Premium Per User
  • If you are using Power BI in a large organization across business areas, with numerous reports and datasets and development teams –> Consider Premium Per Capacity
  • Have a look at your technical requirements. –> Some of the limitations with the Pro licenses might make a premium choice obvious for your organization.

One thing that’s also worth mentioning is that Power BI for sure focus its investment on Power BI Premium. The value provided by Power BI Premium will therefore probably increase over time.

So, what license should you choose?
The short answer: It depends.

Useful links:

Thank you to all that contribute to improve this article!

Source Control in Power BI – What are your options? — 1. Feb 2023

Source Control in Power BI – What are your options?

/Marthe Moengen/1 Comment

Updated 21.06: With the realease of Microsoft Fabric, built in Git integration is now a part of your workspace solution inside Fabric (!). More information here: https://learn.microsoft.com/en-us/fabric/cicd/git-integration/intro-to-git-integration

The rest of this article refers to the possibilities before this built in Git feature was released.

So version control in Power BI has been a challenge from the start. What do you do if the changes you made messed everything up and you want to roll back to an older version? Or if the file you saved on your computer is (unintentionally) deleted? What if parts of the changes you made where really good and you would like to update other reports with the same changes? Then what?

No built-in solution for this exists in Power BI today. If you have a Premium licence, deployment pipelines are an option. The Deployment Pipelines will help you get better control of your data, and provide an overview of the differences between the content in workspaces. However, it is not solving all the challenges we have mentioned above. I have written an article about that feature here:

What, How, When and Why on Power BI Deployment Pipelines [Hill Sprint]

We are still missing source control! This is a feature many wished for from the Power BI team. The idea has been voted on at ideas.powerbi.com. In March 2022 an administrator updated the idea to: We are working on this item but no timeline can be shared yet. We appreciate your patience

So, while we wait for this new feature to come, let’s take a look at your options!

  1. Version control your .pbix files using OneDrive or Sharepoint
  2. Version control your .pbix files using Git
  3. Tabular editor to source control your Power BI data model

Version control your .pbix files using OneDrive or Sharepoint

The .pbix file is a binary file. That means that it is not possible to track changes. So, what can we do?

OneDrive and Sharepoint contain built in solution for version control witch means that you can get access to a previous versions.

Pros:

  • Easy to start using!

Cons:

  • You have to make all changes in Power BI Desktop in order to get the version history. This means that you are losing some flexibility of Power BI Service.
  • As you are working with the binary file you cannot:
    • Do diffs or schema compares of the versions
    • Cannot merge the files or have multiple developers working at the same time

Considerations:

  • Make sure that there is a process in place so that all developers that collaborate on the same reports use the same OneDrive/Sharepoint folder.

Version control your .pbix files using Git

You could also use GitHub or Azure DevOps to store your file and track the version of your .pbix file by committing the file to git after making changes as you would with other files in Git.

Pros:

  • Could be incorporated into existing development process using git

Cons:

  • Same as for using OneDrive or Sharepoint folder:
    • You have to make all changes in Power BI Desktop in order to get the version history. This means that you are losing some flexibility of Power BI Service.
    • As you are working with the binary file you cannot:
      • Do diffs or schema compares of the versions
      • Cannot merge the files or have multiple developers working at the same time

Considerations:

  • As always it is important to keep in mind the development process for developing Power BI reports. You need to make sure all the developers use this setup for it to work.
  • Also consider using large file storage for git. Read more on that topic here: https://git-lfs.com/

Marc Lelijveld has written an article on how you can automate this process using Azure DevOps: https://data-marc.com/2021/06/04/integrate-power-bi-deployment-pipelines-with-azure-devops/

Tabular editor to source control your Power BI data model

When taking advantage of external tools like Tabular Editor, you can save your file as a .bim file.

Before we look into the .bim file, we need to take a quick look at how a Power BI Report is built up. It is divided into two components: Report and Dataset

The Dataset is the data model that holds your data and the changes you have made to it as transformations or measures.

The Report contain all the report pages and visualizations you have set up. It is the visual part of your Power BI report. All of the visualizations in a report come from a single dataset. 

The fact that you can differentiate these two affect how you can enable source control in Power BI.

A .bim file is essentially the metadata of your Power BI datamodel. As this is a json file it works well with source control. We can then use this file to track changes in our Power BI Datamodel.

Pros:

  • Possible to track changes made to your datamodel.

Cons:

  • We are missing tracking on the visual part of your report. For instance if you make some changes to your visuals or report pages, that is not part of the .bim file.
  • Extraction of the .bim file requires an external tool and a manual step to your process.

Considerations:

  • To automate this you need to take advantage of the XMLA endpoint. However, this requires premium capacity or a premium per-user license.
  • You cannot deploy these changes back into Power BI Desktop. If you have a premium lisence you could use tools like ALM Toolkit to deploy changes to the XMLA endpoint.

Gerhard Brueckl has written an article on how you can automate some of the manual steps in this article: https://blog.gbrueckl.at/2022/02/automating-the-extraction-of-bim-metadata-from-pbix-files-using-ci-cd-pipelines/

Useful links:

What, How, When and Why on Power BI Governance — 21. Dec 2022

What, How, When and Why on Power BI Governance

/Marthe Moengen/1 Comment

The What, How, When and Why on Power BI Deployment Pipelines!

  1. What is Power BI Governance?
  2. How can you set up Power BI Governance?
    1. Power BI Governance Kick-start!
      1. Roles and Access management
      2. Processes
      3. Styleguide
      4. Monitoring
      5. Training
    2. Microsoft Power BI Implementation Plan
    3. Ásgeir Gunnarsson’s Power BI Governance Series
  3. When should you use Power BI Governance?
  4. Why should you use Power BI Governance?

What is Power BI Governance?

Power BI Governance is supposed to help you out with leveraging the value and insights from your Power BI Reports.

Let’s start with what is Governance.

Governance is the process of interactions through the laws, norms, power or language of an organized society[1] over a social system (family, tribe, formal or informal organization, a territory or across territories), (Wikipedia)

Then what is Power BI Governance?

Power BI Governance is the policies, processes, roles, rules and guidelines to ensure a level of control and management to leverage the value of Power BI, (from Marthe’s head)

How can you set up Power BI Governance?

This is not an easy question to answer within a short hill sprint. A more detailed article might be needed here, but – let’s start with the most important.

Every Power BI Governance will be set up differently for each organisation. I, therefore, think a set of open questions to start your journey makes the most sense.

Below I have listed a set of domains with questions and if you can answer these I think you have a good starting point for your Power BI Governance! You might also not need all the domains, or maybe some of these can be scaled down for you – it depends on how your organization uses Power BI.

Further down in this post I have listed the documentation on this from Microsoft as well as from my friend Ásgeir Gunnarsson that provide two different approaches on how to take on Power BI Governance.

Power BI Governance Kick-start!

Roles and Access management
  • What roles do you need?
  • Do you need any hands-on roles defined?
    • Report Developer Role
    • Report Consumer
    • Power BI Business Analyst
    • Other?
  • Do you need any Power BI Management roles?
    • Power BI Administrator
    • Report Owner
    • Workspace/App owner
    • Data Owner
    • Other?
  • What are the scope and responsibilities of these roles?
  • What accesses should they have?
  • What are they allowed to do and not to do?
  • What is the level of access for these roles?
  • What processes do these roles need to follow?
Processes
  • Development Process
    • Who can develop Power BI Reports?
    • What sources can you use for your reports?
    • Is there a best practice for reusing dataflows, datasets or datamarts?
    • Where do you store your report? Are there for instance a pipeline in DevOps set up for versioning control? A common OneDrive folder to save your work? Or should you use the Deployment Pipelines in Power BI Service?
    • Should there be any guidelines on Import vs Direct Query?
  • Publishing Process
    • Who can publish and to what workspaces?
    • Who can set up a workspace in Power BI Service?
    • What are the access guidelines to the workspace and who manages these?
    • What are the accessibility guidelines for the app and who manages these?
    • Should you use Deployment Pipelines or a DevOps pipeline when publishing?
    • Are there any sign-offs that need to be done before publishing?
    • Any checklist that should be considered before publishing?
  • Quality sign-off process
    • This quality process could be a part of the publishing process or something that is repeated in defined intervals to ensure the quality of existing reports in Power BI Service.
    • What should be evaluated in this process? What ensures good Power BI quality?
    • Data modelling?
    • DAX code?
    • Should there be a checklist of best practices on how to improve the performance of the report itself?
  • Security process
    • Should comply with the security rules that already exist for the overall company
    • Is it ok to connect to all data sources? Are there any limitations here?
    • How do you secure:
      • App
      • Workspace
      • Report
      • Dataset
      • Gateway connections
    • When and how to use:
      • AD groups
      • Row-level security
      • Data source security
  • Sharing Process
    • What reports/Datasets can be shared?
    • Are there any limitations on sharing content across business areas, data domains or to another organization?
    • How can you share content for the different Power BI objects (Apps, Workspaces, Reports, Datasets, Dataflows, Datamarts, Deployment Pipelines etc.)?
  • Administration Process
    • How should the Power BI tenant be managed?
    • What settings in the tenant should be fixed?
    • How are these settings and the reasoning behind these documented?
    • When can you change tenant settings?
    • How are the users of Power BI informed when such changes are made?
    • How can a new administrator be added?
Styleguide
  • Do you want to provide strict rules or guidelines?
  • Should you have a Power BI template?
  • Are there any colour standards, or themes that should be used?
  • Any guidelines on the usage of logos?
  • Should there be standardization on where you place different types of visuals on a report page?
  • Any best practices on the type of visuals that should be used?
  • Should there be different templates for different levels of reporting (Management Report, Operational Report, Trend report, etc.)?
Monitoring
  • I don’t think there should be a question on whether or not you should perform monitoring, but it could be good to decide on:
    • What role is responsible for following up on monitoring?
  • There are different ways of monitoring your Power BI objects.
    • Track user activities in Power BI. Read more HERE
    • Power BI Premium capacities: App delivered by Microsoft. Read more HERE.
Training
  • Should there be any training?
    • (I think the answer here should be Yes. I mean, think of all the amazing governance you have just set up. People need to know that this exists and how to use it. )
  • Should there be different training for different roles?
  • Is the training mandatory?
  • Should some roles get the training with a set interval? (Every year, Every other year)

Microsoft Power BI Implementation Plan

Microsoft got a Power BI Adoption Roadmap and a Power BI Implementation Plan that provides a step-by-step roadmap you can take advantage of.

Power BI Adoption Roadmap steps:

Key takeaways:

  • The power BI Adoption roadmap provides a broader view of how you should move forward when implementing Power BI. You start out by evaluating maturity, data culture and how you should govern your solution.
  • By going through this roadmap you end up with an overview on the current state of each point mentioned above, and you should be able to pinpoint the next steps to how you can achieve your future state.

Power BI Implementation Guideline steps:

  • BI strategy
  • User needs and opportunities
  • Authoring tools and user machines
  • Tenant setup
  • Subscriptions, licenses, and trials
  • Roles and responsibilities
  • Power BI service oversight
  • Workspaces
  • Data management
  • Content distribution and sharing
  • Change management and deployment
  • Security
  • Information protection and data loss prevention
  • Power BI Premium
  • Gateways
  • Integration with other services
  • Auditing and monitoring
  • Adoption tracking
  • Scaling and growing

Key takeaways:

  • The implementation guideline focus also on technical implementation, and not only governance. In that sense, they get a bit technical which is nice!
  • The Power BI Implementation guideline is under construction, meaning that all articles have not been created yet.

Ásgeir Gunnarsson’s Power BI Governance Series

Also, my friend Ásgeir Gunnarsson has set up a great article on a Power BI Governance Series.

I think this is a great overview of what to do when implementing Power BI Governance without making it too big. In contrast to the Microsoft documentation, this is a bit easier to wrap your head around (I would argue).

Key takeaways:

  • Ásgeir focus on the non-technical aspects of data governance and why this is so important.
  • He describes a Power BI Governance Strategy by defining five pillars:
    • People
    • Processes and framework 
    • Training and support
    • Monitoring
    • Settings and external tool
  • My approach to data governance is inspired by Ásgeir’s work. You should check it out!

When should you use Power BI Governance?

Well, you should always use Power BI Governance, but here are some specific points for when you REALLY should use Power BI Governance.

Also, how much governance, and for what domains you choose to focus on should vary based on the size and complexity of your organization.

When there are multiple report developers and Power BI resources that are not working in the same team (and hence would therefore not necessarily have a common way of working with Power BI).

When you have a large organization gathering data from multiple data sources, and delivering reports for multiple business areas.

When there is a need to control access, source connections, and improve overall control and quality of your Power BI Reports, Dataflows, Datasets, Datamarts and usage. Have your reports gotten out of control?

Why should you use Power BI Governance?

In need of some points to add to your presentation when trying to convince the sponsors or IT department that Governance is needed for your Power BI?

  • Improves trust in your entire analytical database solution. If the reports continuously deliver the right insights and are findable and trustworthy, the overall view of your analytical platform will increase. Why spend a lot of time governing your database, when your Power BI Reports are all over the place?
  • Increased report quality – both on the user experience side and data quality.
  • Accelerate your organization to becoming data-driven. When your reports are both trustworthy and deliver high-value insights, making business decisions based on data becomes a lot easier.
  • Competitive advantage in the market.
  • You get more out of your Power BI resources as they are not needed to answer ad-hoc questions, dig into duplicate reports or KPIs, errors, access control that went wrong, etc. They can now focus on creating that insight from that data!

Useful links:

When should you use Power BI Dataflows vs Power BI Datamarts? — 13. Dec 2022

When should you use Power BI Dataflows vs Power BI Datamarts?

/Marthe Moengen/Leave a comment

I have previously written articles on the What, How, When and Why of Power BI Datamarts and Power BI Dataflows. Have a look below if you want to get a quick overview of the two features of Power BI Service.

But when should you use what?

What, How, When and Why on Power BI Dataflows [Hill Sprint]
What, How, When and Why on Power BI Datamarts [Hill Sprint]

Power BI Dataflows vs Power BI Datamarts

Let’s revisit the When of both Power BI Dataflows and Power BI Datamarts!
Use casePower BI DataflowPower BI Datamart


Tables that are reused throughout your organization		Dataflows are particularly great if you are dealing with tables that you know will be reused in your organization, e.g. dimension tables, master data tables or reference tables.You can also reuse a datamart, but it is unnecessary to build a datamart to solve this use case.


Azure Machine Learning and Azure Cognitive Services
If you want to take advantage of Azure Machine Learning and Azure Cognitive Services in Power BI this is available to you through Power BI Dataflows. Power BI Dataflows integrates with these services and offers an easy self-service drag-and-drop solution for non-technical users. You do not need an Azure subscription to use this but it requires a Premium license. Read more about ML and Cognitive Services in Power BI Dataflows here.When looking through Power BI Datamarts today I cannot see this functionality easily available. Dataflows was however designed to solve this use case and is, in my opinion, a good place to start.


Incremental refresh		Power BI Dataflows provides the possibility to incrementally refresh your data based on parameters to specify a date range. This is great if you are working with large datasets that are consuming all your memory. However, you need a premium licence to use this feature.It is also possible to set up incremental refreshes for your separate tables in your Datamart. If you have a couple of large tables within your datamart, this could be a nice feature to take advantage of.


Ad-hoc SQL querying and data exploration		You can explore your data through a dataflow, but it is not possible to run SQL queries with dataflows.Datamarts are particularly great if you want to do ad-hoc querying or data exploration of your data as sort, filter, and do simple aggregation visually or through expressions defined in SQL
This image has an empty alt attribute; its file name is image-17.png

Self Service Data modelling		Dataflows do not support setting up relationships between tables, building measures or writing DAX. A great thing with Power BI Datamarts is that you can model your star schema right in Power BI Service. That way you do not have to wait for the data warehouse to make smaller (or larger) improvements or changes to your data model as you can do these changes yourself – but remember that permanent transformations should be moved as close to the source as possible. This also enables Mac users to do some modelling in Power BI Service.


Need to connect to your data in Power BI Service through a SQL endpoint.		Not possible with dataflows.Power BI datamarts provide a SQL end-point to your data. This is great if that is a requirement from developers or data analysts. You can then use database tools as SSMS to connect to your datamart as any other DB, and run queries.

Let me know what you think and if you have other use cases where the tools should be compared.

Useful links:

What, How, When and Why on Power BI Deployment Pipelines [Hill Sprint] — 7. Dec 2022

What, How, When and Why on Power BI Deployment Pipelines [Hill Sprint]

/Marthe Moengen/2 Comments

The What, How, When and Why on Power BI Deployment Pipelines!

  1. What are Power BI Deployment Pipelines?
  2. How can you set up Power BI Deployment Pipelines?
  3. When should you use Power BI Deployment Pipelines?
  4. Why should you use Power BI Deployment Pipelines?

What are Power BI Deployment Pipelines?

Power BI Deployment pipelines makes it possible for creators to develop and test Power BI content in the Power BI service, before the content is consumed by users. It provides a lifecycle management solution for your Power BI content!

Deployment Pipelines creates a development, test and production workspace for you where you can view the differences between the environments. You can also set up deployment rules that change your data source when deploying from one environment to the next. Like changing from test data in the test workspace to production data in the production workspace.

You can also review your deployment history to monitor the health of your pipeline and troubleshoot problems.

Hence, Deployment Pipelines can help you collaborate with other developers, manage access to testers and automate data source connections.

If you want to learn more on Power BI Deployment Pipelines, you can read the documentation from Microsoft here.

What Deployment Pipelines do NOT help you with is version control. This brings us on to another existing topic that I have not yet created a blog post on – Azure DevOps and Power BI. However, my friend Marc has. You can read his post on how you can utilize Azure DevOps to manage version control on your Power BI Reports here.

How can you set up Power BI Deployment Pipelines?

You set up a Power BI Deployment Pipelines in Power BI Service. This is done through the menu on your left side when login into Power BI Service OR directly in the workspace you want to assign to a Deployment Pipeline.

You then follow these steps:

  1. Click “Create a pipeline”

2. Fill in the name of the pipeline. This needs to be unique for your organization. Make sure the name makes sense for other developers and fill in a description as well.

3. Assign a workspace (if you did not create the pipeline directly from the workspace)
If you created the deployment pipeline directly from the workspace you need to decide if you want to assign the existing workspace to Development, Test or Production. Essentially you are deciding if the existing workspace already is a production environment or a development environment (it could also be a test environment, but dev and prod would probably make the most sense for most).

In the following example, the Development environment was chosen as the starting point/the workspace was assigned to Development.

4. Choosing “Deploy to test” will automatically generate a test workspace for you. Inside this workspace, you can then decide to create an app that can be used to view the content for business testers if you don’t want to give access to the workspace.

5. Choosing “Deploy to production” will automatically generate a production workspace for you. This will be where you provide access to the reports and datasets, datamarts and dataflow to your business analysts that want to take advantage of these assets, and where you create your app to provide access for report consumers.

6. You can change the name of the workspaces by clicking on the ellipse and choosing “Workspace settings”

7. By selecting the lightning bolt above the Test or Production environment you open up “Deployment Settings”.

Depending on the data source you can define deployment rules for your data source. For instance, you can change the file path, database or parameter when deploying from test to production changing the data from test data to production data. Nice!

8. Create apps on top of the development, test and production workspace as needed and assign access to relevant users.

You need premium capacity to get access to Power BI Deployment pipelines.

When should you use Power BI Deployment Pipelines?

When there is a need to provide business users with a test environment to test reports, test the layout of the app or new functionality without mixing with reports that already are in production. Additionally, when there is a need to provide more technical testers with access to a workspace with only content that is ready for testing.

When there are multiple report developers and business domains and there is a need for collaboration and exploration. The development workspace provides an area where multiple Power BI developers can make changes and adjustments to the same files (as long as these changes are made in Power BI Service).

When there is a need to separate test data from production data, where reports should not connect to production data unless the report itself is ready for production.

Why should you use Power BI Deployment Pipelines?

Power BI Deployment Pipelines help us with the lifecycle management of Power BI content

  • Provides a tool to improve and automate the management of the lifecycle of Power BI content
  • Provide a visual overview of developer content and the gap between development, testing and production.
  • Improved access control as you can provide data analysts with access to test apps, and super users to test workspaces instead of being forced to send the reports to your production workspace/app. You also ensure that production data is not made available unless the content is ready for production.
  • Provides collaboration environment for developers
  • Automates source connections when deploying

Useful links:

What are Hill Sprints?

I am having a series called hill sprints (since we are climbing mountains – hehe) that will provide a to the point introduction on a topic covering the What, How, When and Why.

Why hill sprints?

Hill sprints are essentially a form of interval training – probably one of the more intense (but engaging) options. They are quick, brutal and to the point. Let me know if you have another fun analogy towards climbing mountains that would make sense for a series name! (Having way to much fun with this)

First Hill Sprint Series will be on Power BI Service. In this series we will go through some of the main components in Power BI Service, explaining what is it, how can you set it up, when should you use it, and why should you use it.

Hopefully, this can provide some quick insights and knowledge on the components and help decide if this is the tool for you with your current setup or challenge.

What, How, When and Why on Power BI Datamarts [Hill Sprint] — 8. Nov 2022

What, How, When and Why on Power BI Datamarts [Hill Sprint]

/Marthe Moengen/2 Comments

The What, How, When and Why on Power BI Datamarts!

  1. What are Power BI Datamarts
  2. How can you set up Power BI Datamarts?
  3. When should you use Power BI Datamarts?
  4. Why should you use Power BI Datamarts?

What are Power BI Datamarts

Power BI Datamarts are a self-service analytics solution that provide a fully managed database that enables you to store and explore your data in a relational and fully managed Azure SQL DB.

That means that you can connect your sources, transform these, set up relationships between the tables and build measures – resulting in a data model in Azure SQL database that you can connect to as any other database.

Datamarts are not a new thing though. A datamart in the world of a data warehouse is the access layer containing a focused version of the data warehouse for a specific department that enables analytics and insights to the business. A datamart could be a star schema designed to provide specific KPIs.

Hence, in Power BI Datamarts we can now build this access layer for specific business domains in Power BI Service as a star schema with relationships and measures.

If you want to learn more on Power BI Datamarts, you can read the documentation from Microsoft here.

How can you set up Power BI Datamarts?

You set up a Power BI Datamarts in Power BI Service. This is done through the workspace you want to hold the datamarts and by clicking “New”.

You then do the following:

  1. Choose source type and connect to your source
  2. Load the data source and transform this (if you want to) in Power Query
  3. Then the data source is loaded into a datamart. You can now do the following based on what you want and need to do to your data:
    • Set up relationships
    • Build measures
    • Run queries with SQL
    • Run queries using low-code functionality

You need premium capacity or premium per user to get access to Datamarts.

When should you use Power BI Datamarts?

Datamarts are particularly great if you want to do ad-hoc querying or data exploration of your data as sort, filter, and do simple aggregation visually or through expressions defined in SQL

A great thing with Power BI Datamarts is that you can model your star schema right in Power BI Service. That way you do not have to wait for the data warehouse to make smaller (or larger) improvements or changes to your datamodel as you can do these changes yourself. If these changes should be permanent or a in between solution while one wait for the datawarehouse depends on the governance that is set up.

In addition, Power BI datamarts provide a SQL end point to your data. This is great if that is a requirement from developers or data analysts. You can then use database tools as SSMS to connect to your datamart as any other DB, and run queries.

Why should you use Power BI Datamarts?

Power BI Datamartscan help us solve a range of challenges with self-service BI. Many of these are similar gains as one could get from Power BI Dataflows.

  • Improved access control as you can provide data analysts with access to the datamart instead of direct access to the data source
  • One source of truth for business logic and definitions
  • Provides a tool for standardization on the ETL process
  • Enables self-service BI for non-technical users
  • Enables reusability

Specific worth from datamarts (compared to Power BI dataflows) are:

  • Self-service solution for quering and explore data for data analysts, as well as for non-technical users as you can query the datamart using low-code functionality
  • Reduce time to production if the alternative is to wait for the needed changes or development to be delivered through the data warehouse. Also, Datamarts developers do not need code experience, and can ingest, transform and prepare the models using existing knowledge from Power Query and Power BI Desktop.
  • Power BI Datamarts support row-level-security (where Power BI Dataflows do not)

Useful links:

What are Hill Sprints?

I am having a series called hill sprints (since we are climbing mountains – hehe) that will provide a to the point introduction on a topic covering the What, How, When and Why.

Why hill sprints?

Hill sprints are essentially a form of interval training – probably one of the more intense (but engaging) options. They are quick, brutal and to the point. Let me know if you have another fun analogy towards climbing mountains that would make sense for a series name! (Having way to much fun with this)

First Hill Sprint Series will be on Power BI Service. In this series we will go through some of the main components in Power BI Service, explaining what is it, how can you set it up, when should you use it, and why should you use it.

Hopefully, this can provide some quick insights and knowledge on the components and help decide if this is the tool for you with your current setup or challenge.

What, How, When and Why on Power BI Dataflows [Hill Sprint] — 3. Nov 2022

What, How, When and Why on Power BI Dataflows [Hill Sprint]

/Marthe Moengen/3 Comments

Let’s do a hill sprint on Power BI Dataflows: The What, How, When and Why of Power BI Dataflows!

I am having a series called hill sprints (since we are climbing mountains – hehe) that will provide a to the point introduction on a topic covering the What, How, When and Why.

Why hill sprints?

Hill sprints are essentially a form of interval training – probably one of the more intense (but engaging) options. They are quick, brutal and to the point. Let me know if you have another fun analogy towards climbing mountains that would make sense for a series name! (Having way to much fun with this)

First Hill Sprint Series will be on Power BI Service. In this series we will go through some of the main components in Power BI Service, explaining what is it, how can you set it up, when should you use it, and why should you use it.

Hopefully, this can provide some quick insights and knowledge on the components and help decide if this is the tool for you with your current setup or challenge.

  1. What are Power BI Dataflows
  2. How can you set up Power BI Dataflows?
  3. When should you use Power BI Dataflows?
  4. Why should you use Power BI Dataflows?

What are Power BI Dataflows

Power BI Dataflows are essentially Power Query Online that provide a self-servie data integration tool.

This way you can create reusable transformation logic and build tables that multiple reports can take advantage of.

How can you set up Power BI Dataflows?

You set up a Power BI Dataflow in Power BI Service. This is done through the workspace you want to hold the dataflow and by clicking “New”.

Here you can choose if you want to create a new dataflow or build on top of an existing one.

For more information on how to set this up you can follow the Microsoft documentation here.

When should you use Power BI Dataflows?

Dataflows are particularly great if you are dealing with tables that you know will be reused a lot in your organization, e.g. dimension tables, master data tables or reference tables.

If you want to take advantage of Azure Machine Learning and Azure Cognitive Services in Power BI this is available to you through Power BI Dataflows. Power BI Dataflows integrates with these services and offers an easy self-service drag-and-drop solution for non-technical users. You do not need an Azure subscription to use this but requires a Premium license. Read more about ML and Cognitive Services in Power BI Dataflows here.

In addition, Power BI Dataflows provides the possibility to incrementally refresh your data based on parameters to specify a date range. This is great if you are working with large datasets that are consuming all your memory – but you need a premium licence to use this feature.

Why should you use Power BI Dataflows?

Power BI Dataflows can help us solve a range of challenges with self-service BI.

  • Improved access control
  • One source of truth for business logic and definitions
  • Provides a tool for standardization on the ETL process
  • Enables self-service BI for non-technical users
  • Enables reusability