Quiz 4 Review Outlines:

This test is focused on the ETL/Cube skills using SQL Server. It is open-book/open-notes.

1.       How to load a text file to a SQL Server table

2.       How to create new destination table in ETL using either SQL scripts or automatically generated in accordance with the source data.

3.       How to use ETL node Aggregate, Sort, Duplicate, and Data Conversion

4.       How to create time dimension table for a data mart

5.       How to design and configuring a cube

Review materials and exercises:

1.       Lecture slides (BI-06 & BI-09)

2.       Your exercises (4, 5, 6 & 7)

----------------------

Quiz 3 Review Outlines:

Questions

1.       What is data integration?

2.       What are ETL topics?

3.       What are major functions of SQL Server SSIS?

4.       What is referential integrity? How does it affect the process of ETL?

5.       What are three types of measures?

6.       What are aggregate functions in SSIS?

7.       What is granularity in data warehousing?

8.       Review types of dimensions

Review materials and exercises:

3.       Lecture slides (BI-06 & BI-08)

4.       Your exercises (4, 5 & 6)

----------------------

Quiz 2 Review Outlines:

Questions

1.       How to design a dimensional model? Review the cases lectured in the class. Solve the following case.

 

The following three tables are in a course enrollment database.

STUDENT (StudentNumber, CustomerLastName, CustomerFirstName, Phone, IntStudentFlag, AthleteFlag)

COURSE (CourseNumber, Course, StartDate, Cost)

ENROLLMENT (StudentNumber, CourseNumber, PaidDate, PaidFlag, RetakeFlag)

1)      Identify the measures

2)      Design an enrollment fact table attributes for an enrollment fact table:

FactEnrollment( , , , ). Highlight the primary key.

3)      Identify 3-4 dimension tables, and indicate their types

 

2.       What are two types of dimensional models?

3.       What is UDM?

4.       How to plan a data mart project?

5.       What are three fact tables? Provide the examples.

6.       What are 7 popular dimension tables? Provide examples for at least three of them

Review materials and exercises:

5.       Lecture slides

6.       Your exercises

--+--+--

Quiz 1 Review Outlines:

Questions

1.       Concepts/Terms:

BI, Visualization, Data mart, Data warehouse, OLAP, OLTP, metadata, Hadoop, Map/Reduce, Operational data store, 4 Vs of big data

2.       Short answers questions

Comparison: Database vs. data warehouse, data warehouse vs. data mart, SQL Server vs. Hadoop, data vs. information vs. knowledge, OLAP vs. OLTP

Properties/characteristics: data ware house, OLAP

3.       Other fundamental concepts and knowledge about business intelligence

4.       Basic knowledge about SQL Server BIDS

Review materials and exercises:

7.       Slides and videos in the slides

8.       Basic knowledge in using Citrix SQL Serve