I am adding in a couple of new tables and I am stuck on the scripting because of loops and at a loss for how to fix it. I have just added the RPT_EHRG_BUILD_HOURS, GovernanceCheckbook and xwalk tables. The problem is that the xwalk is needed because some of the Governing Groups in the Governance Checkbook table are not a 1:1 match for the PRIMARY_DEFINE_BODY in the Fact table called EHRG_TICKET.
MY QUESTION: How can I edit the script to use the PRIMARY_DEFINE_BODY from the EHRG_TICKET table to do some logic to determine the applicable GOVERNING_GROUP (from the xwalk) and then write it as a new field to the RPT_EHRG_BUILD_HOURS table?
left join (RPT_EHRG_BUILD_HOURS) LOAD distinct (DEFECT_ID),[Governing Group]&'-'&monthstart(month) as %GG_MONTH_KEY Resident EHRG_TICKET;
One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. Loop(s) will be cut by setting one or more tables as loosely coupled.
For example: ticket 814 should be Nursing Practice but the Governing Group in the top table shows No Define Needed, which isn't correct.
I have an idea. If I can omit the xwalk and edit the script for the Build Hours table to add a field that shows the applicable Governing Body based on the Primary Define Body in the EHRG TICKET table, that might work. I am not sure how to do that.
Ideally, your data model should be a star. however, due to 1-to-many relationships, you may be forced to snow flake. if you can collapse the tables well and good. but that is not your problem. again your problem if PrimaryDefineBody is on two associated tables which causes the link. the first question to ask is should that field be associated to defect id or governing body? if it is associated with defect id then just drop it from xwalk. if for some reason you still need it (maybe you need to compare?), just rename it in xwalk. and you have a simple change (i think).
collapsing the tables in an existing data model would require more testing:
binary [gov checkbook grp.qvw];
load PRIMARY_DEFINE_BODY as PRIMARY_DEFINE_BODYFromxwalk, [Governing Group]
drop table xwalk;
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.