90 likes | 188 Views
MIS 4346/5346 Data warehousing. Data Warehouse Implementation. Agenda. Review Dimensional Modeling Implementing Data Mart Physical Structures Creating the data mart database Creating dimension tables Creating fact tables Using scripts. Review: Dimensional Modeling. DIM. DIM. FACT.
E N D
MIS 4346/5346 Data warehousing Data Warehouse Implementation
Agenda • Review Dimensional Modeling • Implementing Data Mart Physical Structures • Creating the data mart database • Creating dimension tables • Creating fact tables • Using scripts
Review: Dimensional Modeling DIM DIM FACT DIM DIM
Creating the Data Mart Database • Typically one database per data mart • Example: USE MASTER CREATE DATABASE ClassPerformanceDW; GO ALTER DATABASE ClassPerformanceDW SET RECOVERY SIMPLE GO
Creating Dimension Tables • Naming typically DimTableName • Consider data compression • Example: CREATE TABLE DimStudent( student_skintidentity(1,1), student_idvarchar(9), firstnamevarchar(30), lastnamevarchar(30), city varchar(20), state varchar(2), major varchar(6), classification varchar(25), gpa numeric(3, 2), clubnamevarchar(25), undergradschoolvarchar(25), gmatint, undergradORgradvarchar(10), CONSTRAINT dim_student_pk PRIMARY KEY (student_sk)); GO ALTER TABLE DimStudent REBUILD WITH (DATA_COMPRESSION = PAGE); GO
Creating Fact Tables • Naming typically FactTableName • Example: CREATE TABLE fact_enrollment( student_skint, class_skint, date_skint, professor_skint, course_grade numeric(2, 1), CONSTRAINT fact_enrollment_pk PRIMARY KEY (student_sk, class_sk, date_sk, professor_sk), CONSTRAINT fact_enrollment_student_fk FOREIGN KEY (student_sk) REFERENCES dim_student(student_sk), CONSTRAINT fact_enrollment_class_fk FOREIGN KEY(class_sk) REFERENCES dim_class (class_sk), CONSTRAINT fact_enrollment_date_fk FOREIGN KEY(date_sk) REFERENCES dim_time (date_sk), CONSTRAINT fact_enrollment_professor_fk FOREIGN KEY(professor_sk) REFERENCES dim_professor (professor_sk) ); GO
Using Scripts • Contains all statements to create data mart tables • Advantages: • Can easily create test environments • Can easily create production tables • Fewer files to manage • Code reuse • Example: • http://business.baylor.edu/gina_green/teaching/sqlserver/scripts/generate_class_performance_dw_tables/create_class_performance_dw_tables.sql • http://business.baylor.edu/gina_green/teaching/dw/databases/map_prefixes_to_depts.xls NOTE: this is NOT a script!!!
Summary • Creating and Naming: • Database • Dimension tables • Fact tables • Considerations when creating above objects • Using scripts
Next Time… • ETL • Chapter 12 • *** Assignment 2 Due Tues. 2/11 ***