I need to model inheritance kind of behaviour, for example I have
generic DimBase and specific DimExtended.
Its not possible to merge these dimensions since I have many such
specific dimensions(it will just blow up DimBase).
Similarly I have FactBase and FactExtended. I have modeled the schema
as per Kimball methodologies as

DimBase
BaseKey(primary key) BaseAttribute
1 B1
2 B2
3 B3
4 B4

DimExtended
BaseKey(foregin key DimBase) ExtendedAttribute
2 E2
4 E4


FactBase
BaseKey(foregin key DimBase) BaseValue
1 1
2 2
3 3
4 4

FactExtended
BaseKey(foregin key DimExtended) ExtendedValue
2 22
4 44


I want to extract the data from the cube as follows
ExtendedAttribute ExtendedValue BaseValue
E2 22 2
E4 44 4

I using following query
select {[Measures].[ExtenedValue],[Measures].[BaseValue]} on columns,
non empty {[Dim Extended].[Extended Attribute].Children
}
on rows
from [MyCube]

It returns incorrect results
ExtendedAttribute ExtendedValue BaseValue
E2 22 15
E4 44 15

Is there any way I can achieve desired results by modifying dimensional
relationships in Dimensions Usage tab ?
I know I can write mdx query to do this, but I want the olap engine to
take care of this.

Can you provide a pointer to which specific Kimball methodology you're
using? For example, does each extended fact record have a corresponding
base record and, if so, what is the key joining them (just like
DimExtended joins to DimBase via the BaseKey).

If you're using AS 2005, and there is a join between the fact tables,
you could try defining [Dim Extended] with a many-many dimension
relationship to the FactBase measure group (which includes
[Measures].[BaseValue]). The intermediate measure group would be
FactExtended, and the intervening dimension would be the degenerate
FactBase dimension.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Deepak,

Thanks for the suggestions.


We are using AS 2005, I was thinking of similar solution too.
Other approach would be repalce FacExtened by a view combining columns from
FactBase.

Which approach do you think is better and more efficient ?

-vaibhav

Hard to say which approach is better in general - the many-many
dimension feature provides more flexibility, but may incur a run-time
performance penalty, particularly if the intermediate measure group fact
table is large. However, if you need to analyze the Base Value by both
base and extended dimensions, without the many-many approach you may
have to define Unknown Members for extended attributes.

This paper on many-to-many dimensional modelling by Marco Russo also
discusses some performance implications:

http://www.sqlbi.eu/Home/tabid/36/ct...ID/7/Default.a
spx
many-to-many dimension relationships, a feature that debuted available
with Analysis Services 2005.
...
Only the Distinct Count scenario contains a section discussing the
impact on performance. Since the considerations presented there may be
applied to other many-to-many relationship uses, I recommend you read
this scenario if you are interested in performance evaluations.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I would not use the many-to-many relationship in this case.
If I had a similar situations, I would model a single dimension with
this query:

SELECT
b.BaseKey,
b.BaseAttribute,
COALESCE( e.ExtendedAttribute, '<unknown>' ) AS ExtendedAttribute
FROM DimBase b
LEFT JOIN DimExtended e
ON e.BaseKey = b.BaseExtended

And I would use this query as the fact table:

SELECT
b.BaseKey,
b.BaseValue,
e.ExtendedValue
FROM FactBase b
LEFT JOIN FactExtended e
ON e.BaseKey = b.BaseKey

The resulting UDM is fast and easy to navigate. NULL measures can still
be counted separately to get right averages on ExtendedValue if you
need them, and <unknown> member links Base members that don't have
extended attributes.

Did you see any problem on this?

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


Deepak Puri wrote:

Thanks for the feedback, Marco - performance should be better with your
option. The only scenario where I could see any issue would be if there
were a requirement to drill through the extended value to only extended
fact records. Would this require a 2nd (non-default ?) drillthrough
action with a condition clause to exclude the <unknown> base fact
records - I'm not sure?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Marco

Actually I have many extened Dimensions and Facts, if I combine all of
them together, it would result in tables with more 100 columns. Base
dimension and fact are the kind of master tables which contains row for
each member of exteneded ones.

I am essentially looking at the solution where I can analyze data
across all types and also at the specific level too.

I tried the approach suggested by Deepak, I defined relationship
between DimExtended and FactBase using intermediate measure
FactExtened. It gave me desired result, but I need to try it on volume
data. DimBase will to have apprx 1 million members and FactBase data
will be capurted everyday, ie at the granularity of day level.

I was thinking of replacing FactExtened by a view joining with
FactBase, so I will have multiple Fact tables. Depending on the type of
data required I can query either generic fact table or specific one. I
am not sure if this is right approach, please let me know you thoughts
on this.


Thanks
-Vaibhav



Deepak Puri wrote:

You are right - I just made a simple model and I thought the best model
could be one with 2 fact table (FactBase and FactExtended) that feeds 2
measure groups and a single dimension (DimComplete?) that is the result
of a LEFT JOIN.
I am going to write a more detailed answer at the end of this thread.

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

Deepak Puri wrote: