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 data\Batchinfor CSV File\

of the shared driver) and Lin.OrderProcessingSystem (SQL server database, Not needed this time)

  1. Create two new package BatchLoad and FactLoad in the previously created SSIS project
  2. Define the source dataset in Connections tray:

 

Tasks:

 

    1. Create a Table InventoryFact in database MMMdimLoad-lastnanme.
    2. Load DimBatch dimension table
    3. Load MaxMinManufacturingFact fact table
    4. Load InventoryFact table

 

Instructions:

1.      InventoryFact Table

         Compound primary key: DateOfInventory, ProductCode, and Material

         Define two foreign keys

 

Column Name

Data Type

Allow Nulls

InventoryLevel

Int

No

NumberOnBackorder

Int

No

DateOfInventory

Datatime

No

ProductCode

Int

No

Material

Varchar(30)

No

 

2.      The control flow

 

No need to do ^Load Inventory ̄

 

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

 

 

This is the new one using SQL Server Visual Studio 2106. See the difference with the above:

1)      The order is different C could some of the orders are flexible?

2)      There is one more node C Data Conversion. Why?

 

The debugging result:

 

 

  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.

 

 

 

 

 

  1. InventoryFact table

 

n  OLE DB Source

q  OrderProcessingSystem.InventoryFact

n  OLE DB Destination

q  MaxMinManufacturingDM-lastname.InventoryFact

n  No transformation

 

 

Screenshot the successful run of the project for submission.