X

Welcome to All Things Warehouse Builder

OWB 11gR2 – Degenerate Dimensions

David Allan
Architect

Ever wondered how to build degenerate dimensions in OWB and get the benefits of slowly changing dimensions and cube loading? Now its possible through some changes in 11gR2 to make the dimension and cube loading much more flexible. This will let you get the benefits of OWB's surrogate key handling and slowly changing dimension reference when loading the fact table and need degenerate dimensions (see Ralph Kimball's degenerate dimensions design tip).

Here we will see how to use the cube operator to load slowly changing, regular and degenerate dimensions. The cube and cube operator can now work with dimensions which have no surrogate key as well as dimensions with surrogates, so you can get the benefit of the cube loading and incorporate the degenerate dimension loading. What you need to do is create a dimension in OWB that is purely used for ETL metadata;

  • the dimension itself is never deployed (its table is, but has not data)
  • it has no surrogate keys
  • has a single level with a business attribute the degenerate dimension data and a dummy attribute, say description just to pass the OWB validation.

When this degenerate dimension is added into a cube, you will need to configure the fact table created and set the 'Deployable' flag to FALSE for the foreign key generated to the degenerate dimension table. The degenerate dimension reference will then be in the cube operator and used when matching.

Create the degenerate dimension using the regular wizard.

degenerate1

Delete the Surrogate ID attribute, this is not needed.

degenerate2

Define a level name for the dimension member (any name).

degenerate3

After the wizard has completed, in the editor delete the hierarchy STANDARD that was automatically generated, there is only a single level, no need for a hierarchy and this shouldn't really be created.

degenerate4

Deploy the implementing table DD_ORDERNUMBER_TAB, this needs to be deployed but with no data (the mapping here will do a left outer join of the source data with the empty degenerate dimension table). Now, go ahead and build your cube, use the regular TIMES dimension for example and your degenerate dimension DD_ORDERNUMBER, can add in SCD dimensions etc.

degenerate5

Configure the fact table created and set Deployable to false, so the foreign key does not get generated.

degenerate6

Can now use the cube in a mapping and load data into the fact table via the cube operator, this will look after surrogate lookups and slowly changing dimension references.
degenerate7

 

If you generate the SQL you will see the ON clause for matching includes the columns representing the degenerate dimension columns.

degenerate8

Here we have seen how this use case for loading fact tables using degenerate dimensions becomes a whole lot simpler using OWB 11gR2. I'm sure there are other use cases where using this mix of dimensions with surrogate and regular identifiers is useful, Fact tables partitioned by date columns is another classic example that this will greatly help and make the cube operator much more useful. Good to hear any comments.

Join the discussion

Comments ( 16 )
  • David Allan Monday, May 3, 2010
    Could be vastly simplified and improved by not doing the join on the empty degenerate stub table but just pass on through to fact table.
    Cheers
    David
  • Stewart Bryson Wednesday, May 5, 2010
    I don't typically use the Dimension and Cube operators... mostly due to issues that have existed with them in the past. I know I should re-evaluate with the newest version.
    With that slight disclaimer, I'm wondering... why can't I just cheat the system and load a degenerate dimension as if it were a measure? Is that what your comment about "vastly simplified" is speaking too?
    Thanks as usual.
  • David Allan Wednesday, May 5, 2010
    Hi Stewart
    If you used the measure approach the degenerate dimension references would not be included in the matching clause - so updates would be problematic. Vastly simpler, meaning if the update mode was a problem then you'd resort to using the table operators - for big facts 'vastly' (cough, cough, been talking with too many PMs through the years) simplifies the mapping.
    Cheers
    David
  • David Allan Wednesday, May 5, 2010
    Hi Stewart
    The lookup operator also had a significant facelift in 11gR2, so can get scalar subquery syntax generated amongst other stuff.
    Cheers
    David
  • Stewart Bryson Wednesday, May 5, 2010
    I rarely update facts... but excellent point. Thanks.
  • Maren Eschermann Thursday, May 6, 2010
    I am a great fan of the warehouse builder dimension and cube operators (since their introduction in 10g). With 11gR2 these operators seem to be even more flexible, great!
  • Scott Friday, May 7, 2010
    I'm wondering if I can use similar functionality for date dimensions. I HATE having date dimensions with a surrogate key - like the meaning of 01-JAN-2009 is ever going to change.
    It seems like I can use the above method to create a date dimension with a real date field as key, not a bogus surrogate. And then include the date dimension in a cube operator. This would vastly simplify date partitioning fact tables as well.
    Just a thought, need to test...
    Thx,
    Scott
  • David Allan Friday, May 7, 2010
    Hi Scott
    Spot on, that's what now happens with the time dimension created in 11gR2, so you could just partition the fact table using date based partitioning scheme. The time dimension in 11gR2 no longer has surrogate keys, just date based business keys and that's whats exposed through the cube operator - so that's why you can leverage this for other best practices such as degenerate dims.
    Cheers
    David
  • jeff b Wednesday, November 17, 2010
    3 dimensions fail to deploy RTC-5325
  • David Allan Wednesday, November 17, 2010
    Have you been able to define and generate any degenerate dimensions? Are you using 11gR2? Can you describe what you have done in a little more detail?
    Cheers
    David
  • guest Wednesday, February 1, 2012

    Hi,

    I want to desgin a degenetate a dimesion for my cube transaction.Is it possible to send me an example or a mdl to design it.

    Regards,

    Sahar


  • guest Wednesday, February 1, 2012

    Hi,

    Thanks for link but I need a mdl to show me the create of degenerate dimesion step by step.Is it possible to send me.


  • David Wednesday, February 1, 2012

    The post illustrates how you construct the degenerate dimension and how it needs to be configure.


  • guest Sunday, February 5, 2012

    Hi,

    I have create a DIM_DD_LOAN with two attribue loan_no_business with idenitifier business and a description then I deploy the table related to this dim is called DIL_DD_LOAN_TAB but I got this error

    ORA-37162: OLAP error

    XOQ-02102: cannot find object "BANK_STG.DIM_DD_LOAN"

    XOQ-02106: invalid property "Dimension" with value "BANK_STG.DIM_DD_LOAN" for object "BANK_STG.CUBE_PAID_LOAN.DIM_DD_LOAN" in XML document

    XOQ-02106: invalid property "ConsistentSolve" with value "SOLVE ( SUM OVER DIM_BANKS HIERARCHIES (STANDARD), SUM OVER DIM_BRANCHES HIERARCHIES (STANDARD), SUM OVER DIM_CUSTOMERS HIERARCHIES (STANDARD), SUM OVER DIM_REQUESTS HIERARCHIES (STANDARD), SUM OVER DIM_CURRENCIES HIERARCHIES (STANDARD), SUM OVER DIM_BONDS HIERARCHIES (STANDARD), SUM OVER DIM_COLLATERALLS HIERARCHIES (STANDARD), SUM OVER DIM_CONTRACTS HIERARCHIES (STANDARD), SUM OVER DIM_OWNERSHIPS HIERARCHIES (STANDARD), SUM OVER DIM_SEGMENTS HIERARCHIES (STANDARD), SUM OVER DIM_USAGE_CODES HIERARCHIES (STANDARD), SUM OVER DIM_LOAN_DIVISIONS HIERARCHIES (STANDARD), SUM OVER DIM_LOAN_PURPOSES HIERARCHIES (STANDARD), SUM OVER DIM_NOMINALS HIERARCHIES (STANDARD), SUM OVER DIM_LOCATIONS HIERARCHIES (STANDARD), SUM OVER DIM_DATES HIERARCHIES (STANDARD), SUM OVER DIM_DD_LOAN)" for object "BANK_STG.CUBE_PAID_LOAN" in XML document

    XOQ-02005: The Dimension "BANK_STG.DIM_DD_LOAN" referenced from object "BANK_STG.CUBE_PAID_LOAN" is not found.

    XOQ-02100: cannot parse server XML string

    ORA-06512: at "SYS.DBMS_CUBE", line 433

    ORA-06512: at "SYS.DBMS_CUBE", line 465

    ORA-06512: at "SYS.DBMS_CUBE", line 523

    ORA-06512: at "SYS.DBMS_CUBE", line 486

    ORA-06512: at "SYS.DBMS_CUBE", line 475

    ORA-06512: at "BANK_STG.OWB$XMLCLOB_TAT_BANK_STG_DW", line 513

    ORA-06512: at line 3

    can you help me??

    REgards,

    Sahar


  • guest Sunday, February 5, 2012

    Hi,

    I have create a cube in model rolap with a dimesion degenerete and I create the cube.But I want to have a dimesion and cube with type rolap with mv but I got the error.gor this type what should I do??


  • David Friday, February 10, 2012

    The degenerate dimension best practice I mention in the blog will not work for cube MVs, the cube MV will expect a dimension defined in the AW for any dimension defined in the cube. So if you followed the blog post to the work, you did not deploy the dimension so when the cube is deployed it is looking for the dimension object (which does not exist).


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.