OBIEE Aggregate Navigation (not hitting?)

Often you will find people complaining that they have setup aggregate navigation, but when they run report, OBIEE is not hitting the right fact table.

Common suggestions you will find to resolve this are:

• Go to Content tab of the Logical Table Source and check if Logical Level are set correctly set.
• Go to General tab of Dimension Level and check the value in textbox "Number of elements at this level"

In this post, I am just going to show that even after following above, the query may not hit aggregate table if you are not following modeling practice right.

Let's take following data as example. There are two sales fact tables and a time dimension. DAILY_SALES has day level sales data. WEEKLY_SALES has sales data pre-aggregated at week level.

Here's repository created on top of above physical data. The Logical Level is set correctly for WEEKLY_SALES.


Even the number of elements are set correctly for Week and Day level of Time dimension.




Let's execute following query which fetches sales by week. Since we have setup aggregate navigation, it is expected that the query should fetch data from WEEKLY_SALES table.

SELECT "Time"."WEEK_DESC", "Sales"."SALES_AMT" FROM SALESCATALOG

Now have a look at NQQuery.log. The query didn't hit WEEKLY_SALES table, in fact it is grouping data form DAILY_SALES table instead.


So what went wrong?
Our query has two columns WEEK_DESC and SALES_AMT. If you see the earlier repository screen carefully, you will see that WEEK_DESC attribute is not associated with any level of the Time dimension, so OBIEE assumes it to be most granular level attribute i.e. day level. When we query data by WEEK_DESC, OBIEE goes to fact table with day level granularity (i.e. DAILY_SALES) and fetches the data.

Let's add WEEK_DESC to Week level of the time dimension as shown below and execute the query again.


Now let's see NQQuery.log again. Bingo! it just hit the table we wanted it to.



Alternative approach with Snow-flake:
In the example I have given, the Time dimension is snow-flake, so it is also possible to set the logical level for invidual source table to make aggregate navigation work. Setting logical level "Time Week" for source table TIME_WEEK and "Time Detail" for source table TIME_DAY will do the work.

I hope this post will give a different perspective to your troubleshooting effort when you face that aggregate navigation problem again.

Switch OBIEE Repository and Catalog

At times, for development you may need to setup another OBIEE Repository and Catalog. In this post I will show how to do this.

First, stop Oracle BI and Presentation Services from Control Panel > Administrative Tools > Services. Also stop OC4J.

Configure Repository
Copy new repository to Drive:\OrabeBI\Server\Repository folder.

Open NQSConfig.INI file from Drive:\OrabeBI\Server\Repository folder. Under [ Repository ] section, add new repository alias. See the screen below. I added Star2 = MyProject.rpd.



Create ODBC DSN for Repository
Goto Control Panel > Administrative Tools and open Data Source (ODBC). Under System DSN tab click Add. Follow the steps in the wizard as shown below:

Step1
Select "Oracle BI Server" from the drivers list.


Step2
Provide a name for the ODBC DSN. I used "AnalyticServer"
Specify BI Server name.


Step3
Specify alias of the repository you want to connect to. We added alias in NQSConfig.INI.
Not needed if you want to connect to default repository.



Step4
Click Finish.



Configure Presentation Service
Copy new catalog under Drive:\OracleBIData\web\catalog\ folder. In case you want to create new catalog just add an empty folder under it.

Open instanceconfig.xml from Drive:\OracleBIData\web\config folder. Change the DSN name to the one we just created (AnalyticServer). Change the CatalogPath to point to path for the new catalog.


Finally, start OC4J, Oracle BI and Presentation Services.

Falling Back
To fall back to original repository and catalog

• Stop OC4J, Oracle BI and Presentation Services.
• Revert the DSN and CatalogPath in the instanceconfig.xml
• Start OC4J, Oracle BI and Presentation Services.

OBIEE Showing Data on a Calendar

Sometimes there's need for the business data related to promotional events, holidays etc. to be presented on a calendar to improve readability. In this post I will explain how to achieve this in OBIEE using mooTools and javscript calendar (http://dansnetwork.com/mootools/events-calendar).

Here's Sales report of an ogranization by day. Wouldn't it be great if we can plot this on a calendar and higlight the day when it's holiday.



Prerequisites:
To render the calendar we will need mooTools Core, mooTools More and mooTools events calendar. Here are URLs for download: (I used No Compression for all downloads)

MooTools Core: http://mootools.net/download
MooTools More: http://mootools.net/more
Select Date, Scroller, Tips while downloading mooTools More.
Events Calendar: http://dansnetwork.com/mootools/events-calendar/download/

Here's the list of all files you should have after download:

• mooECal.css
• mooECal.js
• mooECalLarge.css
• mooECalSmall.css
• mootools-1.2.4-core-nc.js
• mootools-1.2.4.4-more.js

Create a folder name mooTools on OBIEE server under Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\ folder and copy all the files to it.

Narrative View to render Calendar
I added Narrative View to the report I showed earlier.




Added following information to each textbox and selected "Contains HTML Markup" checkbox.

Prefix:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECal.css">
<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>
<div id="calBody"></div>
<script language="javascript">

function getDiv(holFlag)
{
if(holFlag=='Y')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div>';
}
}

new Calendar({calContainer:'calBody', newDate:'1/21/2010',
cEvents:new Array(

Narrative:
{
title: getDiv('@3') + '@4</div>',
start: '@2',
end: '@2',
location: ''
}

Row Separator:
,

Postfix:
)
}); </script>

Please note:

• The path to .js and .css files is ./res/mooTools.
• getDiv function returns div tag with red background when it's holiday. You can do other event specific handling if required.
  
• Calendar instance is passed an array of events. In our case this is sales information.
  Sales value is passed in the title. Start and End date are sale date.
• The comma (,) in Row separator will separate event array element.
• You can change mooECal.css in prefix section to mooECalSmall.css or mooECalLarge.css if you want smaller or bigger calendar.
  

Here's data rendered on calendar. Note, background color for holidays.



Please read licensing terms of mooTools and event calendar before making commercial use.

Common OBIEE Errors and Resolutions

In this post I am going to cover some common errors faced during OBIEE implementation and its resolution, in the windows environment. I am hoping this will be handy to anyone who is new to OBIEE.

Unable to Log In
Error connecting to the Oracle BI Server: Could not connect to the Oracle BI Server because it is not running or is inaccessible. Please contact your system administrator.
Error Codes: WH4KCFW6:OPR4ONWY:U9IM8TAC

Common cause(s):

• "Oracle BI Server" windows service is not running. Starting the service could resolve the issue.
• In case the BI Server service is running and still the error occurrs, check the port the service is listening to is matching with the port specified in the ODBC DSN for the analytic repository. In NQSConfig.ini file look for RPC_SERVICE_OR_PORT. The default value is 9703. The ODBC DSN name can be found in instanceconfig.xml

"Oracle BI Server" windows service is not starting.
Common cause(s):

• The RPD file specfied in the NQSConfig.ini doesn't exist in the repository folder.
• RPD file may be inconsistent. Open it using Admin tool in offline mode and fix the consistency errors.

Error Generating Chart
Error Codes: ETI2U8FA

Common cause(s):

• "Oracle BI Java Host" windows service is not running. Starting the service could resolve the issue.

Access denied for user to path /users/username/_portal.
Error Codes: O9XNZMXB

Common cause(s):

• The specified user doesn't have permission to read/write to the his/her catalog folder. To resolve the issue, login as Administrator and grant permission to the user to his/her catalog folder using catalog manager.