a generic calendar dimension

Finally had the time to activate on of my other blogs. This one will contain my more generic articles. Here is the first one: http://bizintell101.blogspot.com/2009/10/bi-101-generic-calendar-dimension.html

Feel free to comment.

Till Next Time

OBIEE Multiple repository’s / Multiple presentation servers

Excellent research by RNM:

• http://rnm1978.wordpress.com/2009/08/25/multiple-rpds-on-one-server-part-1-the-bi-server/
• http://rnm1978.wordpress.com/2009/08/25/multiple-rpds-on-one-server-part-2-presentation-services/

Need I say more?

Till Next Time

OBIEE Open a Linux-Hosted OBIEE Repository in Online Mode (repost)

For those who run their OBIEE servers in a Linux environment, it’s possible to open your repository in Online mode from your Windows-based local machine. I just discovered this, so I thought I would pass it along. This assumes that you have the appropriate version of OBIEE installed on your local machine.
Update: Do not use spaces in your System DSN names. You will get an error when trying to connect to that server.

This article has quite a few screenshots, so continue reading by clicking below:
All you need to do is create a new System DSN: Go to Start > Run…, and type odbcad32, then press enter (or open ODBC Data Sources from Administrator Tools).
Click the System DSN tab and click Add:

Choose Oracle BI Server as the Driver:

Give the DSN a Name (no spaces) and optionally a Description, and specify the hostname of your BI server, then click Next:

Type a login ID and Password for your repository, and specify the BI Server Port. Check the option “Connect to Oracle BI Server to obtain default settings…” to make life easier, and click Next:

Accept the defaults if you’re happy with them, and click Finish:

You’ve now added a new System DSN to your local machine and are ready to open the repository in Online mode:

In the Administrator tool, choose the blue folder to open in online mode, and choose the DSN you just created from the list below the login prompt. Enter your login details and click Open to work on your repository in Online mode:

Of course, as the OBIEE documentation recommends, I would only use Online mode for making small changes to your repository. Any significant amount of work should be done in offline mode.
This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

OBIEE When the Top N Filter fails… (repost)

If you’ve read my previous post about Using the Top N Filter, this will be a follow up post that covers a trick I discovered when the Top N filter didn’t do what I wanted it to do.
My requirement is to only show data for the past 6 weeks.  I have a base table that has massive amounts of weekly data, designated by a column week_ending.  At first, I thought I could simply add a Top N filter for "Week Ending" in Top 6.  This didn’t work however, since I have multiple rows containing the same date - it only returned data for the latest date in the table (which makes sense, because that date would be the same for each of the Top 6 since it occurs many times).  I also tried using the DISTINCT keyword in various places in the filter formula, as well as in the column definition - still no luck.
What I ended up doing was the following:

1. I decided to turn to SQL to give me the results I need.  I started by building a query that gave me the Top 6 dates in my base table:
   

   SELECT * FROM (SELECT DISTINCT week_ending
   FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6;
   
   WEEK_ENDING
   ------------------------- 
   13-JUL-08
   06-JUL-08
   29-JUN-08
   22-JUN-08
   15-JUN-08
   08-JUN-08                 
   
   6 rows selected

   
   
2. 
   
   
   
   Next, I used the query above in the WHERE clause for a view:
   
   

   CREATE OR REPLACE VIEW top_six_weeks_vw AS
   SELECT *
   FROM base_table bt
   WHERE bt.week_ending IN (SELECT * FROM (SELECT DISTINCT week_ending                                          FROM base_table
   ORDER BY week_ending DESC)
   WHERE rownum <= 6);
   
   

   
   
3. 
   
   
   
   Now, simply model this view in the OBIEE Repository, and your Answers reports will only display the latest 6 weeks worth of data.
   
   

This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html









Till Next Time

OBIEE Using the Top N Filter (repost)

Learn to use the Top N filter to filter results in Answers requests. Covers advanced usage of the Top N filter using the by clause, to show the Top N results within a particular grouping (i.e., Top N Customer Revenue amounts by Product):

Suppose you want to see the Top 5 results from an Answers report, such as the Top 5 overall customers by revenue:

This can easily be acheived by using a simple “in Top N” filter:

What if you want to see the Top 5 Customers by Revenue for each Product? Using a simple Top N filter won’t work in this case, as it will produce the following results:

To get OBIEE to show the correct results, you need to take the Top N filter into SQL mode. Starting with a normal Top N filter, click Advanced at the bottom of the filter window and select Convert this filter to SQL:

Next, edit the filter with the by clause enclosed in red - unfortunately you’ll have to type the column name in manually, so be sure to use quotes if the column or presentation table contains spaces. The column used here is the field you want to group by (Product, in this example):

Now, the Top N filter will give the desired results - The Top 5 Customers by Product:

The full request criteria (notice the sorting options used to produce the results above):


This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time

OBIEE Grand Totals with Calculated Columns (repost)

This is a bit of a specific problem, but I could see it being something that those new to OBIEE could run into. It deals with incorrect Grand Total values being displayed for a Calculated Column when using report filters.
Consider the following report:

In this report, Amount A and Amount B are standard columns in a database table, and Variance is a calculated column in the repository. The calculation for Variance is 100 * (Amount B - Amount A) / Amount A. So, going by the Grand Total amounts in the report above, 100 * (696 - 550) / 550 = 26.55.
Suppose you want to filter the results of this request so that it only shows items having a Variance greater than 30:

Note the Grand Total for Amount A and Amount B are summed correctly for the filter, but the Variance still shows 26.55. Obviously, this is incorrect, so what’s going on here? The filtered amount should be reported as 100 * (220 - 130) / 130 = 69.23.
If you want to show the correct variance for the filtered Grand Total amounts, you need to enable an option in your instanceconfig.xml file. Open the file $OracleBIData/web/config/instanceconfig.xml, and add the following line somewhere inside your <ServerInstance> and </ServerInstance> section:

<ReportAggregateEnabled>true</ReportAggregateEnabled>

Make sure you don’t accidentally put this inside an element nested inside <ServerInstance>, or it won’t work.

Save instanceconfig.xml, and restart your BI Server and Presentation service.
Now, the correct amount should be shown for the filtered Grand Total variance:

This was previously a bug in OBIEE that was addressed by adding the ReportAggregateEnabled option. It’s not really documented anywhere other than on Metalink, so hopefully this will be helpful to someone.
This article was original posted on the Kevin C. oraclebi blog. See: http://obiee101.blogspot.com/2009/09/obiee-blog-lost.html
Till Next Time