Data Warehousing Exercise Guidelines


Exercise 5: Populating Maximum Miniatures Manufacturing Data Mart Fact Tables



Preparation: Data sources and destination definition


  1. Review data source BatchInfo.csv (in ~\Downloaded\0072260904_code-DeliveringBI\BatchInfo%20CSV File of the shared driver) and Lin.OrderProcessingSystem (SQL server database, Not needed this time)
  2. Create a SSIS project ISQS<course#>-<term>-EX5-<lastname> (for example, ISQS3358-2016fall-EX5-Lin), or use the previous project but save it in this name.
  3. Create two new package BatchLoad and FactLoad in the previously created SSIS project
  4. Define the source dataset in Connections tray:




    1. Load DimBatch dimension table
    2. Load MaxMinManufacturingFact fact table




1.      The control flow


Note: No need to do ¡°Load InventoryFact¡± table.


2.      DimBatch dimension table (data source: BatchInfo.csv)



¡¤         Data types

BatchNumber, MachinNumber: four-byte signed integer [DT_I4]

ProductCode, NumberProduced, NumberRejected: four-byte signed integer [DT_I4]

TimeStarted, TimeStopped: database timestamp [DT_DBTimeStamp]

¡¤         Only check BatchNumber as the input of Dim Batch

¡¤         All columns are needed for fact tables




  1. MaxMinManufacturing Fact table (data source: BatchInfo.csv)




n  AcceptedProducts

q  [NumberProduced] ¨C [NumberRejected]

n  ElapsedTimeForManufacture

q  DATEDIFF(¡°mi¡±, [TimeStarted],[TimeStopped])

n  DateOfManufacture

q  (DT_DBTIMESTAMP)SUBSTRING((DT_WSTR,25)[TimeStarted],1,10)

n  This expression converts TimeStarted into a string and selects the first ten characters of that string. This string is then converted back into a date time, without the time portion.







Screenshot the successful run of the project for submission.