• close

CME

This page lists the entities, attributes, indexes and the ER diagram for CME database.

Search
 

 Entities : 22                Attributes : 246                Indexes :56                Primary Keys : 16

Entities
T_DABT

Ask Bid or Trade indicator. There are only those three types of transactions in CME BBO database.
DK_ABT   ABT_CD   ABT_NM
0                A                Ask
1                B                Bid
2                T                Trade

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_ABT Tinyint YES YES NO DimensionKey: distinguish between Ask, Bid and Trade (0,1,2)
ABT_CD Varchar(1) YES NO NO The codes for:
Ask = A
Bid = B
Trade = T
ABT_NM Varchar(5) YES NO NO Names of the ABT codes (Ask, Bid or Trade)
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DBKI

Indicator for Book quotes. There are two kind of rows with B (book) and O (other)

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_BKI Bit YES YES NO DimensionKey: Indicator for Book quotes (0 for Book and 1 for other)
BKI_CD Varchar(1) YES NO NO Indicator for Book quotes : Code (B when book or O when other)
BKI_NM Varchar(6) YES NO NO Indicator for Book quotes: Name (Book or Other)
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DCPF

Dimension Table: Call Put or Future indicator. It holds three values:
DK_CPF   CPF_CD   CPF_NM
0                C                Call
1                P                Put
2                F                Future

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_CPF Tinyint YES YES NO Dimension Key: Call Put or Future
It holds three values:
0 for Call, 1 for Put, and 2 for Futures
CPF_CD Varchar(6) YES NO NO Code for Call Put or Future indicator (Values are: C,P,F)
CPF_NM Varchar(6) YES NO NO Name for Call Put of Future indicator (values are Call,Put,Future)
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DCTI

Dimension Table: Ticket Symbol Indicator. It contains a list of all tickers used for different instruments.There are currently 770 tickers in this dimension. Note, that some of the ticker codes can fall into multiple categories; for example OG: Gold , Goldman Sachs C.I ON:Natural Gas (Henry Hub) Physical, Nikkei-USD OS: S&P 500 , Brent Crude Oil Last Day There are currently 34 such a cases. This query displais them: select cti_cd,COUNT(*) from T_DCTI group by cti_cd having COUNT(*)>1

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_CTI Smallint YES YES NO Dimension Key: Ticket Symbol Indicator. Its numeric substitute of alphanumeric ticker code.
CTI_CD Varchar(3) YES NO NO The original code of the Ticket Symbol
CTI_NM Varchar(15) YES NO NO Underlying name of the Ticker code (not available, if you have a mapping it can be updated)
CTI_TP Varchar(15) YES NO NO Ticker type is derived column that indicates source of the ticker (Commodities dataset or BBO)
CTI_DSC Varchar(60) YES NO NO Ticker description is extracted from the path of the source file. It gives meaningful information about the ticker.
For example "Jacksonville International Airport HDD Monthly" for ticker code "VS"
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DDAT

This is Date Dimension. It stores the list of all the days within multiple years that are used in entire data warehouse. It's primary key: DK_DAT uniquely identifies each row and is used as foreign key in Fact Tables (should be used as join column). The role of the this table is to provide a set of attributes that are already computed/formatted to required shape and can be just used without spending redundant effort. One can just use YYYYMMDD attribute or DDMMYYYY without the need of using formatting functions. It also reduces effort normally needed to learn how to format in SQL. This dimension can be also linked to fact tables as role playing dimension. This means there will be few DK_DAT columns named for example DK_DAT_TRD for date of trade or DK_DAT_ETR for date of entry. In that case one has to use T_DDAT few times in join conditions:

example:

SELECT DAT_ETR.DATE_ISO AS DATE_ETR, DAT_TRD.DATE_ISO AS DATE_TRD, T_FBTF.TRD_QTY, T_FBTF.TRD_PRI FROM T_FBTF INNER JOIN T_DDAT AS DAT_TRD ON T_FBTF.DK_DAT_TRD = DAT_TRD.DK_DAT INNER JOIN T_DDAT AS DAT_ETR ON T_FBTF.DK_DAT_ETR = DAT_ETR.DK_DAT WHERE (DAT_TRD.YYYYMM = 200504)

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_DAT Int YES YES NO DimensionKey: Unique number different for every day. It is used in all fact tables and can be translated via this dimension many different formats.
DATE Date YES NO NO Date in datetime type (can be used as function parameter)
DATE_ISO Int YES NO NO Date in iso format: YYYYMMDD, sotored as integer (example 20031228 )
DATE_SAS Int YES NO NO Date in SAS format.
"The SAS system stores dates as the number of elapsed days since January 1,1960. For example, January 1, 1960 is stored as 0. December 30, 1959's SAS date equals -2, and December 31, 1960 SAS date is stored as 365"
DATE_EOM Date YES NO NO Data that indicates last day of the month
DD Tinyint YES NO NO Day part of the date (type used is integer, there is no leading zero in days <10)
MM Tinyint YES NO NO Month part of the date (type used is integer, there is no leading zero in months <10)
YYYY Smallint YES NO NO Year part of the date.
CYEAR Smallint YES NO NO Cutoff date, used in some financial applications to indicate the end of accounting year
DDMMYYYY Varchar(10) NO NO NO Date in Day Month Year format (DD.MM.YYYY) stored as text with "." as separator
YYYYMMDD Varchar(10) NO NO NO Date in Year Month Day format (YYYY.MM.DD) stored as text with "." as separator
YYYYMMDD_SEP1 Varchar(10) NO NO NO Date in Year Month Day format (YYY/MM/DD) stored as text with "/" as separator
MMDDYYYY Varchar(10) NO NO NO Date in Month Day Year format (MM-DD-YYYY) stored as text with "-" as separator
YYYYMM Int NO NO NO Year and Month of the date stored as integer
DW_SH Varchar(3) NO NO NO Short name of the day (SUN,MON,TUE,WED,THU,FRI,SAT)
DW_LO Varchar(10) NO NO NO Full name of the day (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
QQ Tinyint NO NO NO Quarter number (1,2,3 or 4)
DY Smallint NO NO NO Day of year (number from 1 to 365)
WK Tinyint NO NO NO Number of the week in year
IsWeekend Tinyint NO NO NO Indicates weekend (1 for Saturday or Sunday, else 0)
IsUSHoliday Tinyint NO NO NO Indicates US holidays (1 for holiday, else 0)
NextWD_DK_DAT Int NO NO NO This number points to next working day (DK_DAT). It helps for example to compare stock closing price of given date with next busines day.
PrevWD_DK_DAT Int NO NO NO This number points to previous working day (DK_DAT). It helps for example to compare stock closing price of given date with previous busines day.
IsCPriceDaily Tinyint NO NO NO Internal system column, please don't rely on it
IsCPriceWeekly Tinyint NO NO NO Indicates if date is closind day for week (each Friday will have 1 here, otherwise 0)
IsCPriceMonthly Tinyint NO NO NO Indicates if date is closind day for a month (30,31 and 28th of each month will have 0 here, otherwise 1)
IsCPriceQuarterly Tinyint NO NO NO Indicates if date is closind day for quarter period (last days of eavery month closing quarter will have 1 here, otherwise 0)
IsCPriceYearly Tinyint NO NO NO Indicates if date is closind day for a year period (31 of each December will have 1 here, otherwise 0)
YYMMDD Varchar(6) NO NO NO Date in YYMMDD format, type is text (there will be leading 0 for year 2000 - 2009 => 00,01,02... in YY part)
There are only days from 1950 up to 2019 in T_DDAT dimension, what eliminates the problem of ambiguity YY for 2005 vs 1905 vs 2105 years (drop first two digits and all of thouse would become YY=05)
USHolidayNM Varchar(30) NO NO NO Name of the US holiday:
January 1 New Year's Day Celebrates beginning of the Gregorian calendar year. Festivities include counting down to midnight (12:00 AM) on the preceding night, New Year's Eve. Traditional end of "holiday season."

Third Monday in January Birthday of Martin Luther King, Jr., or Martin Luther King Day Honors Martin Luther King, Jr., Civil Rights leader; combined with other holidays in several states (traditionally January 15). This year's observance: 2008 - January 21

January 20, every fourth year, following Presidential election Inauguration Day Observed only by federal government employees in Washington D.C., and the border counties of Maryland and Virginia, in order to relieve congestion that occurs with this major event. Swearing-in of President of the United States and Vice President of the United States. Celebrated every fourth year. Note: Takes place on January 21 if the 20th is a Sunday (although the President is still privately inaugurated on the 20th). If Inauguration Day falls on a Saturday or a Sunday, the preceding Friday or following Monday is not a Federal Holiday

Third Monday in February Washington's Birthday Washington's Birthday was first declared a federal holiday by an 1879 act of Congress. The Monday Holiday Law, enacted in 1968, shifted the date of the commemoration of Washington's Birthday from February 22 to the third Monday in February. Many people now refer to this holiday as "Presidents' Day" and consider it a day honoring all American presidents. However, neither the Monday Holiday Law nor any subsequent law changed the name of the holiday from Washington's Birthday to Presidents' Day.[2] This year's observance: 2008 - February 18

Last Monday in May Memorial Day Honors the nation's war dead; marks the unofficial beginning of summer. (traditionally May 30)

July 4 Independence Day Celebrates Declaration of Independence, also called the Fourth of July.

First Monday in September Labor Day Celebrate the achievements of workers and the labor movement, marks the unofficial end of summer.

Second Monday in October Columbus Day Honors Christopher Columbus, traditional discoverer of the Americas. In some areas it is also a celebration of Italian culture and heritage. (traditionally October 12); celebrated as American Indian Heritage Day and Fraternal Day in Alabama;[1] celebrated as Native American Day in South Dakota.[2]

November 11 Veterans Day Honors all veterans of the United States armed forces. A traditional observation is a moment of silence at 11 AM remembering those who fought for peace. (Commemorates the cease-fire in the 1918 armistice which was scheduled for "the eleventh hour of the eleventh day of the eleventh month.")

Fourth Thursday in November Thanksgiving Day Traditionally celebrates the giving of thanks for the autumn harvest. Traditionally includes the consumption of a turkey dinner Traditional start of the "holiday season." (Note: Thanksgiving is not celebrated on the same day as it is in Canada).

December 25 Christmas Day Celebrates the Nativity of Jesus which (traditionally) took place 25 December 1 BC. Some people consider aspects of this religious holiday, such as giving gifts and decorating a Christmas tree, to be secular rather than explicitly Christian.
*/
T_DEXC

Dimension Table: Exchange Code It holds the list of exchanges used:
DK_EXC       EXC_CD        EXC_NM
0                    CBT                Chicago Board of Trade
1                    CME               Chicago Mercantile Exchange
2                    NYM               New York Mercantile Exchange

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_EXC Tinyint YES YES NO Dimension Key: Exchange Code is unique identifier of the exchange.:
DK_EXC EXC_CD EXC_NM
0 CBT Chicago Board of Trade
1 CME Chicago Mercantile Exchange
2 NYM New York Mercantile Exchange

Join with T_FCED on DK_EXC
EXC_CD Varchar(3) YES NO NO Exchange code is : CBT, CME or NYM
EXC_NM Varchar(35) YES NO NO Exchange name is:
Chicago Board of Trade
Chicago Mercantile Exchange
New York Mercantile Exchange
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DFOI

Dimension: Future or Option Indicator. It distinguishes between those two instruments

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_FOI Bit YES YES NO DimensionKey: Future or Option Indicator. It distinguishes between those two instruments (0 for Futures and 1 for Options).
dk_foi cnt
0 134307021
1 68522571
FOI_CD Varchar(1) YES NO NO Future Option Indicator Code:
F- for Futures
O - for Options
FOI_NM Varchar(8) YES NO NO Future Option Indicator Name:
Future or Option
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DIND

The Indicators dimension contains a set of the following indicators:
ITC_SI --[Session Indicator]
ITC_IQT --[Indicative Quote Type]
ITC_MQ --[Market Quote]
ITC_COT--[Close Open Type]
ITC_VOE--[Valid Open Exception]
ITC_PC -- [Post Close]
ITC_CCT-- [Cancel Code Type]
ITC_ICT --[Insert Code Type]
ITC_FLI --[Fast Late Indicator]
ITC_CI --[Cabinet Indicator]
ITC_BI --[Book Indicator]

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_IND Tinyint YES YES NO Dimension Key: Indicators
IND_SES Varchar(3) YES NO NO The indicator for the trading session. R indicates Open Outcry, E indicates Electronic
(Globex).
IND_OAB Varchar(3) YES NO NO Open Ask/Bid Indicator Indicates that the opening price was a bid or offer. A $ is used to indicate a cabinet price.
IND_OAB_RNG Varchar(3) YES NO NO Opening Range Ask/Bid Indicator A Indicates that prices in the opening range were bids or offers.
IND_HAB Varchar(3) YES NO NO High Ask/Bid Indicator Indicates that the high price was a bid or offer. A $ is used to indicate a cabinet price.
IND_LAB Varchar(3) YES NO NO Low Ask/Bid Indicator Indicates that the low price was a bid or offer. A $ is used to indicate a cabinet price.
IND_CAB Varchar(3) YES NO NO Close Ask/Bid Indicator Indicates that the closing price was a bid or offer. An N indicates a nominal price.
IND_CAB_RNG Varchar(3) YES NO NO Closing Range Ask/Bid Indicator Indicates that the closing price was a bid or offer.
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DITC

Dimension: Indicators, Types and Codes
It holds set of the following indicators

ITC_IQT [varchar](1) NULL, --[Indicative Quote Type]
ITC_MQ [varchar](1) NULL, --[Market Quote]
ITC_COT [varchar](1) NULL, --[Close Open Type]
ITC_VOE [varchar](1) NULL, --[Valid Open Exception]
ITC_PC [varchar](1) NULL,--[Post Close]
ITC_CCT [varchar](1) NULL,--[Cancel Code Type]
ITC_ICT [varchar](1) NULL,--[Insert Code Type]
ITC_FLI [varchar](1) NULL,--[Fast Late Indicator]
ITC_CI [varchar](1) NULL,--[Cabinet Indicator]
ITC_BI [varchar](1) NULL,--[Book Indicator]

This modeling trick helped to reduce the number of dimensions of the TIC fact table, impoved performance and saved storage. Every row from T_FTIC table has a coresponding entry in this dimension and specific values for all the indicators.

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_ITC Smallint YES YES NO DimensionKey: Indicators, Types and Codes
Its unique number used in T_FTIC as join/lookup column.
ITC_SI Varchar(1) NO NO NO Session Indicator: The indicator for the trading session. R indicates Open Outcry, E indicates Electronic
(Globex).
ITC_IQT Varchar(1) NO NO NO Indicative Quote Type
ITC_MQ Varchar(1) NO NO NO Market Quote
ITC_COT Varchar(1) NO NO NO Close Open Type
ITC_VOE Varchar(2) NO NO NO Valid Open Exception
ITC_PC Varchar(1) NO NO NO Post Close
ITC_CCT Varchar(1) NO NO NO Cancel Code Type
ITC_ICT Varchar(1) NO NO NO Insert Code Type
ITC_FLI Varchar(1) NO NO NO Fast Late Indicator
ITC_CI Varchar(1) NO NO NO Cabinet Indicator
ITC_BI Varchar(1) NO NO NO Book Indicator
ITV_CNT Bigint NO NO NO Count number of transactions in T_FTIC table with fiven combination of indicator values
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DMON

Dimension Table: Month It holds a list of all months for which data is registered in the data warehouse. Its a role playing dimension, month can be delivery month, transaction month, release month etc.

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
PK DK_MON Int YES YES YES NO Dimension Key: Month is a unique number that distinguishes between months. Format is YYYYMM
YYYYMM Int YES YES NO NO Month in YYYYMM format. This is "official" attribute that explains dimension key, however for simplicity and faster queries (less joins are needed)
DK_MON has meaningful value. All role playing dimensions like DK_MON_DRY (delivery month) have also meaningful values in YYYYMM format
YYYY Int YES YES NO NO Year part of the YYYYMM
MM Int YES YES NO NO Month part of the YYYYMM
YYMM Varchar(4) NO NO NO Year and Month (2 char each)
T_DMQI

Dimension : Indicative Market Quotes It stores only two rows 0 and 1 to distinguish between Indicative Market Quotes and other.

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_MQI Bit YES YES NO DimensionKey:Indicative Market Quotes
MQI_CD Varchar(1) YES NO NO Indicator for Market Quotes:
M for Market or O for Other
MQI_NM Varchar(6) YES NO NO Indicator for Market Quotes - Name:
Market or Other
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DOPT

Option Type distinguishes between Call and Put options:
There are two entries in this dimension, where DK_OPT is :
0=call
1=put
Join with fact table on DK_OPT

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_OPT Bit YES YES NO DimensioKey: Option Type (0=call,1=put)
OPT_CD Varchar(1) YES NO NO Option Type Code (C or P)
OPT_NM Varchar(4) YES NO NO Option Type Name (Call or Put)
CRE_DAT Datetime YES NO NO
MOD_DAT Datetime YES NO NO
CRE_USR Varchar(15) YES NO NO
MOD_USR Varchar(15) YES NO NO
T_DSCN

Dimension Table: Month It holds a list of all months for which data is registered in the data warehouse. Its a role playing dimension, month can be delivery month, transaction month, release month etc.

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_SCN Tinyint YES YES NO Dimension Key: Settle Cabinet
Join with T_DFCED on DK_CED
The following formula was used for mapping: convert(tinyint,case when settle_cabinet='$' then 1 when settle_cabinet='0' then 2 when settle_cabinet='C' then 3 else 0 end) as DK_SCN,--settle_cabinet ,
SCN_CD Varchar(3) YES NO NO Settle Cabinet original code
SCN_NM Varchar(35) YES NO NO Settle Cabinet name
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DSFL

Dimension table: Source File contains a complete list of all source files that were loaded into the data warehouse. It helps to track given record source in case of suspected discrepancies

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
PK DK_SFL Int YES YES NO Dimension Key: Is unique identifier of each file from which data was loaded into the database..
SFL_NM Varchar(45) YES NO NO The name of the file (short, name.extension)
SFL_QNM Varchar(150) YES NO NO Qualified name of the file (full name drive:\path\file.extension)
SFL_TP Varchar(25) YES NO NO Source file type defines its source: Commodity TICK/EOD or BBO
SFL_SIZE Int NO NO NO
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRR_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DTIC

icker dimension holds a list of the tickers used in BBO Options table (T_FBTO).

DK_TIC TIC_CD
1 ES
2 EW
3 EW1
4 EW2
5 EW4
6 EZ

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
PK DK_TIC Tinyint YES YES NO DimensionKey Ticker is a number that can be translated to ticker code.
TIC_CD Varchar(3) YES YES NO NO Ticker code :
ES
EW
EW1
EW2
EW4
EZ
TIC_NM Varchar(15) YES NO NO The name of the ticker
TIC_TP Varchar(15) YES NO NO
TIC_DSC Varchar(35) YES NO NO The description of the ticker
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
CRE_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
MOD_USR Varchar(15) YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and performed recent update
T_DTIM

The Time dimension holds one row per second. The primary key is DK_TIM. Time starts at 00:00 with DK_TIM = 0 and 1 is added for each row/second. Having T_DTIM one does not have to use formating functions to extract second, minute of hour, they are available as attributes.

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
PK DK_TIM Int YES YES NO DimensionKey: Unique number that changes for every second. It translates 24 hours time into integers. Once sec is one incremental tick, time starts from 12:00am (24h:00s).
TIM_VAL Datetime YES NO NO This is given time value in datetime type. Can be used as time functions parameter. The date part of the type is left null (which coresponds to 1900.01.01)
TIM_INT Int YES YES NO NO Time value represented as integer. hhmmss is the format, but when leading digit is 0 it is droped (for example 05:10am becomes 510).
TIM_HH Tinyint YES NO NO Hour part of the time in integer format (without leading 0s)
TIM_SS Tinyint YES NO NO Second part of the time in integer format (without leading 0s)
TIM_MM Tinyint YES NO NO Minute part of the time in integer format (without leading 0s)
TIM_ISO Varchar(8) YES NO NO NO Time in ISO format represented as text (HH:MM:SS). Leading 0s are not droped (example:04:10:00 am will remain this way)
CRE_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and perform recent update
TIM_DAT Datetime YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and perform recent update
RDK_ADT Int YES NO NO One of the audith columns. It helps to track record creation date, last update date, user who registered the row and perform recent update
TIM_TIV_05M Int YES NO NO This is incremental indicator marks only those second that are closing 5 minutes time intervals starting from 00:00:00
TIM_TIV_10M Int YES NO NO This is incremental indicator marks only those second that are closing 10 minutes time intervals starting from 00:00:00
TIM_TIV_15M Int YES NO NO This is incremental indicator marks only those second that are closing 15 minutes time intervals starting from 00:00:00
TIM_TIV_20M Int YES NO NO This is incremental indicator marks only those second that are closing 20 minutes time intervals starting from 00:00:00
TIM_ACC_SS Int YES NO NO Integer number that ticks every second (it numbers all seconds within 24h, similarly like DK_TIM, except that its not technical internal number and will not change)
TIM_TIV_01M Int YES NO NO
T_FBTF

This table holds BBO Futures

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
FK DK_DAT_TRD Int YES NO NO DimensionKey: Trade Date
It should be joined with T_DDAT on DK_DAT_TRD=DK_DAT, however key has meaningful values in YYYYMMDD format. Its recommended to filter based on this column (instead of dimension attribute), becasue table is partitioned on it.
FK DK_MON_DRY Int YES YES NO NO Delivery Date Indicates the month the contract expires
FK DK_DAT_ENT Int YES YES NO NO DimensionKey: Entry Date, It has YYYYMMDD format. Join towards T_DDAT for other attributes.
FK DK_TIM Int YES YES NO NO Time the trade or quote was entered in the system
FK DK_ABT Tinyint YES YES NO NO Ask/Bid/Trade indicator (values are 0,1 or 2)
FK DK_MQI Bit YES YES NO NO Indicative Market Quotes (0 or 1)
FK DK_BKI Bit YES YES NO NO Indicator for Book quotes (0 or 1)
DD_TSN Int YES YES NO NO Trade Sequence Number is a sequence the quote or trade was entered into the system
TRD_QTY Int YES NO NO Traded quanty is a number of contracts available for trade or traded
TRD_PRI Numeric(6,2) YES NO NO Trade Price indicates actual price traded
T_FBTO

This table holds BBO Options

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
FK DK_DAT_TRD Int YES NO NO DimensionKey: Trade Date
It should be joined with T_DDAT on DK_DAT_TRD=DK_DAT, however key has meaningful values in YYYYMMDD format. Its recommended to filter based on this column (instead of dimension attribute), becasue table is partitioned on it.
FK DK_MON_DRY Int YES NO NO DimensionKey: Delivery Date Indicates the month the contract expires
FK DK_DAT_ENT Int YES YES NO NO DimensionKey: Entry Date, It has YYYYMMDD format. Join towards T_DDAT for other attributes.
FK DK_TIM Int YES YES NO NO Time the trade or quote was entered in the system
FK DK_TIC Tinyint YES YES NO NO Internal Ticker of the option (EW,EW1,EW4,EZ)

convert(tinyint,case [Ticker Symbol] when 'EW' then 1 when 'EW1' then 2 when 'EW2' then 3 when 'EW4' then 4 when 'EZ' then 5 else 0 end) as dk_tic ,--Ticker Symbol
FK DK_ABT Tinyint YES YES NO NO Ask/Bid/Trade indicator (values are 0,1 or 2)
FK DK_OPT Bit YES YES NO NO Option Type (0=Call or 1=Put)
FK DK_MQI Bit YES YES NO NO Indicative Market Quotes (0 or 1)
DK_BKI Bit YES YES NO NO Indicator for Book quotes (0 or 1)
DD_TSN Int YES YES NO NO Trade Sequence Number is a sequence the quote or trade was entered into the
system
TRD_QTY Int NO NO NO Traded quanty is a number of contracts available for trade or traded
STR_PRI Numeric(6,2) NO NO NO Strike price of the option as in the contract
TRD_PRI Numeric(6,2) NO NO NO Trade Price indicates actual price traded
T_FBTS

Table - Fact - BBO Transactions on S&P500 Mini

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
FK DK_DAT_TRD Int YES YES NO NO DimensionKey: Trade Date
It should be joined with T_DDAT on DK_DAT_TRD=DK_DAT, however key has meaningful values in YYYYMMDD format. Its recommended to filter based on this column (instead of dimension attribute), becasue table is partitioned on it.
FK DK_MON_DRY Int YES YES NO NO DimensionKey: Delivery Date Indicates the month the contract expires
FK DK_DAT_ENT Int YES YES NO NO DimensionKey: Entry Date, It has YYYYMMDD format. Join towards T_DDAT for other attributes.
FK DK_TIM Int YES YES NO NO DimensionKey: Time the transaction was entered into the system
FK DK_TIC Tinyint YES YES NO NO DimensionKey Ticker is a number that can be translated to ticker code. Join with T_DTIC on DK_TIC.
FK DK_OPT Bit YES YES NO NO DimensioKey: Option Type (0=call,1=put)
FK DK_FOI Bit YES YES NO NO DimensionKey: Future or Option Indicator. It distinguishes between those two instruments (0 for Futures and 1 for Options).
dk_foi cnt
0 134307021
1 68522571
FK DK_ABT Tinyint YES YES NO NO DimensionKey: distinguish between Ask, Bid and Trade (0,1,2)
FK DK_MQI Bit YES YES NO NO DimensionKey:Indicative Market Quotes
FK DK_BKI Bit YES YES NO NO DimensionKey: Indicator for Book quotes (0 for Book and 1 for other)
DD_TSN Int YES NO NO NO Trade Sequence Number is a sequence the quote or trade was entered into the system
TRD_QTY Int NO NO NO Traded quanty is a number of contracts available for trade or traded
STR_PRI Numeric(6,2) NO NO NO Strike price
TRD_PRI Numeric(6,2) NO NO NO Trade price
T_FEOD

Fact Table: Commodities End of Day
It holds transactions of futures and options on about 770 various commodities. The most common are (name, number of transactions)
cti_dsc cnt
Natural Gas (Henry Hub) Physical 15063469
Crude Oil 10997055
Heating Oil Physical 4853400
Copper 4408892
Gold 3442885
Eurodollar 3035366
Soybean 2821718
Silver 2622304
S&P 500 2365513
U.S. Treasury Bond 2325640
E-mini S&P 500 2319027
Aluminum 2212936
Euro FX 1938686
Japanese Yen 1692573
Soybean Oil 1564014

Data availability
technical note: I added more data from Anna - this was bonds data only. Can be captured via T_DSFL, there is "ANNA" in the path.

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
FK DK_DAT_TRD Int YES YES NO NO DimensionKey: Trade Date
It should be joined with T_DDAT on DK_DAT_TRD=DK_DAT, however key has meaningful values in YYYYMMDD format. Its recommended to filter based on this column (instead of dimension attribute), becasue table is partitioned on it.
FK DK_CTI Smallint YES YES NO NO Dimension Key: Ticket Symbol Indicator. After joining with T_DCTI attribute with ticker code can be populated (CTI_NM)
FK DK_CPF Tinyint YES YES NO NO Dimension Key: Call Put or Future indicator. It holds three values:
0 for Call, 1 for Put, and 2 for Futures
FK DK_MON_DRY Int YES YES NO NO Dimension Key: Delivery Month. It's a key to be used for join with T_DMON, however it has meaningful values in YYYYMM format.
FK DK_EXC Tinyint YES YES NO NO Dimension Key: Exchange Code helps to distinguilsh between currently three exchanges:
DK_EXC EXC_CD EXC_NM
0 CBT Chicago Board of Trade
1 CME Chicago Mercantile Exchange
2 NYM New York Mercantile Exchange

Join with T_DEXC dimension on DK_EXC
FK DK_SFL Int YES YES NO NO Dimension Key: Source File points (via T_DSFL dimension) to source file from which given row was loaded.
DD_DAY_DRY Tinyint YES YES NO NO Degenerated Dimension: Day of Delivery. It holds numeric value which coresponds to the day in month at which delivery takes place. Zeros mean there was no such a information in source file.
FK DK_SCN Tinyint YES YES NO NO Dimension Key: Settle Cabinet
Join with T_DSCN to populate attribute explaining the code or name of the cabinet.
This formula was used for mapping: convert(tinyint,case when settle_cabinet='$' then 1 when settle_cabinet='0' then 2 when settle_cabinet='C' then 3 else 0 end) as DK_SCN,--settle_cabinet ,
FK DK_IND Tinyint YES YES NO NO Dimension Key: Indicator.

This column priovides an important link to multiple indicators. To save space and improve performance some columns were moved to dedicated dimension. They carry descriptive information distinguising in most of the cases between different categories/types/classes like:
*The indicator for the trading session. R indicates Open Outcry, E indicates Electronic(Globex).
*Open Ask/Bid Indicator Indicates that the opening price was a bid or offer. A $ is used to indicate a cabinet price.
*Opening Range Ask/Bid Indicator A Indicates that prices in the opening range were bids or offers.
*High Ask/Bid Indicator Indicates that the high price was a bid or offer. A $ is used to indicate a cabinet price.
*Low Ask/Bid Indicator Indicates that the low price was a bid or offer. A $ is used to indicate a cabinet price.
*Close Ask/Bid Indicator Indicates that the closing price was a bid or offer. An N indicates a nominal price.
*Closing Range Ask/Bid Indicator Indicates that the closing price was a bid or offer.

Join with T_DIND dimensions on DK_IND to lookup value from given indicator.
STR_PRI Int NO NO NO Strike price
OPN_PRI Real NO NO NO Open price
OPN_RNG Real NO NO NO Open range
HIG_PRI Real NO NO NO High price
LOW_PRI Real NO NO NO Low price
CLO_PRI Real NO NO NO Close price
CLO_RNG Real NO NO NO Close range
STL_PRI Real NO NO NO Settle price
ACT_VOL Real NO NO NO Actual volume
OPN_INT Int NO NO NO Open interest
OPT_EXE Int NO NO NO Option exercise
IMP_VOL Real NO NO NO Implied volatility
T_FTIC

Table - Fact - TICK data. It holds intraday transactions for options and futures on over 1700 securities. Data is available from 19720103 to 20120514

The 10 top liquid ones are:

cti_cd cti_dsc
LN Natural Gas (Henry Hub) Physical
ON Natural Gas (Henry Hub) Physical
LO Crude Oil
AO Crude Oil
HX Copper
OG Gold
OH Heating Oil Physical
CZO Soybean
SO Silver
CG U.S. Treasury Bond

Attributes
Key Attribute Name Data Type Index Not Null Unique Check Comments
FK dk_dat_trd Int YES YES NO NO DimensionKey: Trade Date
It should be joined with T_DDAT on DK_DAT_TRD=DK_DAT, however key has meaningful falues in YYYYMMDD format. Its recommended to filter based on this column (instead of dimension attribute), becasue table is partitioned on it.
FK dk_dat_ent Int YES YES NO NO DimensionKey: Entry Date, It has YYYYMMDD format. Join towards T_DDAT for other attributes.
FK dk_mon_dry Int YES YES NO NO Dimension Key: Delivery Month. It's a key to be used for join with T_DMON, however it has meaningful values in YYYYMM format.
FK dk_tim Int YES YES NO NO DimensionKey: Time gives hour, second and minute of the transation
Join with T_DTIM using DK_TIM to translate ID into time in ISO or other format
FK dk_cti Smallint YES YES NO NO Dimension Key: Ticket Symbol Indicator. After joining with T_DCTI attribute with ticker code can be populated (CTI_NM)
cti_cd cti_dsc
LN Natural Gas (Henry Hub) Physical
ON Natural Gas (Henry Hub) Physical
LO Crude Oil
AO Crude Oil
HX Copper
OG Gold
OH Heating Oil Physical
CZO Soybean
SO Silver
CG U.S. Treasury Bond
FK dk_itc Smallint YES YES NO NO DimensionKey: Indicators, Types and Codes
This key points to a row in T_DITC dimension that holds set of the following indicators
--[Indicative Quote Type],
--[Market Quote],
--[Close Open Type],
--[Valid Open Exception],
--[Post Close],
--[Cancel Code Type],
--[Insert Code Type],
--[Fast Late Indicator],
--[Cabinet Indicator],
--[Book Indicator],
This modeling trick helped to reduce number of dimensions of the TIC fact table, impoved performance and saved storage.
FK dk_sfl Int YES YES NO NO Dimension Key: Source File points (via T_DSFL dimension) to source file from which given row was loaded.
FK dk_exc Tinyint YES YES NO NO Dimension Key: Exchange Code helps to distinguilsh between currently three exchanges:
DK_EXC EXC_CD EXC_NM
0 CBT Chicago Board of Trade
1 CME Chicago Mercantile Exchange
2 NYM New York Mercantile Exchange

Join with T_DEXC dimension on DK_EXC
FK dk_cpf Tinyint YES YES NO NO Dimension Key: Call Put or Future indicator. It holds three values:
0 for Call, 1 for Put, and 2 for Futures
FK dk_abt Tinyint YES YES NO NO DimensionKey: Ask Bid Type
I can be 0 for Ask, 1 for Bid and 2 for unknown
dd_tsn Int YES NO NO NO Degenerated Dimension: Trade Sequence Number
It can be used to sort transactions that fall into common second (time precision ends on second level, this sequence number plays role of milisecond)
trd_qty Real NO NO NO Traded quantity
str_pri Real NO NO NO Strike Price
trd_pri Real NO NO NO Trade Price
TickData_Trd_SP

This table contains Global Futures Trade data from 2010-06-30 until 2013-04-23.

Attributes
Key Attribute Name Data Type Not Null Unique Check Comments
TradeDate Date NO NO NO Trade date
TradeTime Time(7) NO NO NO The time when the trade took place
Price Numeric(8,2) NO NO NO Price (filtered price)
Volume Int NO NO NO Traded volume (except in a few markets pit trades do not show Volume)
MarketFlag Char(1) NO NO NO Market Flag (P/E for Pit or Electronic trades)
SalesCondition Tinyint NO NO NO Sales Condition (if available; see below for specific exchange info)
0 Normal Trade
1 EFP CCX block trade
2 Broker Deal with no condition
3 Crack Price Leg
4 System Price Leg
5 EFP block trade
6 EFS EFP Contra block trades
7 Hedge Price Leg
10 Adjusted price
111 Implied Spread at Market Open
13 Block trade
17 NG EFS/EFP block trade
21 QV Deal
23 EFS block trade
24 Contra block trade
26 Off-Exchange block trade
31 Cross Contra block trades
ExcludeRecord Char(1) NO NO NO Exclude Record Flag (flags off-exchange trades, i.e. EFPs and block trades)
UnfilteredPrice Numeric(8,2) NO NO NO Unfiltered price
ExpMonth Tinyint NO NO NO Expiration month - this value was derived from the file name and the following mapping was applied:
update SRC_TickData_SP_2
set ExpMonthInt =
(
case ExpMonth
when 'F' then 1
when 'G' then 2
when 'H' then 3
when 'J' then 4
when 'K' then 5
when 'M' then 6
when 'N' then 7
when 'Q' then 8
when 'U' then 9
when 'V' then 10
when 'X' then 11
when 'Z' then 12
else 0
end
)

sample file name (SRC_FL): SPZ11_2011_10_04.txt
ExpYear Tinyint NO NO NO Expiration Year - the data was extracted from the file names by this sql:

update TickData_Trd_SP set ExpYear=convert(tinyint,substring(SRC_FL,5,2))
sample file name (SRC_FL): SPZ11_2011_10_04.txt
FileName Varchar(25) NO NO NO The name of the source file from which the row comes from

ER Diagram


Hover over the image to see zoomed result.

BBO - Options SPX (sp500 mini)


BBO Options on S&P500 mini Top-of-Book (BBO) Data Contains all top bid, bid size, top ask, ask size, last trade, trade volume and time-stamp data for CME Globex traded products.



    Zoom Result -->    






BBO Futures (SP500)


Model of the BBO Futures star schema Top-of-Book (BBO) Data Contains all top bid, bid size, top ask, ask size, last trade, trade volume and time-stamp data for CME Globex traded products.



    Zoom Result -->    






BBO Options (SP500)


BBO Options Top-of-Book (BBO) Data Contains all top bid, bid size, top ask, ask size, last trade, trade volume and time-stamp data for CME Globex traded products.



    Zoom Result -->    






EOD - End of Day


This star schema holds end of day transactions for futures and options on over over 1700 instruments like Natural Gas (Henry Hub) Physical, Crude Oil, Heating Oil Physical, Copper, Gold, Eurodollar, Soybean, Silver, U.S. Treasury Bond & S&P 500



    Zoom Result -->    






TIC - Tick transactions


This star schema holds intraday transactions for options and futures on over 1700 securities. Data is available from 19720103 to 20120514 The 10 top liquid ones are:
cti_cd    cti_dsc
LN          Natural Gas (Henry Hub) Physical
ON          Natural Gas (Henry Hub) Physical
LO          Crude Oil
AO          Crude Oil
HX          Copper
OG          Gold
OH          Heating Oil Physical
CZO          Soybean
SO          Silver
CG          U.S. Treasury Bond

    Zoom Result -->    






TickData - Trades SP


Global Futures Trade and Quote Data

http://www.tickdata.com/pdf/Futures_File_Format_Guide.pdf

Through 6/30/2003, Tick Data’s Historical Futures Data Set contains only day-session pit trading activity for all markets that were not electronic-only (i.e. had pit-only or pit and electronic trading). Electronic-only markets have partial night session trading (i.e. eMini trading days begin at 12:00am and close on the day session close), but do not have trading volume. While additional fields can be output via TickWrite, the source data only contains five (5) fields:Date,Time,Price (always the filtered price),Volume (always zero),Market Flag (‘P’ or ‘E’ for Pit or Electronic trades),TickWrite can also output the symbol, tick count, up-ticks, down-ticks, and Date and Time (combined into one field; i.e. used by NinjaTrader)

From 7/1/2003 forward, all futures data has a second stamp, and all electronic trades contain volume. Tick Data’s Historical Futures data contains same five (5) fields: Date,Time,Price (always the filtered price),Volume (except in a few markets pit trades do not show Volume),Market Flag (‘P’ or ‘E’ for Pit or Electronic trades),Beginning 7/1/2011, Tick Data’s Historical Futures data contains a millisecond time stamp and includes additional information in the following eight (8) fields: Date,Time,Price (filtered price),Volume (except in a few markets pit trades do not show Volume),Market Flag (P/E for Pit or Electronic ,trades),Sales Condition (if available; see below for specific exchange info) Exclude Record Flag (flags off-exchange trades, i.e. EFPs and block trades),Unfiltered Price

More information can be found at the FAQ on Tick Data’s website: http://www.tickdata.com/support/futures-data-support/futures-and-index-faq/

This data was purchsed by Anna Cielsak from TickData.

    Zoom Result -->