I'm no expert in MSAS Cube so may be this is obvious, but this is blocking an important feature in our team.
We have a fact table of "Indicators" (basicaly values from a calculator), that are computed for a specific date. indicators have a versionId, to group them following a functional rule.
It goes like :
From Date, Value, NodeId, VersionId
D0 - 1.45 - N2 - V0
We have a fact table of "VersionsAssociation" that lists all the versions (the very same versions as the ones in the "Indicator" fact table) that are valid and visible and for what date.
To fit with a customer need, some versions are visible at multiple dates. For instance, a version computed for date D0, may be visible/recopied for date D1, D2, ...; so for a specific version V0, we would have in "VersionAssociation" :
VersionId , Date From (computed), Date To (Visible at what date)
V0 - D0 - D0
V0 - D0 - D1
V0 - D0 - D2
V0 - D0 - D3
...
In our cube model, "Indicators" facts have a "From Date", the date they are compute for, but no "To Date", because when they are visible is not up to the indicator, but rather decided by the "VersionAssociation".
The means that in our "Dimension Usage" panel, we have a many-to-many relation from "Indicator" pointing to "VersionAssociation" on the dimension "To Date".
So far, this part works as expected. When we select "To Date" = D1 in Excel, we see indicators recopied from D0, with right values (no duplicate).
Then we have a thing called projection, where we split an indicator value alongside a specific dimension. For that we have a third measure group called "Projection", with values called "Weight".
Weights have a "To Date", because the weight are computed for a specific date, and even if an indicator is copied from D0 into D1, when projected, it is projected using D1 Weights.
Also we duplicate the weight regarding all the available from date, that's strange, but without it, the result are pure chaos.
Meaning we would have in the weights:
NodeId,From Date, To Date, Projection Axis, Weight
N2 , D0 , D0 , P1 , 0.75
N2 , D0 , D0 , P2 , 0.25 (a value on node N2 would be split into 2 different values, where the sum is still the same)
N2 , D0 , D1 , P1 , 0.70
N2 , D0 , D1 , P2 , 0.30
Here goes the issue:
To apply the Projection weights, we use a measure expression on the mesures from the "Indicator" Measure group, having something like "[Value Unit] * [Weight]".
Because of reasons, this causes MSAS to not properly disciminate the weight that are eligible to apply to a certain value in the "Indicator" measure group.
For instance, if we look into excel and ask for the D1 date (same behavior for all date), on the Projection Axsi P1 we got :
Value Weight
1.45 * 0.75 (Weight: From Date D0, To Date D0, P1)
+ 1.45 * 0.70 (Weight: From Date D0, To Date D1, P1)
for D1 and P2 we have :
Value Weight
1.45 * 0.25 (Weight: From Date D0, To Date D0, P2)
+ 1.45 * 0.30 (Weight: From Date D0, To Date D1, P2)
This cause the values to mean nothing and be non readable.
So what all of this is for, is to ask for a way to limit the weights that can be applied in the measure expression. We tried to use scope on "From Date" , "To Date" with the "Weight" measure or the "Value" measure, but the cube never step in our SCOPE instructions.
This is very long, and complicated, but we're stuck.
For closure, as it turns out the behavior expected is not possible (as far as out team tried). so we reverted to merging two of the 3 tables together, and ahving only one many-to-many join in the measure groups.