Unpivot CSV columns

In SeekTable, you can transform a set of CSV columns into attribute-value pairs and get 2 special dimensions (one for column names and another one for appropriate column values) where columns become rows.

For example, let's assume we have this table and unpivot is for columns C1, C2, C3:

After this unpivot reports may use either Columns Dimension (it can be filtered) or Values Dimensions (it can be used in measures too) or both. At the same time, reports may use other dimensions (that are not unpivoted like C0) for grouping purposes. Note that since values of these columns are duplicated many times because of unpivot operation, it is a little sense to use measures over non-unpivoted columns in the same report where you use at least one of these special unpivoted dimensions.

Typical cases when you need an unpivot:

• CSV is a survey (questionnaire) results where each question is a separate column, and you want to have a report where it is possible to filter (with a report parameter) by question.
• CSV contains date-related data where each date comes in a separate column, and you want to summarize values by your own grouping criteria - say, by date parts (year/month/day) or have an ability to choose a date range.

How to configure unpivot for CSV cube

1. Go to CSV cube view and click on Edit cube configuration
2. Open Unpivot section:
   
3. In Unpivot Columns specify which CSV columns should be unpivoted; for the example from above they are C1, C2, C3. If this list contains multivalue columns and unpivot should 'expand' each value separately then multivalue_column (Split) should be specifed for the unpivot.
   It is possible to specify a set of columns using patterns like that:
   • 1*: include all columns that start with "1" (wildcard pattern with *)
   • /[0-9]{1,2}[.].*/: include all columns that start with 2-digits and then a dot (.NET regex pattern - should start and end with /).
4. Then specify 2 special dimension names that should be used for (column name; column value) pairs.

Once unpivot operation is configured you may use these special dimensions in pivot reports. On-the-fly unpivot is performed only when at least one of these dimensions is used in the report.