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:
ID OI_ID RPO_ID RPOV_ID
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 --
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?
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