1 Reply Latest reply: Jan 16, 2013 6:59 AM by DavidGreenfield RSS

    Populate a junk dimension with 13 columns

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