This discussion is archived
1 Reply Latest reply: Jan 16, 2013 4:59 AM by DavidGreenfield RSS

Populate a junk dimension with 13 columns

934896 Newbie
Currently Being Moderated
Hi guys, I am new to datawarehousing and I have this problem

I have a table named comb, this has 3 columns XYand Z. These columns can have either Y or N. So I want to have distinct records with Y and N populated. I know the number of rows are 2 raised to the power 3. that would be 2*2*2 = 8.

Manually I can get this as

COLX COLY COLZ
N N N
N Y N
N N Y
Y Y N
Y N Y
N Y Y
Y Y Y

Those were the 8 distinct rows.
Now if I have 4 columns it would be 2 raised to the power 4, ie 2*2*2*2 = 16. I have 13 columns and these should have distinct values with either Y or N. the number of rows will be 8192. Can anyone suggest how I could write a program to insert those 8192 rows( a Y or N in 13 columns and each row should be distinct.

I will appreciate all the help.

Thanks
  • 1. Re: Populate a junk dimension with 13 columns
    DavidGreenfield Expert
    Currently Being Moderated
    The simplest thing would be to insert from a SELECT statement. Here is one that should give you want you want.
    SELECT col13, col12, col11, col10, col9, col8, col7, col6, col5, col4, col3, col2, col1
    FROM DUAL
    MODEL
      DIMENSION BY (0 d)
      MEASURES (
        CAST(NULL AS VARCHAR2(1)) col1,
        CAST(NULL AS VARCHAR2(1)) col2,
        CAST(NULL AS VARCHAR2(1)) col3,
        CAST(NULL AS VARCHAR2(1)) col4,
        CAST(NULL AS VARCHAR2(1)) col5,
        CAST(NULL AS VARCHAR2(1)) col6,
        CAST(NULL AS VARCHAR2(1)) col7,
        CAST(NULL AS VARCHAR2(1)) col8,
        CAST(NULL AS VARCHAR2(1)) col9,
        CAST(NULL AS VARCHAR2(1)) col10,
        CAST(NULL AS VARCHAR2(1)) col11,
        CAST(NULL AS VARCHAR2(1)) col12,
        CAST(NULL AS VARCHAR2(1)) col13)
      RULES UPSERT
      (
        col1[FOR d FROM 0 TO POWER(2, 13) - 1 INCREMENT 1] 
                  = DECODE(BITAND(cv(d), POWER(2, 0)), 0, 'N', 'Y'),
        col2[any] = DECODE(BITAND(cv(d), POWER(2, 1)), 0, 'N', 'Y'),
        col3[any] = DECODE(BITAND(cv(d), POWER(2, 2)), 0, 'N', 'Y'),
        col4[any] = DECODE(BITAND(cv(d), POWER(2, 3)), 0, 'N', 'Y'),
        col5[any] = DECODE(BITAND(cv(d), POWER(2, 4)), 0, 'N', 'Y'),
        col6[any] = DECODE(BITAND(cv(d), POWER(2, 5)), 0, 'N', 'Y'),
        col7[any] = DECODE(BITAND(cv(d), POWER(2, 6)), 0, 'N', 'Y'),
        col8[any] = DECODE(BITAND(cv(d), POWER(2, 7)), 0, 'N', 'Y'),
        col9[any] = DECODE(BITAND(cv(d), POWER(2, 8)), 0, 'N', 'Y'),
        col10[any] = DECODE(BITAND(cv(d), POWER(2, 9)), 0, 'N', 'Y'),
        col11[any] = DECODE(BITAND(cv(d), POWER(2, 10)), 0, 'N', 'Y'),
        col12[any] = DECODE(BITAND(cv(d), POWER(2, 11)), 0, 'N', 'Y'),
        col13[any] = DECODE(BITAND(cv(d), POWER(2, 12)), 0, 'N', 'Y')
      )
    The select statement produces output like this:
    C C C C C C C C C C C C C
    - - - - - - - - - - - - -
    N N N N N N N N N N N N N
    N N N N N N N N N N N N Y
    N N N N N N N N N N N Y N
    N N N N N N N N N N N Y Y
    ...
    Y Y Y Y Y Y Y Y Y Y N Y Y
    Y Y Y Y Y Y Y Y Y Y Y N N
    Y Y Y Y Y Y Y Y Y Y Y N Y
    Y Y Y Y Y Y Y Y Y Y Y Y N
    Y Y Y Y Y Y Y Y Y Y Y Y Y
    
    8192 rows selected.
    
    Elapsed: 00:00:00.53

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points