You guessed it… more on transactional schemas. For a quick recap, here are the posts to date:

Using OBIEE against Transactional Schemas Part 1: Introduction

Using OBIEE against Transactional Schemas Part 2: Aliases

Using OBIEE against Transactional Schemas Part 3: Simple Dimensions and Facts

Combining Entities

In the last post on Simple Dimensions and Facts, we discussed logical facts and dimensions, factless fact tables, and fake count measures. In constructing the simple dimension in that post, we saw the collapsing of normalized relationships into a single conformed dimension table when building Dim – Customer. Another important entity for reporting in the Customer Tracking application is the Contact entity. At first glance, it might seem that we should simply build another dimension called Dim – Contact, and use analyses to combine our Customer and Contact dimensions along with our Activity fact table to analyze Customer and Contact behavior. When we look again at the data model for the Customer Tracking application below, Customer and Contact are clearly two separate entities in this application, and so this probably means that we should continue to report on them this way, and structure them as such in the OBIEE Business Model and Mapping layer. Right?

Is this the way that business users think about their data? Although it makes perfect sense in a normalized data model to have separate entities for Customer and Contact, the business user likely won’t see it this way, especially when it comes to defining reporting requirements. Contacts are clearly specific instances of a Customer at a lower level in the Customer-Contact hierarchy, and our end users will expect to be able to drill cleanly from Customer on a report down to the specific Contact that is associated with a particular activity. Although OBIEE supports building hierarchies across multiple logical dimension tables so we can provide the drill-down behavior mentioned here, the end user is still confronted with two entities when building the report. This is perhaps the most challenging part of reporting against transactional schemas: deciding not to settle for the model that the OLTP designers have given us. Instead of resigning to have both Dim – Customer and Dim – Contact in our logical BMM, we should attempt to build the appropriate dimensional rendering of this model: a single Dim – Contact dimension of which Customer is simply another element in the hierarchy.

The first step in this process is to rename Dim – Customer to Dim – Contact, as it’s always best to name dimension tables according to the lowest level in the hierarchy: the declared grain of the dimension table. Then I add the physical contact tables to the Dim – Contact logical table source (renaming that as well for clarity purposes), and map all the new Contact-specific attributes to the appropriate physical table.

I also do some renaming of the Customer dimension, and include Contact Detail as the new, lowest level in the hierarchy.

I consider this merging of entities into a single logical dimension table to be more profound than our original constructing of Dim – Customer in the Simple Fact and Dimension post. Although we combined multiple normalized tables to get our original Customer dimension, those tables represented labels and descriptors, such as Customer Category and Customer Status. They didn’t represent true entities, which I argue is the distinction that exists when combining Customer and Contact. This type of high-level distinction comes into play when we try to think about our source model in dimensional terms instead of transactional ones.

An important feature of OBIEE when considering whether to build logical table sources with increasing amounts of physical tables added to them is performance. It might seem that combining the Customer and Contact entities in this way might have a negative impact on performance, because of all the tables that have to be joined to return records for the Dim – Contact dimension. Perhaps keeping the two dimension tables separate would mean less work that the database has to do. What if our query only requires Contact or Customer attributes in the result set, but not both? Doesn’t the BI Server have to include all the joins in the logical table source — in this case six tables — every time this dimension is used in an analysis? The answer is no. The BI Server is very, very smart, and in many ways acts like a database optimizer when structuring the physical SQL for an analysis. In each Intelligent Request Generation, it is aware of the smallest number of physical tables required to produce the result set in the logical query, and only includes those tables in the physical SQL. For instance, a sample analysis that returns only Customer attributes from our new Dim – Contact logical dimension only includes three tables in the SQL issued against the database:

Building a Date Dimension

One consistent element of every BI system is the necessity to handle dates effectively. For this purpose, when building a conformed data warehouse, we always build a date dimension of some kind or another. Ralph Kimball describes the special place that a date (or time) dimension plays in the data warehouse. We may have to split the difference here with Kimball, as you will see later. Regardless, we usually don’t have a date dimension table in our transactional schema. If you recall from earlier posts in this series, the application code for transactional systems usually exists in API’s designed specifically for single record “gets” and “puts”. For this reason, it’s very easy to develop a series of API’s designed to insert or return specific date attributes, and rarely do these systems deal with date conceptually as a hierarchy. It’s simply not part of what they are designed for. It’s not just dimensional models that require date dimensions: OBIEE as a product requires them in particular situations. If we want to capitalize on the built-in feature of automatic drill-down, then we have to build a logical dimension table that contains our levels: year, to quarter, to month, to day, for example. Additionally, the time-series capabilities that OBIEE has also require that our date attributes be configured in a logical hierarchy. We have two options for producing the non-existent date dimension:

• We can build a date dimension in our transactional database, or in our foundation layer, and create aliases for this physical table in the OBIEE physical layer. We can then use these aliases in our BMM to form one or more role-playing dimension tables for our facts.
• We can use the functionality in the OBIEE business model to logically “construct” a date dimension using a DATE attribute from the source table for the logical fact, and then use calculated attributes to flesh out the remainder of the required level-specific attributes.

The first bullet point is an attractive option, especially if the reporting environment is a replicated version of the transactional system, or better-yet, a foundation layer (described in this post), where we are able to inject additional physical structures specifically for reporting purposes. The join between the date dimension and the source table is constructed using a CAST of DATETIME to DATE for Oracle databases, which will generate the necessary TRUNC command in the join syntax. Although I recommend using DATE datatypes as the surrogate key in date dimensions, it’s not required, as date dimensions usually have a DATE dataype in them somewhere, and this column can be used as the join criteria in the physical model. Don’t be tempted into using OBIEE’s federation capabilities to use a date dimension from another database for this purpose. Though it may seem like the ideal solution on the surface, keep in mind that date-based filters would not be pushed down to the reporting database in this scenario, as these filters would have to be applied logically in memory by the BI Server. This produces a performance nightmare, and inevitably, the production DBA will blame OBIEE for not applying date filters on it’s queries.

Though it’s tempting to dive into the first bullet point further (feel free to leave comments about this), it seems like a fairly standard approach for developers familiar with using OBIEE against traditional data warehouses. We will instead focus on the second bullet point: constructing a date dimension out of sheer nothingness. We start by building an empty dimension table called Dim – Activity Date and mapping only one single attribute from our source table containing the DATE column, in this case, EBA_CUST_CUST_ACTIVITY.ACTIVITY_DATE. This will be the only attribute that we map back to a source table; all the remaining attributes will be calculated measures in the BMM. The OBIEE SQL language contains a series of date conversion functions specifically designed to extract date elements from a DATE datatype. Any functions that OBIEE can’t extract on it’s own can be extracted using the EVALUATE function to push database-specific syntax transparently down to the underlying database engine. I prefer to use the logical option Derived from existing columns using an expression as opposed to Derived from physical mappings whenever possible because we can reuse the Dim – Activity Date dimension with other facts, and we will only have to map a single column in the logical table source: the DATE attribute.

If we choose to use physical mappings instead, we will have to map all the attributes each time we add an additional logical table source to the logical dimension table. When our date dimension is complete, we’ll have a complete logical date dimension with accompanying dimension (hierarchy) ready to be used for drill-down, time-series, etc.

When we are all done with the BMM mapping, we can see how the BI Server generates the physical SQL against an Oracle Database. As we saw earlier, the BI Server doesn’t need to join to the EBA_CUST_CUST_ACTIVITY more than once, even though it’s now part of the logical table source for three logical tables: Dim – Customer Activity, Dim – Activity Date and Fact – Customer Activity.

In the next post, we’ll describe Complex Fact Tables… and see some really, really cool stuff. I promise!

It seems like a different life when I posted the introduction to reporting against transactional schemas. Since that time, we’ve had some exciting new customers come aboard, which has required a lot of my time (and which I have been glad to provide). We’ve also had Kscope 12, where I was a member of the BI content committee, and where Rittman Mead was a sponsor, and our own Jordan Meyer won Best Speaker for the BI Stream. And, we’ve opened our brand new office in Atlanta, and have our first public training event at that location in progress. All together… we’ve been busy at Rittman Mead America. But a promise is a promise… so here goes Part 2.

The first thing we have to approach is the intelligent use of alias tables in OBIEE. There’s a best practice nugget out in the community that espouses “alias everything”. You’ve heard this, right? It’s not unlike the same advice I’ve been hearing in the database world now for going on two decades, that tells us to always use synonyms, or views, or provide some layer of abstraction. Abstraction I completely understand… but we already have two layers of abstraction in OBIEE without using a single alias. I think the argument goes something like this: “You have no idea when you’ll need to introduce a new way of accessing a particular table… or when it will need to play multiple roles… so do yourself a favor and alias everything right out of the gate.”

My response to this advice: maybe. The longer I’ve worked in IT in general and BI in particular, the more I treat any “absolute” commandment with suspension. When reporting against a transactional system… we have a very complex set of tables and joins. Where dimensional models are characteristic of sparse normalization, OLTP schemas are spilling over the brim with it. And if we choose to “alias everything”, as the good book says, then OBIEE’s ability to import foreign key relationships, and therefore, joins, is completely wasted. We will have to go through the process of re-applying all those joins that we just imported because we want to replace our entire physical layer with aliases. So whatever flexibility you gain by aliasing everything… remember that it comes with a price: the need to redefine every single join once again.

(Do you have a different opinion? Please let me know… I’d love to hear about it. Even you Venkat!)

Regardless of where you stand on the subject of mandatory aliasing… there will always be a need to use some aliasing when dealing with OLTP. To understand why, let’s first take a look at our entire model for the Customer Tracking application:

The first thing to notice is the relationship between EBA_CUST_CONTACTS and EBA_CUST_CUSTOMERS. Basically… this relationship is the heart of the application. Being able to add Customers as well as Contacts, and apply relationships between them (and perhaps sever them if necessary) is one of the key capabilities of any good CRM application. But when looking at this model, how would I write a query to display all the Contacts by Customer? I seem to have two options:

SQL> select count(*) from eba_cust_associations eca
2  join eba_cust_customers ecu on eca.customer_id = ecu.id
3  join eba_cust_contacts eco on eca.contact_id = eco.id;

COUNT(*)
----------
6

1 row selected.

Elapsed: 00:00:00.02
SQL> select count(*) from eba_cust_links ecl
2  join eba_cust_customers ecu on ecl.customer_id = ecu.id
3  join eba_cust_contacts eco on ecl.contact_id = eco.id;

COUNT(*)
----------
0

1 row selected.

Elapsed: 00:00:00.03
SQL>

To the human eye, it’s perfectly clear that the initial query is the correct one. It’s clear by looking at the table name EBA_CUST_ASSOCIATIONS that this table serves as the cross-reference table between Customers and Contacts. EBA_CUST_LINKS is actually a place for recording important URL’s, and they can be stored against either a Contact or a Customer. In this way, the links table is “reused”, and that is why it joins to both EBA_CUST_CUSTOMERS and EBA_CUST_CONTACTS.

But the BI Server has no way of knowing this. To the electronic eye, both EBA_CUST_ASSOCIATIONS and EBA_CUST_LINKS are attractive cross reference tables.

So we have to tell the BI Server how this model plugs together from an “analytics” perspective, and to do this, we’ll use aliases. Once I have created the aliases for the two different “roles” that the EBA_CUST_LINKS table plays, it is very clear to the BI Server which route to take between tables.

Up next in Part 3, I’ll talk about putting together logical dimension tables, and some of the design patterns to keep in mind. Also, when we discuss building logical fact tables (Part 4, perhaps?), we’ll discuss aliases again, and again… I’ll show you common situations where aliases are overused.