OWB 11gR2 – Degenerate Dimensions

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.

Comments:

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

Posted by David Allan on May 03, 2010 at 02:17 PM PDT #

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.

Posted by Stewart Bryson on May 05, 2010 at 12:24 AM PDT #

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

Posted by David Allan on May 05, 2010 at 12:47 AM PDT #

Hi Stewart The lookup operator also had a significant facelift in 11gR2, so can get scalar subquery syntax generated amongst other stuff. Cheers David

Posted by David Allan on May 05, 2010 at 12:53 AM PDT #

I rarely update facts... but excellent point. Thanks.

Posted by Stewart Bryson on May 05, 2010 at 02:03 AM PDT #

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!

Posted by Maren Eschermann on May 06, 2010 at 03:31 AM PDT #

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

Posted by Scott on May 07, 2010 at 02:23 AM PDT #

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

Posted by David Allan on May 07, 2010 at 03:23 AM PDT #

3 dimensions fail to deploy RTC-5325

Posted by jeff b on November 16, 2010 at 11:44 PM PST #

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

Posted by David Allan on November 17, 2010 at 12:18 AM PST #

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

Posted by guest on February 01, 2012 at 08:47 AM PST #

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.

Posted by guest on February 01, 2012 at 08:54 AM PST #

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

Posted by David on February 01, 2012 at 09:03 AM PST #

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

Posted by guest on February 05, 2012 at 05:53 AM PST #

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??

Posted by guest on February 05, 2012 at 07:21 AM PST #

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).

Posted by David on February 09, 2012 at 05:18 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today