Mapping fact tables to a cube - dimension driven

Use the Dimension Driven mode when the values to import are held in more than one column of the source table. For example, you might have a Measure table with columns for Units, Price, Discounts, etc. In that case, you select Measure as the driving dimension.

This procedure describes how to create a mapping for a full fact load and uses the dimension driven value column mode.

  1. Select Dashboards > OLAP > Edit Database.
  2. Select the database to administer.
  3. Select the Cubes node of the Database Structure.
  4. Click the Mappings icon of the cube to map.
  5. Click New Mapping.
  6. Specify a name and unique name for the mapping.
  7. Optionally, add comments to the mapping.
  8. Select the data connection to the relational data source.
  9. Select the source table or view from which to load the fact data.
  10. Click the Column Mapping tab.
  11. Select Dimension Driven as the value column mode. Select the driving dimension.
    In the Data Area grid, the selected dimension becomes uneditable and the Mapping Type column shows a value of Leading.
  12. Click Add Column and select the name of one of the value columns in the source. Add a column for each value column in the source.
  13. Optionally, create one or more parameters for use with Target Element mappings.
    Click Add and specify a name and description of the parameter. When you run a mapping, this description prompts the user to specify an appropriate value.
  14. In the Mapping Type column, select one of these options for each dimension:
    Option Description
    Source Column In the Source Column Name column, select the name of the column in the source table from which the element to be populated is read.
    Target Element In the Target Element Name column, specify the name of the element to be populated. Or, reference the name of a parameter or of a custom setting, using the format @[parameter_name] or @[custom_setting_name].
  15. Optionally, to load values for only a selection of elements in the source table, use the Source Column Filter column. You can specify one or elements, or use parameters or custom settings.
    Note: See Elements, parameters, and custom settings in cube mappings
  16. In the Element if not Found and Element if Empty columns, specify alternative elements to use if the specified target element is not found or has a null value.
  17. Optionally, turn off Clear Values.
    If Clear Values is on, regions of the cube are cleared before the fact data is loaded. The regions are defined by the selections in the data area table. These rules apply:
    • If a target element is specified for a dimension, then only values for that element are cleared.
    • If a target element is a consolidated element, then the values of its descendants are cleared.
    • If a source column is specified for a dimension, and no elements are specified in the Clear Cube Area column, then the values for the entire dimension are cleared.
    • If a source column is specified for a dimension, and an element of that dimension is specified in the Clear Cube Area column, then only values for that element are cleared.

      You can use custom settings and parameters in the Clear Cube Area column.

  18. Select one of these writing mode options:
    Option Description
    Add If a cube cell has a value, the new value is added to the current value.
    Overwrite If a cube cell has a value, then that value is overwritten by the most recently processed value from the source table.
  19. Optionally, to load only data created since a specific date or from a timestamp expressed as a number, select the Process Latest Records option. Select the column that contains the required date or numerical value.

    After a mapping is run, the maximum value of the run is updated. When the mapping is run the next time, only records greater than the maximum value of the previous run are processed.

    Clear the option, to clear the maximum value.

    When processing only the latest records, the Clear Values option is switched off automatically because it is not likely to be combined with processing the latest records. However, Clear Values can be selected again if required.

  20. Click the Run Settings tab and select one of these error handling options:
    • Abort at the first error, commit nothing
    • Commit all data and ignore errors
    • Try to run over errors, commit nothing if an error occurs
  21. Optionally, select Log warnings for element name replacements.
    If selected, this option logs every instance of an element being replaced during import.
  22. Save the mapping.
    You run saved mappings in the Mappings Overview dashboard.