Skip to main content

SSAS: Picking The Right Data Types For The Measures

Symptoms

You don't see what you expect to see. As an example, you get a negative amount for aggregation in an INT type Measures even though it only contains positive values. To explain this I'll take above mention example.

Cause

This is most likely caused by the capacity insufficiency of INT data type.

Resolution

Update Measures' data type to BIGINT (Since that will be the next large best datatype for INT).

Into the Depth

When the data warehouse fact table contains records like below,
Record # Amount Month_Key Year_Key
Record 1 500000000 2016/Jan 2016
Record 2 500000000 2016/Feb 2016
Record 3 500000000 2016/Mar 2016
Record 4 1000000000 2016/Apr 2016
Record 5 1000000000 2016/May 2016
Record 6 100000000 2016/Jun 2016
Record 7 500000000 2016/Jul 2016

Querying amount for each month will show positive values 

 The amount for January 2016
2016/Jan   500000000

 The amount for May 2016
2016/May   1000000000

 But when querying for the year 2016 result will give a negative value
2016           -32215793781

 This is caused because of the insufficient capacity in the datatype given for the Amount measure. Once you assign the right datatype instead of the minus value it will show the right value. 

 Don't just pick datatype by looking at the fact table. Always consider the aggregate value of the measure, if it exceeds the assigned datatype's capacity. Go to the next large best datatype.

 So picking the right data types for the measure is important :)

 May the force be with you!

Labels:

Comments

Post a Comment

Popular Posts

SSAS: The Sort Order Specified For Distinct Count Records Is Incorrect.

Symptoms During a processing time of a cube that contains a distinct count measure, the process fails with the following error message: The sort order specified for distinct count records is incorrect Cause This is most likely caused by different sort order used on the data warehouse data set and the analysis service database data set. Resolution Modify the sort order of the data warehouse data set so that it will match with the analysis service database data set. Into the Depth  Navigate to the analysis database and start to process the desired database. When the processing begins, the Process progress window will pop up. Wait till the process gets failed. After the process failed, find the failing measure group and expand to the last node where you can see an SQL query. Double click on the query and view the details. This query gives you the exact order by the column which caused you this trouble.  Execute the query using the data warehouse datab...
Post a Comment
Read more

Dynamic Assembly in C#

Static Assembly Vs Dynamic Assembly Static Assemblies are those assemblies which are stored on the disk permanently as a file or set of files. Since these assemblies are stored in the disk, those are only loaded when CLR requests.  These are the assemblies we are dealing with daily. Assemblies that I'm going to talk about today bit different. It completely opposite of the Static Assemblies. Those Assemblies are not stored on the disk before execution. When an application requires any type, which references from these assemblies, DOT NET will create these Assemblies at the runtime so that it will directly load into the memory. Why is it important ? Like I mentioned, this is not something we do very often. It is not all about how important it is. Personally, I think it is better to know this kind of hidden language features. More you play with this more you learn. I found cool stuff I can do with this. Hope it will be same for you as well. This is an old feature. How ...
Post a Comment
Read more

global.json file

  Symptoms Suddenly you get build errors in your build pipeline which perfectly builds in your local machine 🤔🤔🤔 Cause Most likely the root cause is .NET SDK versions. Your local machine builds on a different SDK and the pipeline builds on a different SDK. Resolution The ideal way of solving this is to force everyone to use one define .Net SDK. Regardless of whether it is a developer or CI pipeline. Simply to make this happen .NET  provides you a file called "global.json". The global.json file allows you to define which .NET SDK version is used when you run .NET CLI commands. Selecting the .NET SDK version is independent from specifying the runtime version a project targets. For information about specifying the runtime version instead of the SDK version, see Target frameworks . If you always want to use the latest SDK version that is installed on your machine, no global.json file is needed. Into the Depth The sample global.json file looks like the below, {    ...
Post a Comment
Read more