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

Measure data type from UDM to ADOMD

Ok, so here is my dilemma.

I need to get the data type of a measure in ADOMD.NET.

I have a SSAS project which deploys a cube. The measure is defined in this project file as having a FormatString of "Currency" (this is in the "Basic" section of the measure properties in Visual Studio).

I have tried to use the format_string property by using "

Code Snippet

CELL PROPERTIES FORMAT_STRING

", but this still returns "#, #" as the format string.

How do I get the data type of the measure?

Thanks

hello,

MDSCHEMA_MEASURES schema rowset has DATA_TYPE column which contains measure's the data type.

In Adomd.net you can get to this information in 2 ways: either execute schema rowset request directly (with connection.GetSchemaDataSet method), or via metadata objects. Measure object has .Properties collection, and you can find the DATA_TYPE property in it. So Measure.Properties["DATA_TYPE"].Value should contain the data type of the measure. (i think you can compare the values with System.Data.OleDb.OleDbDataType enum values), for example i can test if measure's data type is returned as currentcy with something like this:

(UInt16)measure.Properties["DATA_TYPE"].Value == (UInt16)System.Data.OleDb.OleDbType.Currency

You should be able to get to Measure object either from .Measures colleciton exposed on cube, or by

cube.GetSchemaObject(SchemaObjectType.ObjectTypeMeasure, <unique name of the measure>) as Measure

hope this helps,