Course Outline of Record

Item	Value
Curriculum Committee Approval Date	10/20/2021
Top Code	070720 - Database Design and Administration
Units	3 Total Units
Hours	72 Total Hours (Lecture Hours 45; Lab Hours 27)
Total Outside of Class Hours	0
Course Credit Status	Credit: Degree Applicable (D)
Material Fee	No
Basic Skills	Not Basic Skills (N)
Repeatable	No
Grading Policy	Standard Letter (S),  Pass/No Pass (B)

Course Description

Covers the terminology, technology and software used to build a data warehouse model and prepare that model for data mining. Students will learn how to design and process a data warehouse database using SSIS (Sql Server Integration Services), and format a cube in SSAS (SQL Server Analysis Services) in preparation for data mining operations. ADVISORY: CHT A176, IT A176 or CIS A176 or equivalent. Transfer Credit: CSU.

Course Level Student Learning Outcome(s)

1. Demonstrate the ability to implement a SSIS (SQL Server Integeration Services) package to load, unload and manipulate data in a data warehouse database.
2. Demonstrate the ability to implement a SSAS (SQL Server Analysis Services) cube to prepare the data warehouse data for the data mining process.

Course Objectives

• 1. Install and configure SSIS (SQL Server Integration Services) and SSAS (SQL Server Analysis Services).
• 2. Use the Import/Export Wizard to load and unload data from a database.
• 3. Create an Integration Service package including connections, control flow and data flow objects.
• 4. Debug and log errors from an Integration Services package.
• 5. Prepare a SSIS package for production and implement it into production.
• 6. Design and define a SSAS (SQL Server Analysis Services) cube including dimensions and fact tables.
• 7. Partition a cube for optimal loading and processing of data.
• 8. Extract and manipulate data from a cube for data mining purposes.

---

Lecture Content

Installing SQL Server Overview of SQL Server (SQL, SSIS, SSAS, SSRS) Install SQL Server, SSIS and SSAS Install the Sample Databases Importing and Exporting Data with the Import/Export Wizard Using the Import/Export Wizard Developing a SSIS Package - Connection Manager Defining a Connection Connection Scope Flat File Connections OLEDB Connection External Configuration Files Developing a SSIS Package - Control Flow File System Task FTP Task Execute Package Task Execute Process Task Send Mail Task Execute SQL Task Data Flow Task Analysis Services Task Script Task Task Containers Precedence Constraints Defining and Using Variables Using Parameters Creating Expressions Developing a SSIS Package - Data Flow Data Flow Source Adapters Data Source Destinations SSIS Data Types Data Flow Transforms Row Count Transform Data Conversion Transform Copy Column Transform Derived Column Transform Lookup Transform Fuzzy Lookup Transform Merge and Merge Join Transforms Union All Transform Multicast Transform Conditional Split Transform Sort Transform Aggregate Transform Pivot and Unpovot Transforms Slowly Changing Dimension Script Component Data Viewers Debugging a SSIS Package Control Flow Breakpoints Data Flow Data Viewers Tips for Performance Parrell Execution of Steps Preparing SSIS for Production Error Handling Logging Package Execution Package Transactions Checkpoints and Restartability Package Configuration Files Deploying a Package Executing a Package Data Mining and SSAS Connections Data Sources Data Source Views Named Queries and Calculations SSAS Dimensions Creating a Dimension Dimension Properties Dimension Hierarchies Creating a Time Dimension Creating a Parent Child Dimension SSAS Cube Definition Creating a Cube Cube Meausres and properties Additive and Non-Additive measures Financial Measures Creating a Virtual Cube Attaching Dimensions Creating Measure Groups Partitioning a Cube Creating Key Performance Indicators Using MDX Queries Processing a Cube and Storage Models Creative Cube Perspectives SSAS Data Mining Models Data Mining Models Data Mining Algorithms Forecasting Models

Lab Content

Practical Application of Installing SQL Server.Practical Application of Importing and Exporting Data with the Import/Export Wizard.Practical Application of Developing a SSIS Package - Connection Manager.Practical Application of Developing a SSIS Package - Control Flow.Practical Application of Developing a SSIS Package - Data Flow.Practical Application of Debugging a SSIS Package.Practical Application of Preparing SSIS for Production.Practical Application of Data Mining and SSAS Connections.Practical Application of SSAS Dimensions.Practical Application of SSAS Cube Definition.Practical Application of SSAS Data Mining Models.

Method(s) of Instruction

• Lecture (02)
• DE Live Online Lecture (02S)
• DE Online Lecture (02X)
• Lab (04)
• DE Live Online Lab (04S)
• DE Online Lab (04X)

Instructional Techniques

Students will be presented material from several different sources, including, but not limited to:    Microsoft study guides.    Web-based curriculum.    Lectures of systems integration and personal experiences of industry professionals.Students will need to complete individual exercises to:   Perform many of the tasks required for building a data mining database.

Reading Assignments

Minimum of 3 hours per week (45 hours) reading from textbook material

Writing Assignments

Student performance on exams and assignments will be used to determine proficiency. This course will be oriented toward a practical and hands-on approach to the subject. Lab assignments will consist of performing and documenting common functions performed by a database administrator. Minimum of 3 hours per week creating and editing class and software projects.

Out-of-class Assignments

45 hours (3hrs/wk). Student performance on quizzes, tests, including short essays, and laboratory assignments will be used to determine proficiency

Demonstration of Critical Thinking

.

Required Writing, Problem Solving, Skills Demonstration

Student performance on exams and assignments will be used to determine proficiency. This course will be oriented toward a practical and hands-on approach to the subject. Lab assignments will consist of performing and documenting common functions performed by a database administrator.

Eligible Disciplines

Computer information systems (computer network installation, microcomputer ...: Any bachelors degree and two years of professional experience, or any associate degree and six years of professional experience. Computer service technology: Any bachelors degree and two years of professional experience, or any associate degree and six years of professional experience.

Textbooks Resources

1. Required Chinchilla, J., Uchhana, R.. Implementing a SQL Data Warehouse , ed. Microsoft Corporation, 2017