MRMBI Knowledge Base

Microsoft Power BI® (RPS) Installation Steps

This page describes the steps required to configure Microsoft Power BI® for use with MRM BI RPS.


Prepare Power BI Data Caches

For the standard reports to work, the following imported data caches need to be manually refreshed once:

  • BI_Geocode Summary

  • BI_Job Summary

  • BI_Route Summary


Once these caches are refreshed, unzip MRMBI-RPS-SQL-v1_0.zip and run the scripts in the RPS database in the following order:

  1. Fn\BIStaging.BIGetCacheTable.sql

  2. Proc\BIStaging.BICreatePowerBICacheView.sql

  3. Proc\BIStaging.BIMapPowerBICacheView.sql


The final step is to execute the BIStaging.BIMapPowerBICacheView stored procedure with the following parameters:

Parameter

Description

@HasTMU

Should be 1 if Telematics data is available, otherwise 0.

@MRMBIDBName

Should be the name of the MRMBI database.
Note: only required if @HasTMU=0.

@MRMBIServerName

Should be the name of the MRMBI SQL server.
Note: only required if @HasTMU=0 AND it is on a different server.

The following is an example of how to call the stored procedure:

EXEC BIStaging.BIMapPowerBICacheView @HasTMU=0, @MRMBIDBName='MRMBI_DEV'


Create Power BI Gateway SQL Account

The gateway will require a database account to access the data. It is recommended to create an account dedicated for this purpose with the permissions required (db_datareader role in RPS and MRM BI database).

The following is an example of how to create the Power BI gateway SQL account:

USE [master]
GO
CREATE LOGIN [PBIService] WITH PASSWORD=N'{enter password}',
DEFAULT_DATABASE=[RpsACME], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [RpsACME]
CREATE USER [PBIService] FOR LOGIN [PBIService] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_datareader] ADD MEMBER [PBIService]
GO
USE [FWACME]
CREATE USER [PBIService] FOR LOGIN [PBIService] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_datareader] ADD MEMBER [PBIService]
GO
USE [MRMBI_ACME]
CREATE USER [PBIService] FOR LOGIN [PBIService] WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_datareader] ADD MEMBER [PBIService]
GO


Install Power BI Gateway Service

  1. Download and install the Power BI gateway utility from https://powerbi.microsoft.com/en-us/gateway/.

  2. Accept the default settings.

  3. When prompted for a password, use a Microsoft account that has the Power BI Pro license. This account should be a service account where the password does not change. This same account is required for RPS.

  4. Once installed, register a new gateway to the SQL server hosting the MRM BI and RPS databases.

Note: A recovery key will be generated and should be stored in a secure place.


Configure RPS Power BI Settings

Configure Registry: RPS needs to be configured in the registry with the Power BI workspace details. The following is an example using Microsoft PowerShell®:


The client GUID can be obtained from the Microsoft Azure® portal:


Configure the Workspace

  1. Login to DCF UI (http://server/DCF) as Descartes Systems Group/dsgdeveloper/{password}.

  2. Navigate to Setup > Organizations.

  3. Right-click the Organization > Identifiers.

  4. Select Power BI Workspace GUID as the Identifier Type.

  5. Copy the GUID from the URL of the workspace in the web browser.


  6. Paste the GUID into the Identifier Value.


  7. Click Save.

  8. Recycle the DCF COM+ application and then the RPS IIS application pool.

  9. Login using the Google Chrome or Microsoft Edge browser (Microsoft Internet Explorer is not compatible).


Configure RPS Environment Flags

The following settings from RPS\GeneratedFiles\Config.xml control which reports created by Descartes will be displayed:

  • PBI_HIDE_SUFFIX="_#HIDE#"

  • PBI_PROD_SUFFIX="_#PROD#"

  • PBI_PREPROD_SUFFIX="_#PREPROD#"

  • PBI_EnvMode="_#HIDE#"

  • PBI_ExclOrgs="||"


If PBI_EnvMode is set to PROD, then only reports created by Descartes with the suffix _#PROD# in the report name will be shown in RPS. The suffixes themselves will not be visible. For example, a Power BI report named "Summary_#PROD#" will be listed as "Summary" in RPS under Reporting Services > Power BI Reports (other).

If PBI_HIDE_SUFFIX is set, any reports with that suffix will not be displayed. For example, a report named "NotForRPS_#HIDE#" will not be listed in RPS.

  • On pre-production web servers, set PBI_EnvMode="PREPROD" in config.xml.

  • On production web servers, set PBI_EnvMode="PROD" in config.xml.


Upload Power BI Report to Workspace

The Power BI report (.pbix) needs to be uploaded to the workspace and named with the appropriate environment flag. Once uploaded, it will appear in RPS under Power BI (other).

To set up automatic refresh:

  1. Right-click on the report and choose Select Dataset.

  2. Choose the three caches prepared earlier:

    • BI_Geocode Summary

    • BI_Job Summary

    • BI_Route Summary


Once linked, the data will be uploaded to Microsoft Azure Power BI via the Gateway Service when any of the caches are refreshed.


Power BI Capacity Planning

The Power BI reports will execute using the embedded capacity defined in the Microsoft Azure portal. See the Azure Power BI Embedded Capacity documentation for details.


Back to top