Validating and adjusting the data mart

Whenever you edit a data mart, certain actions need to be performed to ensure that the data mart is valid. For a data mart to be considered valid, the tables defined in the data mart need to be identical to the physical tables in terms of metadata. Depending on the change, this may require adjusting the physical tables or dropping and recreating them (via Compose).

Additionally, the generated task statements must reflect the current state of the data mart. So, for example, if a filter or expression was added/edited, you will need to regenerate the task statements before running the data mart task.

Situations in which you need to validate the data mart and/or regenerate the task statements include:

• Each time the data warehouse is adjusted
• Each time a new dimension is added to a star schema
• Each time a new star schema is added to a data mart
• Adding or removing columns
• Changes to a dimension’s history type
• Changes to transformations (expressions/filters)
• Changes to a star schema’s transaction date
• Changes to a star schema’s aggregation type (max, min, etc.)

Note that clicking the Validate button only verifies that the table metadata is valid. In certain cases, even if the metadata is valid, Compose will prompt you to regenerate the task statements (by clicking the Generate button).

When you validate a data mart, Compose presents you with a list of operations that it needs to perform for the data mart to be valid. Examples of such operations include adding dimension and fact tables, deleting the fact table when the transaction date column has been deleted from the model, and so on. You can either click Adjust Automatically or Drop and Recreate Tables to approve the operations or click Cancel to continue working with the data mart in its present state.

To validate the data mart:

1. Click the Validate toolbar button in the Manage Data Marts window. The Validating the Data Mart progress window opens.

   If any differences are detected, the following message will be displayed:

   Data mart validation failed. The data mart is different from the model.

2. Click Close. The Model and Data Mart Comparison Report window opens.

3. Review the report and then click Adjust Automatically or Drop and Recreate Tables to resolve the differences.

   Either the Adjust Data Mart progress window opens or, if you clicked Drop and Recreate Tables, confirm the drop and recreate operation. When you confirm the drop and recreate operation, the Creating Data Mart: Name window is displayed.

4. When the "The data mart was adjusted successfully." or "The data mart has been created successfully." (in the case of drop and recreate) message is displayed, close the window.
5. Click the Generate toolbar button to regenerate the task statements.

You can also adjust the data mart automatically using the generate_project CLI. For more information, see Generating projects using the CLI.

Auto-adjust limitations and considerations

The Adjust Automatically option has the following limitations, which also apply when the data mart is adjusted using the generate_project CLI.

• If a new data warehouse attribute was added to a dimension or to a fact by directly editing them in Compose:

  • All columns are supported except Transaction Date columns, which cannot be added automatically.

  • For existing records, the newly added column will be set to the database default value, usually NULL. If you want to load historical data for this column, you need to drop and create the data mart and then reload it. For information on reloading the data mart, see Reloading the data mart.

• If a logical attribute was dropped from a dimension or from a fact in the data mart, the data mart adjust will:

  • Drop it in the relevant tables, except Transaction Date columns which cannot be dropped automatically.

  • When there is an external dependent object that prevents deletion of the column (for example, a View is defined on top of the data mart table), Compose will report the error in the adjust execution messages. You then need to drop that object and run the adjust again.

• For referenced dimensions:

  • Adjusting a data mart does not adjust any dimensions that are referencing that data mart. The data mart containing the referencing dimensions needs to be adjusted separately.

  • Adjusting a dimension might also affect the referencing data mart facts.