Aside from managing BI/EPM projects in which I am still able to get my hands dirty, it’s always fun conducting technical architecture assessment for clients.  This is especially fun when a client is just ramping up their BI/EPM infrastructure or just getting these Oracle tools in house and simply need help with planning the architecture for their environment.   With that said, Oracle lends a hand for this fusion release of the EPM suite 11.1.2 with an updated best practice layout of the Essbase, HFM, and Planning environments scoped for 100 – 1000 users at 35% concurrency.  Like most situations we encounter this his helpful but it is never an exact infrastructure plan, usually due to one or more unique challenges within an organization.  However, it gives any organization a great starting point and some nice design ideas to learn from.  In rare “cookie-cutter” situations this architecture may just be perfect to implement just the way it is drawn out.  If you find that scenario, let me know, I’d love to hear about it.

Reference

Deployment Scenarios: http://download.oracle.com/docs/cd/E17236_01/epm.1112/epm_install_start_here/ch03s01.html

Server Disk Space: http://download.oracle.com/docs/cd/E17236_01/epm.1112/epm_install_start_here/frameset.htm?ch03s03s02.html

Client Disk Space: http://download.oracle.com/docs/cd/E17236_01/epm.1112/epm_install_start_here/ch03s03s01.html

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.