Dimension uniquely identifies a row in Fact

[T4FF]

Hi there

Designing a model in 8.3 and have a query to run by you.  Fresh off the FM course a fortnight ago I am developing my first model and have come across a potential problem with the star schema I am pulling in to FM.

Within the schema there are 2 facts - factAccount and factAccountMonthEnd and a dimension called dimAccountDetail which conforms to both facts (there are many other tables but these are the 3 I am currently reviewing).

factAccount holds current measures for all savings accounts and the factAccountMonthEnd fact is a monthly summary of factAccount.  dimAccountDetail holds lots of relevant details about each account - years with bank, type of account etc and has an entry for every account.

Because of this, the relationship between factAccount and dimAccountDetail is a one-to-one relationship but the relationship between dimAccountDetail and factAccountMonthEnd is one-to-many.  Therefore, factAccount has amiguous joins (this is the only place in the schema this occurs).

Does or will this cause any problems with query generation?  Is there any way I can get round this, or is there a correct way I should model this? 

Thanks in advance

[goose]

You need to tell Cognos how you want the data to get rolled up. This will ensure that if you need data from both fact tables on the same report no double counting will occur.

http://support.cognos.com/supported/docs/en/html/cognos8_bi/8.4/ug_fm_id20067bp-relational_modeling_concepts.html

[blom0344]

If factAccountMonthEnd is a monthly accumulating snapshot than it's measures are non-additive by nature.
What I mean is that you cannot add 2 or more monthEnd level values for a given account and have a meaningful outcome.

If you primary interest lies in comparing with values from the most recent snapshot , then add an SQL object that (or add a view to the database) that holds this data. This will cope with the 1:n , making it 1:1 / 1:0

Another strategy is to denormalize the factAccountMonthEnd into a denormalized 'flattenend' structure, creating 'buckets' for each month..

[T4FF]

Thanks for responses guys

blom, this makes sense and is something I can work from, setting up a "current monthend" table would probably be the most useful to the business.  I can then split out the monthend table into a seperate entity.

The third is a nice thought however, in practice probably isn't that practical to manage ;D


Thanks for the link angus.  I also take the point that the snapshot can't be additive.