many to many relationship

[sibbi82]

Hi,

is it possible to imagine a many to many relationship in TM1?

For example, i have a incident fact table and a time and status dimension table. The relationship between incidents, time and status allows storing the state of every incident and for every month. And an incident can have several states assigned for a month.

The result is that i have the same incident_id multiple times in the fact table. (differently then in an ordinary star schema)

I can count the distinct number of incidents for every data point with following rule:
['number_incidents'] = C:ConsolidatedCountUnique( 0, 'Incident', '', 'All_Incidents', !Status, !Time, 'number_incidents');

But if there is measure at basis of an incident_id i am out of ideas howto handle aggregation with this ...

What are doing if u get a 'many 2 many' requirement to reproduce in TM1? Is there a way or is TM1 not the right tool?

Best regards!

[Solanna]

Maybe I'm not understanding your problem here, but it seems that you don't have a good understanding of multi-dimensional databases

Given your example, I would assume your cube would have the following dimensions:

Time - By Month (Jan 2012, Feb 2012, Mar 2012, etc.)
Incident Type - Incident A, Incident B, Incident C, etc. which rolls up to All Incident Types
Status - Status 1, Status 2, Status 3, etc. which rolls up to All Statuses
Measures - number_incidents

As you load your data you will accumulate 'number_incidents'

So lets say you have 5 separate Incident A's that have Status 1 in Jan 2012, your cube would then show 5 'number_incidents' as the intersection of all these elements
You would not have 5 separate records for Incident A, you would have only 1 record
However if you needed to have 5 separate records by the individual ID of the Incident you could just add another dimension to the cube and have all Incident ID's roll up to All Incident ID's
Either way you will still get a total of 5 based on the query except by adding the additional dimension your data is more granular and you can see each record individually

(Note to TM1 Experts: Incident ID could also be the N level with Incident Type since it's probably a 1 to 1 relationship but I don't want to confuse the audience)

TM1 is all about many to many
Every element in any dimension can intersect with every other element in all of the other dimensions

For example, 12 Months X 3 Incidents X 3 Statuses X 1 Measure = 108 possible data points across your cube where the possiblity of a value can exist

Unless I'm completely not understanding your problem, there doesn't seem to be a problem unless of course your dimensionality has not been completely thought out

Over the years I have worked with many folks that come from a relational database background... these are the folks that typically have the hardest time understanding the intersection of data and data points

If I've missed your point please give me some additional information since this seems very TM1 101 to me

Regards,

Solanna

[sibbi82]

Hi,

I try to give you some additional information. I attached a sample model, some sample data and desirable results. Hopefully it illustrate my issue a bit more.

As long as a measure (e.g. downtime) rely on one incident_id and one incident_id can affect more services i have a summarisability problem.

As far as i know a star schema layout is not capable of doing this.

Many thanks for any hint!

[sibbi82]

Really no ideas / recommendations how to handle this in TM1?

Below link is about how other tools handly those business needs. Furthermore it describes my issues more detailed and it gives a good overview about the subject in general.
http://www.sqlbi.com/articles/many2many/

[David Usherwood]

Your reference is to a link describing a feature of MS Analysis Services which started life in MSAS2005 and has now been enhanced. Sounds nice, but to quote from your link:
Many other OLAP engines do not offer many-to-many relationships. Yet, this lack did not limit their adoption and, apparently, only a few businesses really require it.
and:
The theory of chaos applies wonderfully to the usage of many-to-many relationships.

TM1 doesn't support the feature you want. But if you step back from the technology, stop spouting relational and data warehouse jargon and think through the business problem, you should be able to make it work in TM1.