Preparing Source Data for Data-Driven Modeling

image_pdfimage_print

This article describes the way a data file should be prepared for data-driven modeling in Jedox Web.

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