1 Reply Latest reply on Jan 16, 2013 12:59 PM by DavidGreenfield

    Populate a junk dimension with 13 columns

      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

      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.

        • 1. Re: Populate a junk dimension with 13 columns
          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
            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