issue with intervalmatch and joining / datamodel

[amien]

I have a fact table that contains two intervalmatch in the loading script. In the fact table i have two column : GL_account1, GL_account2. Intervalmatch1 takes GL_account1, intervalmatch2 takes GL_account2.

To prevent synthetic keys, each intervalmatch has it's own table, which is linked using the specific column (based on GL_account1 or GL_account2)

See screenshot for data model. Pivot looks great and the values are correct.

The intervalmatch inline contains an overlap of GL accounts, like:

inline:

costs I,  100, 200

costs II,  100, 300

etc.

If i left join the result of the intermatch to my fact table, the values of the pivot are not correct anymore. Seems like this is caused by the overlap of the intervalmatch. So, I choose to link the two 'GL_account descriptions' (result of intervalmatch) tables instead of a left join.

But not comes the issue:

I want to add a certain company to the datamodel. This data does not need a intervalmatch because it's precalculated (there is no GL_account)

So my source looks like this:

GL_account_description, Company, Value.

If the intervalmatch tables are joined with the fact table, this would not be a problem. I can just concatenate the additional datasource to the fact table.

Problem is that GL_account_description can't be concatenated, because it's in a different table. If i can join the two intervalmatchs into the fact table, than i don't have this problem.

anyone knows how i can solve this issue?

thank in advanced!!!

[johnw · Accepted solution]

So most companies have one GL_Account_ID assigned to each.  However, we may have multiple GL_Account_Descriptions for each GL_Account_ID (based on ranges of IDs).  Also, some companies have no GL_Account_ID, and ONLY have a single GL_Account_Description.

If so, then instead of linking by GL_Account_ID, I would link them by Company_ID (or whatever you call it).  Left join the Company_ID onto the table that contains the ranges and GL_Account_Description.  Now drop the GL_Account_ID from that table.  Now concatenate new lines to that table for the companies that map directly to a GL_Account_Description.  You want to end up with something like this:

Descriptions:
Company_ID, GL_Account_Description

Main Data:
Company_ID, GL_Account_ID, Finance.Amount, %calendar_key, etc.

The ranges and interval match, then are only a temporary measure taken during the load in order to assign one or more GL_Account_Descriptions to each company.  But the description table must remain separate since there is a one to many relationship here (at least for some companies).

Of course, I may have completely misunderstood the problem.

[amien]

Thank again John .. it solved my issue