MS SQL Server OLAP - Named Set Alias

Author

Named Set Alias

Paul

2005-09-23, 9:23 am

Hi,

I have a named set with 5 non-leaf members from the same dimension, same
level, different parents. This works fine.

When the named set is chosen, rathan than displaying the 5 individual
members, I want it to display a new parent first, totalling up the 5 members,
which the user can drill into if they want to see the detail of the members.

Any ideas??
Ta
Paul

Darren Gosbell

2005-09-25, 8:23 pm

I don't think you can do this with a named set. There are 2 alternatives
I can think of off the top of my head (there may be more).

1) Instead of using a named set, create a calculated member, you will
see the "parent" calculation but this will not let you drill down. This
is the simplest and easiest to maintain option.

2) Introduce duplicate members into the dimension to create a fake
alternate partial hierarchy. If you go down this path you cannot
optimize this dimension. (the technique relies on the inner joins
producing duplicate records) and you must use unary operators to stop
the secondary hierarchy from affecting the "All member" totals.

Below is a simple example of a fake partial alternate hierarchy. Note
that RowId 2 & 5 are duplicated with different parents and the alternate
parent has a unary operator of "~" to stop it rolling up to the All
member and double counting those facts.

Unary
RowID Operator Level_1 Leaf
===== ======== =========== =======
1, +, Parent 1, Child 1
2, +, Parent 1, Child 2
3, +, Parent 2, Child 3
4, +, Parent 2, Child 4
5, +, Parent 2, Child 5
2, ~, Fake Parent, Child 2
5, ~, Fake Parent, Child 5

NOTE: This scheme can be difficult to maintain, we built a dimension
management UI that created and maintained these alternate hierarchies
for our client.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <70A48BD4-BA06-441C-A1FE- FBF5BE41B313@microso
ft.com>,
Paul@discussions.microsoft.com says...
> Hi,
>
> I have a named set with 5 non-leaf members from the same dimension, same
> level, different parents. This works fine.
>
> When the named set is chosen, rathan than displaying the 5 individual
> members, I want it to display a new parent first, totalling up the 5 members,
> which the user can drill into if they want to see the detail of the members.
>
> Any ideas??
> Ta
> Paul
>

Paul

2005-09-28, 11:24 am

Hi Darren,
Thanks for your reply! I have already thought of the first option and
really not keen on the other option so I'm going to opt for real hierarchies.

Ta
Paul
"Darren Gosbell" wrote:

> I don't think you can do this with a named set. There are 2 alternatives
> I can think of off the top of my head (there may be more).
>
> 1) Instead of using a named set, create a calculated member, you will
> see the "parent" calculation but this will not let you drill down. This
> is the simplest and easiest to maintain option.
>
> 2) Introduce duplicate members into the dimension to create a fake
> alternate partial hierarchy. If you go down this path you cannot
> optimize this dimension. (the technique relies on the inner joins
> producing duplicate records) and you must use unary operators to stop
> the secondary hierarchy from affecting the "All member" totals.
>
> Below is a simple example of a fake partial alternate hierarchy. Note
> that RowId 2 & 5 are duplicated with different parents and the alternate
> parent has a unary operator of "~" to stop it rolling up to the All
> member and double counting those facts.
>
> Unary
> RowID Operator Level_1 Leaf
> ===== ======== =========== =======
> 1, +, Parent 1, Child 1
> 2, +, Parent 1, Child 2
> 3, +, Parent 2, Child 3
> 4, +, Parent 2, Child 4
> 5, +, Parent 2, Child 5
> 2, ~, Fake Parent, Child 2
> 5, ~, Fake Parent, Child 5
>
> NOTE: This scheme can be difficult to maintain, we built a dimension
> management UI that created and maintained these alternate hierarchies
> for our client.
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
>
> In article <70A48BD4-BA06-441C-A1FE- FBF5BE41B313@microso
ft.com>,
> Paul@discussions.microsoft.com says...
>

Darren Gosbell

2005-09-28, 8:24 pm

> Hi Darren,
> Thanks for your reply! I have already thought of the first option and
> really not keen on the other option so I'm going to opt for real hierarchies.
>

Great! If you have the option of adding another dimension/hierarchy that
is probably the best way to go.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links