Using Dynamics NAV 2013 OData Services with SSAS 2012 Multidimensional

While SSAS Tabular and PowerPivot allow to directly connect to OData feeds, SSAS Multidimensional is limited to relational databases and Microsoft Access. So in order to use the data as source for an SSAS cube it has to be transferred to a relational database. Since we are already working with SQL Server we would like to use Integration Services to achieve this and thanks to the recently released (but long overdue) OData Source for Microsoft SQL Server® 2012 it is now easily possible. Prior to this release, there was no native connector for OData sources in SSIS so the connection had to be coded and was not fully supported.

We will build a simple fact table with some sales data and a date and customer dimension.

Providing NAV OData Feeds

First we need to create the query / page objects in Dynamics NAV and publish them as OData feeds.

As basis for our date dimension I am reusing the date table we have used in a previous post http://www.navida.eu/report-on-navs-account-schedules-in-excel-by-using-odata/.

As fact table I have created a simple query based on the sales invoice header and sales invoice line that includes the bill to customer number and the posting date as dimension keys and the quantity and line amount as measures.

As source for our customer dimension, we will simply use the standard customer list (Page 22).

To publish these objects as OData web services I switch to the RoleTailored client and search for “web services”. Via “edit list” we add the three objects and set the publish check box.

Creating the SSIS Jobs

First, we download and install the Microsoft OData source plugin for SSIS.

After this is done we switch to visual studio and create a new SSIS-Project “OData_Demo”.

Note that after inserting a DataFlow task, we can see the new option under “Common”.

We insert the “ODate Source” element and are prompted to choose the OData connection manager. We create a new one called “OData source”. Now we have to specify the “Service document location”. This is the base part of the OData connection URL we can see in the “Web Services” view in NAV, in our case:

http://test-server:7048/DynamicsNAV71/OData/

Nevertheless the data source needs to point to a specific resource. In the dialog we choose “Resource path” and enter the second part of the OData URL:

Company('CRONUS%20EXT%20AG')/PostedSalesDocs

When we click on “Preview” we will see the first 20 lines from our NAV web service.

We insert an “OLE DB Destination”, specify the connection to our relational database and create a destination table “PostedSalesDocs”. You can use a “DataConversion” component to adjust the data types since some of the string sizes in the OData Feed might not be specified and will by default be set to “max”.

We repeat these steps for our remaining data sources DimDate and DimCustomer and run the job.

Creating the SSAS Cube

After the ETL part is finished we can start building the cube. We create a new SSAS Project and point the data source to our relational database. Now we create a data source view, import our three tables and set the table relationships.

Next, we model our date and customer dimension. I created a simple hierarchy for both dimensions and added a couple of additional attributes.

The last remaining step will be to build the cube based on our fact table and add the two measures and a line count.

We can now process the cube and view the result in Microsoft Excel.