topic Re: Circular Reference between 2 Fact Tables in App Development

Circular Reference between 2 Fact Tables

zahidrahim_ocp — Sat, 16 Nov 2024 05:05:12 GMT

Hello,

I want to join two fact tables (sale and targets) using two keys but it is making a circular reference.

qvf is attached for your reference.

Please help.

Regards,

Zahid Rahim

Re: Circular Reference between 2 Fact Tables

petter — Fri, 02 Aug 2019 10:59:37 GMT

You already seem to have a valid data model with no circular reference connecting the Sales and Target tables in a valid way... So could you explain a bit more about which fields you need to use and how the tables would have "new" fields to enable maybe a target on a customer level?

Re: Circular Reference between 2 Fact Tables

zahidrahim_ocp — Fri, 02 Aug 2019 11:12:21 GMT

I want to join the date as well as i show in new attached file. So that sale and target can be seen against one month.

Re: Circular Reference between 2 Fact Tables

martinpohl — Fri, 02 Aug 2019 11:34:37 GMT

Hi,

if you want to compare different fact values (actual and budget) you have to load them into ONE fact table.

You can achieve this by concatenate (table) load

In your case, you have actual on customer an target on territory. This won't work by concatenate only.

So you have to load actual, join territory by customer into actual table and then concatenate load target.

then you will have one table with

date

customerid

territoryid

actual

budget

and you can create a chart with actual and target by territory.

Regards

Re: Circular Reference between 2 Fact Tables

zahidrahim_ocp — Fri, 02 Aug 2019 11:45:08 GMT

Thank you Martinpohl,

Can i achieve the same using Link table as in real scenario i have many dimensions linked with both fact tables. so i made a simple one just to understand the solution to such problems.

Taking so many tables in one using concatenate also reduces performance.

Regards,

Zahid Rahim

Re: Circular Reference between 2 Fact Tables

martinpohl — Fri, 02 Aug 2019 12:48:29 GMT

Hi,

see attached a very simple example

Regards

Re: Circular Reference between 2 Fact Tables

marksouzacosta — Wed, 29 Jul 2020 18:59:05 GMT

Hi,

We just released a new video in our YouTube channel related to your question.
I hope it helps!

IPC Tips - Removing Circular References
https://youtu.be/s-QA8fbQDC4

Regards,

Mark Costa