Plug and Play solution for NAV Dimensions in Power BI
Handling NAV dimensions in Power BI has been a pain point since the first days of Power Pivot. Most solutions required that the dimension codes that were of relevance needed to be hard-coded as additional calculated columns to the fact table. Although this approach is working, it makes maintenance hard since changing reporting dimensions always meant to change the data model and some calculations as well.
Power BI’s new feature to handle many-to-many relationships natively helps to create a very nice solution that can act as a template for any Power BI report that requires dimensions. It is also working nicely in Excel 2016. Making it work in Excel 2013 is possible as well but requires some extra steps.
Plug and play
Leveraging a many-to-many-relationship our final data model looks like the following:
The biggest part of the data model here consists of our dimension template. This is the part the can be re-used in any report. The connection from the fact table (in this case the sales invoices) to that template is what we call the plug. This connection is all what is required to bring dimensions to the report.
Dimensions in User Interface
From the end user perspective the dimension show up as normal lookup tables:
…and can be used in any visualization…
First step – which dimensions do we need?
First we need to decide where we want to be able to define those dimension that are required for our report. One approach that is commonly used would be to use the global dimensions one and two and shortcut dimension 3 to 8 according to general ledger setup. Alternatively if more than 8 dimensions in total are required or if some reports need to work on another dimension, a custom table structure could be used in NAV that allows us to define the dimensions per Report Type:
This means that reports of type SALES should offer the listed dimensions.This following steps are nearly the same regardless of whether this approach is used or just the 8 dimension from G/L Setup. In this blog post will explain the template based on the latter.
Defining the data source
As usually we will be using NAV objects as OData sources for our report. We will need the following Odata-feeds for the dimension-template:
GeneralLedgetSetup
DimensionValues
DimensionValues is a query on the dimension value and the dimension table. Joining the dimension table is necessary because we want to use the code caption of each dimension in Power BI:
DimensionSetEntries is a copy of standard page 479 Dimension Set Entries just extended by the Dimension Set ID. GeneralLedgerSetup is just the standard page 118 General Ledger Setup.
Save time by creating functions
Now it’s time to define queries that bring the data into the right shape. After transforming it, the data should be loaded into the aforementioned data model:
Power BI as a “real” BI-solutions requires us to split all our dimensions into separate dimension lookup tables – here called Dimension 1 to 4. If we wanted to be able to report on 10 dimensions, we would need 10 dimension lookup tables. The same is the case for the dimension set entries table.
In order to save us a lot of work, we created functions that can be used for any of these 4, 8 or 10 dimension and dimension set entries tables.
The first function GetDimensionTable is a function that returns the dimension values:
The input parameter is the dimension number – corresponding to Shortcut 1 to 8 in G/L-Setup. In this example Global Dimension 1 was “Kostenstelle” what is why these values were returned. The query does some transformations in order to obtain the Dimension Code and Dimension Code Caption as column headers.
A similar function is the function GetDimSetEntry. It returns all dimension set entries of a certain dimension number.
Creating the dimension tables
Now we can easily create the queries for all the necessary dimension values and dimension set entries tables. The queries only consist of one single line – a call to the function and only differ by their input parameter.
The query Dimension Set Entries Lookup is also based on the GetDimensionSetEntries-Query but retrieves the distinct Dimension Set IDs. This table is needed in the data model and acts as the connection between the fact tables (Sales) and the dimension template.
Table relationships – diagram view
In the diagram view the following relationships need to be established.
It is important that all relationships have the cross filter direction both activated:
Chris Webb nicely explains what Cross filter direction Both means in detail here.
Repeatability
This template can be used on any fact table that provides a Dimension Set ID – entries, posted documents, sales orders etc. It is only necessary to create a table-relationship between that fact table and the dimension set entry lookup table. Whenever we are creating power BI reports that should work with dimension we use this workbook as a template. In order to get it to work you only need to change the URL in the query ODataBaseQuery if the necessary web service are available.