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

By | November 5, 2018

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.

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

Step 2:

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

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

Step 3:

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

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

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.

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

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.

  1. 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.

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

Step 6:

Here you can see your tables imported from SQL Server.

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

Step 7:

Here you can see relationship between your tables.

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

Step 8:

Here you can see all the data of tables.

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

Step 9:

Now create a report using power bi visualizations and filters.

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

Step 10:

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

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

Step 11:

After publishing report it will populate in power service.

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

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.

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

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.

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

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

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

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

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

Conclusion:

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

Click2Clone-One-Click-Productivity-App-to-Copy_clone-Dynamics-365_CRM-Records