Post reply

Hierarchy Product struture in relational database

  • February 17, 2011 at 9:33 am

    #235269

    I am currently trying to prepare a dimension table for one of our reporting cubes in SSAS. The dimension is a product structure derived from one our financial applications. I need to create a parent child table which can then be used as a dimension in ssas.

    I have added some sql and some test data to further explain what I need.

    CREATE TABLE #Product

    (

    category_id int,

    name VARCHAR(20) NOT NULL,

    LEVEL INT DEFAULT NULL,

    prod_structure varchar(20)

    )

    insert into #Product(category_id,name,level,prod_structure)

    SELECT 1, 'BT1',0,'BT1'

    UNION ALL

    SELECT 2, 'DB2',1,'BT1 DB2'

    UNION ALL

    SELECT 3, 'DB2',2,'BT1 DB2 DB2'

    UNION ALL

    SELECT 4, 'DB3',2,'BT1 DB2 DB3'

    UNION ALL

    SELECT 5, 'SP2',1,'BT1 SP2'

    UNION ALL

    SELECT 6, 'SKU',2,'BT1 SP2 SKU'

    I first need to clean up the data such that 3 should not be included as Product DB2 is found at both level 2 and level 1. The reason for this is that the members in the dimension should be unique. Hence I need to find the firs occurence of every product in the structure readin from left to right. Based on the daat supplied

    Table data above should change from :

    category_id|name|level|prod_structure

    1 BT1 0 BT1

    2 DB2 1 BT1 DB2

    3 DB2 2 BT1 DB2 DB2

    4 DB3 2 BT1 DB2 DB3

    5 SP2 1 BT1 SP2

    6 SKU 2 BT1 SP2 SKU

    to:

    category_id|name|level|prod_structure

    1 BT1 0 BT1

    2 DB2 1 BT1 DB2

    4 DB3 2 BT1 DB2 DB3

    5 SP2 1 BT1 SP2

    6 SKU 2 BT1 SP2 SKU

    Once that is achieved I then need to change the data such that it is represented like:

    ID|Category_id|prod_structure|Level|ParentID|

    1 1 BT1 0 NULL

    2 2 DB2 1 1

    3 4 BT1 DB2 DB3 2 2

    4 5 BT1 SP2 1 1

    5 6 BT1 SP2 SKU 2 4

    Any help would be much appreciated

  • February 17, 2011 at 9:47 am

    #1288343

    Have you considered using a Nested Sets style hierarchy? They are much, much faster to query, and usually work better in SSAS that way.

    Data on hierarchies at: http://qa.sqlservercentral.com/articles/T-SQL/65540/

    Data on nested sets hierarchies at: http://en.wikipedia.org/wiki/Nested_set_model

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 17, 2011 at 2:42 pm

    #1288537

    I haven't tried nested sets. Will definitely have a look into it this weekend. Thanks for the info.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply