PowerBI.com integration with on-premises environments

The cloud version provides several features that allow for deeper integration with Microsoft Power BI. Some of these features aren't yet available for on-premises deployments. However, the availability of Entity Store in on-premises deployments lets you use PowerBI.com to report on and analyze data.

This article outlines the analytical capabilities that are available in on-premises deployments that run Microsoft Dynamics 365 Finance platform update 26 (May 2019) and later.

Enable Entity Store on-premises

This article supplements the Set up and deploy on-premises environments (Platform update 12 and later) article. The section numbers that follow correspond to the section numbers in that article.

3. Plan your users and service accounts

14. Configure the databases

The steps in this section are optional.

To create a database for Entity Store, first modify the ConfigTemplate.xml file.

1. Under DbServer – Security, set the generateUser flags for axdwadmin and axdwruntimeuser to True. The scripts that you run in the next step create these two users. You're prompted to set passwords for the users.

2. Run the following scripts.

   .\Initialize-Database.ps1 -ConfigurationFilePath .\ConfigTemplate.xml -ComponentName EntityStore
   .\Configure-Database.ps1 -ConfigurationFilePath .\ConfigTemplate.xml -ComponentName EntityStore
   

   The Initialize-Database.ps1 script performs the following actions:

   • Creates an empty database named AXDW. This database is used for Entity Store.
   • Creates a new user named axdwadmin with SQL authentication enabled, and prompts you for the user's password.
   • Creates a new user named axdwruntimeuser with SQL authentication enabled, and prompts you for the user's password.
   • Grants the db_owner permissions on the database to the axdwadmin and axdwruntimeuser users.

   The Configure-Database.ps1 script performs the following actions:

   • Sets the specified database file and log settings.
   • Runs GRANT VIEW SERVER STATE TO axdwadmin.
   • Runs GRANT VIEW SERVER STATE TO axdwruntimeuser.

15. Encrypt credentials

Create a Credentials.json file as shown in the following example. The AosDWAuth category is optional and is used only if Entity Store is enabled.

{
    "AosPrincipal": {
        "AccountPassword": "<encryptedDomainUserPassword>"
    },
    "AosSqlAuth": {
        "SqlUser": "<encryptedSqlUser>",
        "SqlPwd": "<encryptedSqlPassword>"
    },
    "AosDWAuth": {
        "DWUser": "<encryptedDWUser>",
        "DWPwd": "<encryptedDWPassword>",
        "DWRuntimeUser": "<encryptedDWRuntimeUser>",
        "DWRuntimePwd": "<encryptedDWRuntimePassword>"
    }
}

Here's an explanation of the preceding code lines:

• AccountPassword is the encrypted domain user password for the Application Object Server (AOS) domain user (contoso\axserviceuser).
• SqlUser is the encrypted SQL user (axdbadmin) that has access to the application database (AXDB). SqlPwd is the encrypted SQL password.
• DWUser is the encrypted SQL user (axdwadmin) that has access to the Entity Store database (AXDW). DWPwd is the encrypted SQL password that you enter when you run the Initialize-Database.ps1 script.
• DWRuntimeUser is the encrypted SQL user (axdwruntimeuser) that has access to the Entity Store database (AXDW). DWRuntimePwd is the encrypted SQL password that you enter when you run the Initialize-Database.ps1 script.

More information

Entity Store was enabled in Platform update 26.

Creation of the Entity Store database and users is optional. When you configure a deployment in Microsoft Dynamics Lifecycle Services (LCS), you can leave the Entity Store customizations blank.

If you want to enable Entity Store in an environment, complete the following tasks:

• Create an AXDW database by using the scripts described in step 14.

• Create axdwadmin and axdwruntimeuser users that have appropriate privileges by using the scripts described in step 14. Users are defined in the following files: ConfigTemplate.xml and DatabaseTopologyDefinition.xml.

• Create a Credentials.json file as described in step 15. Define user names and passwords for axdwadmin and axdwruntimeuser as described in step 14, and encrypt them.

• Fill in the Entity Store SQL Server name and Entity Store database name during deployment in LCS.

  • The database name is created in step 14 and is defined in the DatabaseTopologyDefinition.xml file. The default name is AXDW.
  • The SQL Server name is the fully qualified domain name (FQDN) of the Microsoft SQL Server or Always on listener. An example of this FQDN is sqlinstance.onprem.contoso.com. It's the server that the AXDW database is created on.

To enable Entity Store in an environment that you already deployed, use the Update Settings action under the Maintain button in LCS. This action opens a dialog that allows you to specify the Entity Store configuration.

Author and distribute reports by using Entity Store on-premises

Entity Store is an operational data warehouse that's included. It lets power users and business analysts author reports that use simplified and enriched data. Entity Store contains aggregate measurements, which are simplified star schemas. For more information about how to author reports by using Entity Store, see Create analytical reports by using Power BI Desktop.

Note the following additional steps that are specific to on-premises deployments:

• For on-premises environments, you don't have to use LCS to deploy Power BI reports to production or sandbox environments. Because admins can point PowerBI.com datasets to specific Entity Store databases in on-premises environments, you don't have to use the functionality that LCS offers. However, you might have to configure the on-premises gateway to enable PowerBI.com to access data on-premises. For more information about the gateway, see Power BI gateway documentation.
• Although cloud-based application environments support only reports that are authored by using the DirectQuery option, on-premises Entity Store supports both DirectQuery reports and Import mode reports.
• Analytical workspaces aren't yet implemented in on-premises deployments. Instead of viewing reports in analytical workspaces, you can deploy them to PowerBI.com environments. The reports can then be used by users who have access to PowerBI.com. Your users might require appropriate licenses to access reports on PowerBI.com.