Re: How to aggregate a leaf-item in parallel parent-child hierarchies

#1

Hi Gabor,

I think you will find a good solution to your problem reading the
"Multiple parent-child hierarchies" scenario in my paper "The
many-to-many revolution" available here:
http://www.sqlbi.eu/manytomany.aspx

Please note that a part of your specific problem could be solved with
named sets, calculated members and with a correct definition of
attribute relationships in the Time dimension (for example, I tipically
use two Month levels, one with a strong attribute relationship with
Year and another without, so users can cross years and months into a
pivot table). But it seems that this cannot satisfy all your
requirements.

Have a good read.

Marco Russo
http:/www.sqlbi.eu
http:/www.sqljunkies.com/weblog/sqlbi

Gabor76 wrote:

Quote:

Hello,

I have two question concerning building parallel hierarchies within a
dimension.

I mean by it the following: I would like to aggregate the same leaf-item in
more than one parent-child hierarchies within the same dimension.

the Questions:
1) is there a way to do it using the parent-child hierarchy? (it would be
nice ;-)
2) if I don't use parent-child hierarchies, does it exist a better way to
solve the problem using many hierarchy levels, than the one I show below?

Please, let me illustrate the case through an easy example:
- you have as leaf item the single month (you get your data in the fact
table for single months (January, February, etc.)
- you would like to build many different hierarchies in the "Period"
dimension; e.g.:
1) year - quarter - single month (e.g. Year=Q1+Q2+Q3+Q4 and Q1 = Jan + Febr
+ March)
2) cumulated month (e.g. cum_April = Jan + Febr + March + Apr)
3) special periods (e.g. SP1 = Febr + March)

In this example you should aggregate the single month item "March" in all
three Hierarchies.

The three hierarchies should be independent from each other (there is no
risk to aggregate the leaf element many times).

BUT -> my problem:
If I build a dimension with a parent-child hierarchy I get the item "March"
(03) only as a leaf item for one of the hierarchies: e.g. I find "March" as
the leaf item in year -. Quarter- single month hierarchy, but in the others
not.
(I guess, I don't get "March" in Cum_April or in SP1 as a Child-Element,
since the item got already assigned to the year-quarter-single month
hierarchy...)

I can solve the case with the following solution that works ok for small
dimensions: using different hierarchy-levels, building many hierarchies, and
showing them separately in the Dimensions-Browser e.g.:

HL1 HL2 HL3 HL4 ... HL6 HL7
2006 Q1 Cum_April Cum_March ... SP1 March
... ... ... ... ... ...

(HL =Hierarchy Level)

... where I build different hierarchies, e.g.:
Hierarchy 1: HL1 - HL2 - HL7
Hierarchy 2: HL3 - HL7
Hierarchy3: HL4 - HL7
Hierarchy4: HL6 - HL7

etc.

BUT:
For larger hierarchies and dimensions with thousands of items it seems to me
quite complicated...

It would be nice to be able to use the following exemplary structure for
import files (extract):

PARENTNAME NODENAME
2006 Q1
2006 Q2
... ...
Q1 01
Q1 02
Q1 03
... ...
Cum_ March 02
Cum_March 03
... ...
Cum_April 01
Cum_April 02 (Febr)
Cum_April 03 (March)
Cum_April 04
... ...
SP1 02
SP1 03

(As I said above: right now I get March (03) only as a child of the Q1,
although it belongs to many hierarchies.

Thanks in advance for any helpful input.

I use Microsoft SQL Server Analysis Services-Designer, Version 9.00.2047.00

#2

Hi Marco,

Thanx a lot for you advice!

Your paper was a great help not only in this special case but also by
solving other open issues of me concerning m2m relationships.
Hence I was fairly busy lately with other topics, my response comes so tardy…

For Everybody:
Although you need some time to read through the almost 90 pages but I would
recommend this paper for anybody working with m2m.

Using other structures for your dimension-tables you can even simplify your
own solution compared to the examples described in the paper.

Best wishes,

Gabor

"Marco Russo" wrote:

Quote:

Hi Gabor,

I think you will find a good solution to your problem reading the
"Multiple parent-child hierarchies" scenario in my paper "The
many-to-many revolution" available here:
http://www.sqlbi.eu/manytomany.aspx

Please note that a part of your specific problem could be solved with
named sets, calculated members and with a correct definition of
attribute relationships in the Time dimension (for example, I tipically
use two Month levels, one with a strong attribute relationship with
Year and another without, so users can cross years and months into a
pivot table). But it seems that this cannot satisfy all your
requirements.

Have a good read.

Marco Russo
http:/www.sqlbi.eu
http:/www.sqljunkies.com/weblog/sqlbi

Gabor76 wrote:
Hello,

I have two question concerning building parallel hierarchies within a
dimension.

I mean by it the following: I would like to aggregate the same leaf-item in
more than one parent-child hierarchies within the same dimension.

the Questions:
1) is there a way to do it using the parent-child hierarchy? (it would be
nice ;-)
2) if I don't use parent-child hierarchies, does it exist a better way to
solve the problem using many hierarchy levels, than the one I show below?

Please, let me illustrate the case through an easy example:
- you have as leaf item the single month (you get your data in the fact
table for single months (January, February, etc.)
- you would like to build many different hierarchies in the "Period"
dimension; e.g.:
1) year - quarter - single month (e.g. Year=Q1+Q2+Q3+Q4 and Q1 = Jan + Febr
+ March)
2) cumulated month (e.g. cum_April = Jan + Febr + March + Apr)
3) special periods (e.g. SP1 = Febr + March)

In this example you should aggregate the single month item "March" in all
three Hierarchies.

The three hierarchies should be independent from each other (there is no
risk to aggregate the leaf element many times).

BUT -> my problem:
If I build a dimension with a parent-child hierarchy I get the item "March"
(03) only as a leaf item for one of the hierarchies: e.g. I find "March" as
the leaf item in year -. Quarter- single month hierarchy, but in the others
not.
(I guess, I don't get "March" in Cum_April or in SP1 as a Child-Element,
since the item got already assigned to the year-quarter-single month
hierarchy...)

I can solve the case with the following solution that works ok for small
dimensions: using different hierarchy-levels, building many hierarchies, and
showing them separately in the Dimensions-Browser e.g.:

HL1 HL2 HL3 HL4 ... HL6 HL7
2006 Q1 Cum_April Cum_March ... SP1 March
... ... ... ... ... ...

(HL =Hierarchy Level)

... where I build different hierarchies, e.g.:
Hierarchy 1: HL1 - HL2 - HL7
Hierarchy 2: HL3 - HL7
Hierarchy3: HL4 - HL7
Hierarchy4: HL6 - HL7

etc.

BUT:
For larger hierarchies and dimensions with thousands of items it seems to me
quite complicated...

It would be nice to be able to use the following exemplary structure for
import files (extract):

PARENTNAME NODENAME
2006 Q1
2006 Q2
... ...
Q1 01
Q1 02
Q1 03
... ...
Cum_ March 02
Cum_March 03
... ...
Cum_April 01
Cum_April 02 (Febr)
Cum_April 03 (March)
Cum_April 04
... ...
SP1 02
SP1 03

(As I said above: right now I get March (03) only as a child of the Q1,
although it belongs to many hierarchies.

Thanks in advance for any helpful input.

I use Microsoft SQL Server Analysis Services-Designer, Version 9.00.2047.00

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: How to aggregate a leaf-item in parallel parent-child hierarchies

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Re: How to aggregate a leaf-item in parallel parent-child hierarchies - 12-08-2006 , 10:38 AM

Re: How to aggregate a leaf-item in parallel parent-child hierarch - 02-02-2007 , 10:53 AM