PowerBI Content Package for Microsoft Dynamics NAV 2016


To help you get started with PowerBI and Microsoft Dynamics NAV 2016, we are providing a package that serves as a proof-of-concept for PowerBI content packages for Microsoft Dynamics NAV.

Content package – dashboard, reports, and dataset

With this package, users can start using and exploring their own data using PowerBI in just a few minutes. The dashboard gives users KPIs and charts on their sales and finance data, as you can see in the following screenshot:

NAVBlog_PowerBI1

By selecting an element on the dashboard, users can drill into more detailed information. The package contains six sales reports and one finance report.

NAVBlog_PowerBI2

NAVBlog_PowerBI3

Each report contains multiple charts for a given set of data.

Users can modify, copy to new, or create new content on the pages to get KPIs and charts from the data that fits their individual needs. Users can pin report elements to their existing dashboard, or they can create a new dashboard.

Data sources in Microsoft Dynamics NAV

The data for the package is pretty basic: sales and finance data. We’re looking into creating richer content in the future, as well as adding more capabilities from within the dataset when used in PowerBI.com

The data sources are OData feeds, such as OData web services from any Microsoft Dynamics NAV solution:

  • Sales –Six basic sales queries
  • Finance – exposing page 197, using the Account Schedule KPI Web Service Setup feature and the Account Schedules that source the data for some of the charts on the Small Business Role Center:
    • To allow for an extended data set, we expanded the options in the Period field to include Current Fiscal Year + 3 Previous Years, so you can pull the general ledger data monthly, for 4 years.

All web services are exposed in the product, ready to go in the demo version of Dynamics NAV 2016:

NAVBlog_PowerBI4

Note: Page 42 is not used in the PowerBI setup

Setup

The setup consists of three easy steps:

  1. Sign in to PowerBI.com.
  2. Select the content package for Microsoft Dynamics NAV.
  3. Connect a Microsoft Dynamics NAV instance to the content package.

You can find the URL for the OData feeds of the Microsoft Dynamics NAV Server instance in the Web Services list. However, you must remove the specific web service name from the string (the last /wsname part), so that it ends with the company name. Otherwise it will not work as a link for all the needed OData feeds in PowerBI.

It takes less than five minutes to get going.

Working with the Dashboard, reports, and dataset

Note: Depending on the setup and the account type in PowerBI, not all features will be available to all users of a given content package.

Users have the KPIs and charts available and can move them around, resize, and rename them according to their own preferences. From the dashboard elements, users can drill into the originating report.

Reports consist of one or multiple KPIs or charts based on the dataset.

There are no restrictions on how to mix and match the data from the queries in the package, but in typical use, a report focuses on a specific area of the business, such as one report for finance, one for sales, one for purchases, one for warehouse, and so on, or broken down into even more detail.

The report elements can be modified, copied to a new element and then modified, or users can create entirely new elements from the data sources in the package. When changing an existing element or creating a new one, a design pane appears, and the user can choose the data elements for a KPI or chart, choose from a palette of visualization types, set filters, select formatting, and so on. The following screenshot illustrates the design pane (the captions are shown in Danish, but you will see captions according to your system settings):

NAVBlog_PowerBI5

There is no need to go into all design details here, as there’s plenty of info on this on the PowerBI.com site and the corresponding YouTube channel.

Dataset

The package is based on a dataset from Microsoft Dynamics NAV. The package content can be set to be updated regularly – currently daily or weekly – or the user can update on demand.

NAVBlog_PowerBI6

Current limitations in the content package

The PowerBI.com content package is currently only a proof-of-concept that we wanted to release for Microsoft Dynamics NAV 2016. This means that the package does not include every facet of PowerBI that we wanted to include.

Date management

PowerBI does not automatically group dates, so the typical ERP views on months or quarters is something that must come from the dataset. We didn’t add that part to the dataset, so the sales data is just a list of all the transaction dates.

When dealing with dates, you also want to look at datasets that might contain empty groups, such as a quarter with no expenses for the KPI you’re looking at. Since raw, aggregated transactional data is missing, this would result in a date series that jumps or skips the gaps.

Dimensions

Although dimensional analysis is an essential part of PowerBI, we didn’t add that for the queries in this proof-of-concept. Obviously, this is one thing we’ll want to add in one of the first updates to the content package.

Domain coverage

We focused on sales and finance in this first version because it was easy to get data. For sales, it’s very simple queries and for finance we could reuse existing features to get some structured data easily. In coming package updates, we want to look into broader coverage of the various domains, such as warehouse, inventory, service management, jobs, and so on.

Comments (19)

  1. Hi Team,
    Unable to connect to Select the content package for Microsoft Dynamics NAV.
    I am using windows credentials. I am able to get access to web client as well as windows client but not to the content pack.

    1. navteam says:

      Hello Saurav,
      Did you sign in to PowerBI.com? If you sign in to PowerBI.com, you should be able to access a library of content packages, and the Microsoft Dynamics NAV package is one of them.

      1. paul says:

        I can see the content pack and can add my odata url (which is enabled) however. I cant get past the credentials once i have entered the odata url in mthe content pack. I have tried windows credentials that work for logging into the web client but i get an error as follows:

        Failed to update data source credentials: InstanceNotRegistered
        Hide details
        Activity Id: add89904-c6af-dfb9-cb20-caf39287027d
        Request Id: 491e0ab8-dc91-73d3-e235-aa11a3dbe7b5
        Status Code: 400
        Time: Thu Oct 08 2015 11:32:34 GMT+0100 (GMT Summer Time)
        Version: 11.0.9168.231
        Cluster URI: https://wabi-north-europe-redirect.analysis.windows.net
        Details: InstanceNotRegistered

        1. navteam says:

          Hi Paul,
          PowerBI attempts to connect to the specified Dynamics NAV instance as an external service, so to speak. This means that your instance must be accessible from outside your firewall, for example, so that you can access it from your devices at home or in your car. So if you cannot access the URL to your Dynamics NAV Web client from your non-domain connected device, then PowerBI also cannot connect to it and display your data.
          We hope this helps you get your data displayed in PowerBI.

          1. Saurav Dhyani says:

            Hi Team,

            1. Yes I have signed in with Power BI with an organization Account.
            I have the Error Message as below

            Failed to update data source credentials: The credentials you provided for the data source are invalid. Please ensure the credentials you have provided for all the data sources are valid.
            Hide details
            Activity Id: 762a6096-0ed9-4f28-bdf2-3e3cc32bcf6a
            Request Id: 50a53541-10ce-b6c6-390a-e01bcbe1c3d9
            Status Code: 400
            Time: Tue Oct 13 2015 17:19:42 GMT+0530 (India Standard Time)
            Version: 11.0.9168.231
            Cluster URI: https://wabi-us-east2-redirect.analysis.windows.net
            Details: The credentials provided for the OData source are invalid.

            I can use the same Odata Feed outside my organization (say in Excel) without any issues.
            I also use my organization Web Client from Home.
            The Problem lies with Power BI only, Please check.

          2. Philippe Mawet says:

            Hello Saurav,
            Did you find a solution?
            We have the same error : The credentials provided for the OData source are invalid.
            I can use the same Odata Feed outside my organization (say in Excel) without any issues.

            Thanks.

          3. Philippe Mawet says:

            I solved the problem by using authentication NavUserPassword in NAV and selecting the method BASIC in PowerBi

            Philippe.

  2. Hi Team,

    after I fill in the credentials and press next, Power BI just sits there with the spinning indicator going. It does not return an error message, but it does not go further than that either. I have tried Basic and Key authentication methods, but neither seems to work. Without an error message to indicate were the problem might be, I’m stuck. I have verified that I can access the odata page externally, and through https.

    1. Ok, I got a little further. When I switched the company name from CRONUS USA, Inc. to MyCompany, the system accepts my entry and starts to Import Data. Now I get the following error:

      Failed to import data
      Do you want to delete the dataset and report?
      Activity Idc402c191-4de7-4c97-afcf-d11ea4b50f2e
      Request Id4e33b685-202d-8794-a777-d1ec5127f601
      Status Code200
      TimeThu Oct 08 2015 14:13:29 GMT-0400 (Eastern Daylight Time)
      Version11.0.9168.231
      Cluster URIhttps://wabi-west-us-redirect.analysis.windows.net

      1. It works! I found that I needed to create three extra years of Accounting Periods to fix the last issue. During the error checking I found out that if you get an error running Page 197 through another client, the Power BI dashboard will not pull up correctly either.Hope this helps someone who is struggling to get this to work with the North American database.

  3. Joe Draeger says:

    Will this work with NAV 2015?

  4. Angel says:

    Unable to connect PowerBi for NAV

  5. Jayamurugan says:

    Hi Team,

    When I connect Power BI using OData web services, I get the following error message.

    https://clap34.cetas.com:2048/DynamicsNAV90/OData/Company('CRONUS%20International%20Ltd.’)

    Failed to update data source credentials: Cannot connect to the data source. Ensure the data source is accessible.
    Hide details
    Activity Id: cbb2292e-082e-45a7-9806-1e559ed18b53
    Request Id: 5b61c1d3-de3c-e22b-04d1-e7344e74fa0f
    Status Code: 400
    Time: Wed Oct 14 2015 14:42:14 GMT+0530 (India Standard Time)
    Version: 11.0.9168.320
    Cluster URI: https://wabi-south-east-asia-redirect.analysis.windows.net
    Details: OData: Request failed: The remote name could not be resolved: ‘clap34.cetas.com’

    1. nicolas says:

      Did they find any solutions? I have the same problem with credentials in source data

  6. navteam says:

    Hello all,
    We can see that some of you are having problems connecting to your Dynamics NAV instance from Power BI. This can be caused by a number of different things, including but not limited to your deployment scenario, the URL itself, authentication, and so on. We encourage you to contact Support at https://support.powerbi.com/ with any problems connecting to Dynamics NAV from the content pack in Power BI.

    Also, you can see a sibling to this blog post at http://blogs.msdn.com/b/powerbi/archive/2015/10/07/exploring-your-microsoft-dynamics-nav-data-with-power-bi.aspx.

  7. Pallea says:

    Hi Devteam

    Can Microsoft Dynamics NAV content pack for Power BI be downloaded so it can be used in the PowerBi Desktop program?

    Thanks
    PalleA

    1. JL says:

      I am having a problem using the content pack too. I am using the NAV Server on an Azure setup and my credentials aren’t accepted to connect to the OData feed from outside the azure environment. I have access to the content pack with my O365 credentials, but then the authentication fails. Alternatively, we already have the PowerBI desktop app published as a RemoteApp, which connects to our NAV Server successfully and obtaining the .pbix pack would be the next easy move to avoid the credentials bit and actually be able to show internally this solution.

      Please allow downloading a .pbix with this dashboard!

  8. karthik says:

    Can I use this NAV content package in power BI for connecting to NAV 2013 R2 as well?

  9. Patricia Huerta| says:

    How can I connect a multitenant NAV, with the Dynamics NAV content package of power BI? What is the url format?