1 / 9

Data Warehouse Implementation: Dimension and Fact Table Creation Guide

This comprehensive agenda covers the implementation of a data warehouse, focusing on dimensional modeling and the construction of data marts. Key steps include creating the data mart database, dimension tables, and fact tables, along with discussions on naming conventions and data compression methods. Utilize SQL scripts to streamline the creation of these elements, facilitating testing and production environments. The agenda includes practical examples, emphasizing best practices for structuring your data warehouse effectively.

ursula
Télécharger la présentation

Data Warehouse Implementation: Dimension and Fact Table Creation Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MIS 4346/5346 Data warehousing Data Warehouse Implementation

  2. Agenda • Review Dimensional Modeling • Implementing Data Mart Physical Structures • Creating the data mart database • Creating dimension tables • Creating fact tables • Using scripts

  3. Review: Dimensional Modeling DIM DIM FACT DIM DIM

  4. 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

  5. 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

  6. 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

  7. 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!!!

  8. Summary • Creating and Naming: • Database • Dimension tables • Fact tables • Considerations when creating above objects • Using scripts

  9. Next Time… • ETL • Chapter 12 • *** Assignment 2 Due Tues. 2/11 ***

More Related