In Books Online is said that it is possible to have only ONE Fact Dimension
per measure group (i.e. fact table). For variuos reasons I need to have two.
Obviously SSAS05 gives an error if I try to configure the second dimension
as Fact ( a rather incomprehensible error code, if you ask me), but accepts
it as Regular and it works fine, apparently.

What are the implications? Am I going to have a problem having a second Fact
Dimension used as Regular??

Thanks in advance

Enzo

Not really a problem unless you plan to use the dimension for drillthrough
and it has to go into the ROLAP mode -- that is the scenario when a fact
dimension has some special optimizations...

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Enzo" <enzo (AT) martoglio (DOT) com.nospam> wrote

each measure group have a set of dimension associated. and his own
optimization.
its like creating a cube in AS2000, 1 fact table by cube, then a virtual
cube to "merge" 2 cubes.

by default AS2005 create as new measure group when you create a DCount
measure on a fact table allready added in a cube, because the best practices
recommand to create a dedicated cube for this type of aggregation.
is it your issue?

why its a problem for you?
what you can't do with multiple measure group?


"Enzo" <enzo (AT) martoglio (DOT) com.nospam> wrote

Hi Jéjé
My udm is of medium complexity, having at the moment (half way through the
project) 3 fact tables and one bridge table, with lots of regular and
many-to-many dimensions.
The project does not yet require any distinct count.
The main fact table stores data about mortgages. Because of the design
(that I am only partially responsible for) and of SSAS05 characteristics I
have two degenerate dimensions.
Both of them use the grain of the fact table, mortgage element: a mortgage
xyz is made of different applications stu, in turn an application is made of
different elements 1,2,etc. (you can assimilate an element to a specific
mortgage product).

Elements, Applications and Mortgages roll-up into Types (re-mortgage,
personal loan etc.) as well as into Stages (application, offer, completion,
rejected).
Because a single mortgage can have an elements belonging to Personal loan
and another to re-mortgage I need to set a collection of keys as KeyColumn to
roll numbers up properly.
Unfortunately I need two different sets of KeyColumn keys, one for Types and
one for Stage: therefore two degenerate dimensions, each of them with a user
hierarchy with same leaf (element) but different root (either Stage or
Types), as well as two different sets of KeyColumn collections for mortgage.
I hope it is clear!
Cheers
Enzo

"Jéjé" wrote:

I'm not sure if I have understand everything correctly ;-)

but what is the problem with "1 fact table by measure group"?

if you have 1 key with 2 possible dimensions, you can connect 1 key column
to 2 different dimensions for 2 different usage.

also, AS2005 support the "unknown" member, so when a member is not found,
AS replace it by an unknown member instead-of raising an error.
so, maybe you can add some columns in the fact table with null value if you
don't have any data for these columns.

sometimes parent-child dimensions can solve complex dimensions schema.

This website:
http://sqlserveranalysisservices.com/default.htm
has an article to duplicate a member, maybe this can help you.

This website present an interesting article too:
http://geekswithblogs.net/darrengosb...les/57811.aspx

maybe these articles will ive you some way to find the right solution.

"Enzo" <enzo (AT) martoglio (DOT) com.nospam> wrote

Thanks for the links. I knew Richard Tkachuk web site but I never
encountered Darren Gosbell one.
Unfortunately both cases are different from mine: we do not use parent-child
tables and we need to tye up the roll-ups in a complex but orthodox way. My
hierarchies are like this:
<Type> <Mortgage> <Application> <Element> //"Type" hierarchy
Personal Loan
12345
02
001
Re-Mortgage
12345
02
002
The other hierarchy is just the same:
Rejected //
"Stage" hierarchy
12345
01
001
002
Completed
12345
02
001
002

It is a praise to the design of SSAS05 that the above is very easy to
configure, using a collection of keys in the KeyColumn field in the member
properties tab (pity that this important feature is so poorly documented in
BOL and takes lots of trial and error to figure out).
A limitation of the current SSAS05 implementation is that I can have only 1
KeyColumn collection per member (I would rather make it a property of the
member of a hierarchy allowing a KeyColumn collection per hierarchy instead).
So if a member needs to be part of two "complex" hierarchies as above, I
need to create another dimension to be able to specify another set of keys.
Another limitation in SSAS05 (that I partly accept) is that if you have a
key column collection you cannot create discretisation buckets...

On the other hand I am quite satisfied by the solution I found, pity being
forced to configure the second fact dimension as regular.

Incidentally, the problem is not "1 fact table per measure group", but is "1
Fact Dimension (MSFT parlance for degenerate dimension) per Measure Group".
Cheers
Enzo

"Jéjé" wrote: