|
|
[SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
-
 07-24-2008, 5:52 PM |
|
|
[SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
E.g. DimGeography has a user hierarchy with the following levels.
Hierarchy Name: Geographies
Level 1: All
Level 2: Country-Region
Level 3: State-Province
Level4: City
Level5: Postcode
These attributes come from only one table [AdventureWorksDW].[dbo].[DimGeography] in the relational database. Nowhere can I see any mapping (constraints) defined that would tell SSAS that members VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) come under the member AUS (Level 2: Country-Region). Similarly I can't seem to understand how it figures out the right members when you drill down through the hierarchy.
My guess is that while processing the cube it executes some distinct statements such as find me the distinct states where country is Australia and maintains this meta-data somewhere. Another thing that comes to mind is that all the non-key attributes (Country-Region,State-Province,City,Postcode) link to the same key attribute (GeographyKey) i.e. AUS (Level 2: Country-Region) and VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) must be linking to the same Geography key. Again I don't know how that is possible because the source binding column for the GeographyKey is [AdventureWorksDW].[dbo].[DimGeography].[GeographyKey] which increments with each postcode.
I am aware that you can define relationships (Rigid or Flexible) in the attribute pane in the Dimension Designer form; however SSAS does the job even without explicitly defining these relationships. And to an extent isn't it the same as dropping attributes at appropriate levels in the Hierarchies and Levels pane of the Dimension Designer? This is the only way to define a hierarchy right?
������r��z�-�+%��i��zǧu���Ơy����-��b��"�����r��y�-��.n7���0z��o)��W�i�^r����fjv�y���i��0·�v'��
|
|
-
| 07-24-2008, 7:09 PM |
|
|
Re: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
Is it Tejas?....I know you have said "user" hierarchy - by that do you mean that you defined the hierarchy yourself?
The way i understand it each attribute in a dimension by default have two levels of hierarchy: an all (Default Level Name: All) level, and a level that contains that each attribute member (Default Level Name: Same as the attribute name). This is called attribute hierarchy.
In the dimension designer you drag attributes from the 'Attribute' pane and construct a hierarchy in the 'Hierarchy & Levels' pane. This grouping is called user hierarchy. Hence in the example I provided 'Geographies' is a user hierarchy defined for the dimension DimGeography.
I've just picked up these terms as I am going through the BOL tutorials. I am not sure if I am right.
In Lesson 4: Defining Advanced Attribute and Dimension Properties there is an exercise for Specifying Attribute Relationships Between Attributes in a User-Defined Hierarchy. Even without defining the relationships explicitly SSAS still drills down to correct members at any level. I just can't figure out how SSAS deduces which States belong to which country and so on...
On Thu, Jul 24, 2008 at 6:46 PM, Lucas Ferguson <high_spl2k2@...> wrote:
Hi There, Is it Tejas?....I know you have said "user" hierarchy - by that do you mean that you defined the hierarchy yourself? Cheers
Date: Thu, 24 Jul 2008 17:49:44 +1000 From: high_spl2k2@... To: high_spl2k2@...
Subject: [SQLDownUnder] How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
E.g. DimGeography has a user hierarchy with the following levels.
Hierarchy Name: Geographies
Level 1: All
Level 2: Country-Region
Level 3: State-Province
Level4: City
Level5: Postcode
These attributes come from only one table [AdventureWorksDW].[dbo].[DimGeography] in the relational database. Nowhere can I see any mapping (constraints) defined that would tell SSAS that members VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) come under the member AUS (Level 2: Country-Region). Similarly I can't seem to understand how it figures out the right members when you drill down through the hierarchy.
My guess is that while processing the cube it executes some distinct statements such as find me the distinct states where country is Australia and maintains this meta-data somewhere. Another thing that comes to mind is that all the non-key attributes (Country-Region,State-Province,City,Postcode) link to the same key attribute (GeographyKey) i.e. AUS (Level 2: Country-Region) and VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) must be linking to the same Geography key. Again I don't know how that is possible because the source binding column for the GeographyKey is [AdventureWorksDW].[dbo].[DimGeography].[GeographyKey] which increments with each postcode.
I am aware that you can define relationships (Rigid or Flexible) in the attribute pane in the Dimension Designer form; however SSAS does the job even without explicitly defining these relationships. And to an extent isn't it the same as dropping attributes at appropriate levels in the Hierarchies and Levels pane of the Dimension Designer? This is the only way to define a hierarchy right?
������r��z�-�+%��i��zǧu���Ơy����-��b��"� ����r��y�-��.n7���0z��o)��W�i�^r����fjv�y���i��0·�v'�
������r��z�-�+%��i��zǧu���Ơy����-��b��"�����r��y�-��.n7���0z��o)��W�i�^r����fjv�y���i��0·�v'��
|
|
-
| 07-24-2008, 9:33 PM |
|
|
RE: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
Hi Darren,
Is there any further info around on how SSAS works it out? It doesnt seem to matter how i create the DSV the wizard always seems to work out the correct hierarchies. I have in fact never ventured into defining them myself.
I would be really curious to know about the logic behind it. The obvious thing to me it seems is to compare distinct values from two columns of a dataset and establish that there are either many to one or one to many relationships between the values. But how does it then achieve this across different dimensions?
I second Tejas' question.
Lucas
From: dgosbell@... To: SQLDownUnder@... Subject: RE: [SQLDownUnder] How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension? Date: Thu, 24 Jul 2008 20:52:18 +1000
Hi TJ,
SSAS does do some distinct queries to figure out which members in one attribute exist with which members of the other attributes in the dimension. But the way it knows what queries to run is by looking at the attribute relationships. By default you will often find all of the attributes related to the key attribute, which (as you have observed) appears to work fine.
The only problem with this is that when all the attributes are only related to the key attribute is that you are effectively telling SSAS that it is possible for any member of one attribute to exist with any member of any other attribute. With this sort of setup it is difficult for SSAS to optimize performance. But once you start explicitly setting up the relationships ie. A postcode relates to one city and a city relates to one state, etc. Then SSAS can use these relationships to build better aggregations and indexes.
Regards
Darren
From: SQLDownUnderList@... [mailto:SQLDownUnderList@...] On Behalf Of bytebugs bugs
Sent: Thursday, 24 July 2008 5:50 PM
To: sqldownunder@...
Subject: [SQLDownUnder] How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
E.g. DimGeography has a user hierarchy with the following levels.
Hierarchy Name: Geographies
Level 1: All
Level 2: Country-Region
Level 3: State-Province
Level4: City
Level5: Postcode
These attributes come from only one table [AdventureWorksDW].[dbo].[DimGeography] in the relational database. Nowhere can I see any mapping (constraints) defined that would tell SSAS that members VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) come under the member AUS (Level 2: Country-Region). Similarly I can't seem to understand how it figures out the right members when you drill down through the hierarchy.
My guess is that while processing the cube it executes some distinct statements such as find me the distinct states where country is Australia and maintains this meta-data somewhere. Another thing that comes to mind is that all the non-key attributes (Country-Region,State-Province,City,Postcode) link to the same key attribute (GeographyKey) i.e. AUS (Level 2: Country-Region) and VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) must be linking to the same Geography key. Again I don't know how that is possible because the source binding column for the GeographyKey is [AdventureWorksDW].[dbo].[DimGeography].[GeographyKey] which increments with each postcode.
I am aware that you can define relationships (Rigid or Flexible) in the attribute pane in the Dimension Designer form; however SSAS does the job even without explicitly defining these relationships. And to an extent isn't it the same as dropping attributes at appropriate levels in the Hierarchies and Levels pane of the Dimension Designer? This is the only way to define a hierarchy right?
rz-+%izǧuƠy-b"ry-.n70zo)Wi^rfjvyi0·v' to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
������r��z�-�+%��i��zǧu���Ơy����-��b��"�����r��y�-��.n7���0z��o)��W�i�^r����fjv�y���i��0·�v'��
|
|
-
| 07-25-2008, 11:00 AM |
|
|
Re: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
Thanks Darren,
It makes sense to me. I would have liked to see the queries that it executes to deduce the hierarchical relationships between the attributes. Would I be able to see them when I process the cube?
On Thu, Jul 24, 2008 at 8:52 PM, Darren Gosbell <dgosbell@...> wrote:
Hi TJ,
SSAS does do some distinct queries to figure out which members in one attribute exist with which members of the other attributes in the dimension. But the way it knows what queries to run is by looking at the attribute relationships. By default you will often find all of the attributes related to the key attribute, which (as you have observed) appears to work fine.
The only problem with this is that when all the attributes are only related to the key attribute is that you are effectively telling SSAS that it is possible for any member of one attribute to exist with any member of any other attribute. With this sort of setup it is difficult for SSAS to optimize performance. But once you start explicitly setting up the relationships ie. A postcode relates to one city and a city relates to one state, etc. Then SSAS can use these relationships to build better aggregations and indexes.
Regards
Darren
From: dgosbell@... [mailto:dgosbell@...] On Behalf Of bytebugs bugs
Sent: Thursday, 24 July 2008 5:50 PM
To: dgosbell@...
Subject: [SQLDownUnder] How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
How does SSAS figure out the correct lower level members in a drill down path defined in a user hierarchy of a cube dimension?
E.g. DimGeography has a user hierarchy with the following levels.
Hierarchy Name: Geographies
Level 1: All
Level 2: Country-Region
Level 3: State-Province
Level4: City
Level5: Postcode
These attributes come from only one table [AdventureWorksDW].[dbo].[DimGeography] in the relational database. Nowhere can I see any mapping (constraints) defined that would tell SSAS that members VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) come under the member AUS (Level 2: Country-Region). Similarly I can't seem to understand how it figures out the right members when you drill down through the hierarchy.
My guess is that while processing the cube it executes some distinct statements such as find me the distinct states where country is Australia and maintains this meta-data somewhere. Another thing that comes to mind is that all the non-key attributes (Country-Region,State-Province,City,Postcode) link to the same key attribute (GeographyKey) i.e. AUS (Level 2: Country-Region) and VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) must be linking to the same Geography key. Again I don't know how that is possible because the source binding column for the GeographyKey is [AdventureWorksDW].[dbo].[DimGeography].[GeographyKey] which increments with each postcode.
I am aware that you can define relationships (Rigid or Flexible) in the attribute pane in the Dimension Designer form; however SSAS does the job even without explicitly defining these relationships. And to an extent isn't it the same as dropping attributes at appropriate levels in the Hierarchies and Levels pane of the Dimension Designer? This is the only way to define a hierarchy right?
rz-+%izǧuƠy-b"ry-.n70zo)Wi^rfjvyi0·v' to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
h�{.n�+�����b�X���^jǬzwZ��,j�i�-��^�+-�+a�{.n�+�����^���y�O��y�zv���(����f�j�o(m���� +y�b)�
|
|
-
| 07-25-2008, 11:35 AM |
|
|
RE: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
Hey Tejas,
You can see/copy/paste the ‘Details’ of the hierarchies as the SSAS
cube is processing. Click on the ‘cogs’ and drill down.
With regards,
Rohan
From: SQLDownUnderList@...
[mailto:SQLDownUnderList@...] On Behalf Of bytebugs
bugs
Sent: Friday, 25 July 2008 10:58 AM
To: SQLDownUnder@...
Subject: Re: [SQLDownUnder] How does SSAS figure out the correct lower
level members in a drill down path defined in a user hierarchy of a cube
dimension?
It makes sense to me. I would have liked to see the queries
that it executes to deduce the hierarchical relationships between the
attributes. Would I be able to see them when I process the cube?
On Thu, Jul 24, 2008 at 8:52 PM, Darren Gosbell <dgosbell@...> wrote:
Hi TJ,
SSAS does do some distinct
queries to figure out which members in one attribute exist with which members
of the other attributes in the dimension. But the way it knows what queries to
run is by looking at the attribute relationships. By default you will often
find all of the attributes related to the key attribute, which (as you have
observed) appears to work fine.
The only problem with this is
that when all the attributes are only related to the key attribute is
that you are effectively telling SSAS that it is possible for any member of one
attribute to exist with any member of any other attribute. With this sort of
setup it is difficult for SSAS to optimize performance. But once you start
explicitly setting up the relationships ie. A postcode relates to one city and
a city relates to one state, etc. Then SSAS can use these relationships to build
better aggregations and indexes.
Regards
Darren
From: dgosbell@...
[mailto:dgosbell@...]
On Behalf Of bytebugs bugs
Sent: Thursday, 24 July 2008 5:50 PM
Subject: [SQLDownUnder] How does SSAS figure out
the correct lower level members in a drill down path defined in a user
hierarchy of a cube dimension?
How does SSAS figure out the correct
lower level members in a drill down path defined in a user hierarchy of a cube
dimension?
E.g. DimGeography has a user hierarchy with
the following levels.
Hierarchy Name: Geographies
Level 1: All
Level 2: Country-Region
Level 3: State-Province
Level4: City
Level5: Postcode
These attributes come from only one table
[AdventureWorksDW].[dbo].[DimGeography] in the relational database. Nowhere can
I see any mapping (constraints) defined that would tell SSAS that members VIC,
NSW, QLD, WA, SA, TAS (Level 3: State-Province) come under the member AUS
(Level 2: Country-Region). Similarly I can't seem to understand how it figures
out the right members when you drill down through the hierarchy.
My guess is that while processing the cube it
executes some distinct statements such as find me the distinct states where
country is Australia and maintains this meta-data somewhere. Another thing that
comes to mind is that all the non-key attributes
(Country-Region,State-Province,City,Postcode) link to the same key attribute
(GeographyKey) i.e. AUS (Level 2: Country-Region) and VIC, NSW, QLD, WA,
SA, TAS (Level 3: State-Province) must be linking to the same Geography key.
Again I don't know how that is possible because the source binding column for
the GeographyKey is [AdventureWorksDW].[dbo].[DimGeography].[GeographyKey]
which increments with each postcode.
I am aware that you can define relationships
(Rigid or Flexible) in the attribute pane in the Dimension Designer form;
however SSAS does the job even without explicitly defining these relationships.
And to an extent isn't it the same as dropping attributes at appropriate levels
in the Hierarchies and Levels pane of the Dimension Designer? This is the only
way to define a hierarchy right?
rz-+%izǧuƠy-b"ry-.n70zo)Wi^rfjvyi0·v'
to unsubscribe to this list, please send a message back to
the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net
rzzuyrynzoirjy¶
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
|
|
-
| 07-25-2008, 9:55 PM |
|
|
RE: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
From: SQLDownUnderList@...
[mailto:SQLDownUnderList@...] On Behalf Of Lucas
Ferguson
Sent: Thursday, 24 July 2008 9:29 PM
To: sqldownunder@...
Subject: RE: [SQLDownUnder] How does SSAS figure out the correct lower
level members in a drill down path defined in a user hierarchy of a cube
dimension?
Hi Darren,
Is there any further info around on how SSAS works it out? It doesnt seem
to matter how i create the DSV the wizard always seems to work out the correct
hierarchies. I have in fact never ventured into defining them myself.
I would be really curious to know about the logic behind it. The obvious
thing to me it seems is to compare distinct values from two columns of a
dataset and establish that there are either many to one or one to many
relationships between the values. But how does it then achieve this
across different dimensions?
I second Tejas' question.
Lucas
From: dgosbell@...
To: SQLDownUnder@...
Subject: RE: [SQLDownUnder] How does SSAS figure out the correct lower level
members in a drill down path defined in a user hierarchy of a cube dimension?
Date: Thu, 24 Jul 2008 20:52:18 +1000
Hi TJ,
SSAS does do some distinct queries to figure out which members
in one attribute exist with which members of the other attributes in the
dimension. But the way it knows what queries to run is by looking at the
attribute relationships. By default you will often find all of the attributes
related to the key attribute, which (as you have observed) appears to work
fine.
The only problem with this is that when all the attributes are
only related to the key attribute is that you are effectively telling
SSAS that it is possible for any member of one attribute to exist with any
member of any other attribute. With this sort of setup it is difficult for SSAS
to optimize performance. But once you start explicitly setting up the
relationships ie. A postcode relates to one city and a city relates to one
state, etc. Then SSAS can use these relationships to build better aggregations
and indexes.
Regards
Darren
From: SQLDownUnderList@...
[mailto:SQLDownUnderList@...] On Behalf Of bytebugs
bugs
Sent: Thursday, 24 July 2008 5:50 PM
To: sqldownunder@...
Subject: [SQLDownUnder] How does SSAS figure out the correct lower level
members in a drill down path defined in a user hierarchy of a cube dimension?
How
does SSAS figure out the correct lower level members in a drill down path
defined in a user hierarchy of a cube dimension?
E.g.
DimGeography has a user hierarchy with the following levels.
Hierarchy
Name: Geographies
Level
1: All
Level
2: Country-Region
Level
3: State-Province
Level4:
City
Level5:
Postcode
These
attributes come from only one table [AdventureWorksDW].[dbo].[DimGeography] in
the relational database. Nowhere can I see any mapping (constraints) defined
that would tell SSAS that members VIC, NSW, QLD, WA, SA, TAS (Level 3:
State-Province) come under the member AUS (Level 2: Country-Region). Similarly
I can't seem to understand how it figures out the right members when you drill
down through the hierarchy.
My
guess is that while processing the cube it executes some distinct statements
such as find me the distinct states where country is Australia and maintains
this meta-data somewhere. Another thing that comes to mind is that all the
non-key attributes (Country-Region,State-Province,City,Postcode) link to the
same key attribute (GeographyKey) i.e. AUS (Level 2: Country-Region) and
VIC, NSW, QLD, WA, SA, TAS (Level 3: State-Province) must be linking to the
same Geography key. Again I don't know how that is possible because the source
binding column for the GeographyKey is
[AdventureWorksDW].[dbo].[DimGeography].[GeographyKey] which increments with
each postcode.
I
am aware that you can define relationships (Rigid or Flexible) in the attribute
pane in the Dimension Designer form; however SSAS does the job even without
explicitly defining these relationships. And to an extent isn't it the same as
dropping attributes at appropriate levels in the Hierarchies and Levels pane of
the Dimension Designer? This is the only way to define a hierarchy right?
rz-+%izǧuƠy-b"ry-.n70zo)Wi^rfjvyi0·v'
to
unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net
rz-+%izǧuƠy-b"ry-.n70zo)Wi^rfjvyi0·v'
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
|
|
-
| 07-26-2008, 9:43 AM |
|
|
Re: [SQLDownUnder] How does SSAS figure out the correct lower levelmembers in a drill down path defined in a user hierarchy of a cube dimension?
Don't under estimate the effect of Attribute relationships. Besides reducing the number of dimensions <by combining what many people assume are different dimensions>, setting attribute relationships is the single most important technique you can use for optimising a cubes performance. You can get orders of magnitude better performance!
In SSAS2008 attribute relationships are set in their own tab diagrammatically <the dev team realised they are a bit hidden in SSAS2005>.
In SSAS2005 the best way is to:
- Create the hierarchy (you will see a yellow warning)
- drill in on each level to show attributes (only lowest level is likely to have any at this point)
- drag appropriate level from left pane onto the appropriate hierarchy level attribute eg Quarter onto Months attributes etc (if you have done it correctly the yellow warning disappears)
- Remove dupe attribute relationships eg Quarter from Day
MAKE SURE IT IS A REAL HIERARCHY - if it isn't and there are many to many relationships you will get "unusual" results, to put it politely, in the built cube.
Grant
MVP SQL Server
2008/7/25 Darren Gosbell <dgosbell@...>
IF you are really interested in the internals then there is a really deep book that was written by some of the members of the product team.
Microsoft SQL Server Analysis Services 2005 http://www.amazon.com/Microsoft-Server-2005-Analysis-Services/dp/0672327821/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1216985702&sr=8-1
I'm not clear on what you mean by the wizard always working out the correct hierarchies. Are you saying that it always figures out that Sydney belongs to NSW and NSW belongs to Australia? SSAS can usually do a good job of figuring this out on it's own because of the indexes and attribute maps that it builds during processing time.
The problem with this is that with the default situation where all the attributes are only related to the key attribute, what you are essentially telling SSAS is that Sydney could possibly also intersect with VIC and QLD. You can think of attribute relationships in terms of "has one". So a city "has one" state and a state "has one" country. SSAS can then build aggregations along these relationships as it knows that there are no overlaps.
When it comes to relationships across dimensions, this is all done through the fact table. Conceptually it's not dissimilar to doing inner joins in the relational engine
Regards
Grant Paisley
MVP SQL Server
Angry Koala
http://ak.com.au
Mobile: +61 416 00 55 17
-��칻 �&�a��b��ey� ���jg��� m�$��azX�����칻 �&�j�az˛��->� ��[�f����nW��b�ٚ�� u����� 0�杉��z
|
|
|
|
|