Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I'm searching a solution to the problem below. I built a database, and now I need to link a plan table which contains plan values. How can I do this without "loop"? I would like to filter dates, tags etc. in Analysis after.
Important: one EXP ACC LINES ID can have multiple TAG ID.
Thanks in advance!
The easiest way is not to join and/or to associate both fact-tables else to concatenate (UNION in sql) them.
- Marcus
Dear Marcus,
EXP ACC LINES has unique rows, but it may happen that one row has more TAG ID. If I use Concatenate unique rows will be multiplyed, and the consequence of this the values will be multiplyed too (SUM(NET VALUE) return 1000 instead of 500). Or am I wrong?
No, that shouldn't happens - and without merging the measure-fields into a single field (requires an extra field which identified the measure-type respectively the data-source) the measures aren't touched in any way because for the net-data you used:
sum([NET VALUE])
and for the plan-data:
sum([PLAN VALUE])
- Marcus
I did not mean that way, I don't want to join or concatenate the plan table, I just want to link it with two key field (DATE and EXP TYPE) to the right field and to the other tables without Circular Loop.
By the simple association between two tables you could just create a composite key from your multiple key-fields. But if you have two fact-tables which needs to be connected to eachother and at the same time to further dimension-tables ... it's not possible in a classical way.
A common approach to handle such a scenario is the use of a link-table - but it needs some efforts to create it and from a UI performance point of view it's often not the best choice.
Therefore my suggestion to merge the fact-tables which could be done by various join/mapping measurements (which is usually quite complex but results in the smallest datamodel and best UI performance) - or much simpler by just concatenating the fact-tables. This approach might look "ugly" from a pure theoretical datamodeling point of view (especially with a sql-background) but it's very easy and the performance is usually more as sufficient.
Should you decide against a merging of tables and also against the link-table you need in each case to consider the possibility that there are current data which have no plan data or reverse - which is a quite common use-case and which couldn't be handled in any way with associating the tables.
- Marcus