topic Re: Two Key Field more Tables without Circular Loop in App Development

Two Key Field more Tables without Circular Loop

TakacsZsolt — Sat, 16 Nov 2024 04:04:21 GMT

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!

Re: Two Key Field more Tables without Circular Loop

marcus_sommer — Wed, 20 Nov 2019 13:19:52 GMT

The easiest way is not to join and/or to associate both fact-tables else to concatenate (UNION in sql) them.

- Marcus

Re: Two Key Field more Tables without Circular Loop

TakacsZsolt — Wed, 20 Nov 2019 13:41:09 GMT

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?

Re: Two Key Field more Tables without Circular Loop

marcus_sommer — Wed, 20 Nov 2019 13:54:06 GMT

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

Re: Two Key Field more Tables without Circular Loop

TakacsZsolt — Wed, 20 Nov 2019 16:04:27 GMT

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.

Re: Two Key Field more Tables without Circular Loop

marcus_sommer — Thu, 21 Nov 2019 08:30:43 GMT

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