Introduction
It is very common to encounter pivoted excel data. e.g. Survey data - where each question is displayed in an individual column or financial General Ledger data where you have entries for each month in individual columns. Data in pivoted format is not conducive for data manipulation in BI tools. e.g. If we want to plot a monthly or quarterly trend, all the months have to be in a single column.
Solution
- One option is to create a custom SQL view in the Elastic Cube Manager, this can be very complex and laborious and also does not dynamically adapt to a changing number of pivoted columns.
- With the new connector frameworks introduced in vs 6.4.1.x. It is possible to create a custom wrapper around an Excel file and unpivot the data. Attached here is one such implementation of an Excel Unpivot Data Connector.
Excel Unpivot Data Connector (XLSX only)
Instructions
#1 Download the .zip file from the below link (extract contents) and place it in the following folder:
C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors
#2 Make sure the Windows service SisenseJVMConnectorsContainer service is running.
#3 In the Elastic Cube Manager select Add Data and choose Microsoft Excel File Pivoted
#4 Enter the URL in the following format:
FilePath=C:/Tech/Sales Pivoted.xlsx;Sheet=Monthly Sales;NumHeaders=2;DataType=float
- FilePath: Absolute File Path with / file separator. (Spaces Allowed)
- Sheet: Name of the Sheet in the Excel Workbook.
- NumHeaders: The number of header columns in the pivoted data. i.e. The number of columns to skip before it begins unpivoting the data. E.g.
- Department, Expense Type, Jan, Feb, March, April, May, June
- NumHeaders= 2.
- DataType:=int/float/text specifies the data type of the pivoted data. If you choose int or float all non numeric values will be set to null.
- ConfigFile:=Optional setting, if you wish to manually specify the columns to be extracted from the file. e.g. ConfigFile=col.properties. Create a text file and rename the .txt. to .properties. Add a line entry in the following format. col.list=headercol1;headercol2;....;pivotcol1;pivotcol2;pivotcol3.... The list should include both header and pivot columns. The order need not match the order in the excel file. Place the config file in C:\Program Files\Sisense\DataConnectors\JVMContainer\Connectors
#5 Click on Connect to Server. The Database will always default to NA.
Note: There is currently no authentication.
#6 Choose the table from the table list. If your have entered the connection information correctly then you will see your header columns + 2 additional columns. Dim Name and Dim Value which contain the unpivoted data.
#7 Run the build and build the elastic cube.
e.g.
Raw Pivoted Excel file:
Unpivoted Data in Elastic Cube:
