Make Power BI report using data from Azure SQL server and view in Dynamics 365

Introduction:

In this blog, we will see how to show data of SQL server into Dynamics CRM. For this first, you need to connect your Power BI Desktop with SQL server. After that, we can show Power BI report into Dynamics CRM.

Step 1:

Register with Microsoft Power BI and by Sign in with your Dynamic 365 Credentials.

Step 2:

Once you Sign in with Power BI, click on Get Data button which is present on left top.

Step 3:

Then select the Source as Azure SQL Database and click on connect.

Step 4:

After clicking on connect then enter the Server Detail of your Azure SQL database and mention the database on which you are going to create reports.

Meaning of selection of Data Connectivity Mode:

If we select-

1. Import –

It will give you all the tables which you have selected from SQL Server to Power BI. For real-time data, you have to refresh the dataset manually or you can schedule a refresh for it.

2. Direct Query –

By this you can run queries directly to SQL Server means all data will not be imported to Power BI. Here you will get real-time data so no need to refresh dataset in Power BI.

Step 5:

After clicking on ok button select the tables which you want to import to Power BI. Then click on the Load button.

Step 6:

Here you can see your tables imported from SQL Server.

Step 7:

Here you can see relationship between your tables.

Step 8:

Here you can see all the data of tables.

Step 9:

Now create a report using power bi visualizations and filters.

Step 10:

To view this report in Dynamic CRM we have to publish the report.

Step 11:

After publishing report it will populate in power service.

Step 12:

Now Click on Pin Live Page to make report on dashboard of Dynamic CRM. Then add that report in the new dashboard or existing dashboard.

Step 13:

Now go to CRM dashboards from here you can get this Power BI report.

Step 1: From here you can get all Power BI dashboard reports.

Step 2: After that choose your Workspace and Dashboard where you had pin that Power BI report.

Step 3: Here is your Power BI report which you show in Dynamic CRM Dashboard.

Conclusion:

Using the simple steps above we can show Power BI reports with Azure SQL database (Data Source) in Dynamics CRM.