Re: How to handle heirarchies in dimension attributes (Fairly green design question)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 12/14/04

  • Next message: Adam Machanic: "Re: How to handle heirarchies in dimension attributes (Fairly green design question)" 
    Date: Mon, 13 Dec 2004 23:21:07 -0500

    Actually I was asking without any preconcieved notions. I am very new to
    the whole process of building a data warehouse so I am just learning. I
    subsequently found the very same answer in Kimball's book, which I
    apparently missed the first time I read through it. Being that I have built
    only OLTP systems for 10 years, it is still a really strange process to me
    :) 

    -- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message 
news:OdnDtTV4EHA.664@TK2MSFTNGP10.phx.gbl...
> Louis,
>
> If I understand your question properly, you're interested in how to
> represent a hierarchy in a dimension table, I assume without snowflaking
> your schema?  How deep is the hierarchy?  One possible solution may be to
> create additional columns in the dimension table such that each row will
> contain a cross-tabulated representation of the hierarchy.  That will
> generally solve the problem easily -- and perform well -- if the hierarchy
> is shallow enough (and its depth is known in advance).  If you can't make
> those kinds of guarantees during the design phase, I'm not sure how to 
> solve
> the problem other than snowflaking and having a separate hierarchy table.
>
>
> -- 
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:elbcAnU4EHA.604@TK2MSFTNGP10.phx.gbl...
>> For example, say I have a purchase fact table.  Then I have a customer
>> dimension.  In the customer dimension, we have city, state, name, etc, 
>> and
>> then we have a group level that needs to have a heirarchy exposed.  For
>> example:
>>
>> Super
>>         Super Level 1
>>         Super Level 2
>> Good
>>         Good Level 1
>>         Good Level 2
>>
>> Obviously this is not what we are really using, but it is the same sort 
>> of
>> thing.
>>
>> How to best do this?  Using a related table?
>>
>> Also, if there is a good way to search for this kind of discussion on
>> groups.google.com, advice there would be graciously accepted :)
>>
>> -- 
>> --------------------------------------------------------------------------
> --
>> Louis Davidson - drsql@hotmail.com
>> SQL Server MVP
>>
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services.  All other replies may be ignored :)
>>
>>
>
>
  • Next message: Adam Machanic: "Re: How to handle heirarchies in dimension attributes (Fairly green design question)"

    Relevant Pages