Analyzing Navision data in Power BI

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

Checking the webservice

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

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

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‘CRONUS%20India%20Ltd.’)/ItemSalesAndProfit
• ItemSalesByCustomer‘CRONUS%20India%20Ltd.’)/ItemSalesByCustomer
• Powerbifinance‘CRONUS%20India%20Ltd.’)/Powerbifinance
• SalesDashboard‘CRONUS%20India%20Ltd.’)/SalesDashboard
• SalesOpportunities‘CRONUS%20India%20Ltd.’)/SalesOpportunities
• TopCustomerOverview‘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. “‘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.



Power BI: IPL Analytics- Data Modeling and Transformation-Part 2

In my previous blog we have seen few techniques of data modeling. Now let us  see few more techniques to do the data modeling and data transformation

Add New column:

We have Match table which has all match related details such as winner team, who is the man of the match, is the match won by wickets or runs etc. If we would like to show the data “Win by wickets” and “Win By Runs” based on the winning type, how do we do that?  Just open the table. See there are two columns (Win Type and  Win Margin) which can provide such data, we only need to show this data from columns to the rows.

Let us add new column called Win_By_Wickets and add the formula to show the win_margin in case the match is won by wickets.

Let us follow the steps as shown in below snippets

Win by wickets-New COlumn

Add the column name before the “=” symbol and let us add if condition to check the win_type and display the Win_Margin, Writing this DAX formula is almost similar to how we add formula in excel. Win by wickets-New Column- If Condition

The complete formula would be

“Win_By_Wickets = IF(Match[Win_Type]=”wickets”,Match[Win_Margin],”0″)”

Similarly for Win by Runs, it would be

Win_By_Runs = IF(Match[Win_Type]=”runs”,Match[Win_Margin],”0″)

Now we have these two new columns added in the table for further visualization purpose.

Win by wickets1

Aggregate the data with group by

Ball By Ball table contains the huge data per ball in all the matches. Now if we need to find the statistics of Economy Rate – how many runs the Bowler has given per over. We can use SUMMARIZE function which provides grouping of the data based on particular columns. Output of the summarize function is a table. Its very similar to the select query having group by clause and aggregate function in the SQL server.


Please refer below expression which selects data from Ball_By_Ball table, groups the data by Bowler and adds additional columns to the same table with various aggregate functions such as SUM, COUNTROWS etc.

EconomyTable = SUMMARIZE(Ball_By_Ball,Ball_By_Ball[Bowler],”Runsperbowler”,SUM(Ball_By_Ball[Runs_Scored]),”Oversbybowler”,COUNTROWS(Ball_By_Ball)/6,”Economy”,(SUM(Ball_By_Ball[Runs_Scored])/(COUNTROWS(Ball_By_Ball)/6)))

NewTable1Here we are calculating the economy rate, hence need the number of overs played by the Bowler and the runs scored,

My Economy column contains the formula as RunsperBowler divided by oversbyBowler. Exact formula is as given below (SUM(Ball_By_Ball[Runs_Scored])/(COUNTROWS(Ball_By_Ball)/6)))

Now we can use this table in report for further visualizations. I would recommend everyone to go to the Microsoft Docs (Link is provided below) to refer the details of the different aggregate functions available in Power BI.

Aggregates in Power BI visualizations

We have seen few more techniques of data modeling in Power BI using Power BI desktop in this blog. Let us see how to create different visualizations of this data and create IPL Analytics reports in my next blog. I just loved creating these reports.

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like button if you like my write up.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI: IPL Analytics- Data Modeling and Transformation-Part 2”