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”.
Posts
