Problem: Facts that reside in a Dimension are not aggregating correctly in reports.
I have a fact table that has a fact that is used in a report, it auto aggregates perfectly. I have a dimension joined to that fact (1 to 1 relationship) with 4 facts in it. Those facts do not auto aggregrate correctly. They seem to total for all the data in the report as if the SQL was sum(hours) for report. If I change the properties for the 4 facts from the dim to unsupported for the auto aggregation and then use the aggregate function in report studio, the numbers then come back perfectly. But I don't like that solution. It should work the other way.
Any thoughts? Let me know if I was not clear enough.
Not any help here, but if the performance is not bad, anything working is a solution.
If you have a true 1:1 relation then remodel the objects into one model query subject.
Fact -Dimension should be N:1 or N:0
I actually did merge the two together and the values came back as expected. But it just doesn't make sense to me that it wouldn't auto-aggregate correctly with the normal join. I checked the SQL in both cases, fields from my fact table would have sum(measure) the fields from my dimension wouldn't have a sum before I merged the two together. After I merged them, you saw that both fields were being SUM'd.