Solution to Difficult Question

[Anonymous]

Hi,

I will try and keep this as simple as possible.  I have the below datamodel.

I have two seperate sources of data.  And the only link i could establish between them was a FinancialYearMonth key that i created.

The issue is , that the relationship is not good enough , and creates problems within my data when calculating formulas.

The conflict between two dimensions that are not related is the cause of the issue.

The calculation i use is the below: however i need to present it in a straight table using the Product group as a dimension.

My set analysis is as follows : Sum({$<Category = {'Flour'}>}Amount)

This means that without a decent relationship between the values, the results are invalid.

Can anyone recommend a solution to the problem, the issue here is, i wouldve liked to create a unique key ,consisting of FinYear&Month&Productgroup as Key but the dates are all in the calendar table, and product group is in a seperate table altogether.

If i had done this, the link would be easy, as in the other table, the dates and classification are in the same table, meaning that FinYearMonth&Classification would be easy to accomplish.  Bare in mind that classification and product group are the same.  i just need the relationship to be on the level of product group.

Hope the above provides enough detail.  I was thinking a link table but as I have never used one, im not sure whether that solution would be applicable with the above.

Any advice or help would be appreciated.

Regards,

[sunny_talwar]

You def. need a link table here... but before you do that, you will have to pull the above fields in a single table so that you can create a link table... or you can add Join FinMonthYear within DimInventory and create a keyfield within it and do the same on the FINALGL side and then break the join between calendar and FINALGL and link FINALGL and DimInventory

[Anonymous]

which two tables should i concatenate?

The FINAL GL along with the Salesfact?

[sunny_talwar]

Not concatenate, but link FINAL GL and Dim Inventory (ProductGroup sits in this table, right?)

[Anonymous]

Thats correct , yes it does.  However, there is no way to link these two tables.  See the Diminventory is crucial for the salesfact information.

[sunny_talwar]

I guess then bring ProductCode into SalesFact from DimInventory and bring FinMonthYear from calendar and then link SalesFact to FINAL GL by creating a key field?