Associate measure group with 2 role playing dimensions

Question

I have two fact tables – charges and payments and both of these fact tables are in the cube (as measure groups). i have one date dimension in the cube and 2 role playing date dimensions that are built off of the same date dimension:

1. charge date

2. payment date.

when i look at the data by say charge date and i filter by 2012 Jan, i want to see all the charges in 2012 Jan and all payments associated; but the cube shows all the payments that exist in the system. I have linked these two measure groups using a "transaction dimension" but that doesn't have a date.

any ideas how to get the date filter to work on both these measure group? thanks in advance!

note: a single charge can have multiple payments and vice versa. In other words, 10 different charges could have one pay date and there could be 10 payments associated with one charge..

Answer 1

Take a look at this link .

You'd need to create 2 date dimensions based on your calendar which would resemble the 2 date filters that you would need. The difference would be the relationship between the fact, and the referencing date key. This would mean that you'd require the relationship to be:

• Charges Fact : The charge date key
• Payments Fact : The payment date key

This would mean that you would have 2 date role playing dimensions that you can filter on. So in that case you would be able to filter on both dates in the charges, and also in the payments.