MeasureGroups at different granularity in the UDM

We have run into an issue with Report Builder and was wondering if anyone else has experienced this problem. We have two different measure groups. The first group is transactional at the daily grain and the other is an accounting balance table at the month grain. The measures in the second group are semi-additive. Everything against the cube is working fine. However, when a report model is generated against the cube, queries cannot be built between the semi-additive measures and the date dimension. The transactional table is fine. Has anyone else seen this behavior? I should also note that the month key is the same key as the last day in the month for business reasons. Month account balances should be converted to foreign currencies on the basis of the month end conversion rate and we are only tracking a single conversion rate table. We could always redesign this if needed.

I’m afraid that Report Builder models over a cube can only expose relationships from a measure group to a dimension that are at the key granularity. Other relationships (from the accounting balance measure group in your case) are ignored. The only alternative is to model in the cube as being at the day level (related to the last day of the month), though this would effect how it is seen through other clients.

|||

Thanks for your answer even that's not the answer I was hoping to get. I originally had the Account Balance measure group associated with the last day of the month but the query performance was very poor. By changing the association to the month level, a representative query that was running for minutes returned in less than a second. Even with a NonEmpty crossjoin of six levels, one with 130,000 members and another with 5,000 members! Gee, think AS 2005 is a bit of an improvement over AS 2000? Therefore, switching back is not an option.

Any chance this will be fixed in the future? Because I actually think the interface for Report Builder is more intuitive than ProClarity. We just haven't really found an Adhoc query tool for AS that we really like yet.

sql