Tuesday, March 27, 2012

combining Sat-Sun and also Fri-Sun

One of the dimensions in my cube is Weekday, with 1=Monday through 7=Sunday.

I have created named sets {1,2,3,4,5} for Mon-Fri and {6,7} for Sat and Sun. However many people also want to include Friday as the weekend, they prefer {1,2,3,4} and {5,6,7}, so I created named sets for those too.

However, I can't find any way to use these at the client level (my client is Excel Pivot tables, but I evaluated many other clients). I find named sets are only usable within SSAS's own cube browser (as a filter). Further, I can't find any way to get the cube to pre-calculate the groups. I know named sets don't get calculated, I am open to another way to get this done. I use VBA within Excel to select multiple weekdays in the PivotTable, but this causes lengthy OLAP queries each time.

If I had only one way of breaking weekday vs weekend, I would create a new table with 7 records:

1 Weekday

2 Weekday

...

6 Weekend

7 Weekend

and then create a hierarchy, the weekday/weekend level would then get pre-calculated. But since I have two possibilities, I can't do this.

I've tried all kinds of creative possibilities, does anyone have a suggestion on how to get all 11 "members" of this dimension (7 individual weekdays, plus the 4 named sets) to be pre-calculated? Thanks in advance!

Hello! The simple solution is that you use a client that will present SSAS2005 named sets, like ProClarity Professional 6.3 or Excel 2007.

Older versions of Excel do not support named sets.

If this is not possible I suggest that you add two new attributes for the two different weekday/weekend classifications.

HTH

Thomas Ivarsson

No comments:

Post a Comment