Category: Power BI

All you need to know for migrating your Power Platform environments from one region to another

Geo Migration is a great feature/flexibility offered by Microsoft for customers who wish to move to a region which is in closest proximity to their operations even though initially their Power Platform environment region based out of a different one when they signed up. I checked out online but couldn’t find a good reference blog article yet online, hence this post.

I will make this post detailed but a comprehensive one for anyone to understand the migration. Customers can also opt for Multi Geo for those who have a need to store data in multiple geographies to satisfy their data residency requirements. If you don’t know where your Power Platform environment resides, you can check from Power Platform Admin Center.

If you were not aware yet, Microsoft Azure is the only cloud provider which offers services in more regions when compared to AWS (Amazon Web Services) and GCP (Google Cloud Platform). The Geo Migration feature seamlessly allows customers to move their environments in a single tenant from one region to another. e.g. for Singapore, it is as below.

Important:

  1. Geo Migration is not generally available, so please exercise with caution.
  2. You may reach out to your TAM(Microsoft Technical Account Manager) quoting your request
  3. There were several limitations, see below references for more details.

Mandatory Pre-Migration Check list:

  1. Any Power Apps, Power Automate Flows should be manually exported prior to the migration. Custom Connectors aren’t supported as of now, they must manually reconfigure or created in the new environment. You can export them individually or export them in group.
  2. Canvas Apps, Custom Pages, Code Components like PCF and libraries should be deleted from the environment before your migration activity starts. Else they might be in corrupted state after migration activity.
  3. If any of your Apps are not solution aware because of any reason like App calls a Power Automate when a button is called etc., you may need to explicitly export it out and take the backup.

Post Migration Check list:

  1. After the migration, import all the packages which you have taken backup during pre migration. For those which were not solution aware, import them manually.
  2. If you have Power Portals or Power Virtual Agents, those should be exported explicitly.
  3. Make sure you test all functionalities in order not to impact end users.

Notes:

You don’t need to build Apps and Flows from scratch. Dynamics 365 marketing App is not supported yet. There could be some configuration changes post migration.

While I try to put the information to the best available as per sources from Microsoft, this may change over time and variation could be different as each customer will have different workloads and dependencies with other services, so please read the references carefully before proceeding. Contact Microsoft Support or TAM as necessary.

Hope this helps to get a sneak peek into the migration process.

References:

Where is your data stored?

MultiGeo Architecture

Dynamics 365 & Power Platform new regions

Advance Data Residency Move Program

Geo to Geo Migrations

Cheers,

PMDY

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation, or the server is not responding – Troubleshooting timeouts in Power BI

Hi Folks,

When I was working with my Power BI reports, I suddenly started encountering this error. I don’t have any more clue except this error message which I could see in my Power BI Desktop as below. Initially I thought there could be some problem connecting to my SQL end point of my Dataverse connection, but it isn’t.

The error message above clearly say that the Queries are blocked. I then quickly started reviewing the model of the Power BI Report to see if there were any issues like the Relationships etc. But I couldn’t find anything in my relationships. Since I was using SQL Connection to my Dataverse, I tried to increase the Command timeout in minutes (max value being 120 minutes) from Advanced options of my connection but still the same error.

Cause: Then I quickly noticed that in my model I have fetched the same table data both using Direct Query and Import mode. So, when I was refreshing, because of the relationships, the one imported is being dependent on the one with Direct Query.

Fix: After review, the unnecessary Direct Query table was removed and voila it fixed the issue.

If anyone is facing the same problem, I strongly recommend you review the Semantic Model of your Power BI Report.

Cheers,

PMDY

Enabling TDS End Point for Dataverse (Preview Feature) from Power Platform Admin Center and its advantages

Hi Folks,

Exciting news…

Here is how you can enable TDS End Point in Dataverse…

  1. Navigate to Power Platform Admin Center…https://admin.powerplatform.microsoft.com/home
  2. Next navigate to the respective Environment to which you want to enable TDS End Point
  3. Choose your respective environment, go to Settings
  4. In the Settings windows, select the Product and then Features.
  5. Scroll down to see TDS end point, just enable the TDS end point toggle button
  6. Once this is enabled, you can also enable user level access for this TDS end point by configuring the security role as in the below step.
  7. Open Security from Power Platform Admin Center and navigate to the available security roles, go to Miscellaneous privileges, search for tds, you can find a privilege to Allow user to access TDS endpoint.

Advantages:

  1. With this TDS end point enabled, you can directly access the data in the Dataverse tables using SSMS(Preview) and in Power BI
  2. While the interesting part here is that Dataverse security model will be applied to the data being viewed by the user.
  3. That is whenever you were query the dataverse data using SSMS, the user role will be applied
  4. In the same way, if the Power BI report is built using the TDS(SQL end point), any user who is going to access the report will be only seeing the data he/she can access based on current security roles in Dataverse

While this is a cool feature for anyone who is trying to build Dataverse security in Power BI without using row level security as this is a mystery till now.

Hope this helps…

Cheers,

PMDY

Adjusting the time zone in Power BI DAX – Quick Tip

Hi Folks,

Here is the use case, I was actually pulling data from Model Driven App. As I was testing my report and cross validating the counts with that of CRM, so, it is when I noted that the count of records getting mismatched exactly by the time zone difference and if you were any other time zone except UTC and using Date Diff functions, you may encounter this problem with Power BI which you should definitely handle.

The Power BI Service will be in UTC time zone. Actually, we can’t set the time zone neither in Power BI Desktop nor Power BI Service, we have to amend the time difference, that’s it.

Consider you have a measure which calculates the date difference…consider adding the time zone difference to your DAX measures or using Power Query.

While here I am using the below DAX, I have adjusted here in DAX as below.

While I was in Singapore time zone (SGT), I have added UTC + 8 using the TIME Function available in Power BI and there you go…

Now the numbers should match exactly with that in your DataSource i.e. Power. Apps.

If you want to do this in Power Query based your scenario…you can follow the article in references.

Hope this helps someone…

References:

Edit time zone in Power Query

Cheers,

PMDY

Embed Python Visuals in Power BI Desktop – Quick Review

Hi Folks,

This post is all about embedding Python visuals in Power BI, you will need to install the respective dependent libraries like Seaborn, Matplotlib when you were creating visuals as we are using the respective libraries.

Thank you @Dr.S.Gomathi for sharing insights at GPPB Tamil Nadu, 2024, while I don’t know that Power BI has this capability. I am writing this down.

The first thing you need to do is to install Python, you can install the latest version from internet. Click here to Download Python for Windows.

Once downloaded and installed in your local machine, you can find a folder created under your Windows Start menu like below.

You need to right click on Python 3.11(64-bit) icon which is the current latest version and then click on open file location.

Then you will be able to see the contents in the folder

You need again right click on Python 3.11 (64 bit) and open its actual contents where the library files reside.

Copy this path, we need this in a while.

Now open Power BI Desktop and navigate to File –> Options and Settings –> Options

Now in the options and settings, you need to select on the Python scripting and specify the path which you just copied above as below.

Now you were ready to use Python visuals in Power BI.

Next step is to click on Python visual as highlighted below

You will be then asked to enable Python scripts as below

You will need to click on Enable as shown above. Once it is done, you are ready to start using Python visuals in Power BI.

Then you need to load data from your DataSource. Here is the link to the excel I have used. Once data is loaded into your Power BI report, you need to select respective data fields which you want to visualize. Here I am using two fields for X and Y axis, then in Power BI Desktop, you should be able to see something like below.

and in order to effectively visualize the sales trends, I will be visualizing the data using Violin Chart, which is using Seaborn library, while the Seaborn is actually based on Matplotlib library. So, I need to make sure I have those two libraries installed in my machine. You can install by using Command Prompt in your PC, you need to enter below commands and press enter to install.

pip install matplot lib

pip install seaborn

Once installed, we can plot using the below command in the python script tab in Power BI Desktop

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script:
# dataset = pandas.DataFrame(Sales, Country)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
# Set the aesthetic style of the plots
sns.set_style("whitegrid")
# Create a violin plot for Sales Satisfaction across different Product Categories
plt.figure(figsize=(12, 8))
sns.violinplot(x='Year', y='COGS', data=dataset, palette='muted', split=True)
plt.title('Sales by Product Category')
plt.xlabel('Product Category')
plt.ylabel('Customer Satisfaction Rating')
plt.show()
view raw Violin Plot Python Code.py hosted with ❤ by GitHub

Then you may need to click on run script as highlighted below

This gives your Violin chart showing the sales distribution for different product categories in your Power BI Desktop. If you were facing any problems viewing the report, check the error in the pop-up message displayed by Power BI, you can also follow the Microsoft article on this mentioned in the references.

Hope this helps someone trying to use Python visuals inside Power BI. Same way, you can use different visualizations available with Python which were not available in Power BI by default.

References:

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

Cheers,

PMDY

Community tools for Power BI Reports Development

Hi Folks,

This blog post is all about the tools developed by the community for Power BI Development over the years.

While I have only mentioned about DAX Studio in my earlier blog posts, this post lists down all the tools available till date.

  1. DAX Studio https://daxstudio.org/ – This is the single most important tool with lots of features.
  2. DAX formatter https://www.daxformatter.com/ – formats  DAX code
  3. DAX Guide https://dax.guide/
  4. Power BI Helper https://powerbihelper.org/ – tool to create documentation for the Power BI
  5. ALM Tool Kit (http://alm-toolkit.com/)- manages the application life cycle of models
  6. Bravo https://www.sqlbi.com/tools/bravo-for-power-bi/ – used for simple Power BI Tasks
  7. Tabular Editor https://tabulareditor.com/ – Used to create and manage Models
  8. Power BI Side Tools https://thebipower.fr/index.php/power-bi-sidetools/ – Increases the productivity during report development
  9. Power BI Embedded Analytics Playground https://playground.powerbi.com/en-us/home – Explore how you can use embedded analytics in your applications
  10. Business Ops – https://powerbi.tips/product/business-ops/ deployment tool for adding external tools extensions to Power BI Desktop   
  11. Power BI Embedder https://github.com/DynamicsNinja/PowerBiEmbedder XrmToolBox plugin that allows you to embed the Power BI report into the CDS form. 
  12. Power BI OptionSet Assistant – https://www.xrmtoolbox.com/plugins/GapConsulting.PowerBIOptionSetAssistant/ Creates a custom entity and populates it with records which represent option-set values
  13. Power Query M Builder https://www.xrmtoolbox.com/plugins/PowerQueryBuilder/ Create Power Query (M) scripts for Dynamics 365 and Power BI.

If I missed any, please let me know in comments.

References:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-external-tools

Cheers,

PMDY

Use environment variable to deploy different version of Power BI Reports across environments in Power Platform

Hi Folks,

Thank you for visiting my blog…in this post, we will see how we can create and manage a Power BI Environment variable in Model driven apps in Power Platform.

So, let’s say, we have two environments 1. Dev 2. Default, we want to deploy export the solution with Power BI report from Dev environment as managed solution and import that to Default environment. The report in Default environment should point to Production workspace in Power BI.

I have the following reports in workspaces.

Development workspace:

Production Workspace:

Now in order to deploy the report to Production, we need to use a managed solution and the report should point to Production workspace. So, in order to handle this, we will need to define an environment variable to store the workspace information. So, let’s get started.

First, we will create a Power BI embedded report in Development environment.

While you were creating a Power BI embedded report, you will be presented an option to choose from the Power BI workspace.

In order to achieve this requirement of deploying different versions of Power BI report in different instances, we need to use environment variable, so check the Use environment variable option.

  1. The environment variable will be specific to this report and should be included in the solution when we want to deploy this report to higher environment.
  2. The next thing to note is that Default workspace would reflect the default value for this report and current value is required when we want to set to another report in a different environment.

In Development environment, we choose as below..

Once the environment variable is saved, we now have 1 Dashboard and 1 environment variable component in the solution.

This solution is published and then exported as Managed solution, imported to another environment (Default environment which serves as Production environment here).

While importing, it asks to update environment variable, you can proceed to click on Import.

Now we have the solution in Default environment.

In order to update the value of the report to consider from Production environment, we need to open the report and click on the Pencil icon besides the Power BI Environment variable.

Then choose Prod workspace and its respective report and click save, publish.

That’s it…

You will be able to see two different reports in your Development and Default instances.

In this way, it is very easy to manage and deploy different versions of Power BI Report to different environments like Dev, Test, Prod.

Hope this helps…

Cheers,

PMDY

Show last refreshed time for your Power BI Reports in Import Mode – Quick Tip

Hi Folks,

If you are working on Power BI, this is a good to know tip.

In case you were using Import mode which is by default suggested by Microsoft for medium or small-scale datasets as it uses Vertipaq engine for improved performance and compression, this post is definitely for you.

Did your user ever asked why they were not able to see latest data in the report. Possibly you could have said it is because of refresh frequency.

Then you could have thought if there was a nice way to show when the dataset was last refreshed. This definitely help your users to have a clear idea of what’s going on.

FYI, the refresh frequency could be set in Power BI service as below for import mode.

In your Power BI report, click on Transform data.

Click on New Source –> Blank Query as below.

In the Query Fx expression…. enter the below expression to get the last refresh time and click on Tick symbol.

Next, click on To Table to create a table from this data as below.

Rename it to something meaningful like below.

Rename the Query1 variable as below..you should see the applied steps getting added for each operation you performed.

DateTime.LocalNow() gets the last refresh frequency of your dataset in your local time.

Click on Close & Apply…

Now in your report, just add a card visual at the bottom right corner and drag the Last Refreshed On query.

That’s it, next time onwards, you should see the date and time when the refresh had occurred.

Cheers,

PMDY

Design your Data Model efficiently with Star Schema for Power BI – [Must Know]

Hi Folks,

This blog post isn’t about any use case, rather it just highlights the importance and benefits of designing your data model for your reporting requirements. Every Power BI Developer should consider this at first place.

When designing your Power BI Reports, Data Modelling is the first step whenever you want to work with the Power BI dashboards or reports which plays a very key role. Coming to Schemas, I can say there are two schemas namely Star Schema and Snow Flake Schema. This blog post mainly talks about Star Schema for your Power BI Report/Dashboard design.

With Star Schema, Power BI data models are optimized for performance and usability. While every consultant try to create stunning visuals, they also need to focus on their data model before spending time on their report design.

Star Schema revolves around 2 types of tables in general, they were Fact tables and Dimension tables(talks about the business entities).

Fact table is central table in star schema. Dimension table are the tables which were connected to Fact table using a one-to-many or many-to-one relationship. Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time. So now let’s see how a star schema looks like and taken from Adventure works sample.

Image shows an illustration of a star schema.

Main point to note here is Normalization and Denormalization capabilities which are two great concepts to understand how star schema can help increase the performance of your dataset.

Star schema requires normalized tables and SnowFlake Schema needs denormalized tables. The design fits well with star schema principles:

  • Dimension tables support filtering and grouping
  • Fact tables support summarization

You can visualize the relationship as per the below diagram…

These concepts include, I will brief about the below topics which were not widely popular yet must know for designing an efficient Power BI Dataset.

Last but not the least, I should say that following and designing your data model using Star Schema is a best practice suggested by Microsoft.

You can lookout for references if you want to see video which can be of great help for you to understand the star schema mainly for beginners…

References:

Star Schema Data Model in Power BI

What is star schema?

Hope this helps…

Cheers,

PMDY

5 Power BI Errors..you often encounter – Fixed

Hi Folks,

Here I would like to give some tips regarding Power BI Errors which will be encountered in everyday job of anyone who’s going to or work with Power BI Dashboards or understand any existing dashboards and integrate them with Model Driven Apps. You aren’t alone…so let’s gets started…

Error 1: Unable to open document

Fix:

You were not on the latest version of Power BI desktop with which it was authored. You can quickly check the version by navigating to Help –> About

You should see a update icon as below

Clicking on it will take you to the respective page having the latest update from where you can update it.

Error 2: Class not registered error

Fix:

This can happen when you were trying to open an existing dashboard which was created by you or shared by someone to you, might be because you have two versions of Power BI Desktop installed..in your machine..like shown below.i.e.

  1. Installed Power BI from Microsoft Store
  2. Installed Power BI from Microsoft Downloads

Make sure you choose the right one which you have used earlier to develop the Dashboard so that you won’t run into any issue.

Error 3: Workspace deleted

Fix:

This error can happen when you were trying to access any workspace while you were creating a Power BI Dashboard in Dynamics CE using an existing workspace, make sure you have a workspace and the logged in user have access to the Workspace.

Granting access to the workspace will fix the issue.

Error 4: One or more Cloud Datasources have been deleted

Fix:

Whenever you deploy any new Power BI report or dashboard to Power BI Service, you encounter this problem most of the times. Sometimes after making a change to a dataset or taking over ownership of a dataset, you might receive the following error:

1. Click the “Recreate cloud data sources” button.

2. Reenter credentials in the “Data Source Credentials” section for your data sources

Make sure dataset is connected to Gateway incase you have configured.

Error 5: There is no gateway to access the datasource

Fix:

The first thing to look at when you have a missing gateway is to make sure you created a data source for that gateway. Installing the On-Premises Data Gateway is not enough. That simply registers the gateway with the Power BI service and lists you as an admin for that gateway.

You then need to go into the Power BI service and create a data source for that gateway.

You can create a data source by doing the following.

  1. Select the gear icon within Power BI
  2. Select Manage Gateways.
  3. Select your gateway and then select Add Data Source.
  4. When you are done entering the information, select Add.

References:

Power BI Start up issues – MS Learn

One or more cloud datasources have been deleted

Restore and Recover Deleted Workspace

Hope my post helps someone who’s facing similar issues with their Power BI Dashboards…

Thank you for reading….

Cheers,

PMDY