OBE   OBIEE   Tutorial     
       
          Creating a Repository  

 

 

 

“Connection Pool” window doesn’t appear during import

 

Q: I’m doing the “create repository” tutorial and when I press the “Import” button the “Connection Pool” window doesn’t appear.  Have I done something wrong?

 

No you haven’t.  There are two different methods of importing schema metadata.  What the tutorial describes is a “hot-potch” of both, so it’s no wonder you’re confused.

 

Method 1.  Let’s assume you’ve followed the tutorial instructions to create an “ODBC Data Source” called “SH” that contains connect information for the “SH” schema using a “tnsnames.ora” entry of “ORCL”.  Continuing to follow the tutorial, when you select “Import => from Database” you see “ODBC 3.5” as the default “Connection Type”.  You then select “SH” as the DSN.  What you are doing here is identifying the “ODBC Data Source” that you defined earlier.  When you press “OK” to bring up the “Import” window and select your tables you already have all the information needed to perform the import.  So when you press the “Import” button the import starts and completes without any “Connection Pool” window appearing.  You’ll see the name you gave to the “ODBC Data Source”, “SH”, appear as a node in the “Physical Layer”.  If you expand this node you’ll see that the tables have already been imported (the tutorial is incorrect in saying you have to do something else for the import to proceed).  Just press the “Close” button in the “Import” window and you’re done.  Now right-click on the “Connection Pool” under the “SH” node and select “Properties”.  The “Call Interface” will be set to “ODBC 3.5” (the tutorial is wrong here) and the “Data Source Name” will be set to “SH” or whatever name you used when defining the “ODBC Data Source”.  You don’t actually need to change these values: they worked successfully in allowing you to import the metadata, and they therefore provide an indirect means of connecting to the Oracle database – via the “ODBC Data Source”.  However, changing the “Call Interface” to “OCI 10g/11g” and the “Data Source Name” to the appropriate “tnsnames.ora” entry for your database, say “ORCL”, makes sense as it will effectively bypass the “ODBC Data Source” when you connect to the Oracle database in the future.

 

Method 2.  But, defining an “ODBC Data Source” is redundant.  The statement in the tutorial “An ODBC data source is needed to import schema information into an Oracle BI repository” is incorrect.  All you have to do to import data is to navigate to “Import => from Database” as before.  Then instead of accepting the default “Connection Type” of “ODBC 3.5”, select a “Connection Type” of “OCI 10g/11g” from the LOV.  Then enter a “TNS Name” of “ORCL”, a “User Name” of “SH”, and the appropriate password.  Select your tables from the “Import” pop-up that appears and press “Import”.  In this case the “Connection Pool” window will appear (this is where the tutorial is getting confused).  Press “OK” and the import will start.  Press “Close”.  The only difference is that the node in the “Physical Layer” will be named “ORCL” instead of “SH”. 

 

 

No row count in Physical Layer after import

 

Q: After importing schema metadata and doing an update of all rows the tutorial shows the row counts displayed in the Physical Layer, but I just see the table names?

 

If you run your mouse over the table names you’ll see the row count appear briefly.  What the tutorial forgot to tell you is that row counts are not displayed by default.  Navigate to “Tools => Options”, select the “General” tab, and check the “Show row count in physical view” check-box.  Press “OK” to exit.  Now the row counts will appear and match the screen values displayed in the tutorial.

 

 

Can’t bring up BI Server – control request exception

 

Q: I’ve installed OBIEE and got it working okay.  Then I started on the repository tutorial and now I can’t bring the server up.  I get a control request exception?

 

The error message you’re getting is probably:

 

*  Error 1064: An exception occurred in the service when handling a control request

 

which is not very informative.  You’ll get this message for a variety of reasons.  I think in your case it’s likely to be because of an incompletely defined repository.  When you attempt to bring up the BI Server it will first read file “NQSConfig.ini” from directory “<oraclebi home>\server\config”.  This configuration file will contain the name of the default repository.  The BI Server will then read the contents of that repository.  But if the repository is corrupted or incomplete then the BI Server will refuse to start and you’ll get the above error message.

 

As part of the installation tutorial a repository “sh.prd” is provided to test that everything is working correctly.  This is a completely defined repository which is why you could bring the BI Server up in the past.  But in the repository tutorial you create file “sh.rpd” from scratch.  Unless you’ve finished the tutorial, you’ll have a partially defined repository, so the BI Server will refuse to start.

 

Edit file “NQSConfig.ini” and change the repository name to “paint.rpd” or “samplesales.rpd” (first check that the repository you select is present in “<oraclebi home>\server\repository”).  Then you should be able to bring the BI Server back up.

 

To confirm that an incompletely defined repository is the cause of the problem look in the BI Server log file, “NQServer.log”, in directory “<oraclebi home>\server\log”.  You’ll probably see something like:

 

*  [nQSError: 15014] No subject area is available in the repository Star.

 

The error messages that appear in the log files are sometimes more informative than those that appear on the screen.

 

Just use the Administration Tool to complete the repository tutorial.  Then switch the repository name in “NQSConfig.ini” back to “sh.prd”.

 

 

Can’t log back into repository tutorial

 

Q: I took a break after the first section of the repository tutorial.  I saved it and now I’m trying to log in again.  I presume the repository must be opened offline since the server is down.  But when I try to login as Administrator/ Administrator the login fails?

 

Yes, the issue of passwords in the tutorials can be misleading.  Elsewhere, such as when logging into the Presentation Services, you use “Administrator” as the password, but here you don’t.  Just leave the password field blank and you should be able to log back in and continue with the tutorial.

 

 

Global Consistency Check: Database features do not match the defaults

 

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.

 

 

Global Consistency Check: no warnings for missing keys

 

Q: When I do the global consistency check in the repository tutorial I don’t get the warnings about no keys being defined as in the tutorial.  If I look in the physical layer then the tables don’t have keys so why are the warnings missing?

 

They’re missing because by default the global consistency check doesn’t test for “Best Practice” messages – something the tutorial forgot to tell you.  To enable these messages, from the “Consistency Check Manager” (where the warnings and messages are displayed), select the “Options” tab, click on the “Best Practice” node, and then press the “Enable” button at the bottom of the screen.  Click on the “Messages” tab, and then on the “Check All Objects” button.  Now the two “Best Practice” messages about the missing keys should appear.

 

 

Could not connect due to missing username/password

 

Q: When I use the repository from the second half of the repository tutorial and I try to update the row counts it says it can’t connect due to an invalid username/password?

 

The repository supplied with this tutorial assumes that the password for account “sh” is “sh”.  But it’s quite likely that your DBA has changed the password from the default when he unlocked the account during the installation of the database.  If this is the case change the password for the connection to match the one you currently use.  Expand the “SH” node in the “Physical Layer”, select “Connection Pool”, right-click “Properties”, and set the “Password” field to the appropriate value.

 

 

No messages during consistency check

 

Q: When I do a consistency check in the repository tutorial I’m supposed to see four messages, but I see zip?

 

The section that follows on from where you are in the repository tutorial tells you how to turn the messages that you’re supposed to see – but aren’t seeing – off.  But this task has already been completed for the repository in question so this section of the tutorial is redundant – wouldn’t it be nice if Oracle’s “right hand knew what its left hand was doing?”

 

 

“Gross Profit Physical” not present in “SALES Logical Table Source”

 

Q: When I click on the mapping column tab in the logical table source for sales the gross physical profit is not displayed?

 

By default only the current mapped columns are displayed.  Click on the “Show unmapped columns” checkbox and the “Gross Physical Profit” will be added to the list.

 

 

“Share of Category” report missing decimal places, percent, and currency sign

 

Q: The share of category report I get is missing the percent sign for the share of category and the $ before the amount, and it doesn’t show the decimal places?

 

You’ll need to do a little more formatting to get the screen to match that in the tutorial.  In the “Criteria” pane click on “Column Properties” (the pointing finger) within the “Amount Sold” column.  Select the “Data Format” tab.  Check the “Override Default Data Format” checkbox.  In the “Treat Numbers As” LOV select “Currency”, in the “Currency Symbol” LOV select “$”, in the “Decimal Places” LOV select “2”, and check the “Use 1000’s Separator” checkbox.  Press “OK”.

 

In the “Criteria” pane click on “Column Properties” within the “Share of Category” column.  Select the “Data Format” tab.  Check the “Override Default Data Format” checkbox.  In the “Treat Numbers As” LOV select “Percentage” and in the “Decimal Places” LOV select “2”.  Press “OK”.

 

Press “Results” and the report should now match that in the tutorial.

 

 

“Matching table key does not exist in <table>”

 

Q: I’m using the Physical Editor to create foreign key links.  When I save a link I get a pop-up saying “A matching table key does not exist in <table>. Do you want to create one”?

 

Yes, you do want to create one.