Part-1: Data entity creation, Azure setup, and recurring integration setup
Microsoft Power BI offers the capability to build stunning reports with various sources like Excel, CSV, etc. But now in this blog, we will discuss how Power BI BYOD Recurring integration with Dynamics 365 Operations. Not only creating a report out of the box reports with Dynamics data, but we will also see the steps on how to create a Power BI report that can be pinned back into the Dynamics 365 for Operations workspace.
This integration is planned as a 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 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 a Power BI BYOD data source.
- Create a table with the following structure
2. Create an entity for the table with the following name “DYN service information” (Open the table in designer, then right-click on the table to create a data entity)
Step-2: Azure Database creation
a) Create an 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 the “Configure database” option to set up serverless operation
c) Agree to terms
Towards the 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 the tile Configure entity export to database
- Provide the following connection string of the 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 the following properties:
- Name: DYN Export BYOD job
- Target data format: BYODConn (Connection created in previous step)
- Entity name: DYN service information (Entity created in the previous step)
- Default refresh type: Incremental push only
2. Click on the tile Add entity
3. Click on Create Recurring data job on the 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 the Azure portal and navigate to SQL databases.
2. Click on the Query editor window and log in with the admin user name and password.
3. Click on the New Query editor and click on the Refresh icon. Now you will see the staging table of the entity available in the tables section
4. Execute a simple select statement query window:
- select * from dbo.DYNServiceTableStaging
5. Now, the data entered in Dynamics AX will be available in the Azure SQL server database.
We have completed creating a data entity within Dynamics 365 for Operations and exposed it to Azure SQL (BYOD). Let’s continue with Part-2: Power BI report development and pinning to the Dynamics 365 for operations workspace
