At the base of any good BI project is a solid data warehouse or data mart.  We can talk star schemas all day long and about the variation of snowflake versus but the main goal of this post is acknowledging the neglecting of the fact table as the foundation on which the star schema’s house resides.  I see way too often clients with poorly performing retrievals against their subject areas as I enter new engagements and work from the client’s existing infrastructure.  This is really unacceptable not only from a professional implementation perspective but also from one of query performance.  This post will look at what the default indexing of any fact table should be, why the default indexing is needed, why composite keys are good and bad, and why you should care.

I owe this post to my buddies Owen and Greg.  This one has been a long time in the writing and I have finally gotten around to putting fingers to keys.

Default Indexing Best-Practice

Short and sweet - all foreign key columns should have a non-clustered non-unique index. 

So what does that mean?

It means that a foreign key reference on a column to a table containing the primary key reference only provides the database engine with a point-of-reference to your dimension table.  It does nothing for how the data in the fact table is organized.  So, after you create a foreign key (FK) DDL statement be sure to create the non-clustered, non-unique INDEX as well.
Read the full story

As promised, based on my previous post regarding setting up Essbase Studio Server and OC4J to run as a Windows Service, I have provided a video to visually assist you with your integration.

Essbase Studio Server & OC4J As a Windows Service from ArtOf BI on Vimeo.

If you have any questions or comments please post them so that everyone can benefit from them.

Anyone who has installed OBIEE in the past few years knows that the default web/application server that comes with the installation binaries is OC4J.  A lot of clients question having the application server and the BI Server on the same box, not only from an infrastructure standpoint but as well as a valid performance concern when implementing a distributed environment.  You have probably heard some performance “red-flag” statements such as “don’t use OC4J if you plan to have more than 200+ users hitting the server”, etc. just like I have.  And, that might be a valid statement, however, I have not personally benchmarked it with any performance testing software but it does make perfect sense once you uncover what OC4J really is.

OC4J is a slimmed down version of Oracle Application Server. For those that have done an OBIEE install, you are familiar with the one installation prompt where you can choose either the standard or advanced option.  If you choose advanced you are prompted to provide Oracle Application Server connection information. Otherwise, you are defaulted to OC4J.  And, this version of OC4J that you get with OBIEE is a pre-packaged “StandAlone” (or, unmanaged) version that only has a little more than the basic functionality that the full-blown Oracle Application Server (OAS) has.  Think of OC4J as OAS-lite.
Read the full story

OBIEE has a smart query logging system and it happens to be mainly based on security logging levels.  This is great from an overhead perspective because you wouldn’t want every single user of the system to have a full introspective query log produced each and every time your hundreds of users run a query requests or dashboard view hit. 

Typically one will use logging during development, analysis, or troubleshooting efforts.  Otherwise there is no need to have logging on any higher than the default setting.  By default each user account’s “Logging Level” is set to 0 (zero) which is basically no logging.  For basic purposes of analyzing SQL or MDX syntax that make up the query, row counts, and recordset deliver time you will want to change the “Logging Level” to a 1 or 2.  These two levels start providing basic logging which can be seen from Settings > My Account > Manage Session in Answers.

Here’s how to change the “Logging Level” for a specific account:
Read the full story

We’ve been doing quite a bit of Hyperion Essbase integration into Oracle BIEE lately.  It is just cool stuff and most Oracle BI consulting groups will admit that this is all the rave right now.

Although our group has published several documents internally on the subject and there are several bloggers who discuss the topic I just the other day found Oracle’s best practice guide to the integration.

It is a short and sweet PDF.  In my opinion it could be better (always room for improvement).  Maybe I’ll update the document in my copious time : )

Here’s the link to the PDF:

Oracle’s Best Practices Guide for Implementing Essbase as a Data Source for OBIEE (PDF)