Data Types

This section describes the data types supported by SQL Server Analysis Services (SSAS) for use in multidimensional models, tabular models, and various scripting languages like MDX and DAX.

Understanding SSAS Data Types

Analysis Services uses a rich set of data types to represent information within your models. Choosing the appropriate data type is crucial for efficient storage, accurate calculations, and optimal performance. Data types can influence how values are stored, how comparisons are made, and the operations that can be performed on them.

Common Data Type Categories

Numeric Data Types

These types are used for numerical values. They vary in precision and range.

Type	Description	Notes
Integer	Whole numbers.	Supports various sizes (e.g., 16-bit, 32-bit, 64-bit).
Decimal	Fixed-point numbers with a defined precision and scale.	Ideal for financial data where exact representation is critical.
Float	Approximate-point numbers.	Uses IEEE 754 standard for floating-point arithmetic.
Currency	Fixed-point numbers with a fixed scale of 4 decimal places, suitable for monetary values.	Often used in conjunction with Decimal.

Date and Time Data Types

Used for storing date and time information.

Type	Description	Notes
Date	Represents a calendar date.	Stores year, month, and day.
Time	Represents a time of day.	Stores hour, minute, second, and fractional seconds.
DateTime	Combines date and time information.	Provides a comprehensive timestamp.

String Data Types

Used for storing textual data.

Type	Description	Notes
String	Variable-length character data.	Supports Unicode characters.
Text	Large variable-length character data.	Suitable for very long strings.

Boolean Data Types

Represents logical true or false values.

Type	Description	Notes
Boolean	Represents a true/false value.	Often used for flags or status indicators.

Binary Data Types

Used for storing raw binary data.

Type	Description	Notes
Binary	Variable-length binary data.	Can store images, documents, etc.
Blob	Binary Large Object.	For storing very large binary data.

Special Data Types

Includes types specific to Analysis Services functionality.

Type	Description	Notes
UniqueIdentifier	Globally unique identifier (GUID).	Used for primary keys and unique identification.
Variant	Allows a column to store data of different types.	Use with caution due to potential performance implications.

Data Type Mapping

When importing data from external sources (like SQL Server databases), Analysis Services maps the source data types to its internal data types. It's important to be aware of this mapping to ensure data integrity and prevent unexpected behavior.

Using Data Types in MDX and DAX

MDX and DAX functions often operate on specific data types. Understanding these types is crucial for writing correct and efficient queries. For example, mathematical operations are typically performed on numeric types, while string manipulation functions work on string types.

-- Example MDX query fragment
SELECT
    {[Measures].[Sales Amount]} ON COLUMNS,
    {[Product].[Category].Members} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2023]);

-- Example DAX measure
Sales Amount = SUM(Sales[SalesAmount])

Further Reading

• MDX Syntax Reference
• DAX Syntax Reference