In this Article, We will discuss the Dynamics NAV Data Visualizations in Power BI and its Creation.
The CData ODBC Driver for the Dynamics NAV provides a streamlined, secure way to visualize your Dynamics NAV data in Power BI.
The CData ODBC Driver for MS Dynamics NAV links your dashboards and reports to a live Dynamics NAV data.
By scheduling refreshes and refreshing on demand, you can create dashboards that reflect changes to your information in real time. This article details how to use the ODBC driver to create dashboards featuring the Dynamics NAV data in the Microsoft Power BI Designer.
Create NAV Data Visualizations
Follow the steps given below to connect to Dynamics NAV data, create a NAV data visualizations, and interact with it in the Editing View.
- If you have not already done so, mention connection properties defined in the data source name (DSN). Below is a typical connection string:
You can configure DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of driver installation. See the “Getting Started” chapter in the support documentation for a guide to using the Microsoft ODBC Data Source Administrator to create and configure a DSN.
2. Open the Power BI Designer and click Get information. To start Power BI Designer from PowerBI.com, click the download button and then click Power BI Designer.
3. If you want to use the SQL to import the data, click ODBC Query. If you want to import one or more tables visually, using a Navigator dialog, click ODBC Tables.
4. Enter the ODBC connection string. Below is the connection string using the default DSN created when you install the driver:
Provider=;Persist Security Info=False;DSN=CData DynamicsNAV Source
SELECT * FROM Customer
6. The information is displayed on the Query tab. Start creating the report on the Report tab. To create the Navision data visualizations, drag a field from the Fields list onto the Report view. For example, to create a bar chart, simply click Name from a Customer table. The Power BI Designer choose the Balance_LCY column as the measure. You can change the visualization by clicking the visualizations on the Home tab:
You can change the sort options by right-clicking the chart. Options to choose the sort column and change the sort order are displayed. You can apply view filters to all visualizations in a page. When you are editing the report, you can apply view filters from a Fields and Filters pane. Let’s filter a customer balances by Country: Click the Country_Region_Code field and drag it to Filters. On the Filters pane, choose a country code, for example, US.
You can use both the highlighting and filtering to focus on data. Filtering removes the unfocused data from visualizations; highlighting dims unfocused data. You can highlight fields by clicking them:
Click Refresh to synchronize your report with any changes to the information.
Upload Dynamics NAV Reports to Power BI
You can share the reports based on ODBC data sources with other PowerBI users in your organization. To upload the dashboard or report, log into PowerBI.com, click Get Data -> Files, and navigate to a Power BI Designer file or Excel workbook. You can then view and edit a report in the Reports section.
Refresh on Schedule and on Demand
You can use Power BI Personal Gateway to automatically refresh the Dataset associated with your report. You can also refresh the Dataset on demand. After installing Personal Gateway, follow the steps to configure the Personal Gateway for an ODBC DSN:
- If you have not already done so, then log into Power BI.
- In the Dataset section, right-click Dynamics NAV Dataset.
- Click Schedule Refresh.
- In the Settings section for a dataset, expand the Manage Data Sources node. In the ODBC section, click Edit.
- In the Authentication Method menu, choose Anonymous, as your Dynamics NAV account credentials are already saved in the DSN.
After configuring the Personal Gateway, you can refresh on demand and scheduled refreshes.
To schedule refreshes, expand the Schedule Refresh node and choose Yes for the Keep Your Data Up-To-Date option. After specifying the refresh interval, click Apply.
To refresh on demand, right-click the Dynamics NAV dataset in the Datasets section and then click Refresh Now.
After connecting with your Microsoft Dynamics NAV credentials, your information will begin loading into Power BI and you will be notified when the dashboard is ready. In addition to the out-of-the-box dashboard, there are 7 reports built on top of a dataset. To begin exploring the reports choose one of the tiles on the dashboard. For example, if you choose the tile that shows Sales This Month it will drill into the Sales report.
Each report involves a set of visuals with different insights. For additional data, you can hover over any of the items, or select a particular value to cross filter other visuals on the page. The Sales report gives insights such as the sales amount by customer group and product. On this report, you can choose a specific salesperson from the salesperson name slicer to filter the data for that person.
Any of the visualizations from the reports can be pinned to a dashboard by hovering over it and selecting the pin icon. In each of the reports, you can switch to edit mode where you can edit or include additional visuals to your reports.
After the initial import, the dashboard and reports continue to update daily. You can control the refresh schedule by choosing the Dataset. With the Microsoft Dynamics NAV content pack for Power BI, you have the initial set of metrics and insights enabling you to explore your data even further.
In this Article, we dealt with Dynamics NAV Data Visualizations in Power BI and its Creation and with some Sales Dashboards examples.