Global Consistency Check: Database features do not match the defaults

www.obieefans.com 


Q: When I do the global consistency check in the repository tutorial I get a warning saying that the database features do not match the defaults.  What does this mean?

Even if you have followed the tutorial correctly, you will get the warning message:

  [39028] The features in Database ‘SH’ do not match the defaults. This can cause query problems.

when you do a global consistency check.  This warning is not shown or discussed in the tutorial.

The BI Server has to be able to generate optimized SQL for a wide variety of data sources.  To do this it needs to know what features each data source, typically a database, possesses; for example, some data sources will support an “outer join” and some will not.  This information is called the set of “default database features” (it’s stored in configuration file “DBFeatures.ini” in directory “<oraclebi home>\server\config”).  If you right-click on “SH” in the “Physical Layer”, select “Properties”, and then the “Features” tab you’ll see the list of default checkboxes, ticked or unticked, under the “Default” column heading on the right hand side.  Next to it will be another similar column with a heading of “Value”.  It’s possible to override the defaults by checking or unchecking the value checkboxes.  If the two lists don’t match then you’ll get the above error message when you do a global consistency check.  If the current values are more restrictive than the defaults then queries will work, but they may not be as performance efficient as they might be.  If the current values are less restrictive than the defaults then the query sent to the database might be unsupported, leading to an error or to an invalid result set.  So unless there is a very good reason, the current values should match the defaults (one reason for changing them might be that minor releases of the same database version might support different features or have bugs in specific features).  The “Revert to defaults” button at the bottom of the screen can be used to reset the current values to the defaults, and eliminate the warning message.

Now to explain why you are getting the error message, and why there is an issue of far greater importance that is missing from the tutorial.  Edit file “DBFeatures.ini” using “Notepad”.  Search for “ORACLE_80”.  Note in the section beginning “IS_LEFT_OUTER_JOIN_SUPPORTED” that the left outer join is the only type of outer join supported.  Now search for “ORACLE_10g_R2” and you’ll see that all four types of outer join are supported.

Right click on the “SH” node in the “Physical Layer” and select “Properties”.  Select the “General” tab.  You’ll see the value of “Database” listed as “Oracle 8i”.  Now click on the “Features” tab.  You’ll see that the join attributes, both current and default, correspond to an “Oracle 8i” Database.  If you scroll down the list of features you’ll see a mismatch in places, such as in “ODBC API Conformance”.  The import uses the ODBC data source and uses some of the information it provides to update some of the current values, leading to a mismatch with the defaults – which explains why you are getting the warning message.  Press “Revert to defaults”, press “OK”, and then retry the global consistency check.  You’ll find that the warning message has disappeared.

If you’ve followed the above you should have a question, and it’s a very important one with regards to performance.  If you were to use this repository as a production repository – as can happen – then you would be restricting the SQL sent to your Oracle 10g/11g database to Oracle 8i SQL constructs, which would lead to a significant degradation in performance (note that setting the “Call interface” field in the “Connection Pool” to “OCI 10g/11g” as you did at the start of the tutorial has no affect on the database features held in the repository).

Select the “General” tab from the “SH” properties as before and select the “Oracle 10g R2/11g” value from the “Database” LOV.  Click on the “Features” tab.  You’ll find that both the current values and the defaults have been set to the “ORACLE_10g_R2” values that you found in the configuration file (all four join types are supported).  Press “OK”.  Try the global consistency check as before and the warning will not appear, but this time you’ll be using all the features that your database can support.