This blog post is about OBIEE reporting. Specifically, it is about skipping the data warehouse and reporting from the transactional database instead. Oracle’s OBIEE, like most BI reporting tools, is designed to use star/snowflake schemas as the underlying structures to report from. Additionally, OBIEE’s metadata layer is very rich and extensively well thought out, allowing for a great deal of flexibility. Oracle’s metadata tool (Admin Tool) allows us to leverage this flexibility and features to bridge the gap between an OLTP and an OLAP model. I am not negating the need for a data warehouse, I am just wondering if all BI reporting projects merit one.
So the question to ask is: Is OBIEE up to the task?
Much of the reasoning for this topic arises from the combination of the following factors:
- Long development times for building a data warehouse.
- Sometimes typical short lifespan of the developed product.
- Excellent as usual blog post series from Rittman Mead. OK, this took 8 months to get published but, by all means, if you have the time, read that series instead of this post. No fluff at Rittman Mead’s Blog!
- Ever changing database architecture landscape. (Including never ending new ways of storing and crunching data.)
- The monumental amount of technical planning and provisioning needed to take a BI project to completion for a customer.
- This should be a fun exercise.
Our Database Schema
I am using Oracle’s Order Entry (OE) Schema in this post. This schema is generally available for download and it’s modeled in the typical transactional model. Sample data populate scripts are provided as well. It has been around for more than a decade thou and even Oracle ships more than one version concurrently depending of the database version installed. Personal, Standard and Enterprise Edition versions of Oracle Database currently ship with one version and Enterprise with Partition ships with a different version. All versions I have seen, however, are compatible with each other. I bet Oracle picks a subset of features to include in demos depending on database features installed.
Obtaining Our Schema
If you have access to your database server installation directory, the scripts for installing and populating the OE Schema would be here:
If you do not have access to the server where your database server is installed or if the version you installed does not have the scripts, you can search and find them online. I used the scripts I found at the following locations:
- http://download.oracle.com/otndocs/tech/ias/portal/files/RG/OBE/common/OBEConnection.htm In this second link, search for 8i version of the scripts.
Lastly, if you want to review this and the other demo schemas Oracle bundles with their database, get Oracle Data Modeler and go here:
Note – It is totally worth putting the time into this tool. It slices bread in 3 different locales for multiple database vendors.
I had to combine the above listed script sets in order to populate all the tables in the order entry schema. I used the scripts from the first link to create and populate most tables. I had to use scripts contained in the second link to populate the Warehouses, Inventories and Product Descriptions Tables. Another good sample schema (from Oracle demos) is the Sales History Schema (SH).
Anyhow, the challenges and steps described here can easily be done on a schema of your choosing.
Schema Rules Overview
The following rules define the transactional model by which we are bound in this exercise.
- One warehouse can have many inventories (quantity of product(s)).
- One product can be in many inventories.
- One product can have many descriptions (For text translations).
- One customer can have many orders.
- One order can have many items.
- One product can be in many order items.
Imagine the case where a product is priced different between first and second item bought (Get 50% off second pair at Skechers).
- One category can have many products.
Here we get a clear picture of the transactional model for our Order Entry Schema. The relationships defined in this illustration were picked up straight from the scripts Oracle provides. The schema has been left as is expecting necessary modeling to be done in metadata layer.
SQL Join Overview To Satisfy Rules
1: SELECT a.*, b.*, c.*, d.*, e.*, f.*, g.*, h.* 2: FROM OE.warehouses a
3: LEFT JOIN OE.inventories b ON a.warehouse_id = b.warehouse_id 4: INNER JOIN OE.product_information c on b.product_id = c.product_id
5: LEFT JOIN OE.categories d ON c.category_id = d.category_id 6: INNER JOIN OE.order_items e ON c.product_id = e.product_id
7: INNER JOIN OE.orders f ON e.order_id = f.order_id 8: INNER JOIN OE.customers g ON f.customer_id = g.customer_id
9: LEFT JOIN OE.product_descriptions h ON c.product_id = h.product_id;
Importing into Admin Tool, we can validate that the relationships enforced in the order entry schema are intact. Any modeling we do is based on foreign key relationships available. If any relationships are missing from the model (no referential integrity enforced), this would need to be fixed by declaring them in the metadata. This would ensure that OBIEE picks up the proper relationships for reporting.
Transactional Database in Admin Tool’s Physical Layer
This detailed view displays all the available columns for possible reporting.
After doing your aliases (or not) and ensuring all primary and foreign keys are declared, its time to move over to the Business Modeling and Mapping Layer (BMM).
For this order entry schema, I decided to design two distinct star schemas, one for Inventory and one for Ordering. From here on, design decisions would need to be made to satisfy the project being worked on. For this post, I just wanted to go thru the motions of creating a dimensional model on top of a transactional schema, hence the two star schemas devised.
The first Admin Tool feature to use is the ability to create logical tables from multiple physical tables as shown in the illustrations that follow.
Inventory Star Schema (BMM)
For Inventory Star Schema, I create a logical fact table from all the tables that previously validated (and defined) foreign keys allow. These relationships basically defined (logically) this table for our model. Notice that Product Description Table is not included in this logical table. If model required the fact able to hold the surrogate key for the translated text, it would have been necessary to declare that relationship before this step.
From here, modeling the dimension tables will follow the same, albeit simpler, pattern as described in following illustration.
Once all the desired dimensions are created n the BMM, we end up with our star schema.
Expanding all objects in schema shows all the elements available in reporting. This effectively maintains an OLTP to OLAP layer in the metadata for us. As we use these star schemas in reporting, the SQL generated by OBIEE will rely on these declarations to fetch from our relational system.
Ordering Star Schema (BMM)
The steps for the Ordering Star Schema are exactly the same. First we define our fact table. All the necessary dimensional tables stem from the keys we can include here. Again, the available keys are based on our primary/foreign key previously declared.
following the pattern used for Inventory, the end result is the Fact Ordering Schema (as Kimbal would have called) as displayed here.
It is important to note that, in order to enrich this schema, I unnecessarily modeled multiple dimensions that provide no extra value. This is nothing more than a design exercise for this post. Had this been a real project, it would’ve proved of little value to create dimensions for which we only have a key and key provides the only bit of information we have. For example, the State, Country and City Dimensions. In the background, OBIEE should just pick the key available in our fact table and not use them. For this post, however, I desire a rich model to explore how flexible OBIEE reporting is.
Having completed our modeling, the last step is to expose our schemas into their respective subject areas for reporting.
It would be interesting to do A/B testing between reporting against this transactional model versus a ‘proper’ dimensional model in the physical layer. Given identical hardware, it would be critical to define the point of diminishing returns in skipping the data warehouse all together. Then again, whatever the findings are, it would be worth a complete lab to establish what can be done one way or the other in transactional versus dimensional reporting in OBIEE. These tool capabilities, together with different storage technologies and even Oracle latest’s acquisitions should provide multiple reporting options to fit any case in the wild. Maybe I can explore some of these in future posts.
As always, thanks for reading!