Help with table schema

[hopkinsc]

Hi all,

I am having trouble with a loop in my table schema and i can't seem to figure out how to correct it.

The table 'Case Volumes' has 2 fields that join to the other tables. I need both of these fields though to enable me to get the correct data.

SubCat3 links to the Dimension table, this links the other fields in the Case_Volumes table to a subcat.

Year links to the Calendar and Sales table (Fact), i need the year field in the Case_volumes table as the table will contain data for multiple years.

Obviously, when a year is selected by the user i need the correct data in the Case_Volumes table to be filtered.

Does anyone have any ideas?

[Or]

On paper, the "trivial" approach would be to pull Dimension twice - once as Sales_Forecast_Dimension, and once as Case_Volumes_Dimension. This would break the loop, but it does mean all of your fields will be present twice and you will have to pull the correct ones based on context.

Another potential approach is to concatenate Case_Volumes into Sales_Forecast. I'm not sure what the data represents, so I have no idea if this is feasible for you, but it would break the loop.