Replacing Analysis Services with Power BI shared datasets

Recently (June 2019), shared datasets were introduced in Power BI. Shared datasets let multiple reports, in different workspaces, reuse the same dataset. This makes it possible to separate the data model from the report’s presentation logic in a much better way than before. Up until now you needed to use Analysis Services to get such separation.

Now that we have shared datasets in Power BI, is it possible to fully replace Analysis Services with shared datasets? The short answer is Yes. Continue to read to find out how!

You need to establish an environment and a development process that lets you deal with some critical aspects of ALM (Application Lifecycle Management). One of the most important aspects is to be able to isolate the development, test and production environments from one another. This blog post focus on the setup of a Power BI environment that allows for the use of shared datasets in an environment where development, test and production is separated from each other.

DevTestProd

Furthermore, in order to get an efficient development process, you need to be able to automate the deployment of all artifacts involved in the process of moving from development to test and test to production.

The following work areas are needed:

WorkAreas

  • Local PBIX files
    This is the local repository where each developer is doing development. It is typically connected to a version control system, such as git.
  • Admin Workspace
    This workspace contains the shared datasets for each environment (development, test and production). There might be multiple datasets for each environment, for different information areas, but for simplicity this blog post will consider only one dataset per environment. The production dataset is typically a certified dataset.
  • Test Environment Workspace(s)
    This workspace will contain reports in the testing phase, connected to the shared test dataset in the admin workspace.
  • Production Environment Workspace(s)
    This workspace will contain reports in production, connected to the shared production dataset in the admin workspace. In a more advanced setup there can be multiple production workspaces for different information areas and processes.
  • Self Service Workspace(s)
    These are workspaces created by the business for self-service-purpose. Only the shared production dataset is allowed to be used for self-service.

1 Setting up your Power BI workspaces

You need to create the following workspaces in Power BI:

  • Admin Workspace
  • Test Workspace
  • Production Workspace

Depending on your requirements you might want a separate Development workspace and maybe a QA Workspace. It is easy to adapt the process and scripts below to any development process.

2 Creating the data model file

The first thing we need to do is to create the data model PBIX file. This is the file that will replace the Analysis Services model. We will not create any report content in this file – it will only work as a data model that we create reports from.

As soon as the data model file is created we will publish it to the admin workspace.

PublishToAdmin

The publishing can be done manually, but I recommend setting up a PowerShell script to do it. The following PowerShell script will publish the development data model file (called Dev datamodel.pbix):

. "$PSScriptRoot\_pbi.functions.include.ps1"
. "$PSScriptRoot\_pbi.config.ps1"

# Upload the dev data model to the admin  workspace
PostImportInGroup -pbixFile "$PSScriptRoot\..\Dev datamodel.pbix" `
                  -groupId $adminWorkspace -nameConflict "CreateOrOverwrite"

As soon as the data model has been changed this script needs to be run again (just as you would need to deploy/update a change in an Analysis Services model).

3 Developing reports

Now that the development data model is published to the admin workspace, we can start building reports from it. To create a report, open Power BI Desktop and connect to the recently published dataset in the admin workspace, using Get Data > Power BI Datasets > Dev datamodel.PBISDS

As soon as the report is created you save it to disk together with your dev data model and other reports.

4 Publishing to the Test workspace

As soon as your data model and reports for the current sprint/release are developed you are to deploy them to the Test workspace.

PublishToTest

The first step of the deployment is to create the Test dataset. This is done by publishing a new version of the development dataset and rebinding it to the test data source. In this case the test data source is an Azure SQL Database.

The second step is to publish the developed reports to the Test workspace and rebind them to the shared Test dataset in the Admin workspace. Finally, the reconnected dataset needs to be refreshed to use test data instead.

This following PowerShell script will create the shared Test dataset:

. "$PSScriptRoot\_pbi.functions.include.ps1"
. "$PSScriptRoot\_pbi.config.ps1"

# Create/overwrite the test dataset, reconnect to the test db and refresh it.
PostImportInGroup -pbixFile "$PSScriptRoot\..\Dev datamodel.pbix" `
                  -groupId $adminWorkspace `
                  -nameConflict "CreateOrOverwrite" `
                  -datasetDisplayName $testDatasetName
$testDS = GetDatasetInGroup -groupId $adminWorkspace `
                            -datasetName $testDatasetName
UpdateDatasourcesInGroup_SqlServer -groupId $adminWorkspace `
                                   -datasetId $testDS.id `
                                   -targetServer $testSqlServerName `
                                   -targetDatabase $testSqlServerDBName
RefreshDatasetInGroup -groupId $adminWorkspace `
                      -datasetId $testDS.id `
                      -waitForRefreshToFinnish $True

 

The following PowerShell script will publish the reports to the Test workspace (for simplicity only one report in published in the script below):

. "$PSScriptRoot\_pbi.functions.include.ps1"
. "$PSScriptRoot\_pbi.config.ps1"

$reportName = "Report 1"
$testDataModelDS = GetDatasetInGroup -groupId $adminWorkspace `
                                     -datasetName $testDatasetName
$devDataModelDS = GetDatasetInGroup -groupId $adminWorkspace `
                                    -datasetName $devDatasetName

if ( ReportExistsInGroup -groupId $testWorkspace -reportName $reportName )
{
     # If the report already exists, rebind it to its original dataset (the dev dataset)
     $report = GetReportInGroup -groupId $testWorkspace `
                                -reportName $reportName

     RebindReportInGroup -groupId $testWorkspace `
                         -reportId $report.id `
                         -targetDatasetId $devDataModelDS.id
}
$reportId = PostImportInGroup -pbixFile "$PSScriptRoot\..\$reportName.pbix" `
                              -groupId $testWorkspace `
                              -nameConflict "CreateOrOverwrite"
RebindReportInGroup -groupId $testWorkspace`
                    -reportId $reportId `
                    -targetDatasetId $testDataModelDS.id

5 Publishing to the Production workspace

Exactly the same process that was done by the publication to the Test workspace can be done to the Production workspace (or any other Workspace used by your development process). Simply change the connection to the production data source and change the workspace id to the production workspace id.

The full environment/process will look like the following illustration:

Full

Observations & limitations

  • Before we can overwrite reports in the Test and Prod workspaces, we need to rebind them back to their original datasets (as they were created in Power BI Desktop). Otherwise we will get new reports instead of overwriting the existing ones. This is very important since we want to keep existing bookmarks, comments and links.
  • The Admin workspace will contain a dataset and a report for each environment. Even though the report is empty it is very important that it is not removed. If it is removed and there are not other reports using the dataset, the dataset will automatically be removed as well, resulting in issues if there are PBIX files using that dataset.
  • Object level security is not supported in Power BI (as of 2019-08-22).
    https://docs.microsoft.com/en-us/analysis-services/tabular-models/object-level-security
  • Data models cannot be larger than 10 GB (as of 2019-08-22).
    https://powerbi.microsoft.com/en-us/pricing/

Scripts

All PowerShell scripts are available for download at the following git repo:

https://github.com/fredrikheden/Power-BI-Shared-Datasets-Instead-of-AS

5 comments

  1. nghia says:

    `CreateOrOverwrite` option is not working. When i run 1 Publish Dev Datamodel.ps1 then I got :

    Error occurred in REST call: @{code=MoreThanOneDuplicatePackageFoundError; pbi.error=}
    At C:\Users\nghia.trant\OneDrive – Home Credit International a.s\Homecredit\DeployScripts\_pbi.functions.include.ps1:80 char:13
    + throw “Error occurred in REST call: $($errResp.error)”
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (Error occurred …or; pbi.error=}:String) [], RuntimeException
    + FullyQualifiedErrorId : Error occurred in REST call: @{code=MoreThanOneDuplicatePackageFoundError; pbi.error=}

  2. Thomas Gauchet says:

    Thank you Frederik! This is a great post.
    Please note that when datasource is on premise (ie. SQL server database on premise), there is a constraint to link datamodel data source(s) to gateway datasource(s). In this case when you have a datamodel based on development data source, when you deploy it to production, after POST, you have first to update the data source (to point to production SQL server database on premise) & then bind to the corresponding gateway datasource…

    Something like this:
    PostImportInGroup -pbixFile $pbixFile `
    -groupId $groupId `
    -nameConflict “CreateOrOverwrite” `
    -datasetDisplayName $displayName

    $localDS = GetDatasetInGroup -groupId $groupId `
    -datasetName $displayName

    UpdateDatasourcesInGroup_SqlServer -groupId $groupId `
    -datasetId $localDS.id `
    -targetServer $targetSqlServerInstanceName

    BindDatasourcesInGroupToGateway -groupId $groupId -datasetId $localDS.id -targetGatewayDatasourceJsonBody $targetGatewayDatasourceJsonBody

Leave a Reply

Your email address will not be published. Required fields are marked *