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!


Comments

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...

Satellite Assembly in C#

Satellite Assemblies Resource files in Dot Net Resource files are typically used to store any resources in your Dot Net application. Resource file allows you to store images, icons, audio, files, strings and other types of resources. Of cause it is not mandatory to keep them inside a resource file. But when it comes to localization it is recommended to work with the resource file. localization Which means based on the user's language and culture change the application and the resources. How to archive localization with resources? Seems to be an impossible task. But to make the impossible possible, Dot Net provides you a special assembly called "Satellite Assembly" Satellite assemblies are assemblies that containing resources specific to a given language and culture. Using satellite assemblies, you can place the resources for different languages in different assemblies, and the correct assembly is loaded into memory only if the user selects to view the ...

Setup Azure AD B2C: Identity providers & Policies

This blog post will be the second part of the Setup Azure AD B2C blog series. From this blog post, we will be able to set up identity providers and policies to the Azure B2C AD. Identity providers An identity provider (abbreviated IdP or IDP ) is a system entity that creates, maintains, and manages identity information for principals while providing authentication services to relying applications within a federation or distributed network Azure AD B2C - Identity providers Azure supports many identities such as social, enterprise, or local account identities in B2C AAD. The following image shows some of the identity providers that currently B2C AAD support. As you can see AAD B2C Supports numerous identity providers. Except for Local account every other identity provider, you have to configure manually. Configure Social IDP In order to use social identity providers, we need to configure them first. Different identity providers use different ways to configure IDP. So ...