Data Warehousing Exercise Guidelines

 

Exercise 4: Populating Maximum Miniatures Manufacturing Data Mart Dimensions

 

Note: The following is an outline of the exercise, which will be demonstrated and practiced in the computer lab. Refer to Delivery Business Intelligence with Microsoft SQL Server 2008 for more information.

 

Preparation: Data sources and destination definition

 

  1. Review database Lin.AccountingSystemDatabase There are five tables to be used: Countries, Product, Producttypes, Locations, and CapitalAssets
  2. Create SSIS project ISQS<course#>-<term>-EX4-<lastname> (for example, ISQS6339-2017-EX4-Lin)
  3. Define the source dataset in Connections tray

 

Loading dimensions ĘC Stage 1 (View video DW.EX4-2.MMM_Dim_Loading.Stage_1 in ~\isqs3358\Video demos 6339\)

  ProductType

       Drag a Data Flow Task item from the Toolbox and drop it on the Control Flow tab

       Right-click the Data Flow Task item just created and select Rename from the Context menu. Enter Load ProductType and press ENTER.

       Double-click the Load ProductType item. This takes to the Data Flow tab.

       Create a data flow consists of only two items: OLE DB Source and OLE DB Destination. Set a link between them from source to destination.

       Define Lin.AccountingSystemDatabase.ProductTypes as the source data for the OLE DB Source item

       Define <your database>.DimProductType as the destination table for the OLE DB destination item

       Map the two tables in the OLE DB Destination item.

 

  ProductSubType

       Following the same process as the above but using ProductTypes source table for dimension DimProductSubType

 

  Product

       Following the same process as the above but using Product source table for the dimension DimProduct

 

  Country

       Following the same process as the above but using Countries source table for dimension DimCountry

 

Loading dimensions ĘC Stage 2 (View video DW.EX4-2.MMM_Dim_Loading.Stage_2 in ~\isqs3358\Video demos 6339\)

  Plant (using SQL Command). The source table: Locations. Code for DimPlant loading in the OLE DB Source item:

 

SELECT LocationCode, LocationName, CountryCode

From Locations

WHERE LocationType = 'Plant Site'

 

       Change LocationCode to PlantNumber, LocationName to PlantName in the source item.

 

 

  Material (using SQL Command, Aggregate item). The source table: CapitalAssets. Code for DimMaterial loading in the OLE DB Source item:

 

SELECT AssetCode, AssetName, AssetClass, LocationCode,

Manufacturer, DateOfPurchase, RawMaterial

FROM CapitalAssets

WHERE AssetType = 'Molding Machine'

 

This data flow contains three Data Flow items: OLE DB Source, Aggregate, and OLE DB Destination.

 

       Modify the column names in the Column page of OLE DB Source item

 

Old Name

New Name

AssetCode

MachineNumber

AssetName

MachineName

AssetClass

MachineType

LocationCode

PlantNumber

RawMaterials

Materials

 

       In the Aggregate item, group data by the Material column

       Map columns in OLE DB destination item.

 

  MachineType (copied from the Material loading task). The source table: CapitalAssets.

 

This data flow also contains three Data Flow items: OLE DB Source, Aggregate, and OLE DB Destination.

 

       Copy and modify the Data Flow Control item for Material loading to create the one for DimMachineType loading

       In the Aggregate item, group data by the Material and MachineType columns

       Change the Use a Table or View drop-down list to DimMachineType.

       Map columns in OLE DB destination item.

 

  Machine (copied from the MachineType loading task)

 

This data flow only contains two Data Flow items: OLE DB Source and OLE DB Destination.

 

       Copy and modify the Data Flow Control item for MachineType loading to create the one for DimMachine loading

       Remove the Aggregate item

       Change the Use a Table or View drop-down list to DimMachine.

       Map columns in OLE DB destination item.

 

 

Debugging

  Step by step

       You may disable the complete data flows and only test the data flows behind them.

  Understand the error messages

  Watch database loading status

 

Screenshot the successful run of the project for submission.