Search code examples
olapssas

Measure names containing "Total" have strange grand total calculation in cubes


In programmatically building cubes for SQL Server Analysis Services using AMO, I've discovered that when a measure has "Total" in it's title, the grand total in the cube is calculated by a distinct sum instead of just a sum (creating very strange results)

This doesn't occur when building cubes using DSO. Does anyone know of why this could be happening?

Please pardon my use of python:

class MeasureSpec(MeasureSpec):
    def create(self, measureGroup, cube, dsv, factTable):
        log("creating measure:", self.name)
        measure = measureGroup.Measures.Add(self.name)
        measure.AggregateFunction = self.aggregateFunction
        measure.FormatString = self.format
        # Set datatype to integer for counts otherwise this is set to the same
        # type as the source column in createDataItem
        if self.aggregateFunction in (aggCount, aggDistinctCount):
            measure.DataType = MeasureDataType.Integer
        measure.Visible = self.isVisible
        measure.Source = createDataItem(dsv, factTable, self.column.getColumnName())

Solution

  • Here's what was going on. AMO was tagging the datacolumn as byte for anything with Total in the name. It was cycling to 32 which amazingly was the same number as a distinct sum on the column... Wow.