Help Needed: How to deal with ragged dimensions

#1

Hello All

My problem is this:

I am trying to use MS Analysis services to analyze whether height of
students impacts student scores on an aptitude test. Student height is
one measure and the others are student_id and score on test. I intend
to use the student_id in a count rollup and perform average height and
test score calculations.

The test has different sections with scores. Each section in turn has
subsections with their scores. Section scores are not the summation of
subsection scores.
A schematic:
Score
Subject 600
Section1 60
Subsection11 50
Subsection12 40
Section2 20
Subsection21 10
Subsection22 10
Subsection23 40
Section3 80
In essence the Subject dimension is ragged. I used the hideif (no
name) to get the dimension to look like it is not "ragged".

Problem:
How do I get analysis services to show (using example above) at the
subject level that subject score = 600
and upon drilling down that Section1 score = 60, Subsection11 score
=50 etc
The scores get rolled up (summed) at the All level instead of showing
600.

Any help would be greatly appreciated.

#2

create a calulated measure with this type of formula
iif([Sections].currentmember is [Sections].[All Sections];
sum([Sections].[SubSectionLevel].Members, [Score]);[Score])

"psmith28" <kwaku_duro (AT) hotmail (DOT) com> a écrit dans le message de news:
4691d105.0408290650.43f118ec (AT) po...OT) google.com...

Quote:

Hello All

My problem is this:

I am trying to use MS Analysis services to analyze whether height of
students impacts student scores on an aptitude test. Student height is
one measure and the others are student_id and score on test. I intend
to use the student_id in a count rollup and perform average height and
test score calculations.

The test has different sections with scores. Each section in turn has
subsections with their scores. Section scores are not the summation of
subsection scores.
A schematic:
Score
Subject 600
Section1 60
Subsection11 50
Subsection12 40
Section2 20
Subsection21 10
Subsection22 10
Subsection23 40
Section3 80
In essence the Subject dimension is ragged. I used the hideif (no
name) to get the dimension to look like it is not "ragged".

Problem:
How do I get analysis services to show (using example above) at the
subject level that subject score = 600
and upon drilling down that Section1 score = 60, Subsection11 score
=50 etc
The scores get rolled up (summed) at the All level instead of showing
600.

Any help would be greatly appreciated.

#3

Jeje
Thanks a lot for your response.

I do not want to sum. Every (sub)section has a score. I want to be
able to show the score for each (sub)section as I drilldown from
subject to section to subsection. Initially, I thought to turn off
aggregation altogether but I was not sure how to get the student
count.

I really appreciate your help and time.

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

Quote:

create a calulated measure with this type of formula
iif([Sections].currentmember is [Sections].[All Sections];
sum([Sections].[SubSectionLevel].Members, [Score]);[Score])

"psmith28" <kwaku_duro (AT) hotmail (DOT) com> a écrit dans le message de news:
4691d105.0408290650.43f118ec (AT) po...OT) google.com...
Hello All

My problem is this:

I am trying to use MS Analysis services to analyze whether height of
students impacts student scores on an aptitude test. Student height is
one measure and the others are student_id and score on test. I intend
to use the student_id in a count rollup and perform average height and
test score calculations.

The test has different sections with scores. Each section in turn has
subsections with their scores. Section scores are not the summation of
subsection scores.
A schematic:
Score
Subject 600
Section1 60
Subsection11 50
Subsection12 40
Section2 20
Subsection21 10
Subsection22 10
Subsection23 40
Section3 80
In essence the Subject dimension is ragged. I used the hideif (no
name) to get the dimension to look like it is not "ragged".

Problem:
How do I get analysis services to show (using example above) at the
subject level that subject score = 600
and upon drilling down that Section1 score = 60, Subsection11 score
=50 etc
The scores get rolled up (summed) at the All level instead of showing
600.

Any help would be greatly appreciated.

#4

Another way than using a parent child dimension could be the following:

Level 0 = subject, Level 1 = section, Level 2 = subsection

If you in the dimension table put in a member at level 2 with the exact same
name as level 1 - that means that you have
to name it "section 3".

Then in AS you can choose to hide member if Parent's name in the dimension
editor on levevel 1 and level 2.

This of course would require that you do something in your relational
environment - i like this more - have heard some bad things
about parent - child dimensions so I tend to avoid them if possible.

Don't know if there are limitations in your front end - not all support
ragged hierachies.

--Michael V.

"yongli" <yongli (AT) discussions (DOT) microsoft.com> skrev i en meddelelse
news:7BE70128-D0FE-4F24-8FE9-CA058BC1D400 (AT) microsoft (DOT) com...

Quote:

You have to create a parent-child dimension, and use datamember function
to
retrieve the preaggregated value.

"psmith28" wrote:

Jeje
Thanks a lot for your response.

I do not want to sum. Every (sub)section has a score. I want to be
able to show the score for each (sub)section as I drilldown from
subject to section to subsection. Initially, I thought to turn off
aggregation altogether but I was not sure how to get the student
count.

I really appreciate your help and time.

"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

create a calulated measure with this type of formula
iif([Sections].currentmember is [Sections].[All Sections];
sum([Sections].[SubSectionLevel].Members, [Score]);[Score])

"psmith28" <kwaku_duro (AT) hotmail (DOT) com> a écrit dans le message de news:
4691d105.0408290650.43f118ec (AT) po...OT) google.com...
Hello All

My problem is this:

I am trying to use MS Analysis services to analyze whether height of
students impacts student scores on an aptitude test. Student height
is
one measure and the others are student_id and score on test. I
intend
to use the student_id in a count rollup and perform average height
and
test score calculations.

The test has different sections with scores. Each section in turn
has
subsections with their scores. Section scores are not the summation
of
subsection scores.
A schematic:
Score
Subject 600
Section1 60
Subsection11 50
Subsection12 40
Section2 20
Subsection21 10
Subsection22 10
Subsection23 40
Section3 80
In essence the Subject dimension is ragged. I used the hideif (no
name) to get the dimension to look like it is not "ragged".

Problem:
How do I get analysis services to show (using example above) at the
subject level that subject score = 600
and upon drilling down that Section1 score = 60, Subsection11 score
=50 etc
The scores get rolled up (summed) at the All level instead of
showing
600.

Any help would be greatly appreciated.

#5

You can solve your problem using calculated cells. Please
let me know if you need further information on calculated
cells.

Abhinav Kumar
akumar (AT) itmag (DOT) com.au

Quote:

-----Original Message-----
Hello All

My problem is this:

I am trying to use MS Analysis services to analyze
whether height of
students impacts student scores on an aptitude test.
Student height is
one measure and the others are student_id and score on
test. I intend
to use the student_id in a count rollup and perform
average height and
test score calculations.

The test has different sections with scores. Each
section in turn has
subsections with their scores. Section scores are not
the summation of
subsection scores.
A schematic:
Score
Subject 600
Section1 60
Subsection11 50
Subsection12 40
Section2 20
Subsection21 10
Subsection22 10
Subsection23 40
Section3 80
In essence the Subject dimension is ragged. I used the
hideif (no
name) to get the dimension to look like it is
not "ragged".

Problem:
How do I get analysis services to show (using example
above) at the
subject level that subject score = 600
and upon drilling down that Section1 score = 60,
Subsection11 score
=50 etc
The scores get rolled up (summed) at the All level
instead of showing
600.

Any help would be greatly appreciated.
.

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

Help Needed: How to deal with ragged dimensions

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

Help Needed: How to deal with ragged dimensions - 08-29-2004 , 09:50 AM

Re: Help Needed: How to deal with ragged dimensions - 08-29-2004 , 08:16 PM

Re: Help Needed: How to deal with ragged dimensions - 08-30-2004 , 08:27 AM

Re: Help Needed: How to deal with ragged dimensions - 08-30-2004 , 12:59 PM

Help Needed: How to deal with ragged dimensions - 08-30-2004 , 09:53 PM