Publishing NAV data for Power BI

In order to use NAV data from Power BI, we need to publish it as OData web services (we want to absolutely avoid direct access to SQL Server tables). The best way to work is to use Query objects. This permits you to query the NAV database and retrieve fields from a single table or from multiple tables (by using join clauses):

1. We can start by using the standard NAV Query object with ID = 102(Item Sales by Customer):

   

2. You can directly run the Query object from the NAV Development Environment and see the following result set:

   

3. When your Query is ready to be published, you've to access the Microsoft Dynamics NAV RoleTailored Client and create a new entry on the Web Services page by setting the record as described:

   • Object Type: Query

   • Object ID: 102

   • Service Name: Your desired service name, recommended without using spaces (here it is ItemSalesByCustomer)

   • Published: TRUE

   Your record will appear as follows:

   

4. When published, NAV gives you the OData URL. You can check...