Oracle BI EE 10.1.3.4.1 – Modeling Degenerate dimensions – Fact attributes

January 20th, 2010 by

Degenerate dimensions are quite common when we do a Data Warehouse implementation. They are nothing but a set of dimensions that can be classified as Fact attributes and are hence maintained in the fact table itself instead of separate dimension tables. One of the key advantages of using Degenerate dimensions is the fact that there is no need for doing a separate Surrogate lookup while creating the ETL mappings. Generally degenerate dimensions do not alter the grain of a fact table and hence are maintained within the Fact table itself. In some cases, they act as a reference between the data warehouse and the transactional system.

For example, Order Numbers, Invoice Number, Transaction Dates, Sale Dates, Credit-Debit Indicator etc can all be classified as degenerate dimensions. The interesting aspect of degenerate dimensions is, they can get a little bit tricky while modeling in BI EE. Let me take this through an example. Lets assume that we have 2 fact tables SALES and COST (the SH schema in Oracle). Sales fact table has a degenerate dimension called SALE_ID(which acts as a reference to a SALE made in the transactional system). How do we model this? I will cover the method that is normally used and describe the pros & cons of this approach. After that i will cover 2 separate methods that can prove useful while modeling degenerate dimensions.

The traditional method of modeling degenerate dimensions is to include them in the Logical Fact table itself as shown below without any aggregation.

image

From my experience modeling degenerate dimensions this way can be useful only if you have the following requirements

1. Have just a single fact table and all your reports go against only those measures in that single fact table.
2. Requirement to create logical calculations (not db calculations) out of these degenerate dimensions. For example, FILTER calculations on a measure require the logical column to be present.

But even when you have the requirements listed above, care must be taken while exposing this attribute to the presentation layer. One of the major drawbacks of the above technique is the fact that when we model it this way, BI Server cannot enforce conforming dimension joins.

Non-Conforming Degenerate Dimensions:

For example, lets create a report as shown below.

image

As you see, when we bring in the degenerate dimension in to the report, the UNIT_COST measure which is from the other fact table becomes NULL. One of the main reasons why this happens is, SALE_ID does not have an aggregation associated with it. For conforming dimension based joins to happen, we need dimensional attributes that can be pushed inside the GROUP BY clause of a SQL query. When we do not have an aggregate specified for a column belonging to a fact table, BI Server does not know where to push this column in the SQL query. Hence it basically nulls out the UNIT_COST measure thereby negating the conforming dimension based join. In this case, degenerate dimension SALE_ID does not conform to the COST fact. How do we resolve this?

To resolve this, there are some fundamental questions that needs to be answered. The basic question will be, when we expose degenerate dimensions to end users, we need to know what needs to happen to the measures from other fact tables. In the above example, UNIT_COST has no relation whatsoever with the degenerate dimension. In hindsight, the null in the above report seems correct as it does not have relation to the SALE_ID. But since we have modeled it as a fact attribute, we would expect the UNIT_COST measure to be calculated for each channel and then repeat itself (like a Level Based measure) for every SALE_ID as shown below.

image

This is done by modeling the degenerate dimension as a separate dimension in BI EE as shown below. Remember, from a BI EE perspective one physical table can act as dimension or fact or both depending on how you model it. The model is shown below

image

The new dimension Sale that we modeled is still not related to the COST measures. To enable that we need to assign the COST measures to the total level of the Sale dimension.

image

This will enable reporting on degenerate dimensions even on non-conforming facts. This technique will even more be useful when you have the same degenerate dimension in 2 different fact tables. Lets look at that next.

Conforming Degenerate Dimensions:

Lets assume that the fact tables FACT_COST and FACT_SALES have a degenerate dimension called BOUGHT_BY & PAID_BY respectively. Both these attributes can have only 2 values (Credit-Card or Cash). If we model them as degenerate dimensions using the traditional method, the biggest issue we will face will be the inability to use them for reporting across the fact tables. If we model it as a dimension, we can effectively use a single column for 2 differing perspectives depending on how we look at the data.

We start with basically creating a new dimension in the BMM called Payment as shown below.

image

The payment logical column will be mapped to the BOUGHT_BY degenerate dimension column of the COSTS table and PAID_BY degenerate dimension column of the SALES table.

image

image

In the reporting layer, one can use this single attribute to report on 2 different fact tables even though the attribute maps to 2 different columns in the physical source.

image

Treating degenerate dimensions as normal dimensions in BI EE can prove more beneficial than using it as a fact attribute. Especially when you have users using BI EE as an ad-hoc analysis tool(when you do not want your model to generate wrong numbers/errors).

Comments

  1. Stewart Bryson Says:

    Excellent treatment Venkat. I can recall you walking me through this same explanation at least once :-) .

    The only part that I didn’t grasp completely on the first read is when you make the following point:

    “The new dimension Sale that we modeled is still not related to the COST measures. To enable that we need to assign the COST measures to the total level of the Sale dimension.”

    I’m not certain why this is required… I would think just creating a complex join between Sale and Cost and allowing for default aggregation would do the trick. Is there something more I’m missing?

  2. Venkatakrishnan J Says:

    @Stewart – A complex join in the BMM layer does not join the tables physically. A complex join signifies the fact that a logical dimension & a logical fact are related(we can report on them without any errors). But the physical join can happen only if the tables are joined physically in the physical layer. In the above example, SALE_ID is not related to Cost at all in the physical layer (or no direct join between SALES & COST fact tables). Hence to make sure that we can report on SALE_ID and cost measures together we assign the Sale dimension Total level to the cost measures.

  3. Vaibhav Yelmar Says:

    Nice article. But one quick question

    While mapping payment logical colume to BOUGHT_BY of COSTS table & PAID_BY of SALES table, an alias of COSTS & SALES tables are created or directly mapped to COSTS & SALES fact table ?

  4. Jessie Wu Says:

    If I understood it right and also based on my experience, you don’t need to create alias of fact tables. You can just directly map them to physical fact tables. That way, when OBIEE generates the sql, only one fact table will be used. If you create alias from fact table, two instances of the same fact tables will be joined together and impact performance in case of large fact tables.

  5. Pravin Rangnekar Says:

    I am trying to implement degenerate dimensions. I am using a similar data model. However, they are not working for measures using a display function called, Filter Using. There are about 300+ measures in ootb BI APPS logical table Fact – Fins – GL Balance that use this functions. Some of them are “AP Amount”, “AR Amount”, “Cost of Goods Sold”. Do they work for you?

  6. Pravin Rangnekar Says:

    I found the solution. You need to set the levels of the base columns like “Balance Global1 Amount” to point to the Total Level of the Degenerate Columns to make it work. They all work well for me. :-)

  7. Phil Reid Says:

    Hi,
    I’m using OBIEE for the first time and am really struggling with the treatment of degenerate dimensions. I have 16 years experience with Business Objects and have the task of replicating BO Universes as OBIEE repositories.

    I understand the theory behind your treatment of non-conforming degenerate dimensions, but in your example SALE_ID is shown initially as coming from the ‘Fact – Sales’ logical table, but then is shown as a dimension in the new ‘Sale’ table, along with it’s associated hierarchy (SalesDim). How do you force your new ‘Sale’ logical table to be treated as a dimension and not a fact when it is the same physical table as ‘Fact – Sales’, and is at the end of a string of 1:N joins?

    Many thanks…

  8. Peter Scott Says:

    @Phil
    As it was first modelled, we took all the columns from the physical SALES fact table (including the degenerate dimension) and put them in the Logical (or Business) representation of the Fact table.
    The next stage was to CREATE a new LOGICAL TABLE (SALE) and map the physical column SALE_ID to it and also remove the same column from the Logical FACT – finally, we create a dimension in the logical layer so that OBIEE can aggregate correctly.

    One of the big plus points with OBIEE is that as well as having multiple physical sources mapping to a SINGLE logical source we can also map a single physical source to multiple logical ones

  9. Phil Reid Says:

    Peter,

    Thanks for your response.

    My problem is that when I do the equivalent of creating the new LOGICAL TABLE (SALE) and mapping SALE_ID, it is treated as fact table because it is at the end of the 1:N chain. Because of this, I cannot define the dimension hierarchy. i.e. when I right click on it, I do not see the ‘Create Logical Dimension’ option. How do you get around this?

    This is the Business Objects situation that I am trying to replicate in OBIEE…

    Imagine that you have three tables: D1 joined to F1, and D1 joined to F2.
    D1 has conformed Dimensions, and F1 and F2 have Measures and degenerate (i.e. non-conformed Dimensions). The D1-F1 and D1-F2 joins are both 1:N.
    In Business Objects if you create a report that includes D1 conformed Dimensions and both Measures and degenerate Dimensions from F1 and F2, it will split the query into two unsynchronised queries, and show the results in two discrete blocks. i.e. Conformed Dimensions and columns from F1 in one block, and conformed Dimensions and columns from F2 in the other. In OBIEE it generates an error message which I cannot overcome.

    Can the Business Objects behaviour be replicated in OBIEE?

    Many thanks…

  10. Ryan Morland Says:

    hello.
    when building the degenerate dimension and there is more than one column, eg. sales_ID, sales_type.
    do all the the columns need to be included in the hierachy definition (SaleDim) at the detail level (Sale Detail)

    thanks
    Ryan

Website Design & Build: tymedia.co.uk