Part-1: Data entity creation, Azure setup and recurring integration setup
Microsoft Power BI offers capability to build stunning reports with various sources like Excel, CSV, etc. But now in this blog we will discuss about how Dynamics 365 for operations data can be used for building Power BI report. Not only creating a report out of the box reports with Dynamics data, but we even will see the steps how created Power BI report can be pinned back into Dynamics 365 for operations workspace.
This integration is planned as two part series:
- Part-1: Data entity creation, Azure setup and recurring integration setup
- Part-2: Power BI report development and pinning to Dynamics 365 for operations workspace
As part of this blog lets discuss in detail about the various steps involved in data entity creation, Azure setup and BYOD (Bring Your Own Database) database creation.
Step-1: Create data entity
Log on to Visual studio and create a new table to expose table contents as PowerBI BYOD data source.
- Create table with following structure
2. Create entity for the table with following name “DYN service information” (Open the table in designer, then Right click on the table to create data entity)
Step-2: Azure Database creation
a) Create Azure database
- Resource group: Create a new resource group “Dynamics AX”
- Database name: “trainingbyoddbtest”
Click on Create new link
- Server name : “trainingbyodservertest”
- Admin login : “trainingbyodadmintest”
- Location : East US
b) Select storage as serverless
Click on “Configure database” option to setup serverless operation
c) Agree to terms
Towards end of the Configure (same screen as Step-b). Click on Agree to license terms and agree for it.
Click on the Agree check mark and Apply > Review+Create > Create
Step-3: Configure BYOD database connection in Dynamics 365
- Open Data Management Framework
- Click on tile Configure entity export to database
- Provide the following connection string of Azure database: Name: BYODConn
Data Source=trainingbyodservertest.database.windows.net,1433;Initial Catalog=trainingbyoddbtest ;Integrated Security=False;User ID=trainingbyodadmintest ;Password=Dynamics365
4. Save and Validate the connection
5. Make sure Create clustered column store indexes is set to Yes
6. Click on Publish
7. Search for the created entity (Ex: DYN***)
8. Click on Change Tracking > Enable entire table > Publish
Step-4: Recurring job creation
- Create a new Export job with following properties:
- Name: DYN Export BYOD job
- Target data format: BYODConn (Connection created in previous step)
- Entity name: DYN service information (Entity created in previous step)
- Default refresh type: Incremental push only
2. Click on tile Add entity
3. Click on Create Recurring data job on export entity page
4. Navigate to Azure portal > Azure Active Directory > App registrations
5. Click on New app registration
6. Copy the ClientID and paste it in the Application ID section of Step-3: Configure BYOD database connection in Dynamics 365
7. Mark the checkbox Enabled
8. Set the recurrence to 1 minute
9. Click on ExportNow
Step-5: Verify data in Azure SQL
1. Open Azure portal and navigate to SQL databases.
2. Click on the Query editor window and login with the admin user name and password.
3. Click on new Query editor and click on Refresh icon. Now you will see the staging table of entity available in the tables section
4. Execute simple select statement query window:
- select * from dbo.DYNServiceTableStaging
5. Now data entered in Dynamics AX will be available in the Azure SQL server database.
We have completed creating data entity within Dynamics 365 for Operations and exposed it to Azure SQL (BYOD). Lets continue with Part-2: Power BI report development and pinning to Dynamics 365 for operations workspace
