Version 2021
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 Help.
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 |
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
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 |