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

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

Combining dimensions in 1 hierarchy

Hi,

I have a cube that is migrated from AS 2000 to SSAS 2005.
One of the dimensions in the AS 2000 cube was a combination of 2 tables.
For instance:
Dimension name in AS 2000: Customer Type
Hierarchy: Level 1: Account Type (from table Dim_Account)
Lever 2: Product Type (from table Dim_Product)

Now after migrating this cube to SSAS 2005 this appears to produce an error.
I tried recreating this dimension, but it appears that I can't combine different dimension tables into 1 hierarchy. By this I don't mean the "Referenced" relationship type.

The only reference between the 2 dimension tables is the fact table.

I hope I make myself clear and someone can give me an answer, because otherwise I'm facing a lot of work :-/

Thanks!

It is bit unusual to build a single dimension from two tables that are referenced through the fact table.

There are several solutions you can try:

One, you can build 2 dimensions Account and Product and then stack hierarchy from Account on top of Product's hierarchy. This is probably better overall solution because you will be creating your dimensions and cube to follow the logical structure of the data you have in the relational database.

Another solution is to create Named Query in DSV to combine two tables into one and then build a single dimension on top of it. You'll have to make sure you do all the joins correctly.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hello,

How do i stack those hierarchy?

thanks

sqlsql