In earlier posts, we have learned Power BI on sample data like IPL data. However when it comes to actual requirements, we would need Power BI reports or dashboards on real data from business applications such as ERP, CRM or any other application.

Here lets see how we can connect to Navision 2016 (NAV) data from Power BI. Navision 2016 might be deployed in different ways. It can be completely on premise in the office premises or the other way which is more common these days – on Azure server.  Here database server and NAV application server both would be on Azure. Let us see focus on NAV on Azure from Power BI and develop reports.

Exposing Data from NAV

First we need to understand how to expose data from NAV so that it can be used in Power BI and analyzed. Someone who is NAV technical, would understand it in depth, but let me tell you the concept. We have to create the query based on the requirement and publish it to make it accessible from Power BI or any third application in that case. Its exactly similar how we prepare NAV for any third party integration. Either we can create a query or a custom page with CAL code behind it for the business logic and publish it. You probably can go for any of these methods based on your comfort and ease. I am not going in details of how to do this as we can approach NAV technical team and get this done.

Kindly refer below Microsoft links to know more about creating and publishing queries

How to create queries in NAV

How to publish a web service in NAV

One more important aspect of NAV connectivity is securing the connection with certificate. We can refer below link for more details on this

How to configure ssl to secure the connection to Microsoft Dynamics NAV web client

If we only want to try this connectivity without any security,  we can configure such binding with only http instead of https and test the connectivity without any certificate installation.

Once we have these pages or queries published, we can check the details by below mentioned steps

Open NAV and search for web in the right top as mentioned in below snippet

Once we search for  web, we can see web services in the list below as shown in below snippet.

Let us click on the Web services, which will take us to the Web Services section. Here all published web services are listed. As I mentioned earlier, here we are referring to CRONUS database. Microsoft has already provided out of box queries and pages which are published and can be accessed from other applications.

There is one more way to access the web services as shown in below screenshot from the left section.

Power BI Content Packs/Power BI Apps

At the time of writing this blog, Microsoft has deprecated Content Packs and focusing on Power BI Apps

For basic understanding of different ways to showcase and analyse NAV data in Power BI, Microsoft provides Power BI content pack for Navision. This content pack is free to use and can connect to Navision on Azure. We just have to establish the connectivity and all reports and dashboards are readily available. In case any of our implementation is vanilla implementation, we can directly use this content pack for Power BI dashboards. We just have to keep it mind that the reports and dashboards which are auto created by content pack can not be modified. Hence I would suggest to refer these reports and recreate on our own so that we have complete control on the same. 

Power BI content pack uses below out of box queries as shown in below snippet,

• ItemSalesAndProfit
• ItemSalesByCustomer
• SalesDashboard
• SalesOpportunities
• SalesOrdersBySalesPerson
• TopCustomerOverview

We can also see powerbifinance page in below snippet, which is also used by Power BI content pack.

 

Let us go further and actually use one of the web service (Sales Order by Sales person) in the Power BI. As we can see in the below snippet, sixth column oData URL shows the actual oData URL for that query or page.

As we select that row and right click , we can see an option of Copy cell. Lets select that option and copy the URL in clipboard.

If we see at the oData URLs for all the required queries and pages, we will find that entire URL is same except the last section after “\”.

• ItemSalesAndProfit
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/ItemSalesAndProfit
• ItemSalesByCustomer
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/ItemSalesByCustomer
• Powerbifinance
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/Powerbifinance
• SalesDashboard
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/SalesDashboard
• SalesOpportunities
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/SalesOpportunities
• SalesOrdersBySalesPerson
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/SalesOrdersBySalesPerson
• TopCustomerOverview
http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)/TopCustomerOverview

When we are going to use the content pack, we just need to use the URL which is highlighted in blue color i.e. “http://navdemo.southeastasia.cloudapp.azure.com:7388/DynamicsNAV90/OData/Company(‘CRONUS%20India%20Ltd.’)“. Rest would be managed by content pack logic.

Let us see how to do that in Power BI. Let us login to Power BI service and we can see Apps in the left section. To get the Power BI content pack let us click on Apps. If you already have any Apps installed, it would show those in the right section.However for me nothing is installed as of now.

Now we need to get the Power BI apps for Navision which are already published in Microsoft AppSource. Hence let us click on Search in AppSource link as shown below.

By Default it shows all the available apps.

We have search for the apps for Navision as shown in below snippet.As we are talking about NAV 2016, we have below three apps –

1. Microsoft Dynamics NAV – CRM Report
2. Microsoft Dynamics NAV – Financials Report
3. Microsoft Dynamics NAV – Sales Report

Other three apps are for NAV 2018 which are not in scope of this blog.

Let us go ahead and select any of the above app and click on Get it now button to install the app in our Power BI service as shown in below snippet

Once we click on the Get it Now button, installation would start and we would get below screen asking for the NAV web service URL. Here we need to enter the oData URL which was mentioned above and marked with blue color. We need not worry if this sales report and which web service it would actually require.

Once we enter this url and click next, this app would complete the installation,  Power BI reports and dashboards would be automatically created.

We can see all required components automatically created such as Dataset, report and dashboards etc. Here we can visualize and analyze NAV data in Power BI dashboards.

Below are some sample snippets from the reports

Only drawback of this app is we can not edit this App for any changes in Dataset, report or dashboards. Hence I would recommend to use these Apps for demo purpose and showcase the Power BI capabilities with NAV data. Then based on actual requirements we can actually develop the required components.

I have explained the Power BI with NAV in this blog. The similar concept goes with any other ERP or any other business application. We only need to know how to expose the data from that application and make it available in Power BI.

If you like the blog, kindly hit the like button or share it with colleagues.  Also let me know in case you want me to write on any other topic. I will try to write about it.