All Tables Details

Design Name	hr_and_sh
Version Date	10.01.2012 09:44:38
Version Comment
Model Name	Relational_1 / Relational_1 - SubView_1v1

Table Name	SH.CUSTOMERS
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	dimension table
Notes

 

Number Of Columns	23
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	CUST_ID	P		Y	NUMERIC (22)	LT
2	CUST_FIRST_NAME			Y	VARCHAR (20 BYTE)	LT
3	CUST_LAST_NAME			Y	VARCHAR (40 BYTE)	LT
4	CUST_GENDER			Y	CHAR (1 BYTE)	LT
5	CUST_YEAR_OF_BIRTH			Y	NUMERIC (4)	LT
6	CUST_MARITAL_STATUS				VARCHAR (20 BYTE)	LT
7	CUST_STREET_ADDRESS			Y	VARCHAR (40 BYTE)	LT
8	CUST_POSTAL_CODE			Y	VARCHAR (10 BYTE)	LT
9	CUST_CITY			Y	VARCHAR (30 BYTE)	LT
10	CUST_CITY_ID			Y	NUMERIC (22)	LT
11	CUST_STATE_PROVINCE			Y	VARCHAR (40 BYTE)	LT
12	CUST_STATE_PROVINCE_ID			Y	NUMERIC (22)	LT
13	COUNTRY_ID		F	Y	NUMERIC (22)	LT
14	CUST_MAIN_PHONE_NUMBER			Y	VARCHAR (25 BYTE)	LT
15	CUST_INCOME_LEVEL				VARCHAR (30 BYTE)	LT
16	CUST_CREDIT_LIMIT				NUMERIC (22)	LT
17	CUST_EMAIL				VARCHAR (30 BYTE)	LT
18	CUST_TOTAL			Y	VARCHAR (14 BYTE)	LT
19	CUST_TOTAL_ID			Y	NUMERIC (22)	LT
20	CUST_SRC_ID				NUMERIC (22)	LT
21	CUST_EFF_FROM				Date (7)	LT
22	CUST_EFF_TO				Date (7)	LT
23	CUST_VALID				VARCHAR (1 BYTE)	LT

Columns Comments

No	Column Name	Description	Notes
1	CUST_ID	primary key
2	CUST_FIRST_NAME	first name of the customer
3	CUST_LAST_NAME	last name of the customer
4	CUST_GENDER	gender; low cardinality attribute
5	CUST_YEAR_OF_BIRTH	customer year of birth
6	CUST_MARITAL_STATUS	customer marital status; low cardinality attribute
7	CUST_STREET_ADDRESS	customer street address
8	CUST_POSTAL_CODE	postal code of the customer
9	CUST_CITY	city where the customer lives
11	CUST_STATE_PROVINCE	customer geography: state or province
13	COUNTRY_ID	foreign key to the countries table (snowflake)
14	CUST_MAIN_PHONE_NUMBER	customer main phone number
15	CUST_INCOME_LEVEL	customer income level
16	CUST_CREDIT_LIMIT	customer credit limit
17	CUST_EMAIL	customer email id

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
CUSTOMERS_PK	PK				CUST_ID	ASC
CUSTOMERS_GENDER_BIX					CUST_GENDER	ASC
CUSTOMERS_MARITAL_BIX					CUST_MARITAL_STATUS	ASC
CUSTOMERS_YOB_BIX					CUST_YEAR_OF_BIRTH	ASC

Foreign Keys (referring to)

Name	Refering To	Mandatory	Transferable	In Arc	Column Name
CUSTOMERS_COUNTRY_FK	COUNTRIES	Y	Y		COUNTRY_ID

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
SALES_CUSTOMER_FK	SALES	Y	Y		CUST_ID

Table Name	SH.SALES
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys
Notes

 

Number Of Columns	7
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	PROD_ID		F	Y	NUMERIC (6)	LT
2	CUST_ID		F	Y	NUMERIC (22)	LT
3	TIME_ID		F	Y	Date (7)	LT
4	CHANNEL_ID		F	Y	NUMERIC (22)	LT
5	PROMO_ID		F	Y	NUMERIC (6)	LT
6	QUANTITY_SOLD			Y	NUMERIC (10,2)	LT
7	AMOUNT_SOLD			Y	NUMERIC (10,2)	LT

Columns Comments

No	Column Name	Description	Notes
1	PROD_ID	FK to the products dimension table
2	CUST_ID	FK to the customers dimension table
3	TIME_ID	FK to the times dimension table
4	CHANNEL_ID	FK to the channels dimension table
5	PROMO_ID	promotion identifier, without FK constraint (intentionally) to show outer join optimization
6	QUANTITY_SOLD	product quantity sold with the transaction
7	AMOUNT_SOLD	invoiced amount to the customer

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
SALES_PROD_BIX					PROD_ID	ASC
SALES_CUST_BIX					CUST_ID	ASC
SALES_TIME_BIX					TIME_ID	ASC
SALES_CHANNEL_BIX					CHANNEL_ID	ASC
SALES_PROMO_BIX					PROMO_ID	ASC

Foreign Keys (referring to)

Name	Refering To	Mandatory	Transferable	In Arc	Column Name
SALES_CHANNEL_FK	CHANNELS	Y	Y		CHANNEL_ID
SALES_CUSTOMER_FK	CUSTOMERS	Y	Y		CUST_ID
SALES_PRODUCT_FK	PRODUCTS	Y	Y		PROD_ID
SALES_PROMO_FK	PROMOTIONS	Y	Y		PROMO_ID
SALES_TIME_FK	TIMES	Y	Y		TIME_ID

Table Name	SH.PROMOTIONS
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	dimension table without a PK-FK relationship with the facts table, to show outer join functionality
Notes

 

Number Of Columns	11
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	PROMO_ID	P		Y	NUMERIC (6)	LT
2	PROMO_NAME			Y	VARCHAR (30 BYTE)	LT
3	PROMO_SUBCATEGORY			Y	VARCHAR (30 BYTE)	LT
4	PROMO_SUBCATEGORY_ID			Y	NUMERIC (22)	LT
5	PROMO_CATEGORY			Y	VARCHAR (30 BYTE)	LT
6	PROMO_CATEGORY_ID			Y	NUMERIC (22)	LT
7	PROMO_COST			Y	NUMERIC (10,2)	LT
8	PROMO_BEGIN_DATE			Y	Date (7)	LT
9	PROMO_END_DATE			Y	Date (7)	LT
10	PROMO_TOTAL			Y	VARCHAR (15 BYTE)	LT
11	PROMO_TOTAL_ID			Y	NUMERIC (22)	LT

Columns Comments

No	Column Name	Description	Notes
1	PROMO_ID	primary key column
2	PROMO_NAME	promotion description
3	PROMO_SUBCATEGORY	enables to investigate promotion hierarchies
5	PROMO_CATEGORY	promotion category
7	PROMO_COST	promotion cost, to do promotion effect calculations
8	PROMO_BEGIN_DATE	promotion begin day
9	PROMO_END_DATE	promotion end day

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
PROMO_PK	PK				PROMO_ID	ASC

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
COSTS_PROMO_FK	COSTS	Y	Y		PROMO_ID
SALES_PROMO_FK	SALES	Y	Y		PROMO_ID

Table Name	SH.COSTS
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description
Notes

 

Number Of Columns	6
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	PROD_ID		F	Y	NUMERIC (6)	LT
2	TIME_ID		F	Y	Date (7)	LT
3	PROMO_ID		F	Y	NUMERIC (6)	LT
4	CHANNEL_ID		F	Y	NUMERIC (22)	LT
5	UNIT_COST			Y	NUMERIC (10,2)	LT
6	UNIT_PRICE			Y	NUMERIC (10,2)	LT

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
COSTS_PROD_BIX					PROD_ID	ASC
COSTS_TIME_BIX					TIME_ID	ASC

Foreign Keys (referring to)

Name	Refering To	Mandatory	Transferable	In Arc	Column Name
COSTS_CHANNEL_FK	CHANNELS	Y	Y		CHANNEL_ID
COSTS_PRODUCT_FK	PRODUCTS	Y	Y		PROD_ID
COSTS_PROMO_FK	PROMOTIONS	Y	Y		PROMO_ID
COSTS_TIME_FK	TIMES	Y	Y		TIME_ID

Table Name	SH.TIMES
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	Time dimension table to support multiple hierarchies and materialized views
Notes

 

Number Of Columns	38
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	TIME_ID	P		Y	Date (7)	LT
2	DAY_NAME			Y	VARCHAR (9 BYTE)	LT
3	DAY_NUMBER_IN_WEEK			Y	NUMERIC (1)	LT
4	DAY_NUMBER_IN_MONTH			Y	NUMERIC (2)	LT
5	CALENDAR_WEEK_NUMBER			Y	NUMERIC (2)	LT
6	FISCAL_WEEK_NUMBER			Y	NUMERIC (2)	LT
7	WEEK_ENDING_DAY			Y	Date (7)	LT
8	WEEK_ENDING_DAY_ID			Y	NUMERIC (22)	LT
9	CALENDAR_MONTH_NUMBER			Y	NUMERIC (2)	LT
10	FISCAL_MONTH_NUMBER			Y	NUMERIC (2)	LT
11	CALENDAR_MONTH_DESC			Y	VARCHAR (8 BYTE)	LT
12	CALENDAR_MONTH_ID			Y	NUMERIC (22)	LT
13	FISCAL_MONTH_DESC			Y	VARCHAR (8 BYTE)	LT
14	FISCAL_MONTH_ID			Y	NUMERIC (22)	LT
15	DAYS_IN_CAL_MONTH			Y	NUMERIC (22)	LT
16	DAYS_IN_FIS_MONTH			Y	NUMERIC (22)	LT
17	END_OF_CAL_MONTH			Y	Date (7)	LT
18	END_OF_FIS_MONTH			Y	Date (7)	LT
19	CALENDAR_MONTH_NAME			Y	VARCHAR (9 BYTE)	LT
20	FISCAL_MONTH_NAME			Y	VARCHAR (9 BYTE)	LT
21	CALENDAR_QUARTER_DESC			Y	CHAR (7 BYTE)	LT
22	CALENDAR_QUARTER_ID			Y	NUMERIC (22)	LT
23	FISCAL_QUARTER_DESC			Y	CHAR (7 BYTE)	LT
24	FISCAL_QUARTER_ID			Y	NUMERIC (22)	LT
25	DAYS_IN_CAL_QUARTER			Y	NUMERIC (22)	LT
26	DAYS_IN_FIS_QUARTER			Y	NUMERIC (22)	LT
27	END_OF_CAL_QUARTER			Y	Date (7)	LT
28	END_OF_FIS_QUARTER			Y	Date (7)	LT
29	CALENDAR_QUARTER_NUMBER			Y	NUMERIC (1)	LT
30	FISCAL_QUARTER_NUMBER			Y	NUMERIC (1)	LT
31	CALENDAR_YEAR			Y	NUMERIC (4)	LT
32	CALENDAR_YEAR_ID			Y	NUMERIC (22)	LT
33	FISCAL_YEAR			Y	NUMERIC (4)	LT
34	FISCAL_YEAR_ID			Y	NUMERIC (22)	LT
35	DAYS_IN_CAL_YEAR			Y	NUMERIC (22)	LT
36	DAYS_IN_FIS_YEAR			Y	NUMERIC (22)	LT
37	END_OF_CAL_YEAR			Y	Date (7)	LT
38	END_OF_FIS_YEAR			Y	Date (7)	LT

Columns Comments

No	Column Name	Description	Notes
1	TIME_ID	primary key; day date, finest granularity, CORRECT ORDER
2	DAY_NAME	Monday to Sunday, repeating
3	DAY_NUMBER_IN_WEEK	1 to 7, repeating
4	DAY_NUMBER_IN_MONTH	1 to 31, repeating
5	CALENDAR_WEEK_NUMBER	1 to 53, repeating
6	FISCAL_WEEK_NUMBER	1 to 53, repeating
7	WEEK_ENDING_DAY	date of last day in week, CORRECT ORDER
9	CALENDAR_MONTH_NUMBER	1 to 12, repeating
10	FISCAL_MONTH_NUMBER	1 to 12, repeating
11	CALENDAR_MONTH_DESC	e.g. 1998-01, CORRECT ORDER
13	FISCAL_MONTH_DESC	e.g. 1998-01, CORRECT ORDER
15	DAYS_IN_CAL_MONTH	e.g. 28,31, repeating
16	DAYS_IN_FIS_MONTH	e.g. 25,32, repeating
17	END_OF_CAL_MONTH	last day of calendar month
18	END_OF_FIS_MONTH	last day of fiscal month
19	CALENDAR_MONTH_NAME	January to December, repeating
20	FISCAL_MONTH_NAME	January to December, repeating
21	CALENDAR_QUARTER_DESC	e.g. 1998-Q1, CORRECT ORDER
23	FISCAL_QUARTER_DESC	e.g. 1999-Q3, CORRECT ORDER
25	DAYS_IN_CAL_QUARTER	e.g. 88,90, repeating
26	DAYS_IN_FIS_QUARTER	e.g. 88,90, repeating
27	END_OF_CAL_QUARTER	last day of calendar quarter
28	END_OF_FIS_QUARTER	last day of fiscal quarter
29	CALENDAR_QUARTER_NUMBER	1 to 4, repeating
30	FISCAL_QUARTER_NUMBER	1 to 4, repeating
31	CALENDAR_YEAR	e.g. 1999, CORRECT ORDER
33	FISCAL_YEAR	e.g. 1999, CORRECT ORDER
35	DAYS_IN_CAL_YEAR	365,366 repeating
36	DAYS_IN_FIS_YEAR	e.g. 355,364, repeating
37	END_OF_CAL_YEAR	last day of cal year
38	END_OF_FIS_YEAR	last day of fiscal year

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
TIMES_PK	PK				TIME_ID	ASC

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
COSTS_TIME_FK	COSTS	Y	Y		TIME_ID
SALES_TIME_FK	SALES	Y	Y		TIME_ID

Table Name	SH.CHANNELS
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	small dimension table
Notes

 

Number Of Columns	6
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	CHANNEL_ID	P		Y	NUMERIC (22)	LT
2	CHANNEL_DESC			Y	VARCHAR (20 BYTE)	LT
3	CHANNEL_CLASS			Y	VARCHAR (20 BYTE)	LT
4	CHANNEL_CLASS_ID			Y	NUMERIC (22)	LT
5	CHANNEL_TOTAL			Y	VARCHAR (13 BYTE)	LT
6	CHANNEL_TOTAL_ID			Y	NUMERIC (22)	LT

Columns Comments

No	Column Name	Description	Notes
1	CHANNEL_ID	primary key column
2	CHANNEL_DESC	e.g. telesales, internet, catalog
3	CHANNEL_CLASS	e.g. direct, indirect

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
CHANNELS_PK	PK				CHANNEL_ID	ASC

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
COSTS_CHANNEL_FK	COSTS	Y	Y		CHANNEL_ID
SALES_CHANNEL_FK	SALES	Y	Y		CHANNEL_ID

Table Name	SH.PRODUCTS
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	dimension table
Notes

 

Number Of Columns	22
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	PROD_ID	P		Y	NUMERIC (6)	LT
2	PROD_NAME			Y	VARCHAR (50 BYTE)	LT
3	PROD_DESC			Y	VARCHAR (4000 BYTE)	LT
4	PROD_SUBCATEGORY			Y	VARCHAR (50 BYTE)	LT
5	PROD_SUBCATEGORY_ID			Y	NUMERIC (22)	LT
6	PROD_SUBCATEGORY_DESC			Y	VARCHAR (2000 BYTE)	LT
7	PROD_CATEGORY			Y	VARCHAR (50 BYTE)	LT
8	PROD_CATEGORY_ID			Y	NUMERIC (22)	LT
9	PROD_CATEGORY_DESC			Y	VARCHAR (2000 BYTE)	LT
10	PROD_WEIGHT_CLASS			Y	NUMERIC (3)	LT
11	PROD_UNIT_OF_MEASURE				VARCHAR (20 BYTE)	LT
12	PROD_PACK_SIZE			Y	VARCHAR (30 BYTE)	LT
13	SUPPLIER_ID			Y	NUMERIC (6)	LT
14	PROD_STATUS			Y	VARCHAR (20 BYTE)	LT
15	PROD_LIST_PRICE			Y	NUMERIC (8,2)	LT
16	PROD_MIN_PRICE			Y	NUMERIC (8,2)	LT
17	PROD_TOTAL			Y	VARCHAR (13 BYTE)	LT
18	PROD_TOTAL_ID			Y	NUMERIC (22)	LT
19	PROD_SRC_ID				NUMERIC (22)	LT
20	PROD_EFF_FROM				Date (7)	LT
21	PROD_EFF_TO				Date (7)	LT
22	PROD_VALID				VARCHAR (1 BYTE)	LT

Columns Comments

No	Column Name	Description	Notes
1	PROD_ID	primary key
2	PROD_NAME	product name
3	PROD_DESC	product description
4	PROD_SUBCATEGORY	product subcategory
6	PROD_SUBCATEGORY_DESC	product subcategory description
7	PROD_CATEGORY	product category
9	PROD_CATEGORY_DESC	product category description
10	PROD_WEIGHT_CLASS	product weight class
11	PROD_UNIT_OF_MEASURE	product unit of measure
12	PROD_PACK_SIZE	product package size
13	SUPPLIER_ID	this column
14	PROD_STATUS	product status
15	PROD_LIST_PRICE	product list price
16	PROD_MIN_PRICE	product minimum price

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
PRODUCTS_PK	PK				PROD_ID	ASC
PRODUCTS_PROD_STATUS_BIX					PROD_STATUS	ASC
PRODUCTS_PROD_SUBCAT_IX					PROD_SUBCATEGORY	ASC
PRODUCTS_PROD_CAT_IX					PROD_CATEGORY	ASC

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
COSTS_PRODUCT_FK	COSTS	Y	Y		PROD_ID
SALES_PRODUCT_FK	SALES	Y	Y		PROD_ID

Table Name	SH.COUNTRIES
Functional Name
Abbreviation
Classification Type Name
Object Type Name

 

Description	country dimension table (snowflake)
Notes

 

Number Of Columns	10
Number Of Rows Min.	0
Number Of Rows Max.	9999999
Expected Number Of Rows	0
Expected Growth	0
Growth Interval	Year

Columns

No	Column Name	PK	FK	M	Data Type	DT kind	Domain Name	Formula (Default Value)	Security	Abbreviation
1	COUNTRY_ID	P		Y	NUMERIC (22)	LT
2	COUNTRY_ISO_CODE			Y	CHAR (2 BYTE)	LT
3	COUNTRY_NAME			Y	VARCHAR (40 BYTE)	LT
4	COUNTRY_SUBREGION			Y	VARCHAR (30 BYTE)	LT
5	COUNTRY_SUBREGION_ID			Y	NUMERIC (22)	LT
6	COUNTRY_REGION			Y	VARCHAR (20 BYTE)	LT
7	COUNTRY_REGION_ID			Y	NUMERIC (22)	LT
8	COUNTRY_TOTAL			Y	VARCHAR (11 BYTE)	LT
9	COUNTRY_TOTAL_ID			Y	NUMERIC (22)	LT
10	COUNTRY_NAME_HIST				VARCHAR (40 BYTE)	LT

Columns Comments

No	Column Name	Description	Notes
1	COUNTRY_ID	primary key
3	COUNTRY_NAME	country name
4	COUNTRY_SUBREGION	e.g. Western Europe, to allow hierarchies
6	COUNTRY_REGION	e.g. Europe, Asia

Indexes

Index Name	State	Functional	Spatial	Expression	Column Name	Sort Order
COUNTRIES_PK	PK				COUNTRY_ID	ASC

Foreign Keys (referred from)

Name	Referred From	Mandatory	Transferable	In Arc	Column Name
CUSTOMERS_COUNTRY_FK	CUSTOMERS	Y	Y		COUNTRY_ID