Circular reference through four tables

[thomasmercer]

Trying to add a new dimensions and measure across an existing data set but I can't for the life of me figure out how to do so without creating a circular reference. The data set pre-change works without issue as follows:

What I want to do is add some additional reporting off the existing schema which is able to get measures from extracted job data (workflowcode-procindex) as well as by fund database (database_code). New tables are in the red boxes:

This worked fine when putting my SLA dashboard but then I realized it completely breaks job source and failure config.

I can't wrap my head around how to fix this as the SLA data is meaningless without both workflow and Database information to cut it against.

Any ideas?

Cheers

Thomas

[miskinmaz]

Hi,
You can do one of the following:
1. You can remove the unnecessary fields which are a part of a circular reference.
2. You can create a composite key to link tables.
3. Follow link table schema.

[thomasmercer]

Thanks for your response Miskinmaz but I still haven't been able to figure this out.

---

1. You can remove the unnecessary fields which are a part of a circular reference.

I feel like all the fields I'm using are necessary and required to be linked but happy to be challenged.

• Workflowcode needs to be used to identify the type of job for analysis
• Fundid is a subset of the database code (multiple fundid to one database code but not the other way around).

---

2. You can create a composite key to link tables.

---

I want to measure/record in SLA table at database level but my 'main' output (Extracted job data) is at fundid level. I'm not sure how/where to create a composite key with Extracted job data and SLA config having different fields

---

3. Follow link table schema.

---

I've done a bit of reading on link table schema but each time I try to do so I still end up with a circular reference

More thoughts and ideas are much appreciated! Cheers

[Vegar]

You need to make datamodell changes in some what, you need to decide where the to Job Config is to be done. When decided you will need to either remove the OR rename the workflowcode-procindex field where you want to break the connection, I assume in the new SLA table.

If you still are interested in gettinring the job config information to the SLA data you could add the Job Config table again with slightly current field names where the renamed workflowcode-procindex is identical to the field name in SLA table. 

Edit 06:07: added the last paragraph to my answer.

[thomasmercer · Accepted solution]

Thanks for the advice. In the end it was all a bit much so I've decided to instead store SLA Config at a fundid level (rather than database code level) and link in directly to extracted job data. This turns the data model back into a nice snowflake:

Cheers