OBIEE Data Modeling Tips #1: Integrating 1-1 and 1-Many Source Tables
June 19th, 2007 by Mark Rittman
Here’s a few data integration techniques that you can use with Oracle BI Server and the Oracle BI Administration tool. In this scenario, we’ve got a physical data source that’s a typical normalized transactional schema; the orders data that will go into our fact table is split into an order header record, and an items record, whilst the customer information is split into a customer table, an addresses table, and a customer address intersection table. What we’re looking to do is to build, in the logical and presentation layer, a simple three table star schema containing an orders fact, and customer and product dimensions.
The first task is building the fact table from the order header and items source tables. To do this, you first import the items physical table into the logical layer, as this contains records for each item that makes up the order, which is the level of granularity that we want the fact table to take. Then, I import the products table into the logical model, and join it to the items table using the common Product ID column, giving us this schema layout:
… and this logical model
Now I want to add the information in the Order header table to the main, Items, fact table. To do this I add the Orders table to the model, and join it, just like a dimension table, to the Items fact table, as there’s a one (order header) to many (order items) relationship between the two tables, like this:
…giving us a logical model that looks like this:
Now although the order header information (customer ID, order date and so on) can be accessed from the Order header table, just like a dimension lookup, ideally I want to put all the order information into the one fact table and dispense with this pseudo-dimension table, at least from a presentation perspective. Therefore, I create new logical columns in the fact table that are just copies of the corresponding tables from Orders, so that they’re all in one place and I can remove the Orders table from the presentation layer. The logical, derived columns look like this:
…and when viewed as part of the logical mode, they are shown as formulas (derived columns). This way, I don’t need to include the additional Order details table in the presentation layer, I can just use the more granular Items table as my sole fact.
Then I add the Customers table to the logical model, and then join it to the Orders table using a Complex Join, using the default settings, like this:
Now, I need to add the customer address information in. Now in the particular case of my data, each customer only has one address, so there’ll be at most one entry in the intersection table for each combination of customer ID and address ID. As there’s therefore a one-to-one relationship between customer, customer address combination and address, I can therefore add the address data by first dragging and dropping the Customer ID from the Customer Address intersection table on top of the existing Customer ID column in the Customer table, like this, to create a join between the tables:
Then I add the Address ID from the Intersection table to the Logical Customer table; note how this table now has two sources, the physical Customer table and the Physical Customer Address Intersection table, just like my previous example last year where I integrated Oracle and Excel data into the same fact table.
We can copy the column from the Address Intersection table into the Customer logical table, and create a second table source, because there’s a strict one-to-one relationship between these tables and they join on a specified column, so the BI server can go to either or both tables to get the required column. Earlier on, where we had a one-to-many relationship between products and items, and orders and items, we join the tables instead and create a derived logical column to obtain the “dimension” attributes we need.
Then I do the same thing again with the physical Address table, joining it on the newly-imported Address ID logical column and then adding the rest of the address data as additional columns. See now how the logical Customer table has all the address details as well, and it’s got three physical data sources, all joined.
Finally, I add just the Items, Customer and Products table to the physical layer, to keep the user table selection simple, like this:
and then go and load up Answers and create some simple reports that use data from all of the tables, like this:
Anyway, that’s fairly basic stuff but it’s useful to know how to integrate OLTP-style data models into simple stars, and how to incorporate data from tables with one-to-one and one-to-many relationships into the logical model. I’m sure there’s other ways of doing this (BIEE seems to have multiple ways to do common things), as usual feedback and other techniques are appreciated. Tomorrow, to make things a bit more interesting, I’ll be covering something called “Fragmentation”.
June 19th, 2007 at 7:49 am
Hello Mark and Jon,
I am a big fan of the blog and I think it’s a great idea to provide OBIEE tips & techniques for people new to the product. With regards to today’s example, I think it’s worth mentioning that logical foreign keys are very much against best practice (check Admin Tool help under Logical Table dialog box, foreign key) – all FK joins should be defined in the physical layer and complex joins should be used in the logical layer.
Best regards,
Martin
June 19th, 2007 at 9:42 am
As I understand it, it’s considered bad practice to expose foreign key columns in a Logical Fact table, (although I can’t see this mentioned in the documentation). And, it’s unlikely (except for testing/admin purposes) that you would want to expose the FKs to end users in the Presentation layer anyway; you’d expect them to go to the corresponding Dimension for attributes.
Additionally, only aggregated measures should appear as columns in the Logical Fact table.
Could you have created the ‘Items’ presentation table with the columns you required without having the corresponding multi LTS Logical Fact Table? i.e. have a ‘complex’ presentation table that contains logical columns from more than one Logical Table?
That is, a presentation table could in fact be a ‘pre-formed report’ aimed at different user profiles. Of course, modelling it in the logical layer arguably makes it more re-usable across presentation layers..
“(BIEE seems to have multiple ways to do common things)”
- so true :) it’s versatile to say the least.
Chris.
June 19th, 2007 at 10:36 am
Hi Martin
Thanks for the feedback. Part of the reason for putting the posting up was to float a few ideas, I’d also read that Foreign Keys in the logical layer were bad practice and were only in BIEE for backward compatability, but I couldn’t see *why* – do you know the reason for this? Also, what is the difference between complex joins and foreign keys? To me, it seems they do the same thing, except (a) complex joins don’t let you pick the key columns, and (b) they give you choice over 0:1, 1:1, 1:n and so on. Any advice on this would be appreciated.
regards, Mark
June 19th, 2007 at 10:44 am
Chris, thanks for the feedback. As I said to Martin, I’m trying to come up with some best practices and preferred approaches, part of getting the posting out was to garner some feedback, thanks for this.
Good point about FKs not being preferred nowadays, but as I asked Martin – do you know why? Understand the point about FKs not being in the presentation layer – of course in reality I’d remove the ID columns from the presentation tables (is this what you mean?) and also tidy them up etc.
WRT the non-additive measures in the fact table – again, good point; some of the non-additives (status, for example) I might keep in as a “degenerate dimension”. others, like PARTNUM were there from the source data, as you say these should be removed and kept in their respective dimension, I’ll do this for the example I work on today.
Interesting bit about creating a more complex presentation table and keeping the logical tables more normalized. I hadn’t considered that, I need to read up on creating “complex” presentation tables, my first instinct was that they had to reflect more or less one-to-one the logical tables they were derived from, if as you say you can further combine them, that may make sense.
Good feedback from both you and Martin, thanks for this, it’ll all help to make the final solution more robust. Cheers for this.
Mark
June 19th, 2007 at 1:49 pm
Mark, regarding complex and FK joins in the logical layer. Consider logical fact and dimension tables with multiple logical table sources at different levels (e.g. detail level and aggregate); in such a case the join path may be completely different depending on which LTS is used and it does not make sense to force the OBIEE server to use a particular logical column to execute the join. By specifying a logical complex join you are giving the server all the information it needs, namely that a relationship exists between the two logical tables (note that you also need to define dimension hierarchies and set the correct levels on the logical table sources). The server can then make a decision which logical table sources to use and refer to the physical layer to formulate the join expression. It also means you don’t have to define the same FK relationship twice (physical and logical layers) so makes maintenance easier.
On a different topic, another thing I noticed in your example is that in the Customer logical table you use three separate logical table sources (Customer, Customer_Address, Address). Whilst it’s not breaking anything, my personal preference is that when you have related tables at the same level, it’s better to stick them as mapped sources within a single LTS, and use multiple logical table sources when you expect the server to choose one among them depending on the user request (e.g. detail and aggregate fact tables).
Best regards,
Martin
June 19th, 2007 at 7:50 pm
[...] Comments Martin Hammer on OBIEE Data Modeling Tips #1: Integrating 1-1 and 1-Many Source TablesMark Rittman on OBIEE Data Modeling Tips #1: Integrating 1-1 and 1-Many Source TablesMark Rittman on [...]
September 16th, 2007 at 1:55 am
[...] months ago I wrote a blog post on pulling together an Oracle BI Suite Enterprise Edition business model from a normalized data [...]
January 21st, 2008 at 9:32 am
Martin, your comment from June 19, 2007, 1:49 pm is the most valuable text on the “multiple logical table sources” topic, I’ve ever read! It’s short, it’s clear and it contains some great ideas… Thanks
January 29th, 2008 at 3:08 pm
Just to let everyone know – I updated the article a while later, based a lot on the comments Martin added to the post. It’s at http://www.rittmanmead.com/2007/09/16/obiee-dimension-data-modeling-redux/
regards, Mark