I'm a bit struggling with SSAS and it's about a couple of measures with datatype 'bit' in my SQL Server fact table. At first i thought that i could add them up in SSAS with aggregatefunction 'sum'. Well, that doesn't seem the case here. An error occurred: "bit fields aren't additive". So then i changed the datatype to tinyint and when i processed the cube, the following error occured:
"Errors in the metadata manager. The data type of the 'field' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count "
And i changed the datatype to tinyint and then i got really strange values like -93. Stange because there were only 0 and 1 values in this field. Reading a blog of Darren Gosbell tells me that the -93 is probably an overflow of the datatype. Then i changed the datatype to Integer and still i got errors like the one above (in red). Then i noticed more properties : source.
It seems there is another datatype property at a measure and it's defining the source attribute. When i changed that to integer i got the correct values. So there are four places where you can see and/or set datatypes:
- SQL Server table.
- Datasource view.
- Measure (Source group properties).
1 and 2 are quite obvious. Option 3 is also an option i do understand. You need to set a datatype in your cube when you roll up values from tinyint datatype to integer or perhaps to bigint. But the fourth option is less self explainable. So what is the role of this property group "source"? Is it a sort of CAST operation of the sourceattribute? Looking in on MSDN states that the source properties are describing the source of the measure. Litterally that doesn't seems to me a true statement because when i change the source attribute to the similar datatype in the dsv i got an error.
Below you can read my steps to glory:
- Change the field in the fact table to Tinyint
- Change the aggregatefunction to sum.
- Change the property datatype to integer
- Change the property datatype of the grouped properties 'source' to integer
I've posted a question on the MS SSAS forum about this issue and when i got a satisfied answer i will post it here on my blog.