Data Types » Mapping of external data types to MicroStrategy data types

Mapping of external data types to MicroStrategy data types

When you create a project and add tables from your data warehouse to the MicroStrategy Warehouse Catalog, MicroStrategy automatically maps the columns within those tables to MicroStrategy-specific data types. Each column from your database becomes associated with a MicroStrategy data type.

This database data type to MicroStrategy data type mapping is necessary, in part, because each database names data types in different ways. Data types that may be conceptually the same can have different names. Therefore, MicroStrategy must map every column brought into the project schema to an internal data type.

Suppose you add a table to the Warehouse Catalog. In your relational database, a column within that table has a data type of “SMALLINT.” MicroStrategy maps this column to a MicroStrategy-specific data type, for example, “INTEGER.” This allows MicroStrategy to maintain a consistent SQL generation process.

The MicroStrategy data type stores data values internally and in the metadata repository and is later used during SQL generation when defining intermediate tables, and data mart tables, and generating the correct syntax for literals. The data type is also used whenever multi-pass SQL is used, as with custom groups. For more information about data marts and custom groups, see the Advanced Reporting Guide.

The table below lists the supported data types for supported databases as well as the MicroStrategy data type that is used to define the data in MicroStrategy. For information on MicroStrategy data types, see MicroStrategy data types.The databases that are listed in this table include:

DB2
Generic
Informix
MetaMatrix
MySQL
Netezza
Oracle
PostgreSQL
SQL Server
Sybase
Sybase IQ
Teradata

Database

Supported database data types

MicroStrategy data type

DB2

BIGINT

Big Decimal

BLOB

LongVarBin

CHAR

Char

CHARACTER

Char

CLOB

LongVarChar

DATE

Date

DEC

Numeric

DECIMAL

Numeric

DOUBLE

Double

DOUBLE PRECISION

Double

FLOAT

Double

GRAPHIC

NChar

INT

Integer

INTEGER

Integer

LABEL

VarChar

LONG

VarChar

LONG VARCHAR

VarChar

LONGVAR

VarChar

NUM

Numeric

NUMERIC

Numeric

RAW

VarBin

REAL

Real

SMALLINT

Integer

TIME

Time

TIMESTAMP

Timestamp

TIMESTMP

Timestamp

VARCHAR

VarChar

VARGRAPHIC

NVarChar

Generic

BIT

Binary

BIT VARYING

VarBin

CHAR

Char

CHAR VARYING

VarChar

CHARACTER

Char

CHARACTER VARYING

VarChar

DATE

Date

DECIMAL

Decimal

DOUBLE PRECISION

Float

FLOAT

Float

INT

Integer

INTEGER

Integer

NUMERIC

Numeric

REAL

Real

SMALLINT

Integer

VARBIT

VarBin

VARCHAR

VarChar

Informix

BOOLEAN

Char

BYTE

LongVarBin

CHAR

Char

CHARACTER

Char

DATE

Date

DATETIME

Timestamp

DATETIME HOUR TO SECOND

Timestamp

DATETIME YEAR TO SECOND

Timestamp

DEC

Decimal

DECIMAL

Decimal

DOUBLE PRECISION

Double

FLOAT

Double

INT

Integer

INT8

Big Decimal

INTEGER

Integer

LVARCHAR

LongVarChar

MONEY

Numeric

NCHAR

NChar

NUMERIC

Decimal

NVARCHAR

NVarChar

REAL

Real

SERIAL

Integer

SERIAL8

Integer

SMALLFLOAT

Real

SMALLINT

Integer

TEXT

LongVarChar

VARCHAR

VarChar

MetaMatrix

BIGDECIMAL

Numeric

BIGINTEGER

Integer

BLOB

VarBin

BOOLEAN

Binary

BYTE

Integer

CHAR

Char

CLOB

VarChar

DATE

Date

DOUBLE

Double

FLOAT

Float

INTEGER

Integer

LONG

Integer

SHORT

Integer

STRING

VarChar

TIME

Time

TIMESTAMP

Timestamp

MySQL

BIGINT

Integer

BINARY

Binary

BIT

Unsigned

BLOB

LongVarBin

CHAR

Char

DATE

Date

DATETIME

Timestamp

DECIMAL

Decimal

DOUBLE

Double

ENUM

Char

FLOAT

Float

INT

Integer

LONGBLOB

LongVarBin

LONGTEXT

LongVarChar

MEDIUMBLOB

LongVarBin

MEDIUMINT

Integer

MEDIUMTEXT

LongVarChar

NCHAR

NChar

NVARCHAR

NVarChar

SET

Char

SMALLINT

Integer

TEXT

LongVarChar

TIME

Time

TIMESTAMP

Timestamp

TINYBLOB

LongVarBin

TINYINT

Integer

TINYTEXT

LongVarChar

VARBINARY

VarBin

VARCHAR

VarChar

YEAR

Integer

Netezza

BIGINT

Big Decimal

BIT

Binary

BIT VARYING

VarBin

BYTEINT

Integer

CHAR

Char

CHAR VARYING

VarChar

CHARACTER

Char

CHARACTER VARYING

VarChar

DATE

Date

DATETIME

Timestamp

DECIMAL

Numeric

DOUBLE

Float

DOUBLE PRECISION

Float

FLOAT

Float

FLOAT4

Float

FLOAT8

Float

INT

Integer

INT1

Integer

INT2

Integer

INT4

Integer

INT8

Big Decimal

INTEGER

Integer

NCHAR

NChar

NUMERIC

Numeric

NVARCHAR

NVarChar

REAL

Real

SMALLINT

Integer

TIME

Time

TIMESTAMP

TimeStamp

VARBIT

VarBin

VARCHAR

VarChar

Oracle

BLOB

LongVarBin

CHAR

Char

CLOB

LongVarChar

DATE

Timestamp

DECIMAL

Numeric

FLOAT

Float

INTEGER

Numeric

LONG

LongVarChar

LONG RAW

LongVarBin

LONG VARCHAR

LongVarChar

NCHAR

NChar

NUMBER

Numeric

NVARCHAR2

NVarChar

RAW

VarBin

REAL

Float

SMALLINT

Numeric

TIMESTAMP(6)

Timestamp

VARCHAR

VarChar

VARCHAR2

VarChar

PostgreSQL

BIGINT

Integer

BIGSERIAL

Integer

BIT

Binary

BIT VARYING

VarBin

BOOLEAN

Integer

CHAR

Char

DATE

Date

DECIMAL

Decimal

DOUBLE PRECISION

Double

INTEGER

Integer

NUMERIC

Decimal

REAL

Real

SERIAL

Integer

SMALLINT

Integer

TEXT

LongVarChar

TIME

Time

TIMESTAMP

Timestamp

VARBIT

VarBin

VARCHAR

VarChar

SQL Server

BIGINT

Numeric

BINARY

VarBin

BIT

Binary

CHAR

VarChar

CHARACTER

VarChar

DATE

Timestamp

DATETIME

Timestamp

DEC

Numeric

DECIMAL

Numeric

DOUBLE

Float

FLOAT

Float

IMAGE

LongVarBin

INT

Integer

INTEGER

Integer

MONEY

Numeric

NCHAR

NChar

NTEXT

LongVarChar

NUMERIC

Numeric

NVARCHAR

NVarChar

REAL

Float

SMALLDATETIME

Timestamp

SMALLINT

Integer

SMALLMONEY

Numeric

TEXT

LongVarChar

TIME

TimeStamp

TIMESTAMP

VarBin

VARBINARY

VarBin

VARCHAR

VarChar

Sybase

BINARY

Binary

BIT

Binary

CHAR

Char

DATETIME

Timestamp

DECIMAL

Numeric

FLOAT

Float

IMAGE

LongVarBin

INT

Integer

INTEGER

Integer

LONG VARCHAR

LongVarChar

MONEY

Numeric

NCHAR

VarChar

NTEXT

LongVarChar

REAL

Real

SMALLDATETIME

Timestamp

SMALLINT

Integer

SMALLMONEY

Numeric

TEXT

LongVarChar

TINYINT

Unsigned

UNICHAR

NChar

UNIVARCHAR

NVarChar

VARBINARY

VarBin

VARCHAR

VarChar

Sybase IQ

BIGINT

Integer

BINARY

Binary

BIT

Binary

CHAR

Char

DATE

Date

DATETIME

Timestamp

DECIMAL

Numeric

DOUBLE

Double

FLOAT

Float

INT

Integer

INTEGER

Integer

LONG BINARY

LongVarBin

LONG VARCHAR

LongVarChar

MONEY

Numeric

NUMERIC

Numeric

REAL

Real

SMALLDATETIME

Timestamp

SMALLINT

Integer

SMALLMONEY

Numeric

TIME

Time

TIMESTAMP

Timestamp

TINYINT

Unsigned

UNSIGNED BIGINT

Unsigned

UNSIGNED INT

Unsigned

UNSIGNED SMALLINT

Unsigned

VARBINARY

VarBin

VARCHAR

VarChar

Teradata

BLOB

LongVarBin

BYTE

Binary

BYTEINT

Integer

BYTEINTEGER

Integer

BYTES

Binary

CHAR

Char

CHARACTER

Char

CHARACTERS

Char

CHARS

Char

CLOB

LongVarChar

DATE

Date

DEC

Decimal

DECIMAL

Decimal

DOUBLE PRECISION

Double

FLOAT

Double

INT

Integer

INTEGER

Integer

LONG VARCHAR

VarChar

NCHAR

NChar

NVARCHAR

NVarChar

NUMERIC

Decimal

REAL

Double

SMALLINT

Integer

TIME

Time

TIMESTAMP

Timestamp

VARBYTE

VarBin

VARCHAR

VarChar