Filtering on Hierarchies

  • Im sure this is a very common problem, but even after searching here and the net I can't find a solution. I have a simple cube with a date dimension consisting of datekey; dayname; week-no; year.

    When these are processed I can browse the dayname in the browser and there are 7 distinct days. This is great as I can then use the days in a filter as part of my mdx later.

    However, ive been told its good practice to roll up dates into hiearachies for query optimization. This isn't a probem to do, I just create a composite key for the dayname and week-no and it processes fine.

    But, when I now browse the cube the days are no longer distinct but repeat for every occurrence in the cube. I understand this is fully expected as the dayname now has a composite key to make entry each unique to the cube.

    But here is the big problem. I can no longer include he dayname as a filter in my mdx, also the end user will not be able to filter a pivot table on the dayname as there will be 1000's of entries to choose from!

    The attached picture should hopefully explain a little better where im coming from.

    Does anyone know how to browse/filter a dimensions attributes once a composite key column is created.

    Thanks in advance.

  • The simplest answer is to create a new attribute, call it "Day of Week" or some such and don't have it in a hierarchy or give it a composite key. You can then use this in filtering or whatever. For an example see the AdventureWorks cube Date dimension which has attributes for "Day Name", "Day of Week", "Day of Month" etc. that are separate from the Fiscal and Calendar hierarchies.


    I'm on LinkedIn

  • Sometimes the simplest solutions are the best, i'll give that a try later.

    Thanks SSC.

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

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