Column Headers in Source Data for Data-Driven OLAP

image_pdfimage_print

Related link: Data-Driven Modeling

The imported CSV or XLSX source file can contain meta information for the dimensions and the cube that will be generated. This information can be given in a suffix of brackets, e.g. [Products,1], where you can specify individual levels of a dimension, dimension attributes, measures, time dimensions, and drillthrough columns.

This information, which is contained in the uploaded file, can be changed in the Data-Driven Modeler. 

Possible bracket information:

[<Level>]
The column is Level <level> of a dimension with the column name.
Examples:
“Products[3]” : Level 3 of Dimension Products.
“Products[B]” : The level of base elements of Dimension Products, as alternative to the index of the base element level.

[<Level>,<Dimension>]
The column is Level <level> of a dimension with name <Dimension>.
Example: “Region[2,Country]” : Level 2 of Dimension Country.

[<Level>,<Dimension>,CONCAT]
The column is Level <level> of a dimension with name <Dimension> where the element names are concatenated over all upper levels.
Example: “Region[2,Country,CONCAT]” : Level 2 of Dimension Country with element names e.g. “Europe”, “Europe-West”, “Europe-South”.

[ATTR,<Level>,<Dimension>] The column is an attribute with the column name for Level <level> of a dimension with name <Dimension>.
Example: “Description[ATTR,Country]” : Attribute Description for Level 2 of Dimension Country.

[ATTR,<Level>,<Dimension>,<Attribute>] The column is an attribute with name <Attribute> for Level <level> of a dimension with name <Dimension>.
Example: “Region[ATTR,Country,Description]” : Attribute Description for Level 2 of Dimension Country.

[MES] The column is a measure that will be normalized to dimension “Measure” with the column name as keyfigure name.
Example: “Cost of Sales[MES]” : Normalization is done with dimension “Measure” and keyfigure Cost of Sales.

[MES,<Keyfigure>] The column is a measure that will be normalized to dimension “Measure” with keyfigure name <Keyfigure>.
Example: “column17[MES,Cost of Sales]” : Normalization is done with dimension “Measure” and keyfigure Cost of Sales.

[IGNORE] The column is ignored. Same result when no bracket is used.

[DATE,<sourceformat>,<targetformat>] A dimension based on a Calendar-Extract with column name is created. The column has to contain date-values in the sourceformat. The Calendar Extract will create base elements in the targetformat (and other upper levels and YTD-levels). For the targetformat only symbols ‘d’, ‘M’ and ‘y’ are allowed.
Example: Date[DATE,dd.MM.yyyy,MMM yy] : A dimension Date with base elements of form “Jan 14” is created, the column has to contain values in the form “30.01.2014”.

[DATE,<sourceformat>] A dimension based on a Calendar-Extract is created with same sourceformat and targetformat.
Example: Date[DATE,dd.MM.yyyy]

[DATE,<sourceformat>,<targetformat>,<language>,<start year>,<end year>] A dimension based on a Calendar-Extract is created. The years level is created from start year to end year. If not specified default values 2010 and 2015 are used
Example: Date[DATE,dd.MM.yyyy,MMM yy,EN,2013,2014]

[DRILL] The column is not part of the Cube but only a column of the Persisted Drillthrough with the column name.
Example: Orderline[DRILL] : Drillthrough is done with a drillthrough column Orderline.

No bracket
same as [IGNORE], i.e., the column is ignored.

Some rules:

  • If no column with [MES] is found, no normalization is done. In this case, the last column has to contain the cube value, independent of its column name.
  • The order of the column is not important (except the rule above on the last column for cube values).
  • Each dimension gets a root node in the form “All Products”.
image_pdfimage_print
Was this post helpful?
NoYes (0 rating, 2 votes)
Loading...