Project 2: Populating Data Mart Using Integration Services

   < Previous  Next >

Due Date

See the calendar for due date.

Objectives

  1. To populate a data mart with data from the company accounting system (SQL Server MaxMinAccounting database) and manufacturing automation system (BatchInfo.csv file) using Integration Services
  2. To add a new fact table to an existing data mart
  3. To populate the fact tables in a data mart using Integration services

  4. To become familiar Integration Services packages
  5. To learn about the Integration Services package components and development
  6. To learn how to create control flow tasks
  7. To learn how to create data flow tasks
  8. To learn how to create data sources, transformations, destinations

Data Sources:

  1. SQL Server MaxMinAccounting database
    • When restoring this database, change the default filenames for the DATA folder to avoid getting an error like: 
      Restore of database MaxMinAccounting failed. … *\MaxMinAccounting.mdf' cannot be overwritten …
  2. Manufacturing automation system (BatchInfo.csv)
  3. OrderProcessingSystem relational databases
    • Use this link instead of the one supplied by the textbook's publisher to avoid an error with Change Data Capture (CDC).

Directions

Complete the following Learn By Doing (LBD) sections:
  1. Using Integration Services to Populate the Maximum Miniatures Manufacturing Data Mart Dimensions (pp. 248-255)
    • Always open SQL Server Data Tools 'as Administrator' on your virtual machine (by right clicking on the icon, selecting "More >", then "Run as administrator")
    • Use MaxMinManufacturingDMLoad-<yourInitials> instead of MaxMinManufacturingDMLoad for the project name.
    • Use MaxMinManufacturingDM-<yourInitials> instead of MaxMinManufacturingDM for the database name.
    You get to create and run an IS package that loads data into dimension tables of the MaxMinManufacturingDM-<yourInitials> data mart.
  2. Adding a Second Fact Table to the Manufacturing Data Mart (pp. 286-287)
    Using SSMS, add a new fact table to the MaxMinManufacturingDM-<yourInitials> data mart.
  3. Populating the Fact Tables in the Manufacturing Data Mart (pp. 287-301)
    Using SSIS, create an IS package that loads data into the fact tables of your MaxMinManufacturingDM-<yourInitials> data mart. The input data are loaded from the BatchInfo.csv file and the OrderProcessingSystem relational database.
Note: Grading will be performed on your assigned virtual machine.
Your MaxMinManufacturingDM-<yourInitials> data mart has to be populated with data.

Rubric:

Points      Item
----------  --------------------------------------------------------------
            Population of the MaxMinManufacturingDM-<yourInitials> dimensions
_____ / 10  Creating IS project, data source, data destinations
_____ / 10  Additioanl data flow
_____ / 10  Creating the load Plant, Material-Machine Type-Machine flow
_____ /  5  Execution of the IS package

_____ /  5  Adding second fact table to MaxMinManufacturingDM-<yourInitials>

            Population of the MaxMinManufacturingDM-<yourInitials> fact tables
_____ /  5  creating project connection manager
_____ / 10  creating the load DimBatch data flow
_____ / 10  creating the load ManufacturionFact data flow, derived column data flow, lookup data flow
_____ / 10  creating data destination item, data destination for errors
_____ / 10  creating the InventoryFact data flow, lookup, and data conversion data flow items
_____ / 10  creating data destination data flow, data destination for errors
_____ /  5  execution of the IS package

_____ /100  Total