Add Dimension

Add dimensions to facts to complete a new star or to update existing stars. You can create joins from your custom dimension to a prebuilt fact.

You can define a sort order column to control the sorting order of a logical column, especially when the desired sort order doesn't match the natural sort order of the data. This is useful for situations like sorting months in a specific chronological order or sorting descriptive columns based on a code or hierarchy.

  1. In step 1 of the Add a Dimension wizard, select the schema, and then select the dimension table in Object. For example, COST_CENTER_VIEW1, and add a name in Dimension Name.

    Note:

    If you don’t see the schema or table, then ensure that you have granted select permission to the OAX$OAC schema in the autonomous data warehouse. For example, grant select on <schema>.<table> to OAX$OAC. See Load Customization Data to the Autonomous Data Warehouse.

    You see the attributes available in the selected dimension table. You can use the Search and Filter fields to limit the attributes displayed for the dimension table.

  2. Select the attributes that you want to use from the dimension table and indicate an attribute to be used as the key for joining with a fact table in the target subject area.
  3. If any of the selected attributes have been removed or modified in the source table since the last refresh, then you see such columns highlighted and a message asking whether you want to update the table. Select OK in the message to reload the source columns. If you want to review the changes to the source columns, then click Cancel in the message, and later click Refresh to reload the source columns.
    If any of the attributes that you haven’t selected have been removed or modified in the source table, then you see the refreshed list of source columns. If any of the custom columns fail validation during the refresh, then you see a message asking you to resolve the cause of failure and revalidate.
  4. Optional: Click Create Column to add another column to your dimension table in the target subject area using these instructions:
    1. In Create Column, enter a display name.
    2. Under Data Elements, search for a data element from the physical table of the selected dimension table.
    3. From the search results, double-click the data element to place it in the text pane.
    4. Under Functions, search for a function to construct a column using expressions. For example, search for functions like "substring" or "concatenate" to construct new expression-based columns. From the search results, double-click the applicable result to add it to the central text pane.
    5. Click Validate, and then click Save.
  5. In step 2 of the wizard, assemble the product hierarchy using the attributes from this dimension and click Next. See Add Hierarchy.
  6. In step 3 of the wizard, select applicable values in Sort Order Column for the columns that you selected to expose or use as a key.
    Ensure that the sort order column is within the same hierarchical level as the display column. For columns not requiring a custom sort, leave the sort order column in the default Select state.
  7. On the Logical Star: Fact page, in the Graphic tab, click on the prebuilt fact and drag drop on the custom dimension that you created to open the Join dialog.
  8. In the Join dialog, select the join type, and then select the dimension keys to join them with the extended dimension keys. If you want to provide expressions as join conditions, then click Complex Join and in Create Joins, select applicable Content Level, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and then click OK.
  9. Click Finish.