Challenge on creating production Dimension, any thoughts?


  • Hi there:
      Here is the background:
       Order Line Item  contains the lowest level of details
      and each  Item contains Product Option and option value. 
      For example : For a Photobook Order Item
      It can have up to  10 product options as follows:
    Photo Book Cover Material
    Photo Book Size
    Photo Book Type
    Photo Book Min # Of Pages
    Photo Book Cover Colour
    Dust Jacket Allowed
    Photo book cover type Solid/Soft
    Photo Book Skin Cover look of MiniBook/PhotoCover
    Photo Book Max # Of Pages
    Photo Book Page Increment

    Here is the table orderItemOption that contains Order Line Item and its associated product Options

    CREATE TABLE [dbo].[orderItemOption](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OI_ID] [int] NULL,   -- Order Item 
    [RPO_ID] [int] NULL,  -- Production Option
    [RPOV_ID] [int] NULL    -- Production Option Value
    ) ON [PRIMARY]


    Sample datasets below:

    1 1378 10109 44756
    2 1378 10110 44757
    3 1378 10115 NULL
    4 1378 10118 44769
    5 1379 10109 44756
    6 1379 10110 44757
    7 1379 10115 NULL
    8 1379 10118 44769
    9 1380 10109 44756
    10 1380 10110 44757
    11 1380 10115 NULL
    12 1380 10118 44769
    13 1381 10109 44756
    14 1381 10110 44757
    15 1381 10115 NULL
    16 1381 10118 44769

    Here is what I want to do:

    I want to create a product dimension and create a ProductAlternateKey which will be
    purely based on the combination of all RPO_ID and RPOV_ID for each OI_ID.  I am gonna create a HASH value (Use HASHBYTE to minimize collision) based on the combination
    and this HASH value (convert to BIGINT) will become the BusinessKey for this product dimension.

    The challeng here is to construct the Product Dimension table as I need to pivot the table dbo.orderItemOption so that each line contains one Order Item and
    all its associated option ID and option Value. Since some order item may have up to 10 options, I need to create at least 20 new columns like
    RPO_ID_1, RPOV_ID_1, RPO_ID_2, RPOV_ID_2, RPO_ID_3, RPOV_ID_3 which indicate option 1, option value 1, option 2, option value 2 and option 3 , optionvalue 3...

    Does anyone here have encountered the similiar problem before? 

    I'd appreciate if you would share your experience.



    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, April 04, 2013 1:02 AM

All replies

  • Hi Hui,

    You could easily create a production dimension if you expect the following output:


    Level1 - OI_ID

    Level2 - Product Business Key (combination of RPO_ID and RPOV_ID)

    Is this fine for you or can you explain how your product dimension (not the underlying product dimension table structure) should look like?



    Thursday, April 04, 2013 2:49 AM
  • hi Vinaya, this is what I want to achieve.  one transaction order item may contain multiple product options. 

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Friday, April 05, 2013 6:44 PM
  • Hi Hui,

    You have to create a view for Product dimension in  the below structure:

    SELECT OI_ID, Product Business Key (combination of RPO_ID and RPOV_ID), Rest of the fields....

    In the dimension structure, add all the columns from the table as attributes and finally create a Hierarchy with OI_ID as the top level and Product_Business_key as the next level.  

    When you browse the Dimension Hierarchy you will get something like this:








    So you need not create 20 different columns and unpivot the Product Business Key combination for each of the OI_ID but you can just do a normal select for all columns and achieve the structure you wanted in the dimension by remodelling the data as required.  

    Hope this gives you the result you expect.



    • Edited by Vinaya Ravi Wednesday, April 10, 2013 12:17 AM
    Wednesday, April 10, 2013 12:15 AM