locked
Dimension Hierarchy using 2 tables RRS feed

  • Question

  • I want to create a hierarchy using 2 tables. Can I do this? The Fact table has a key into each table.

    Example:
    Table 1: Fruits, Vegetables, Meat
    Table 2: oranges, apples, peas, carrots, green beans, beef, pork

    I want my hierarchy to be:
    Fruit
     - apples
     -oranges
    Vegetables
        -peas
         -carrots
        -green beans
    Meat
        - beef
        - pork.
    Wednesday, February 4, 2009 10:38 PM

Answers

  • One way is to create a named query  and use columns tables fact and both dimension using join and
    get the new dimension table and here you will have to include all the columns which you want to use for hierarchy
    something like this

    select Fruits, Vegetables, Meat,oranges, apples, peas, carrots, green beans, beef, pork
    from dbo.table1 as b inner join dbo.Fact_table as a
    on a.table1_Key=b.Table1_Key
    inner join dbo.Table2 as c
    on c.Table2_Key=a.Table2_Key
    (Because fact table will have both tables related with some pk-fk and now you have to take columns and using named query create a new table )
    Hope this will help


    Dibyant S U padhyay
    Wednesday, February 4, 2009 10:55 PM