Data Transpose Guide

[intapiuser]

Source data that is flattened may require some or all of the columns of a source table to be transposed, in order to work seamlessly with SiSense’s functionality. This post will outline the cause and two possible solutions to this issue. The first solution uses an excel macro to tranpose data. The second solution provides a method for creating a custom SQL expression that will transpose the data in the ElastiCube Manager.

Required: Excel, basic SQL knowledge.

Suggested: Notepad++, basic knowledge of regular expressions  (Notepad++ Download Page)

Transposing data can make development easier on data sets that are not properly structured. This guide also provides an example of advanced custom SQL expressions and bulk creation of SQL statements.

For our example, we are using survey data, where every question is a column, and the answer is the value in the column. 

The question we want to answer with the data is “How often do married people answer ‘Best’ to a question?” With the original table format, we are not able to answer this question a dashboard. In order to answer this question, we have to transpose some of the data.

To do this, we need to create a more general ‘Question’ column, where each value is a question name, and a general ‘Answer’ column, where each value is an answer to the corresponding question. The original source data and the desired reformatted data can be found in the attached excel book Prism Reformatter.xls. This picture show the desired result of the process.

Method 1 - Excel Macro in Prism Reformatter.xls

The attached document Prism Reformatter.xls contains a macro that will transpose the data in a sheet based upon parameters a user can set. This method should be used if your data already in excel, would be easy to put into excel, and has less than 65,000 rows. 

Prism Reformatter.xls contains 3 sheets.

1. Data- Will contain the data to be transposed. It is currently populated with an example data set.
2. Formatted- This contains a formatted version of the example data set.
3. Configuration- This is the sheet where you define the settings for your data transpose, and it also contains a button that executes the macro to transpose data. The following settings can be configured:

• Row Fields: The columns in the data sheet to be selected, but not transposed. In our example that would be Date, Name, Gender, Marital Status, and Age
• Col Fields: The columns to be transposed. In our example that would be Question 1, Question 2, Question 3.
• Source Sheet: The sheet in the workbook containing the original data.
• Target Sheet: The sheet to put the reformatted data in. This sheet will be created when the macro is executed
• Rows/sheet: How many rows to put on a sheet before creating another sheet to put data into. This is to account for the amount of data a worksheet can contain in different version of excel

Once these settings are configured, click the Reformat button, and you’re done. Use the new sheet as the source for the Elasticube.

Method 2- Custom SQL

This method should be used if your data would be difficult to import into excel, or the amount of data is causing poor performance in excel. This can provide a basic methodology for more complex transpose logic that may not be possible with the excel document. At a high level, this solution requires a table to be created that defines the columns to be transposed, as well as a custom SQL table that will build the transposed data in the ElastiCube manager. Listed below is the final custom SQL expression used for this example to transpose the table DATA. This is to give you a sense of what we’re working toward.

SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age,
 CRS.col_nm AS Question,
 CASE WHEN CRS.VAL=1 THEN D.[Question 1]
      WHEN CRS.VAL=2 THEN D.[Question 2]
      WHEN CRS.VAL=3 THEN D.[Question 3]
 END AS ANSWER
FROM Data D
CROSS JOIN [CRS_JN] CRS 

The CROSS JOIN joins every row from Data to every row in CRS_JN. The following diagram gives a high level overview of the process.

Create the Custom SQL Expression

The steps below explain the process for creating this SQL statement. It can be extended to transpose any number of columns.

1.Create a list of the columns you want to transpose. With our sample data set, we can copy all of the column names that need to be transposed (Question 1,2,3), and transpose them into a new worksheet, and name the column ‘col_nm’. If your source is not excel, a different method will be needed to retrieve the column names, and it will depend on your source system. Once the column names are in an excel sheet, generate a numbered list next to it and name the column ‘val’. I've named the sheet CRS_JN in this scenario. The table should look like this in excel.

2. Import the new workseet into ElastiCube Manager. In your elasticube, click Add Data –> Excel worksheet, and add the sheet that was created in step 1 (CRS_JN). You’ll end up with a new stand-alone table that looks like this:

3. Create the case statement for your custom SQL. This code can be written by hand, but in scenarios where you are transposing large number of columns, this step can be very helpful. Instead of writing each WHEN statement by hand, I used the text editor Notepad++, because it is able to find and replace lines of text based upon patterns.

Take the data from the CRS_JN worksheet, without column names, and copy it into Notepad++. It should just be a list of a number, a tab, and a value. Open up the Find and Replace menu (Ctrl+h), and enter the following for your search pattern. 

(.*)\t(.*)

 And you replace with this pattern:

WHEN CRS.VAL=\1 THEN D\.[\2]

Make sure the ‘Regular Expression’ Search Mode is enabled. 

The following diagram show what happens on a row by row basis in this find and replace.

4. Write the rest of the custom SQL.

• Write the part of the select statement that doesn't require transposing, and add the cross join. In this case it is

SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age

FROM Data D
CROSS JOIN [CRS_JN] CRS

•  Next, select col_nm from the cross join table, alias it, and write the beginning and end of the case statement.

SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age,
CRS.col_nm AS Question,
 CASE
 
END AS ANSWER
FROM Data D
CROSS JOIN [CRS_JN] CRS

•  Insert the WHEN clause you created in step 3 between the CASE and END indentifiers

SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age,
 CRS.col_nm AS Question,
 CASE WHEN CRS.VAL=1 THEN D.[Question 1]
   WHEN CRS.VAL=2 THEN D.[Question 2]
   WHEN CRS.VAL=3 THEN D.[Question 3]
 END AS ANSWER
FROM Data D
CROSS JOIN [CRS_JN] CRS

5. Build the new table. Open the Elasticube Manager, click Add Data -> Custom SQL Expression. Insert the query you generated, parse the SQL to see if it’s correct. If the query parses, build the table and validate the data.

How often do married people answer ‘Best’ to a question?

We can now answer this question. Create a dashboard with the ElastiCube that contains the transposed table. Create a pivot table that selects Marital Status and Answer as attributes,  and add a value that calculates the DupCount(Answer). We now know married people answered 'Best' once.

Attached Files

Prism Reformatter.xls

Transpose_Test.ecdata

Related Links

Regular Expression Guide: http://letconex.blogspot.com/2013/06/how-to-use-regular-expressions-in.html

Cross Join Explanation:  http://en.wikipedia.org/wiki/Join_(SQL)#Cross_join