What are members in dimension hierarchies?

Posted on 2009-02-17
Last Modified: 2016-02-13

It is not clear for me what members in a dimension hierarchy exactly are. Suppose I have the followin dimension table (a bit odd):

Year    Month        Day
2009    January     01
2009    January     02
2009    January     03
2009    February    01
2009    February    02
2010    January      01
2010    January      02
2010    January      03
2010    February     01
2010    February     02

Let's suppose I have established a hierarchy as:

Day    son of
Month son of

What are the "members" in that case? 2009...? January...? 2009-January...?  2009-January-01?

Thanks in advance,

Question by:juarrero
    LVL 5

    Expert Comment

    All of those are members.  Members are the named elements in the dimension.  So 2009, 2009-January, 2009-January-01 are all members.  You can define them moe specifically by their position in the hierarchy.  For instance 2009-January-01 is a level 0 member or leaf member.  2009-January would be considered a level 1 member, 2009 would be considered a level 2 member.
    LVL 12

    Expert Comment

    You can imagine a simple cube with two "dimensions" as a grid in excel with dimension A along one side, and dimension B along the other.  Each "column" or "row" of the grid corresponds to a "Member".  The cells in the middle of the grid (where the numbers are) are referred to as "Tuples", with each "tuple" being referencable by the members along each edge.  

    Levels in a dimension are (usually) simply a 'calculated' member that represents the total of its "children".  So in your example, "January 1 2009" and "January 2 2009" are both members at the "Day" level of your cube.  "January 2009" is another member, but its at the "month" level, and simply represents the total of all the "day" members below it.  "2009" is likely also a member, and there will usually be another member above that which represents "All" dates in the cube.  

    To go back to my spreadsheet example, imagine your cube has all the dates down one side.  If you were to insert a row after "January 31, 2009", and then add in a "Sum()" function to add up all the values from "January 1, 2009" to "January 31, 2009", you'd have a new row (member).  Effecitvely, you'd have created a "month" level in your spreadsheet.


    Author Comment


    After reading the comments and the SSAS tutorial my doubts remain. I will try to be more precise:

    This is my dim table (slightly modified):

    ID    Year    Month        Day
    ----   -----     -----          ------
    1    2009    January     01
    2    2009    January     02
    3    2009    January     03
    4    2009    February    01
    5    2009    February    02
    6    2010    January      01
    7    2010    January      02
    8    2010    January      03
    9    2010    February     01
    10   2010    February     02

    1) The table has four columns, the ID one being the Primary Key.

    2) Every column corresponds to an only attribute in the dimension (so not having composite keys).

    Then, some questions:

    a) Would you say that only "1", "2", etc. (the items in the ID column) can be called members while the other items in the table, such as "January" or "2009" can't be?

    b) Would you say that a member is really  the whole set of items in a row, for instance "1    2009    January     01"?

    c) Is "02" in the Day column/attribute a member of the dimension? Note that I refer to "02", not to "02-Januay-2010" or "02-January" or similar.

       c.1) Does besides exist a member such as "02-January" or "02-January-2010"?


    LVL 12

    Accepted Solution

    You're trying to apply OLAP concepts to a relational database table, which is probably why you're getting a bit confused.  Members are a logical construction based on groupings in the dimensional table.  So for a "day" level, each member would correspond to one row in your table.  But your "year" level would comprise of "groups" based on the year.  

    Members are defined by the dimension definition, which is seperate from the physical database table.  Each "Level" in your heirarchy will have its business key defined by somethign in the table (a "year" level woudl be based on the "Year" data item, while your "month" level would be based on a combination of "Year" and "Month", etc.)

    In the examply table, assuming a three level dimension (year,month, day) you would have 17 members in the dimension.  

         January 2009
             January 1, 2009
             January 2, 2009
             January 3, 2009
         February 2009
             Februrary 1, 2009
             Februrary 2, 2009
         January 2010
             January 1, 2010
             January 2, 2010
             January 3, 2010
         February 2010
             Februrary 1, 2010
             Februrary 2, 2010


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now