locked
Filter dimension to show CUBE relevant data RRS feed

  • Question

  • Folks,

    I have 4 cubes CUBE1, CUBE2, and so on...,  in my SSAS db which leverage several shared dimensions: such as the DATES dimension.

    In the underlying tables for the shared dimensions, i have boolean flagged columns -- such as IS_USED_BY_CUBE1 in my DATES table -- which would be flagged TRUE, if this is the case (nb: setting the value to be TRUE is handled by my SSIS package).

     

    QUESTION: How do I now ONLY show the dates in CUBE1 that have the boolean flagged to TRUE as opposed to ALL the dates in the dates table?

     

    Thanks for your help in advance,
    Phill 

    Thursday, January 26, 2012 10:02 PM

All replies

  • Hi Phill,

    It's possible with

    • Creating 3 additional dimensions (using the appropriate filters in their data sources). This way you will have to clarify the meaning of the time entries (separating, for example, Order Date, Manufacturing Date, Shipping Date, Invoice Date), but you don't load all kind of time entries to a common source table just because the dimension tables would have the same structure anyway.
    • Creating 4 different hierarchies in the existing dimension (you will have to alter your query to have more columns as sources ). Hide every attribute hierarchy (see AttributeHierarchyVisible property). When you browse the hierarchies, all irrelevant date will be shown, but you can put them under an 'Irrelevant' node (which is varying by hierarchy), and the rest can stay under the previous hierarchy nodes.

    I would suggest the first option, because it has a more clear design and helps the users understand the model better - however you'll have to create 3 additional views or tables in the underlying data source.

    Regards,
    Zoli


    -- Zoltán Horváth
    -- MCITP SQL Server Business Intelligence Developer 2005, 2008
    -- MCITP Database Developer 2008, Database Administrator 2008
    -- Please mark posts as answered or helpful where appropriate.
    Friday, January 27, 2012 5:41 PM
  • Thanks for your answer, i am more inclined to implement option #2:

    1) It leverages the BOOLEAN columns present in the underlying table

    2) It means only having one dimension present.

     

    In my scenario, I am working with CUBE3 that has data for the years 2008-2012, however, the other 3 CUBES use dates from 1900-2016. As a consequence, the shared Dimension has dates that cover the 1900-2016 period. I want to create my hierarchy, Can you explain to me the implementation process in a bit more detail for me to configure the hierarchy to only see, within CUBE3, the dates for 2008-2012 period?

     

    I really hope that this possible. However, I am getting the feeling that I can't configure the IS_USED_BY_CUBE3 field, to only show the TRUE node... (after all you mentioned that it would also include the FALSE or IRRELEVANT data within the FALSE level).

     

    Warm regards,
    Phill 

    Friday, January 27, 2012 7:27 PM
  • Unfortunately, there is no option to omit dimension members when building up a hierarchy, the only solution is to assign the less relevant members to a dedicated group, and simply omit this group from the queries.

    So, if you choose the second option:

    You don't have to use the boolean flags. Instead of that, you will need to define separate column groups for every hierarchy. For example, a table structure like DateKey, Hierarchy1Year, Hierarchy1Month, Hierarchy1Day, Hierarchy2Year, Hierarchy2Month, Hierarchy2Day, ...

    Then every date record has to be filled in the following way:
    - Only those columns should be filled with data (1900-2016 in year columns, 1-12 in month columns etc.) in which hierarchy the date must be presented.
    - All other columns should have the same value, for example, "IRRELEVANT". This also means that you must use string-typed attribute keys (instead of numeric ones).

    Then you can create the dimension, adding 4 hierarchies which use different attributes as levels.

    Hope it helps.

    Regards,
    Zoli


    -- Zoltán Horváth
    -- MCITP SQL Server Business Intelligence Developer 2005, 2008
    -- MCITP Database Developer 2008, Database Administrator 2008
    -- Please mark posts as answered or helpful where appropriate.
    • Proposed as answer by Jerry Nee Tuesday, January 31, 2012 9:06 AM
    Friday, January 27, 2012 8:58 PM