SSAS – Negative total value

We encountered a strange issue when connecting to analysis services through Excel where all of the row level sums were correct, however the total value was a negative, as per below:

As you can see the row level sums are all positives, but the grand total is a negative, why?

The underlying data is all positive, however the underlying datatype in the database is a small int, however there are 6 billion rows in the table.

The issue is that when SSAS sums those 6 billion rows of small int values the total ends up breaking the INT barrier (2,147,483,647) which Excel displays as a negative.

The measure group will inherit its data type from the underlying view and attempt to use that:

To fix this issue you need to alter the data type in the Source and then either leave the DataType in Advanced as Inherit, or change it to BigInt:

Now when you deploy and process your cube, the issue should be resolved!

SSAS Tabular – Renaming A Cube

Lots of searching with no results on how to rename a SSAS Tabular cube! So here’s how:

1: In SSDT > DEBUG > <Database Name> Properties

Step 2: Change your cube name!

Not the easiest place for it, but we get there in the end..

Connecting to Analysis Services through Excel

Just a quick one with some pretty pictures..

Step 1: Select a nice blank cell in Excel (B4 for instance)

Step 2: From the Data tab on the ribbon, select From Other Sources > From Analysis Services

Step 3: Enter the name of the server you want to connect to

Step 4: Click Next and then select the name of the Cube to which you want to connect from the dropdown

Step 5: Select the location of your new pivot table

Step 6: click OK and Robert’s your Mother’s brother.