DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • The SPACE Framework for Developer Productivity
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Building a Flask Web Application With Docker: A Step-by-Step Guide

Trending

  • The SPACE Framework for Developer Productivity
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Building a Flask Web Application With Docker: A Step-by-Step Guide
  1. DZone
  2. Data Engineering
  3. Data
  4. Create Dynamics NAV Data Visualizations in Power BI

Create Dynamics NAV Data Visualizations in Power BI

Read this tutorial to learn how to securely visualize your Dynamics NAV data in Power BI using the CData ODBC Driver for Dynamics NAV.

Jerod Johnson user avatar by
Jerod Johnson
·
Feb. 13, 18 · Tutorial
Like (5)
Save
Tweet
Share
6.12K Views

Join the DZone community and get the full member experience.

Join For Free

The CData ODBC Driver for Dynamics NAV provides a streamlined, secure way to visualize your Dynamics NAV data in Power BI. The CData ODBC Driver for Dynamics NAV links your dashboards and reports to the live Dynamics NAV data. By scheduling refreshes and refreshing on demand, you can create dashboards that reflect changes to your data in real time. This article details how to use the ODBC driver to create dashboards featuring Dynamics NAV data in the Microsoft Power BI Designer.

Create Data Visualizations

Follow the steps below to connect to Dynamics NAV data, create a visualization, and interact with it in the Editing View.

  1. If you have not already done so, specify connection properties defined in the data source name (DSN). Below is a typical connection string:
    http://myserver:7048;User=myserver\Administrator;Password=admin;ServerInstance=DYNAMICSNAV71;
    You can configure the DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to use the Microsoft ODBC Data Source Administrator to create and configure a DSN.
  2. Open the Power BI Designer and click Get Data. To start the Power BI Designer from PowerBI.com, click the download button and then click Power BI Designer.
  3. If you want to use SQL to import the data, click ODBC Query. If you want to import one or more tables visually, using the Navigator dialog, click ODBC Tables.
  4. Enter the ODBC connection string. Below is a connection string using the default DSN created when you install the driver:
    Provider=;Persist Security Info=False;DSN=CData DynamicsNAV Source
    Note: Set "Provider=" to use the default .NET OLE DB Provider installed on your system.
  5. Enter the SELECT statement to retrieve the data you want to see in the report. The standard SQL is supported. The following query imports the Customer table:
    SELECT * FROM Customer
  6. The data is displayed on the Query tab. Start creating the report on the Report tab. To create a visualization, drag a field from the Fields list onto the Report view. For example, to create a bar chart, simply click Name from the Customer table. The Power BI Designer selects the Balance_LCY column as the measure. You can change the visualization by clicking one of the visualizations on the Home tab:You can change sort options by right-clicking the chart. Options to select the sort column and change the sort order are displayed.You can apply view filters to all the visualizations in a page. When you are editing the report, you can apply view filters from the Fields and Filters pane. Let's filter the customer balances by Country: Click the Country_Region_Code field and drag it to Filters. On the Filters pane, select a country code, for example, US.You can use both highlighting and filtering to focus on data. Filtering removes 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 data.

Upload Dynamics NAV Reports to Power BI

You can share reports based on ODBC data sources with other PowerBI users in your organization. To upload a 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 the report in the Reports section.

Refresh on Schedule and on Demand

You can use the Power BI Personal Gateway to automatically refresh the dataset associated with your report. You can also refresh the dataset on demand. After installing the Personal Gateway, follow the steps to configure the Personal Gateway for an ODBC DSN:

  1. If you have not already done so, log into Power BI.
  2. In the Dataset section, right-click the Dynamics NAV Dataset.
  3. Click Schedule Refresh.
  4. In the Settings section for your dataset, expand the Manage Data Sources node. In the ODBC section, click Edit.
  5. In the Authentication Method menu, select Anonymous, as your Dynamics NAV account credentials are already saved in the DSN.

After configuring the Personal Gateway, you can refresh on demand and schedule refreshes.

To schedule refreshes, expand the Schedule Refresh node and select 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 click Refresh Now.

Data (computing) Visualization (graphics) Database

Published at DZone with permission of Jerod Johnson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • The SPACE Framework for Developer Productivity
  • Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
  • Essential Architecture Framework: In the World of Overengineering, Being Essential Is the Answer
  • Building a Flask Web Application With Docker: A Step-by-Step Guide

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: