Extend Dimension

Extend prebuilt dimensions with additional attributes from another data source. For example, you can create a category column that isn't available in the prebuilt dimensions.

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 Extend a Dimension wizard, select a schema and table from the database.
  2. Select the columns that you want to expose or use as a key for creating the join, or sort by.
  3. Click in the Display Name table field to enter a new name for the column or to edit an existing one and then click Enter to accept or click Esc to cancel.
  4. In Sort Order Column, 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.
  5. 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.
  6. 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.
  7. Click Next.
  8. In step 2 of the wizard, 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, click Add Joins, select the target and source logical tables, enter the join condition as an expression, and click OK.
  9. Click Finish.