Archives of the TeradataForum

Message Posted: Mon, 03 Nov 2014 @ 09:15:19 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Sughesh and Team,

We are following snowflakes schema. The Dimension tables are holding SKEY.

For example :

     CREATE TABLE TBUIN.BI_DIM_CUST
     (
     CUST_SKEY BIGINT NOT NULL,
     OPER_UNIT_CD varchar(10) NOT NULL,
     CUST_NBR VARCHAR(100) CHARACTER SET UNICODE NOT NULL,
     CUST_NAME VARCHAR(720) CHARACTER SET UNICODE NOT NULL,
     CUST_ACCT_ID VARCHAR(200),
     CUST_COUNTRY_SKEY BIGINT,
     CUST_STAT_SKEY BIGINT,
     ENTITY_CUST_SKEY BIGINT,
     EFF_PERIOD VARCHAR(5),
     CANC_PERIOD VARCHAR(5) NOT NULL DEFAULT '12-99', CREATE_BY_NAME VARCHAR(100),
     CREATE_TS TIMESTAMP(6),
     UPD_BY_NAME VARCHAR(100),
     UPD_TS TIMESTAMP(6)
     )
     UNIQUE PRIMARY INDEX(CUST_SKEY,CANC_PERIOD) INDEX CUST_INDX
     (OPER_UNIT_CD,CUST_NBR,ENTITY_CUST_SKEY)

Similarly we have lot many Dimension tables.

Also we are getting Orders data from ERP which transactional data. ERP is sending us Order header information and Order Line information in two separate files. Order Header table holds Order_Header_Id and Instance_Id as UPI.

For a particular Order Header ID we can have multiple Line ID, so the Order Line table consist UPI(order_line_id, instance_id)

While designing my FACT table, what are all those columns we should consider as Index and Keys which will help me in BO reporting ?

Thanks,

Dipti

	<Prev	Next>		<<First	<Prev	Next>	Last>>