You might be wondering if anything changed and the answer is yes! As part of the process there is now an automated way to handle the Power BI report dataset rebind process after the migration (this does not include Paginated reports). In the February post I showed how you can leverage the API calls and do this manually, well now there is a simple menu option that will do it for you as mentioned in the May post:)
This new rebind report option is available in the AAS database migration details area from the database context menu like shown below
Here you will see three options – Rebind reports, Undo rebind, and Rebind status.
In my example here for the demo after my AAS database migration to a Power BI dataset I have four copies of the report that are in different workspaces – the migration workspace AASMigration, My Workspace, Demo Workspace, and Demo Pro Workspace. Two of the workspaces are Premium – AASMigration and Demo Workspace – and then two are not – My Workspace and Demo Pro Workspace. What we will see is that the new “Rebind reports” process can take care of all reports referencing the existing AAS database across the entire tenant, even workspaces that I might not have permissions to.
Before checking out the new menu options let’s first look at the reports referencing the AdventureWorksAAS_PowerBIRpt dataset that has the Live Connection to the AAS database.
We can see above that there are currently four reports. Now let’s go ahead and test out the new menu options.
When I select the “Rebind reports” option I will get the below pop out panel message (no details since this is the first time I have selected the option).
After a short period of time if I select the “Rebind status” menu option I will see the following –
If I look at the lineage view for the dataset we will see that there are four reports in the four different workspaces now associated with the Power BI dataset that was migrated over from AAS.
I can then go into one of the reports in any of the workspaces and see that the report is working as expected as shown here by opening the report in the “Demo Pro Workspace” that is referencing the dataset in the “AASMigration workspace”. This is using a Pro/Shared workspace going against the dataset that is in a Premium workspace.
If you were just testing out the rebind process or the database migration process you could then go ahead and use the “Undo rebind” process and that will switch the dataset related reports back to the AAS database.
Note: after you run the rebind process it can take a minute to propagate the permissions, so if you try and click on a report immediately after you run the process you might get the following message as shown below – “Permission required – You cannot see the content of this report because you do not have permissions to the underlying dataset. Please contact the dataset owner to request access.” Just be patient and what a minute or two for the permissions to be updated with the source being swapped out within the report.
So that is it for the new Power BI report rebind automation process, pretty cool and saves a lot of time if you have a lot of reports. The nice option is if for some reason you do the rebind and there is an issue you can always use the undo option and revert back;)
So what do you think? Have you tried the AAS migration process yet? If so I would love to hear your feedback and experience, please leave a comment below!
If you have existing Power BI reports using the AAS live connection you are able to use the report rebind API to point the report to use the Power BI dataset. The one thing to note as pointed out in the documentation is that as of now if you need to modify the report layout you would need to do that directly in the service. That portion of the migration process is still being worked on and hopefully should be updated to resolve the issue so that you will be able to download the redirected report from the service, modify it, and then publish the report back to the Power BI service.
In this post I want to touch on this a bit more and provide some follow up based on work that has taken place since that post on the migration rebind (redirection) process.
In my Power BI workspace I have two reports that are connected to my original AAS database used in the migration process, one is a Power BI report and the other is a Paginated (Operational) report.
If we look at the lineage view we can see that both reports are referencing Analysis Services –
I have already gone through the AAS to PBI Premium migration and moved the database from Azure Analysis Services to the Power BI Premium workspace shown above and it is named ‘AdventureWorksLTAAS’ as shown below in the screenshot.
Now what we can do is use the APIs to redirect both reports from using AAS so that they use the new Power BI dataset. Since I have to different types of reports I will be using two different APIs and we will start with the Power BI Report. To do this I will open up Windows PowerShell ISE as Administrator, login to Power BI, and then call the Report Rebind in Group API. Below is the outline of the steps to use and I included the install of the Power BI modules as well. You would need to include the values of the new dataset, workspace, and report and remove the square brackets.
#STEP 1: run PowerShell as Administrator
#STEP 2: run script if you don't have this module installed, might get prompted to install library
If (!(Get-module MicrosoftPowerBIMgmt)) {
Install-Module -Name MicrosoftPowerBIMgmt
}
#STEP 3: login to service (you will get prompted)
Login-PowerBIServiceAccount
#STEP 4: rebind report to Power BI dataset (new model)
$body = @{ "datasetId" = "[new dataset id]" }
Invoke-PowerBIRestMethod -Method Post -Url "https://api.powerbi.com/v1.0/myorg/groups/[workspace id]/reports/[report id]/Rebind" -Body ($body|ConvertTo-Json)
Once this is completed you will see that the lineage view has changed for the Power BI report so it is now referencing the Power BI dataset that was created as a result of the AAS database migration.
Now we will do a similar process with the Paginated report, but for this you need to use the Update Datasources in Group API since this is an RDL report file. Below is the code used for this in PowerShell and you would need to provide the corresponding values for the datasource name, workspace XMLA endpoint, dataset name, workspace, and report and remove the square brackets.
#STEP 1: run PowerShell as Administrator
#STEP 2: run script if you don't have this module installed, might get prompted to install library
If (!(Get-module MicrosoftPowerBIMgmt)) {
Install-Module -Name MicrosoftPowerBIMgmt
}
#STEP 3: login to service (you will get prompted)
Login-PowerBIServiceAccount
#STEP 4: rebind report datasource to Power BI dataset (new model)
$body = '{
"updateDetails":[
{
"datasourceName": "[datasource name]",
"connectionDetails": {
"server": "[workspace xmla endpoint]",
"database": "[dataset name]"
}
}
]
}'
Invoke-PowerBIRestMethod -Method Post -Url "https://api.powerbi.com/v1.0/myorg/groups/[workspace id]/reports/[report id]/Default.UpdateDatasources" -Body $body
The one thing to note about this process for the Paginated report is that the actual RDL file doesn’t get modified. So if you were to download the file or use the option in the service to “Edit in Report Builder” you would still see the AAS information. If you view the lineage though you will see that it is referencing the Power BI workspace now via the XMLA endpoint as shown below.
So an alternate option if you actually want the Paginated report file definition updated would be to use the “Edit in Report Builder” option and change the actual information or update the file that you might have in source control and simply republish.
Now the final thing I want to wrap this post up with is the issue regarding modifying the Power BI report files as I mentioned in my previous post as well as which is highlighted in the “Import Considerations” section of the AAS migration documentation.
During preview, if you use the Reports – Rebind Report Power BI REST API to create a new binding to the migrated dataset, you can’t make changes to reports in a Power BI Desktop .pbix that were previously bound to the model in Azure Analysis Services and then republish to Power BI. Report changes for a migrated dataset can be made in the Power BI service.
What you will see if you download the Power BI report file from the service, modify it, and then try to publish it back to the workspace is the following message.
This is the item that was being worked on and I am happy to announce an update is currently being deployed to resolve this. This update is in the process of being deployed to the service so it will take some time to make it available in all regions, but once it is what you will be able to do is use the “Upload” option in the workspace to get your updated file available.
There is of course still the option to simply update the files directly in the service as mentioned. The other fix that is coming and will be available in the March Power BI Desktop release is to resolve the dialog message above “Couldn’t publish to Power BI” when you publish.
With the March release you will see the following message when you publish your report that you downloaded after the report rebind and want to publish changes back to the service.
That is what we like to see:)
So I would be curious, has anyone been using the AAS to Power BI Premium migration process? If so, what has your experience been and what kind of feedback do you have? Feel free to leave a comment, I would be very curious to know more about your journey!
Being able to publish Analysis Services databases to Power BI Premium has been available to do previously, but this feature makes the option easier. In order to use this feature make sure you read over the prerequisites and information about the migration experience (I highly recommend reviewing this prior to actually attempting to do a migration) Migrate Azure Analysis Services to Power BI. I also got to help review and provide feedback about this documentation as well, so if anything appears to be missing or needs more explanation please use the Feedback option at the bottom of the page.
Some key items that I like about the new experience versus simply doing this manually on your own is the following:
Automates the process of backing up AAS database, copying the backup to ADLS Power BI storage, and then restoring the database which becomes the Power BI dataset
This will not only migrate 1500 compatibility mode models but also 1200 without any issues
It will migrate the roles and permissions over, so database Admins will have write permissions on the dataset and other users will have build permissions
The other item once you have migrated the databases over is the ability to enable the AAS server redirection
AAS server redirection, what does that do you? Well, let’s say that you have users that might have created a bunch of PivotTable reports in Excel against the AAS database. As part of the migration experience you will now be able to simply redirect all of these users to the Power BI dataset and they won’t have to make any changes in their Excel file connections or even have to know that anything changed. All with a click of a button! Wow, pretty cool huh? And once the server redirection is enabled you can actually pause the AAS service and all external client applications, tools, and automated processes will simply get redirected to the Power BI dataset.
If the redirection is not working refer to the documentation link for the client library minimum requirements, initially the redirection wasn’t working with my click-to-run (C2R) install of Excel but with a recent update this past month it now has the updated msolap library:) Actually I ran into an issue with my Office install and to resolve the issue the online repair uninstalled and reinstalled my Office which not only fixed my issue but also allowed me to go with the 64bit version of Office and provide the updated Excel and msolap library;) For Excel (at least the C2R version) you can find the msolap version in the following location –> %programfiles%\Microsoft Office\root\vfs\ProgramFilesX86\Microsoft Analysis Services\AS OLEDB\140
I won’t go over all of the steps of the migration process since those are covered in the documentation as well as shown in the video links above but if you try and create a new migration and get the following message “You don’t have permission to access to the server” as shown below it is most likely that you don’t have owner or contributor access to the AAS service in Azure, so verify that.
And after you create the migration in the Power BI service it might not be obvious but to begin the process you need to click on the AAS server name to review the configuration and to start the database migration process.
Once you access the migration you need to make sure that the AAS server is started and running, otherwise if it is paused you will get the following error message
On the migration page details screen you will see all of the configuration information for the AAS server as well as the Premium workspace and a list of the databases that are available to migrate.
If something is not configured properly as per the prerequisites you will get a warning message letting you know like if you don’t have storage configured for either the AAS server or workspace. This is a requirement for the backup and restore process to complete successfully.
If you try to migrate an AAS database that is larger than the Power BI Premium capacity will allow you will get a message like the following during the attempt to restore the backup file to the workspace. In this case I am trying to restore a database that is larger than 3GB to an A1 SKU which has a 3GB limit and this fails. So make sure that the capacity you are migrating to will support the databases you want to move. You can see the details and progress of the migration if you click on the database name when the migration process starts.
Currently a blank dataset will be created after the above failure in the workspace and it won’t contain any metadata – something that should probably be cleaned up and is a bit unexpected.
If the database does successfully restore then you will be able to start using it to create reports off of and once everything is migrated over you can enable the server redirection so that all of the external connections will start using the Power BI datasets and then you can pause the AAS server.
Once the AAS server is paused after the redirection is enabled you will see that the Excel PivotTables will still continue to work without having to make any changes to the connection string information.
Now that redirection is enabled I pause the AAS server and then change the slicer selection in the Excel PivotTable and the report still works as expected. You might get prompted for authentication and then the other thing you will see is if you are using sensitivity labels in Power BI that the file will inherit that as well.
If you have existing Power BI reports using the AAS live connection you are able to use the report rebind API to point the report to use the Power BI dataset. The one thing to note as pointed out in the documentation is that as of now if you need to modify the report layout you would need to do that directly in the service. That portion of the migration process is still being worked on and hopefully should be updated to resolve the issue so that you will be able to download the redirected report from the service, modify it, and then publish the report back to the Power BI service.
If you are currently using SSAS or AAS and haven’t moved your models to Power BI Premium I would definitely try it out and this new migration experience definitely simplifies the process. Please provide feedback and let us know about your experience.
In this post I just wanted to show the ability to use the Delta Lake format that is very common now with Power BI. I will go over a quick example of creating the files to reference, building a view to use with Power BI, and then querying the data in Power BI.
In my Synapse Workspace I created a Linked service connection to an Azure SQL Database that has the AdventureWorksLT database loaded which is the sample database you can create when you first create a SQL instance in Azure and here is a walkthrough link and see the Additional settings section.
I am going to use this database as the source to generate the Delta Lake format files in an Azure Data Lake storage account. To do this I created a Data flow in my Synapse Workspace and referenced two tables as my source, one being the Product table and the other the Product Category table. Really basic example, just really want to highlight the ability to easily reference the Delta Lake format files that are in the storage account within Power BI.
Synapse Data flow screenshot
On the source side I am referencing the Linked server connection that I already established against the AdventureWorksLT sample database in Azure SQL as you can see below in the Source settings and above you can see I am actually providing a query to simply extract just the columns I am interested in on the Source options tab.
Synapse Data flow source settings screenshot
This setup is done for both the sources in the Data flow and then on the sink side the target destination is going to be an Azure Data Lake storage account. For the Inline dataset type I specified Delta.
Synapse Data flow sink screenshot
And on the sink settings tab you just need to provide the folder path and compression information as shown below for where the product table Delta Lake files will land.
Synapse Data flow sink settings screenshot
After the Data flow is configured and in this example I am just doing the Product and Product Category tables then add the Data flow into a Synapse Pipeline and Trigger the Pipeline to run to create the files in the Azure Data Lake. After you add the Data flow activity to the Pipeline you just need to configure the settings as shown below and then under Add trigger do the Trigger now option. This should just take a few minutes to run to queue the job and spin up the resources.
Synapse Pipeline Data flow screenshot
Now in the Synapse Workspace I can go into the Data hub and view the files that are in the linked data lake storage account as shown below.
Synapse Data hub linked data lake screenshot
With the files in the Azure Data Lake we are now going to reference these with the Synapse Serverless Pool. To make it easier to reference these in Power BI I will actually create a view that combines the data from both tables to include the columns of information that I want for my report. The first thing will be to create a database in the Synapse Serverless Pool and this will store the view that we will reference in the report.
Synapse SQL Serverless script screenshot
In the screenshot above I have the script to create the database. After that gets created as you can see in the screenshot in the top-right I connect to that database and then that is when the script to create the view is run.
If you query the view or run the SELECT statement above you will see the results as shown below in the screenshot.
Query results from Delta Lake format files in ADLS screenshot
In Power BI Desktop now you can Get Data and by using the Synapse Serverless SQL endpoint that you can get from the Azure portal on the Synapse Workspace Overview page you can make a connection to the servless_ondemand database we created and select the vwProductCategory view to include in the report.
Power BI Desktop Get Data Synapse Serverless SQL screenshot
By know means is this a great end to end example, just simply show casing the ability to reference the Delta Lake format files in Power BI, but you can create a report and in DirectQuery mode query and interact with the information that is in the Azure Data Lake with Synapse Serverless Pool that is stored in Delta Lake format!
Power BI Desktop report screenshot
This was a pretty brief overview, but hope you can see the possibilities of using the Synapse Serverless capabilities to easily reference the data in an Azure Data Lake and query the data directly using Power BI and in doing this we even created a view that joined the data from two different tables (files) in the lake!
I thought I would do a short post outlining my career journey up to the point where I finally was hired and accepted a position to join Microsoft❤️I will try and keep it brief because along the way I could highlight lots of stories regarding the people, companies, and customers I have worked with which would get lengthy😉
My first big job coming out of college was at a casino in Minnesota (where I have always lived) that my oldest sister referred me to. I graduated with a Finance degree with Accounting and Economic minors and the position was in the Revenue Audit department (got to audit the daily gaming revenue numbers which included slots, blackjack, pull tabs, and bingo). I really got interested in technology here, not that I hadn’t used computers prior since my dad was a computer programmer, but I started to get into MS Access (Office 2.0) which led me down the path of eventually getting recruited by the IT Department.
In the IT Department I worked with MS Access, FoxPro, Visual Basic, SQL Server, Oracle, SharePoint, and a few more technologies like DataStage and ProClarity just to name a couple more. My passion was always around the Microsoft technologies and in particular the data and reporting side which led me down the path of data warehousing and business intelligence. I still remember loading data into SQL Server OLAP Services and slicing and dicing the gaming revenue numbers, it was amazing🤓
From the casino I was recruited into the consulting world and I will admit that was a scary transition because I had worked at the casino for over 10 years and was very comfortable there. I worked for two different consulting companies over another 10 year stretch that both focused on Microsoft technologies and I got an opportunity to work with lots of different customers and projects, it was a great experience and it was stressful at times when all of the eyes are on you and you are being looked to for all of the answers – hopefully you have built a good internal network and can leverage others when you are stuck or have a question. I have lots of great stories I could go over, but will save them to discuss over a cold one sometime off the record😁
During the consulting years I also helped run the local Minnesota SQL Server User Group, Minnesota BI User Group, PASS Business Analytics Virtual Group, and helped with some of the Minnesota SQLSaturday events. I also did a lot of presentations, helped out in the Microsoft forums, tech edited books, got to co-author a book and was a Microsoft Data Platform MVP. Also during this time I coached my son’s soccer and helped coach his basketball (spending time with the kids was one of those things that I didn’t want to miss out on and the reason I stayed away from a job that required travel).
Eventually I decided to leave consulting and move back to a full-time position at a company. I worked for two different companies over the next 5 years and the last job prior to joining Microsoft is the one that got me the hands-on real-world experience with Azure and the Microsoft cloud technologies. Sure I had dabbled in the cloud a bit being a Microsoft MVP, but using it at a large enterprise is a different story and glad I got that opportunity before joining Microsoft.
My Speaker Background slide screenshot
So now if you see this slide, or one similar, during one of my presentations maybe this post will shed a little light on my journey going from the business world into the land of data warehousing and business intelligence and to the current position at Microsoft (what a dream, I am a huge #MicrosoftAdvocate and #MicrosoftNerd).
In my last post I went over using a Service Principal account to access a Synapse SQL Pool with Power BI. I even showed how you could go across different tenants in my example. In this post instead of going against a SQL Server source I am going to switch to a Synapse Data Explorer (Kusto) Pool. Once again we will use the SQL Server ODBC driver just like we did in my last post.
For this post I will be using the same Service Principal and driver that we used in the previous post, so if you need any guidance with that or link to download the driver please reference that post here.
For the example that I will present here I will be do the following:
Create a Synapse Data Explorer Pool and load data
Grant the Service Principal account permissions to the Synapse Data Explorer Pool database (NOAA Storm Events data)
Configure the ODBC System DSN to access the Synapse Data Explorer Pool
Create a dataset and report using the ODBC source connection in Power BI
Publish the report in a different tenant than the Synapse Workspace
Configure a gateway to use in the Power BI service with the dataset
Refresh the Power BI dataset and view the report in the service
As mentioned I will be reusing some of the items that were covered in the previous post, the only difference is that we will be going against a Synapse Data Explorer (Kusto) Pool. If you are already familiar with Azure Data Explorer (ADX) you can review the document here to see how these compare.
First step will be to create the Synapse Data Explorer Pool within the same Synapse Workspace we used in the previous post with the Synapse Dedicated Pool. This can be done in a few different places within the Azure Portal (Synapse Workspace Overview page or Analytics pools section) or in the Synapse Studio (Data or Manage Hub). In this example I am doing it in the Azure Portal and simply click the New Data Explorer Pool option in the Synapse Workspace Overview page and complete the required information.
Create Data Explorer Pool configuration screenshot
I didn’t make any other setting changes and simply created the pool, this will take a few minutes to complete (approximately 15 minutes for me). In the meantime I have decided to use the NOAA Storm Events data which is a sample you will explore if you doing any of the Kusto Query tutorials on the Microsoft Learn site. I went ahead and downloaded all of the StormEvents_details files (73 of them) and extracted them to a folder on my laptop (1.31 GB total).
Once the pool is created on the Overview page you will be presented with a wizard that you can complete to create the initial database and load data.
Synapse Data Explorer Pool wizard screenshot
Simply click the Create database button, enter a database name and set the day settings, and then click Create.
Data Explorer Database creation screenshot
Creating the database will not take long and after you click the Refresh button on the Overview page you will be at Step 3 – Data Ingestion. Click on the Ingest new data button which will then launch Azure Data Explorer window with a connection to the newly created database. If we right-click on the database we can select the option to Ingest data which will launch us to another wizard to name the table, provide the source, set the schema, and then ingest (load) the data into the new table.
Azure Data Explorer Database screenshot
Provide a name for the new table on the Destination page and then on the next page in the wizard we will set the source.
Ingest New Data Destination screenshot
For the Source page you can simply drag and drop the 73 storm event csv files and it will start to upload them which will take a few minutes to complete (approximately 8 minutes for me). Next you can go to the Schema page and review what was done based on analyzing the data.
Ingest New Data Source screenshot
Go ahead and review the Schema page to how the data was mapped if you want and then click on the Start ingestion button.
Ingest New Data Schema screenshot
Now the storm events data will be loaded into the table and this will take a few minutes to complete but should go relatively quickly and once this is done you will see a Data preview.
Ingest New Data screenshot
Now we are ready to move on to the second step and grant the Service Principal account access to the database. This can be done at the cluster level or database. In the Azure Portal within the Data Explorer Pool Permissions page you can add the Service Principal to the AllDatabasesViewer role.
Data Explorer Pool Permissions screenshot
Or in Azure Data Explorer in the Query tab with the connection to the new database you can use the following syntax –> .add database <databaseName> users (‘aadapp=<application id>;<tenant id>’) ‘<app name>’ as shown below
The third step is to create the ODBC System DSN which is pretty similar to how we did it for the Synapse Dedicated SQL Pool, but there is a slight twist which took me awhile to figure out and had to go into the registry to make a modification to the ODBC entry🤓
Just like we did in the previous post we will go into the ODBC Data Source Administrator (64-bit), click on the System DSN tab, and click Add. As before use the same ODBC Driver XX for SQL Server, click Finish, enter a name for the DSN, and then provide the Data Explorer Pool connection information which in this case will be in the format of <pool name>.<workspace name>.kusto.azuresynapse.net and you can get this from the Overview page of the Data Explorer Pool in the Azure Portal. It is the URI information and simply remove the “https://” information.
First page of the System DSN configuration screenshot
On the next page just like before we will select the option for Azure Service Principal authentication, provide the application id for the Login ID, and then click Next. There is no need to put the Secret Value yet for the Password because we need to simply create the entry first in the registry to modify it and then we can come back in with the password to do an official test.
Second page of the System DSN configuration screenshot
On the next page you can go ahead and set the default database setting and then click Next.
Third page of the System DSN configuration screenshot
On the next page you can click Finish and then OK. Now if you would have provided the Secret Value and then tried to test the data source connection you would have been provided this message about “No tenant identifying information found in either the request or implied by any provided credentials” as shown below.
Data Source Connection Error screenshot
This is what we are going to fix in the registry and it took me awhile to track down the solution and thanks to this article here on MS-TDS clients and Kusto I determined how to include the tenant information into the “Language” field in the ODBC data source🤠 Once you have created the System DSN you need to use the Registry Editor to add a new String Value to the ODBC entry called “Language” with the “any@AadAuthority:<tenant id>” format as shown in the link above.
Registry Editor ODBC Language screenshot
Now if you switch back to the ODBC Data Source Administrator you can configure the System DSN that was created to include the Secret Value for the Password and then as you click through to the final page you will see the language information included now based on what you entered into the registry.
System DSN Configuration Language screenshot
And when you go to Test Data Source now you should see (fingers crossed) a “TESTS COMPLETED SUCCCESSFULLY!” message.
System DSN Test Data Source screenshot
The fourth step now is the easy part, just like we did in the last post, open Power BI Desktop, use the ODBC connection, and connect to our Kusto database to create our dataset.
Power BI Desktop Get Data ODBC screenshot
There is just one table so this is pretty easy. We can clean up the column names to make them more readable, add measures, hide columns that are not needed, etc. and then build out a report. As you can see below in the report we have over 1.7M records loaded and the size of the Power BI file once all of the data is loaded is 188 MB which is much less than the 1.31 GB of CSV files👍
Power BI Storm Events report screenshot
For the fifth step now we simply need to publish the report to the Power BI service which is straight forward and just like the last post and so is the sixth step where we configure the gateway connection once published to include the new DSN data source. Just remember to replicate the ODBC configuration that we did above on the gateway machine that is being used by the Power BI service.
Dataset settings Gateway Connection screenshot
Just make sure to follow the same steps as the last post and reference that if you have any questions.
For the final step you can now go ahead and test everything out by running the dataset refresh and then viewing and interacting with the report.
Dataset Refresh history screenshot
And if we interact with the report everything should be good and just remember once again this demo like the last post is going across tenants using the Service Principal account.
Power BI report screenshot
That completes this post covering the topic of being able to use a Service Principal account to connect to Synapse Data Explorer Pool for a Power BI report.
Let me know what you think and thanks for checking out this post, I hope you found this useful:)
In this post I will go over a topic that is frequently asked about and that is using a Service Principal account with Power BI when connecting to data sources. Currently today none of the built-in connectors support this capability natively, but the SQL Server ODBC driver does support the use of a Service Principal account. The one caveat with using an ODBC driver with Power BI is that a gateway would be required once the report is published to the service.
For the example that I will present here I will be do the following:
Create a Service Principal account (App Registration) in Azure
Grant the Service Principal account permissions to the Synapse Workspace Dedicated SQL Pool database (Adventure Works DW schema)
Install and configure the SQL Server ODBC driver (including the System DSN)
Create a dataset and report using the ODBC source connection in Power BI
Publish the report in a different tenant than the Synapse Workspace
Configure a gateway to use in the Power BI service with the dataset
Refresh the Power BI dataset and view the report in the service
As noted above in this example not everything will be in a single tenant, so that is a slight twist and I have worked with customers where this type of a configuration was needed. This means that the database will reside in one Azure tenant and the report and dataset will be in another and everything will work.
First step is to create the Service Principal account in the Azure portal. In Azure Active Directory in the portal you will go into App registrations and pick the option to add a New registration. When you create the new account this is where you can determine if it can be used multitenant or not and in my example that is what I selected.
New App registration screenshot
Once that is created in the new App registration you will need to create a New client secret which will be used as the password when connecting to the database for authentication purposes. When creating the secret you can determine when it will expire as well.
Adding a Client Secret to App registration screenshot
Once you create this you will want to copy the Value for the client secret and store this in a secure location, that will be the password we will use later. The other item that you will need to capture that will be used as the Login Id or the User Name when connecting to the database is the client Application ID and you can get that guid value on the Overview page for the App registration you created.
App registration Application ID screenshot
The second step is to grant permissions for the Service Principal account to be able to access the database. You can run the following script once you are connected to the database and in this case I am using a Synapse Dedicated SQL Pool database where I have loaded the Adventure Works DW database. Whether you run the script in the Synapse Workspace or using SSMS or Azure Data Studio it will all work the same.
SQL Scripts to add Service Principal user and grant permission screenshot
The third step now is to download and install the SQL Server ODBC driver (I will be using the 64-bit which will be required on the gateway to work with the service) and then configure the System DSN. If the machine you are using doesn’t already have the Visual Studio C++ Redistributable runtime libraries installed that will be required for the driver install.
Once the driver is installed then you can launch the ODBC Data Source Administration (64-bit), click on the System DSN tab, click the Add button, select ODBC Driver XX for SQL Server, and click Finish button.
There will be quite a few screenshots showing the configuration of the System DSN. The first page in the wizard is pretty straightforward provide a name for the DSN and provide the SQL Server instance name (you can see in my example below we are connecting to a Synapse database.
First page of the System DSN configuration screenshot
The next page is where the hidden Easter egg is where you can select the Azure Service Principal authentication. This is where you will put provide the Application ID and the Client Secret Value for the Login ID and Password.
Second page of the System DSN configuration screenshot
The next page is where you can provide the default database name you will be connecting to and after this you can click through until the end where you get a change to test the connectivity.
Third page of the System DSN configuration screenshot
The last page is where you can test the data source connectivity and if you did everything as expected you should see the “TESTS COMPLETED SUCCESSFULLY!” message.
Final page of the System DSN configuration to test connectivity screenshot
Once you have completed this configuration wizard and the connectivity worked successfully you will then see the new System DSN added as shown below.
System DSN added in ODBC Administrator screenshot
So far so good, we are about half-way through the process;)
The fourth step is to open Power BI Desktop and Get Data using ODBC option as shown below.
Power BI Desktop Get Data ODBC option screenshot
For the ODBC Data source name (DSN) you will provide the name you entered when creating the System DSN in the previous step.
ODBC data source name screenshot
Once connected with the System DSN you will then be presented with the Navigator as shown below to search and browse the database.
Navigator view of the ODBC connection screenshot
Now simply select the tables that we want to include in our data model and in this example I selected five tables, four dimension tables and one fact table.
Select tables for data model screenshot
After I selected the tables I went through the transform steps keeping the columns that I wanted to use in the report or for the relationships and renamed the tables. Once the data was loaded I then verified the relationships and created a few measures as well hid some of the columns. I then quickly put together the report below, nothing fancy by any means.
Power BI report using ODBC data source screenshot
The fifth step is simply publishing the report to the Power BI service and in this particular case I decided to deploy this to my demo tenant versus my primary organizational tenant. To do this I made sure in Power BI Desktop to login with my demo tenant account and then publish the report. For this there isn’t much to show, simply switched the account I was logged into within Power BI Desktop and then published the report.
The sixth step is getting the gateway ready which will be needed since I am using an ODBC data source. In my demo tenant I already had a virtual machine already available in my Azure subscription, so I went ahead and started it up, remoted into the virtual machine, downloaded and installed the SQL Server ODBC driver, and then configured the System DSN just like we did in the previous steps above.
Once the virtual machine was configured and running the next thing to do is to review the settings of the dataset that was deployed with the report in the previous step and specifically we want to look at the Gateway connection as shown below.
Dataset settings Gateway Connection screenshot
In the above screenshot I selected the action arrow on the gateway I configured with the ODBC driver and for the data source in the dataset clicked on the link to Add to gateway option. This will then open up a dialog to add a new data source to the gateway as shown below where you will need to add a Data source name and for authentication provide the Application ID and Client Secret Value again for the Service Principal account. Once you have done this you can click Create.
Create ODBC data source in Gateway screenshot
Once the data source has been added to the gateway then we simply need to map this in the Gateway connection, click Apply, and then we can proceed to the next step of running the data refresh!
Map Gateway connection to Data Source screenshot
The seventh and final step is to run the dataset refresh and view the report to make sure everything is working as expected. After running the on-demand refresh and reviewing the dataset refresh history in the settings we see it completed successfully!
Dataset Refresh history screenshot
And then if we view the report and interact with the slicers and visuals we can see everything is working as expected.
Power BI report screenshot
That completes the post covering the topic of being able to use a Service Principal account to connect to a data source for a Power BI report. Currently the only option that I am aware of is the one I have shown, which is using the SQL Server ODBC driver. In this example I used it to connect to a Synapse Workspace Dedicated SQL Pool database located in one Azure tenant and run a report and dataset that was published in a separate tenant.
Let me know what you think and thanks for checking out this post, I hope you found this useful:)
In the last post I went over using Power Automate to perform a data driven report subscription using a Paginated report referencing a Power BI dataset. The flow referenced an Excel file with the information to make the process data driven and generate 2000 PDF files that could then be emailed to users. In the flow the PDF files were simply placed in a OneDrive folder for testing purposes to validate the flow would run as expected and to review the metrics after the fact to evaluate the impact of running the process.
That post was the first follow up to this post where an AAS database was used to run the report bursting process. This post is the second follow up I wanted to do by replacing the Paginated report with a Power BI report to compare performance. Granted not all Paginated reports could be swapped out with a Power BI report, but in this particular case it will work fine since I am not return 100s of records and don’t require multiple pages, plus I am just producing PDF files.
The first thing I needed to do was to recreate the Paginated report to a Power BI report and I did that with Power BI Desktop referencing the Power BI dataset that was made in the previous post follow up. I won’t go into this in detail, it was simply a matter of getting a similar look to the report output and testing it out to make sure it should work for the report bursting process.
Power BI report for data driven subscription screenshot
Once this was tested and ready I cleared the filter and deployed the report to the Power BI service. The next step was to add a new column into the Excel file for the subscription process that could be used to pass the filter to the Power BI report. Steven Wise has a nice blog post that outlines this setup with using URL filters with the report subscription here Filter & Email Power BI Report pages using Power Automate & Excel. I go ahead and add a new column “URLFilter” in the Excel file and add an expression to generate the filter that will be used in the Power Automate process to filter the report based on the Company Name value which is an attribute in the Customer table.
Excel file with the URLFilter column screenshot
Steven has a reference in his blog post to how the filtering works and is used in the export process. Now that we have the report available and the Excel file updated, we just need to modify the Power Automate flow to use the “Export To File for Power BI Reports” steps instead of the Paginated Reports step.
Power Automate Export To File for Power BI Reports step screenshot
In the above screenshot you can see that I am referencing the “Export To File for Power BI Reports” step now, using the new Power BI report that was deployed to the service, and have made a reference to the URLFilter column in the “ReportLevelFilters Filter” section of the step.
Now it is time to test the updated flow and process to validate everything works and then compare the results. After the flow has run and we take a look at the PDF files in the OneDrive folder we can notice one considerable difference…
PDF files in OneDrive folder screenshot
And the difference is in the size of the file that was produced. In the first post I included a screenshot and the size of the files was around 260 KB and now they are around 30 KB which is an 88% reduction in file size. Granted the report / file being generated here is really basic, but that is a pretty significant difference and the output if we look is basically the same (I could have spent a bit more time on getting the formatting identical).
PDF file output from the Power Automate flow screenshot
And the process took around 90 minutes again to complete, so right in line with the Paginated report examples. One thing to note is that I didn’t adjust the concurrency from the 15 setting. If we take a look at the capacity metrics we see similar impact with approximately 15% with the background processes and looks a lot cleaner with just the 2000 background operations.
Capacity Metrics App screenshot during the flow process screenshot
With Power BI reports using the Export API call we should be able to do 55 concurrent report pages using the A4 SKU that I am testing with based on the documentation limits mentioned here. I did bump up the concurrency setting from 15 to 50 in the flow and it did complete in 80 minutes the 2nd time versus the 90 minutes initially, which means like 25 reports per minute instead of 22.
Overall to sum up the three different tests from this series of posts they were basically the same when it came to the following:
Duration – approximately 90 minutes for 2000 reports (PDF exports)
Capacity impact – approximately 15% on A4 (P1 equivalent) for background operations which are evaluated over a 24 hour basis
The differences comes down to the following:
AAS Database versus Power BI Dataset
Advantage to Power BI having everything hosted in the service and has more features like endorsing the dataset in the tenant, using sensitivity labels, central monitoring, etc.
More details on the feature comparison can be found here
We did notice a major difference in file size where the Power BI report PDF files were 88% smaller than the Paginated report ones
So that wraps up the data driven subscription overview and comparison, let me know what you thought in the comments and provide any feedback you have on the series of posts. Thanks!
In the last post I went over using Power Automate to perform a data driven report subscription using a Paginated report referencing an AAS database. The flow referenced an Excel file with the information to make the process data driven and generate 2000 PDF files that could then be emailed to users. In the flow the PDF files were simply placed in a OneDrive folder for testing purposes to validate the flow would run as expected and to review the metrics after the fact to evaluate the impact of running the process.
For the follow up there were two items that I wanted to compare against the original flow
Moving the AAS database being referenced to a Power BI dataset hosted in the same capacity as the Paginated report
Using a Power BI report instead of a Paginated report
In this post I will cover the first comparison. I went ahead and deployed the existing AAS model to the premium workspace being used for the report bursting test. I did this using the Visual Studio project along with the XMLA endpoint. For this to work you will need to make sure that the XMLA endpoint read/write is enabled for the capacity as well as having the XMLA endpoints enabled in the tenant-level settings which are enabled by default.
Once the settings are all enabled then you just need to get the workspace connection information to use the XMLA endpoint and then make sure your model is using compatibility level 1500 which is supported in Power BI Premium.
Visual Studio Tabular Model Compatibility Level 1500 screenshot
Then it is simply a matter of setting the server information for the deployment using the workspace connection information and deploy the model.
Visual Studio Deployment Server Information screenshot
To test the Paginated report with the new model I went ahead and updated the data source connection information to reference the workspace connection instead of AAS. After you do this you will then need to switch over to the ‘Credentials’ section in the properties to enter your user name and password to authenticate.
Paginated Report Data Source connection string screenshot
Once you have authenticated you can then publish the version of the Paginated report referencing the Power BI dataset to the workspace. Now we are about ready to test the Power Automate flow with the Paginated report using the Power BI dataset, just need to update the flow to reference this version of the report which is easy to do. I would also do a quick test with the new report just to make sure it runs fine in the workspace without any issues prior to running the updated flow.
Power Automate flow with updated Paginated report reference screenshot
Once again we let the process run and it completed in approximately 90 minutes and then after reviewing the metrics app we see very similar metrics with the background operations using roughly 15% of the capacity and these operations get evaluated on a 24 hour period.
Capacity Metrics App screenshot during the flow process screenshot
So really not much different than running the process against AAS, expect now we have everything running entirely in our Power BI Premium capacity, so we can leverage all of the features like endorsing the dataset in the tenant, using sensitivity labels, central monitoring, etc.
In the next follow up post we will test out the process using a Power BI report instead of the Paginated report, so stay tuned;)
Being able to do a data driven report subscription with Power BI and Paginated reports is a common request we hear from many customers. Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this.
In this post I will be using a Paginated report that is referencing data in an Azure Analysis Services database and I will be referencing an Excel file that I have in OneDrive for Business which includes the needed information for the data driven subscription with 2000 records. The Paginated report is in a workspace backed by a Power BI Embedded A-SKU (A4 – equivalent of a P1 SKU) for testing purposes and the AAS tier is an S1 (100 QPU).
The report for this example is basic with just a single company name parameter defined that provides a list of the customers (first and last name) and the total amount due.
Paginated report example screenshot
With Power Automate you can use the pre-built Power BI templates that are available and then customize them as needed. The end result of the flow I created looks like the following –
Power Automate flow screenshot
The trigger for this flow is just using a schedule setup with a recurrence which is part of the template. For my demo I am just running it manually to show how it works and to highlight a few options you need to be aware of. For the Excel file it looks like the screenshot below and you can see a few records have already been processed based on the Processed and ProcessedDTM columns (these values are being updated in the flow) –
Excel file referenced for data driven subscription screenshot
In the file there is a RowId column so that I have a unique key value to reference for each record which gets used in the flow to update the processed information, CompanyName column which is used to set the parameter value in the report, user information for who will be getting the report, file name and format type, and then the processed information. The processed information is so I know when the record was last sent as well as if there is a failure or for some reason need to cancel the flow I can then restart the process and it would resume with just the records that are set to ‘No’ because of the filter included in the List rows setup for the Excel file –
List rows Filter Query expression screenshot
In this example instead of emailing files to users I am simply generating the PDF files and placing them in a OneDrive folder.
Pro Tip – It is always a good idea to test your flow out to make sure it will run smoothly without any errors, determine how long the process will take to complete, and verify this will not overload the premium capacity (especially if it is being used by end users for critical reporting).
So before emailing anyone I just want to make sure the process will run from start to finish without any errors. After the process does complete I will review the metrics and determine if any adjustments should be made.
Here are screenshots of the Export to File for Paginated Reports, Create file in OneDrive, and then the two Excel Update a Row steps.
Export to File for Paginated Reports screenshot
In the Export call I am specifying the workspace and report, you could actually provide the workspace id and report id in the Excel file to make this dynamic, referencing the File Format in the Excel file for the Export Format type, and then at the bottom setting the name of the parameter in the report (CustomerCompanyName) and referencing the CompanyName provided in the Excel file.
Create file in OneDrive screenshot
For creating the file I am dynamically setting the File Name based on columns in the Excel file (make sure to also include the file extension information, seems odd, but that is needed).
Excel Update a row screenshot (set processed info)
In the first Excel Update a Row step I set the Key Column which is RowId and make reference to the RowId value currently be processed in the loop and then set the Processed column to ‘Yes’ and ProcessedDTM column to current UTC time.
Excel Update a row screenshot (reset processed column)
In the second Excel Update a Row step once all of the records have been processed in the first Apply to Each loop I simply reset the Processed column back to ‘No’ for all of the records to reset it for the next time we need to run the flow.
After all of the steps have been configured and a test run is kicked off there are a couple of things that we will see, first it reads the Excel file records extremely fast, but after further investigation on the Apply To Each step you will see that it is only loading 256 records and in the Excel CompanyList table there are 2000 records;)
Initial flow run screenshot
So why is this process only loading 256 records? Hmm, thanks to Sanjay Raut who was working with me on testing this process out last year for report bursting he found this Solved: 256 rows limit – Power Automate and it turns out there is a Pagination setting on the Excel List Rows step that you need to turn on and then set accordingly depending on how many records you might have. In this case I simply set this to 2100 to cover my test sample.
Excel file list rows pagination setting screenshot
Now when we reset the Excel file and re-run the process we see that it took a little longer to read all of the records in the Excel file and that the Apply to Each step is going to loop through 2000 records:)
Update flow run screenshot
Another setting that you will want to be aware of is on the Apply to Each step which controls the concurrency. This is important and you will want to adjust this accordingly depending on how long it takes to run a report and stay within the Power BI connector throttling limits (100 calls per 60 seconds). If you do not enable this then the flow will only process one record at a time, in this test I have it set to 15 (20 is the default when enabled and 50 is the max).
Apply to Each step concurrency setting screenshot
I figure if I can process 30 to 60 reports per minute that is really good and will stay within the throttling limits. I don’t believe that will actually happen, probably more like 20 per minute, but we will see;)
For the second Apply to Each step to reset the file I don’t have the concurrency set to avoid any type of blocking, simply loop through the 2000 records and reset them back to Processed value of ‘No’.
Now that the flow is running the PDF files are being generated in the OneDrive folder as expected –
PDF files being loaded into OneDrive folder screenshot
I took a look at the progress after an hour and based on the Excel file I could tell that it had generated 1,384 files which is around 23 files per minute. Based on this information the process will complete in another 30 minutes, so 1 hour and 30 minutes from start to finish.
I reviewed one of the PDF files out in the OneDrive folder just to make sure everything looked good –
PDF file generated from the flow in OneDrive screenshot
Once the process completed I verified the file count and then reviewed the metrics from the Capacity Metrics App and AAS QPU utilization is Azure portal.
Verified file count in OneDrive folder screenshotCapacity Metrics App screenshot during the flow process screenshot
With just this process running the capacity was not overloaded and used roughly 15% with the background process running which gets evaluated over a 24 hour period.
AAS QPU metrics from Azure portal screenshot
And AAS was barely being utilized maxed out at 5 QPU (the S1 has 100 QPU available).
Some things that I will need to compare this process against would be the following –
Moving the AAS database being referenced to a Power BI dataset hosted in the same capacity as the Paginated report
Just don’t forget when running this type of a load to test (and A-SKUs work great for this so you don’t impact production P-SKUs) and make sure to check out the additional settings pointed out earlier like the pagination and concurrency.
Subscribe to Blog feed
Dan English's BI Blog 
My career journey leading up to Microsoft
Posted by denglishbi on September 29, 2022
I thought I would do a short post outlining my career journey up to the point where I finally was hired and accepted a position to join Microsoft❤️I will try and keep it brief because along the way I could highlight lots of stories regarding the people, companies, and customers I have worked with which would get lengthy😉
My first big job coming out of college was at a casino in Minnesota (where I have always lived) that my oldest sister referred me to. I graduated with a Finance degree with Accounting and Economic minors and the position was in the Revenue Audit department (got to audit the daily gaming revenue numbers which included slots, blackjack, pull tabs, and bingo). I really got interested in technology here, not that I hadn’t used computers prior since my dad was a computer programmer, but I started to get into MS Access (Office 2.0) which led me down the path of eventually getting recruited by the IT Department.
In the IT Department I worked with MS Access, FoxPro, Visual Basic, SQL Server, Oracle, SharePoint, and a few more technologies like DataStage and ProClarity just to name a couple more. My passion was always around the Microsoft technologies and in particular the data and reporting side which led me down the path of data warehousing and business intelligence. I still remember loading data into SQL Server OLAP Services and slicing and dicing the gaming revenue numbers, it was amazing🤓
From the casino I was recruited into the consulting world and I will admit that was a scary transition because I had worked at the casino for over 10 years and was very comfortable there. I worked for two different consulting companies over another 10 year stretch that both focused on Microsoft technologies and I got an opportunity to work with lots of different customers and projects, it was a great experience and it was stressful at times when all of the eyes are on you and you are being looked to for all of the answers – hopefully you have built a good internal network and can leverage others when you are stuck or have a question. I have lots of great stories I could go over, but will save them to discuss over a cold one sometime off the record😁
During the consulting years I also helped run the local Minnesota SQL Server User Group, Minnesota BI User Group, PASS Business Analytics Virtual Group, and helped with some of the Minnesota SQLSaturday events. I also did a lot of presentations, helped out in the Microsoft forums, tech edited books, got to co-author a book and was a Microsoft Data Platform MVP. Also during this time I coached my son’s soccer and helped coach his basketball (spending time with the kids was one of those things that I didn’t want to miss out on and the reason I stayed away from a job that required travel).
Eventually I decided to leave consulting and move back to a full-time position at a company. I worked for two different companies over the next 5 years and the last job prior to joining Microsoft is the one that got me the hands-on real-world experience with Azure and the Microsoft cloud technologies. Sure I had dabbled in the cloud a bit being a Microsoft MVP, but using it at a large enterprise is a different story and glad I got that opportunity before joining Microsoft.
So now if you see this slide, or one similar, during one of my presentations maybe this post will shed a little light on my journey going from the business world into the land of data warehousing and business intelligence and to the current position at Microsoft (what a dream, I am a huge #MicrosoftAdvocate and #MicrosoftNerd).
Posted in Personal Comments | Tagged: career, personal | Leave a Comment »