There are many instances when the data supplied to you is not in a format that is conducive to database import. In many situations, this is because the data supplied was originally produced within a Pivot-table, but can also occur in rolling-window reporting with things like months being placed across columns. This formatting is quite common in a variety of reports, but one example is shown below for comparison to what you may have. If you would like to follow along with this step-by-step process, the Excel file shown below is attached to this article (scroll to the bottom of the article to download).
To follow along, you will need to be using the 32-bit version of the Jet Data Manager since we will be connecting to an Excel file source via an ODBC connection.
For the purposes of this example, we'll be using an Excel file source. This same logic holds true to a source table in a database if that is what is present in your environment. If so, please click here to skip past the portion of adding an Excel file source.
This article will detail out the process to import the data as you receive it, and then use a SQL View with Unpivot to make the data usable within a SQL database and the Jet Data Manager.
For files that will continually receive updated information, it is recommended that you add the file into the Jet Data Manager as an additional data source. For the purpose of these examples we will be using an Excel file but the concepts apply to many scenarios.
To add the Excel data source, right-click on 'Data Sources', hover over 'Data Sources' and then select 'Add Excel data source'.
Within the ensuing window, simply give the new source a name and then navigate to the file by clicking the 'folder' icon (shown below).
Once you've added the file as an additional data source within the JDM, you will need to right-click on the data source name and select 'Read objects from Data Source'
Once you've read the objects into the data source, you will need to add the sheet and columns by checking the boxes appropriate on the right-hand menu. For spreadsheets that are going to continue to grow horizontally, you should expand the available fields to encompass any fields that will eventually be used. This is because the column selection cannot be automated. Therefore, if you have columns for January through July, you should create place-holder columns for the remaining months in a year.
5 When you're confident you have all potential columns accounted for, you'll want to 'Deploy and Execute' the new table that is in your staging database (it will be red because you added it from the source). Once you have Deployed and Executed the staging table you can now create your SQL View script using UNPIVOT. Example of this is supplied below for visual representation (example query available as an attachment at the bottom of this KB article).
Unpivot SQL Query
CREATE VIEW [dbo].[UnpivotExample] ([Customer], [Country], [State], [ZIP Code], [Month], [Sales Amount])
SELECT [Customer], [Country], [State], [ZIP Code], [ValC].[Pivot Column] AS [Month], [ValC].[Value Column] AS [Sales Amount]
FROM [JetNavStage].[dbo].[UnpivotExample_CustomerMonthlySales$_V] up
UNPIVOT ([Value Column] FOR [Pivot Column] IN (January, February, March, April, May, June, July, August, September, October, November, December)) AS [ValC]
Once you have the query built and tested, the next step is to go back into the Jet Data Manager, navigate to the 'Staging' database section of the tree, and then right-click on 'Views' and select 'Add Custom View'.
Once your view has been added into the Jet Data Manager, you will need to first right-click the view and select 'Deploy'. When that finishes, right-click the view again and select 'Read View Fields'. These two steps will make the view available and ready for the Jet Data Manager to use.
When you've finished the above, click-and-drag the view onto your Data Warehouse 'Tables' listing as shown in the image below. When asked, select 'Add as new table'
With that view as a table now in your Data Warehouse, you will need to navigate to the table within your Data Warehouse section and 'Deploy and Execute' the new table. This will now be available as a table within your DWH, and can be carried into the Cubes if desired.