Dimension and Cube Interfaces

image_pdfimage_print

Dimension and cube interfaces provide an easy way for the user to upload data into a cube or dimension or download data from them once or repeatedly. The interfaces can use various file-based sources (e.g. CSV or XLSX files). The upload/download is initiated from the Modeler and the process can be executed once or retained as an Integrator job and scheduled as a repeating task.

Dimension Interfaces

The interface for uploading/downloading dimensional data is called up from the Jedox Web Modeler, via the context menu of a specific dimension:

After selecting the “Upload file” option, the user is prompted to select a source location — either the local file system of the PC or the Jedox Web Report Designer — and browse for the file. Additionally, the file type (TXT, XLSX, or WSS) needs to be specified.

After clicking Next, the file is uploaded into the Report Designer, where a preview of the file’s contents is generated. The file data should generally match one of two types: Full hierarchy or Parent-child hierarchy. The preview shows a sample of all columns found in the file, with the default settings (e.g. for data delimiter). The user can change settings and generate a new preview by clicking the Refresh icon.

The columns from the preview then need to be mapped to the data structure of the dimension. This is done by right-clicking a column header and selecting the appropriate data type:

In the parent-child format, the user must select which columns hold parents, children, and weights. Other columns can be mapped to attributes. The Upload Wizard can recognize certain column names and assign them to the correct mapping. The column names :parent, :child, and :weight are recognized; if a column name matches an existing attribute in the dimension, it will be mapped automatically. To map a column to a localized attribute, the naming syntax <attribute>@locale is recognized automatically, e.g. color@fr_FR. All mapped columns will show bold-italic formatting in the column header.

In the full hierarchy format, the user must define which column represents which level in the targeted dimension’s hierarchy. Again, other columns can be mapped to attributes. An attribute can also be created on the fly.

After defining the mappings, the user can define additional options for the dimension build when clicking Next. Finally, the user can decide whether to run the upload once or as an ongoing Integrator job, and also whether a recurring scheduled task should be generated from the execution.

The download interface for a dimension works in a similar fashion, but with a few notable differences. The user here can first decide whether the whole dimension or a selected set of elements should be downloaded, and then choose an output format and target type. Based on this selection, a preview will then be shown, allowing the user to adjust additional settings. Finally, the user can generate a one-time file for download, or an ongoing Integrator job/scheduled task.

Cube Interfaces

The interface for uploading/downloading cube data is called from the Modeler, via the context menu of a specific cube.

With “Upload file …” you can only upload values to a cube. The structure of the cube to address cube cells for these values must exist or must be created beforehand with appropriate dimension uploads.

After selecting the “Upload file” option, the user is prompted to select a source location — either the local file system of the PC or the Jedox Web Report Designer — and browse for the file. Additionally, the file type (TXT, XLSX, or WSS) needs to be specified.

After clicking Next, you receive a dialog window with “Main settings” and “Advanced settings”. In “Main settings” you can set header, data delimiter and enclosure character for TXT or CSV files, and a Range for XLSX or WSS files. In last case, the cube name is set as default range. Delete this entry if it doesn’t match the upload file and press the button “Refresh”.

The Cube Upload Wizard generates a preview of the file data and tries to map them to the cube dimensions. By right-clicking a column header you can change the suggested mapping.
By clicking the Refresh icon you can generate a new preview.

Finally, the user can decide whether to run the upload once or as an ongoing Integrator job, and also whether a recurring scheduled task should be generated from the execution.

The download interface for a cube works in a similar fashion, but with a few notable differences. The user here can first decide whether the whole dimension or a selected set of elements should be downloaded, and then choose an output format and target type. Based on this selection, a preview will then be shown, allowing the user to adjust additional settings. Finally, the user can generate a one-time file for download, or an ongoing Integrator job/scheduled task.

Example of an upload with denormalized data ( 8 steps):

Entries of sheet “Orders1” in file “Orders.xlsx”:
In column A are „Product_IDs“, in range B1:G1 are weeks of the year 2016.

  1. Creation of the dimensions “Product_IDs” and “Weeks”.
  2. Dimension upload of a new created file “Product_IDs.xlsx” with the first column of the file “Orders.xlsx”. The entry in A1 is “Product_IDs”, the sheet should have also the name “Product_IDs”:

  3. First row of file “Orders.xlsx” must be transposed into the first column of a new file “Weeks.xlsx”. Overwrite in A1 “Product_IDs” with “Weeks”. The sheet should also be named “Weeks”. Then you can execute a dimension upload of the week elements (same procedure as step 2).
  4. Creation of the cube “Orders1”:

  5. Setting of “Weeks” as “Semi-additive Dimension Measures”:
    If the upload data are not normalized, you must first set “Dimension Measures” in column “Semi-additive” for the dimension “Weeks” in the cube:

  6. Then you can start the “Cube Upload Wizard” and upload the file “Orders.xlsx”.

  7. After setting the “Mode” to “Denormalized (by Measure)” in “Advanced Settings”, you can refresh preview and ”Weeks” will be mapped automatically:

  8. With “Next” and “Run once” the values will be loaded into the cube “Orders1”.
image_pdfimage_print
Was this post helpful?
NoYes (+1 rating, 1 votes)
Loading...