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