Build Your Own Time Hierarchy

Every single BI system has a time/date/period/calendar hierarchy in it. If you use the BI Apps, it actually comes with 2 different tables for this purpose alone, along with a ton of OOB capability. However, when you are faced with a pure custom system from scratch, how do you build one? What does it look like? What sort of data should it have in it?

I’m going to lay out what a decent Time Hierarchy looks like from an OBI and data model perspective. From there I’ll then demonstrate how to actually build one yourself instead of relying on complex code. Along the way we’ll mix in some best practices and theory as well.

Your mileage may vary depending on the specifics of your system, but my goal is to make the design and building of your hierarchy take only 3 hours and last the lifetime of your project (or career).

BI Apps / Performance Layer Preso

This is my presentation from last week’s Rittman-Mead BI Forum in Atlanta.  Incidentally it happens to be the same topic (similar slides) to the KPI Partners webinar from April and what I gave in Denver at Collaborate also in April.  The webinar has been recorded, so you can hear my commentary and the QA session afterwards.  If you get the chance, I’ll be doing the same preso at Kscope in New Orelans next month.  Enjoy!

2013_24_04_PerformanceTuningOraclesBIApplications

OBI’s Nested Aggregation > Ralph Kimball

Ok so I put a provocative title on this post for a reason.  This post will explore why some of Kimball’s concepts may be out dated when newer query generation or database capabilities are taken into account.  Specifically, I’m going to discuss the OBI concept of Nested Aggregation, also known as Dimension Based Aggregation, in detail.  Using this OBI Modeling technique you can relax a bit on one of the Kimball Dimensional Modeling rules.  I’ll show how it works in a simplified manner, plus identify some things to be careful with and how it does not alleviate you from doing real modeling work.

Keep It Simple Stupid

I’ve been spending a lot of time recently working on performance tuning projects.  Sometimes the BI apps are slow, sometimes it’s custom, sometimes it’s a mix.  I’ve gotten the chance to see what works in both Oracle and SQL Server.

My conclusion about both of these databases is that they are like a cat or dog that gets fooled when you play hide the ball; they aren’t very smart sometimes.  The only way you can really truly ensure database engines, even modern advanced ones, do things the right way to is to make it as simple and easy for them to understand as possible.  I guess this is nothing new; the KISS principle comes from Kelly Johnson of The Lockheed Skunk Works, the guys who created the SR-71 Blackbird spy plane.  I think he knew a few things about complexity in systems and how they tend to break or become difficult to maintain.  (BTW more on that topic as it pertains to OBI and the BI Apps at a later date.)

As I’ve been looking at performance tuning many reports and queries over these days, I find that a lot of time is spent trying to get the database to do the smart thing.  Too much time in fact.  Usually this is due to some small piece of non-simple SQL that causes the problem.  In more unusual cases I’ve seen something on one table completely break down the query plan, even something that should be trivial and very innocuous.

<Vent>For example, not being able to use an index on a table with 2,000 records should not radically alter the query plan, but in fact it will do that on you.  After you spend hours upon hours with it, after you’ve called up the DBA for help to dig into the extreme nitty-gritty details of the query plan, you make a change it works for that one query but not any others.  Then you decide to write this article because you’ve spend 10 hours on something so minuscule that in the end doesn’t even work consistently.  If only the query had been clean to begin with…</Vent>

In this brief article I’m just going to lay out a few things to consider to help make you system simpler for the database engine to understand and therefore do a better, faster job in answering a query.

Build a Performance Layer to Make the BI Apps Fly

The BI Apps from Oracle present customers with a nice head start to getting their BI environment up and running.  Financial Analytics, HR Analytics, Supply Chain Analytics, etc. all come pre built with much of the code and reports you’ll need to build to support your business.  But for many customers they just are too slow for their user community while running dashboards, reports and ad-hoc queries.  In an era where an internet search engine can give you what you want in one second, reports running for a minute or more are just not acceptable.

In this post I’ll discuss some of the inherent performance limitations in the BI Apps and what you should do about it.  Note the vast majority of customers really don’t have a performance problem with their system, but you can always deliver reporting content faster.  If you are running at 15 seconds per page, wouldn’t 5 seconds be that much better?  The performance problem really lies with some large customers with larger data volumes.  It is here where the BI Apps design can be enhanced with more performance in mind.

I’ve written about OBI performance a few times in the past, and I’m sure there will be more to come.  As a refresher, here are a few other posts to take a look at:

• Achieving Good Performance with OBIEE
• OBI Performance Preso
• Performance Tuning Financial Analytics
• Stitch Joins

Degenerate Dimensions and Aggregates

An interesting topic came up recently on my latest project that I think is quite informative on how OBI works under the hood.  It involves how to model degenerate dimensional fields into OBI when there are aggregates present.  Individually each of these two topics are relatively well know with solutions for how to handle them, but when combined they can get pretty tricky.  This is especially true when you are using the BI Apps, which to a large degree still rely on the old method of manually mapping Time Series (Ago & ToDate) metrics (as opposed to the OBI 10.x + technique of Time Series functions) which require an explosion of Logical Table Sources (LTSs).

The core of this post concerns what do you do when some of your aggregates don’t have all of the degenerate fields that your base fact has?  What do you need to do to model them properly?  In the explanation I will shed a bit more light on how OBI “thinks” so that you too can model things properly the first time.

Performance Tuning and Financial Analytics

In the past I’ve written and presented on OBI performance from the ‘before perspective’: before you begin development, what are the things you should plan on and how should you implement them.  This paper however is with the ‘after perspective’; what to do if you are stuck with a performance problem that you need to solve quickly.  It will use the Financial Analytics (All versions) application from Oracle’s BI Applications (aka the BI Apps) to walk through a plan of attack and demonstrate specific changes using the Oracle Database 10x.  Thus, this paper has two purposes:

1. Specifically to document actual changes for Financial Analytics
2. Generally to walk through a performance tuning effort that you yourself may undertake

Note: You do not need to be working on Financial Analytics or even the BI Apps for the concepts in this article to apply.  It merely uses Financial Analytics as its example, and where appropriate I will explain the model.

I’m going to do something a bit different with this article in that I will tell the story of a recent performance tuning project for a client.  A previous integrator had delivered Financial Analytics and it was up and running in a production environment, but the performance was terrible.  Many queries were over 8 minutes.  We were asked to tune the GL Transactions star, but the lessons learned here will work for all modules of Financial Analytics, regardless of version.  In fact, implementing them for only one star actually boosted the performance of the other Financial Analytics Subject Areas.

Webinar on Performance, June 7th, 1pm EST

I’ll be doing a webinar called Planning for High Performance OBI.  I’ve given this preso a few times before, but it’ll be good to do it on the web for a change.  I discuss some of the basics you should have when beginning your designs.  It is not a performance tuning workshop, but rather what to do first to get it done properly the first time.  Register through KPI Partners website.

Approaches to Building BI Applications and Data Warehouses

Original Post date 2/27/09

I’d like to comment a bit on the differences between BI and Data Warehousing as I believe they are commonly confused and used interchangeably.  Having just come from a project which mixed the two together with poor results, I think some discussion on the topic is a good idea and one that is fresh in my head.  This is a long post, so go get some coffee (or Red Bull) first.

Before I get into this, I fully understand and accept that there are varied opinions about this topic.  I am not going to propose a one-size-fits-all philosophy.  There also may be some technical features out there which some may claim make my comments obsolete.  I am going to focus on the purpose of the two and how that drives how you build a BI system.  If you are hoping I’ll get into a Kimball vs. Inmon data warehouse architecture or modeling discussion you will be disappointed.  I recommend you read their books and articles for the details of their solutions.

I am going to approach this by defining two very distinct items at different ends of a spectrum, and then discuss how they can and cannot be merged into the same thing.  The point is to contrast the two needs so conflicts become apparent.

Modeling Header & Details

Originally posted 3/16/2007

A common question I hear is about how to model & configure a Header-Detail scenario.  By saying header-details, we are referring to a transaction that is split over more than one granularity.  This is common modeling scenario such as Order & Order Line Item, Invoice & Invoice Detail, Agreement & Agreement Line Item, etc.