Oracle Exalytics – Consistency Checking or/and Model Checker

Exalytics repository offers two model checking options, one consistency checking which checks the validity of repository while model checker is used to identify potential modeling errors in repository that will cause issues with running aggregate persistence. This blog is an attempt to highlight these two options, it usage, differences and limitations.  

Consistency Check

Consistency Check option checks the validity of repository to ensure that it can load at run time and to identify syntax or semantic errors that may result in failing queries.

In addition, running a consistency check might result in updates to your repository metadata. For example, invalid objects are deleted during Consistency Checks. This behavior might result in deleted expressions and filters on logical table sources and logical columns. Invalid references can occur when objects were deleted in the Physical layer without properly accounting for the references in the Business Model and Mapping layer objects.

In addition the Consistency Check Manager does identify application roles that have been defined in the Administration Tool, but that have not yet been added to the policy store. Messages about placeholder application roles only appear when you perform a consistency check in online mode

Consistency Check Messages

The consistency checker returns the following types of messages:

Errors. These messages describe errors that need to be fixed. An example of the error is as follows, developer has to use information provided with error details and resolve issue and re-run consistency check.

[38082] Type of Hierarchy '"0RT_C41"..."0RT_C41/MDF_BW_Q02"."Product Hierarchy for Material MARA"' in Cube Table '"0RT_C41"..."0RT_C41/MDF_BW_Q02"' needs to be set.

Warnings. These messages indicate conditions that may or may not be errors. An example of warning is as follows. The warnings would be raised by process for number of reasons such as  inconsistent values, feature table changes that do not match the defaults. 

[39024] Dimension '"Paint"."Market Dim"' has defined inconsistent values in its levels' property 'Number of elements'.

Limitations

The Consistency Check Manager does not check the validity of objects outside the metadata using the connection.

It only checks the consistency of the metadata and not any mapping to the physical objects outside the metadata. If the connection is not working or objects have been deleted in the database, the Consistency Check Manager does not report these errors.

Consistency checking rules are relaxed for the lookup tables, hence it does not work for lookup tables which is been used to store translated field names with multilingual schemas.

Model Checker

Model Checker is used to identify potential modeling errors in the RPD that will prevent Aggregate Persistence from being run. Consistency check passes on the corresponding business model – but aggregate persistence script fails. It is important to detect the root causes before executing the aggregate persistence scripts which could be done by Model Checker.  

Model Check Manager requires access to the summary statistics table (when using Filtered by Statistics) and back-end data sources for some checks. Because some of the back-end queries can be expensive, it is recommended to run Model Check Manager during off-peak periods.

Model Check Manager can only be run in online mode.

Model Check Manager does not make any changes to repository metadata - it only flags possible problems.

Model Check Messages

The model checker returns the following types of messages:

Errors: - These messages describe errors that need to be fixed. Oracle BI Summary Advisor recommendations might be incorrect, and the aggregate persistence engine might fail to create aggregates.

Warnings: - It is recommended that you fix warnings, but not required. Issues identified by warnings result in suboptimal recommendations from Oracle BI Summary Advisor, or suboptimal performance from the aggregate persistence engine.

Model Checker Errors and Resolutions

Some of the examples below describe on how to fix certain errors arose during the model checker option

Error A

Business Model BadLevelKeyTraits.01 (multiple logical tables): [38157] The Level 'State' has a primary key whose columns come from more than one logical table. Aggregate Persistence will not be able to create any aggregates at this level.

Explanation: An aggregate can have only one LTS. Therefore if 'State' has columns from two logical tables, we are trying to create an aggregate with two LTSes, which is invalid. Make sure State only comes from one table.

Fix: Modify the State level primary key, in the Columns field, pick State.CountryName instead of Country.CountryName

Error B

Business Model UniqueKeyTest.05 (bad data):- [38153] The primary key of the "UniqueKeyTest.05 (bad data)"."CityDim"."City" logical level is not unique. This will cause failures during aggregate persistence and data discrepancies in created aggregates.

The following physical table keys may be incorrectly defined based on the data: "BI_MODEL_CHECKER".."BI_MODEL_CHECKER"."A_CITY_DENORMALIZED_DIM_W_DUP"."A_CITY_DENORMALIZED_DIM_W_DUP_Key".

The following logical SQL can be used to diagnose the issue:

/* Query to check uniqueness of "UniqueKeyTest.05 (bad data)"."CityDim"."City" */ SELECT DT.key_col_0, DT.key_col_1, DT.key_col_2, COUNT(*) key_count FROM ( SELECT_BUSINESS_MODEL "City"."CountryName" key_col_0, "City"."StateName" key_col_1, "City"."CityName" key_col_2 FROM "UniqueKeyTest.05 (bad data)" ) DT GROUP BY DT.key_col_0, DT.key_col_1, DT.key_col_2 HAVING key_count > 1

Fix: Go to City Logical Table's LTS. Query Related > Physical Table.

View Data on the physical table. Duplicate rows make the key combination non-unique. Remove the redundant row using SQL Developer.

Error C

Business Model BadLevelKeyTraits.02 (derived attribute): [38158] The Level 'State' has a primary key whose columns are derived columns. Aggregate Persistence will not be able to create any aggregates at this level.

Explanation: Aggregate Persistence cannot support columns which are derived attributes. Level keys with such columns are invalid.

Fix: Modify the State level primary key, in the Columns field, pick State.CountryName instead of State.DerivedCountryName

Performance of Model Check Execution

Model Check Manager runs parallel queries against the database for better performance. By default, 24 threads are enabled. To change the default number of threads for model check manager, create and set an operating system environment variable called MODEL_CHECKER_MAX_THREADS. The maximum number of threads you can specify is 100.

I hope you find this information useful.