Many to Many Relationships Fact->Attribute->Leaf in AS2005

I have a dimension with two natural hierarchies climbing up from the leaf.
One hierarchy is quite deep, and is the one I'm interested in. The other
hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
table joins to the simple attribute hierarchy, but I want to analyse using
the deeper one.
There is in effect a many to many relationship between the fact table and
the leaves of the dimension. This information can be implied because we're
descending a natural hierarchy to get from where the fact table joins to the
leaf. Leaf to Attribute is a Many to One relationship, so Attribute to Leaf
is One to Many and Fact to Attribute is Many to One. Many-One-Many =
Many-Many.
What I'd like is for Analysis Services to realise that and give me Many to
Many navigation when I try to analyse the facts using the other hierarchy.
Instead the analysis seems to fail, and I just get the grand total in all
fields at all levels when drilling down the other hierarchy.
To make things work, I'm having to set up the Many-Many relationship
explicitely by creating a Measure Group using the dimension table, along wit
h
two Dimensions (one containing just the joining attribute, the other the rea
l
leaf, the joining attribute and the deep hierarchy). The result is as
expected with correct summations, but it is not as neat a solution as just
having AS work this out from the single dimension.
I notice that attribute relationships in the February CTP version have two
properties - Cardinality and Relationship Type. I can't find any
documentation on these, but wonder if they'd help. I've been treating every
relationship as strict - so if I know a value for an attribute I can always
resolve a single value for any member properties of that attribute. Attribut
e
to Member Property is a Many to One relationship.
As an aside, this introduces subtelty as it means I must be always sure I
can uniquely identify an attribute by its key - a risk where an attribute is
identified by name and the name can appear in multiple places with different
meaning in different parts of the natural hierarchy (two towns in the UK
called Leeds for example). I am currently working around this by using each
parent level as part of the composite key so "Yorkshire, Leeds" is different
to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at the
moment no other way to do this based on the basic principle that an attribut
e
must be uniquely identified in order to allow AS to uniquely identify its
member properties and satisfy the Many-One relationship.
Thanks
- RichardIf there is a many-to-many from the fact table to the leaf level; then you
need to create an intermediate junction table which represents that M:M
relationship. You create a measure group for that M:M junction table -- and
establish relationships from there. See the AdventureWorksDW for an example.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:F952BDFC-CFBC-49C2-BA86-64BAD15F4F47@.microsoft.com...
> I have a dimension with two natural hierarchies climbing up from the leaf.
> One hierarchy is quite deep, and is the one I'm interested in. The other
> hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
> table joins to the simple attribute hierarchy, but I want to analyse using
> the deeper one.
> There is in effect a many to many relationship between the fact table and
> the leaves of the dimension. This information can be implied because we're
> descending a natural hierarchy to get from where the fact table joins to
the
> leaf. Leaf to Attribute is a Many to One relationship, so Attribute to
Leaf
> is One to Many and Fact to Attribute is Many to One. Many-One-Many =
> Many-Many.
> What I'd like is for Analysis Services to realise that and give me Many to
> Many navigation when I try to analyse the facts using the other hierarchy.
> Instead the analysis seems to fail, and I just get the grand total in all
> fields at all levels when drilling down the other hierarchy.
> To make things work, I'm having to set up the Many-Many relationship
> explicitely by creating a Measure Group using the dimension table, along
with
> two Dimensions (one containing just the joining attribute, the other the
real
> leaf, the joining attribute and the deep hierarchy). The result is as
> expected with correct summations, but it is not as neat a solution as just
> having AS work this out from the single dimension.
> I notice that attribute relationships in the February CTP version have two
> properties - Cardinality and Relationship Type. I can't find any
> documentation on these, but wonder if they'd help. I've been treating
every
> relationship as strict - so if I know a value for an attribute I can
always
> resolve a single value for any member properties of that attribute.
Attribute
> to Member Property is a Many to One relationship.
> As an aside, this introduces subtelty as it means I must be always sure I
> can uniquely identify an attribute by its key - a risk where an attribute
is
> identified by name and the name can appear in multiple places with
different
> meaning in different parts of the natural hierarchy (two towns in the UK
> called Leeds for example). I am currently working around this by using
each
> parent level as part of the composite key so "Yorkshire, Leeds" is
different
> to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at
the
> moment no other way to do this based on the basic principle that an
attribute
> must be uniquely identified in order to allow AS to uniquely identify its
> member properties and satisfy the Many-One relationship.
> Thanks
> - Richard|||> If there is a many-to-many from the fact table to the leaf level; then you[vbcol=seagreen]
> need to create an intermediate junction table which represents that M:M
> relationship. You create a measure group for that M:M junction table -- an
d
> establish relationships from there. See the AdventureWorksDW for an example.[/vbco
l]
I have this working thanks. I've hidden the new measure (a count measure on
the join) so as not to confuse the end user.
While'st here - could you explain the Cardinality and Type
(strict/otherwise) properties on the Attribute Relationships and check that
my understanding of having to ensure an attribute key uniquely identifies an
instance of that attribute is correct. I'm working on the basis that it can'
t
infer that two instances of an attribute with the same key are different
because they appear at different points in a hierarchy or have different
member properties. AS2000 could do this but its model was completely
different.
Thanks
- Richard|||With SQK2K5, you *must* have a unique key for every attribute. If it isn't
unique, then you need to make it unique. For example, if you have a Time
dimension with Quarters and Months, then in SQL2K you could have the keys
be:
Month: 1 to 12
Quarter: 1 to 4
And tell the system that member keys were not unique across the dimension.
In this case, the system automatically used the hierarchy to create an
internal unique key by walking up the hierarchy.
With SQL2K5, attributes are first-class citizens and as such they must have
a unique key across the entire dimension. This means that it is now your job
to create a unique concatenated key. In the case above, you need to click on
the key field and you will see an option to create a key "collection" --
i.e. what we call a concatenated key. So now your attribute keys need to be:
Month: year + 1 through 12
Quarter: year + 1 through 4
If you don't do this, then the dimension won't be rendered properly and
members will disappear or appear under the wrong parent.
This is a frequent issue that new users (particular SQL2K users coming over
to SQL2K5) run into. Most new folks know what a concatenated key is -- and
just take it as it was relational terms. The folks who struggle with this is
those of us coming over from SQL2K :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:5BC8D21F-D8E4-4D94-A597-1544A1F24793@.microsoft.com...
you[vbcol=seagreen]
and[vbcol=seagreen]
example.[vbcol=seagreen]
> I have this working thanks. I've hidden the new measure (a count measure
on
> the join) so as not to confuse the end user.
> While'st here - could you explain the Cardinality and Type
> (strict/otherwise) properties on the Attribute Relationships and check
that
> my understanding of having to ensure an attribute key uniquely identifies
an
> instance of that attribute is correct. I'm working on the basis that it
can't
> infer that two instances of an attribute with the same key are different
> because they appear at different points in a hierarchy or have different
> member properties. AS2000 could do this but its model was completely
> different.
> Thanks
> - Richardsql