Project 1: Designing and Creating Data Marts

   Next >

Due Date

See the calendar for due date.

Objectives

  1. To design a data mart with:
    • dimensions
    • hierarchies
    • measures
    • data types
    • Primary Keys (PKs)
  2. To create a data mart using SQL Server Management Studio
  3. To create a data mart using SQL Server Data Tools
  4. To become familiar with data mart design
  5. To learn about the data mart structure and components
  6. To create dimensions, hierarchies, and measures
  7. To learn how to create data marts
  8. To learn how to create data cubes

Directions

  1. Obtain and Install VPN Client (for off-campus connections)
    The software we are using in our TSYS School of Computer Science is under a special VPN (Virtual Private Network). It includes the BI Student Virtual Server (BICxx) will use in our class. In order to access the the BICxx server from off campus, you have to do the following:
    1. Print the VPN Student User Agreement and email a signed copy to Ms Aurelia Smith.
    2. Install a VPN client (e.g., FortiNet) that allows to connect to the VPN and work with the BI Student Virtual Server.
    3. Depending on the program you installed, you usually need to add a new remote access connection.
    4. Set the remote gateway to v1.columbusstate.edu, using port 10443. Use your CSU username and password here. (The same username and password that you use for CougarVIEW or MyCSU.)
  2. Connect to your assigned virtual machine (VM)
    1. Connect using a remote desktop program with the IP address that was emailed to you.
    2. Enter your VM username (<lastname>_<firstname> [and only your first name, do not include a middle name or a number]) and the password that was emailed to you.
    3. (Click Yes on the warning message "The identity of the remote computer cannot be verified....")
    Note, when connected to your VM:
    1. Do not click on "Shut down". This will power off the remote server making it inaccessible. Instead, click on "Disconnect".
    2. Do not click on the "X" icon on the Connection bar. Doing this will leave your programs are running. Instead, click on "Disconnect".
    3. Do not change your user name or password
    4. Do not install or uninstall software
    5. Do not Make any changes to the server software and files (with exception of requirements for class work).
  3. Complete the following Learn By Doing sections:
    1. Designing the Maximum Miniatures Manufacturing Data Mart (pp. 100-108)
      Read, follow along, and make sure you understand each step - from where the data sources are coming, tables, columns, data types, dimensions, hierarchies, measures, PKs, and FKs.
    2. Creating the Maximum Miniatures Manufacturing Data Mart Using the SQL Server Management Studio (pp. 108-116)
      • Always open SQL Server Management Studio 'as Administrator' on the VM (by right clicking on the icon, selecting "More >", then "Run as administrator")
      • Use MaxMinManufacturingDM-<yourInitials> instead of MaxMinManufacturingDM for the database name.
      • For the data types that are not available from the drop down menu, just type it in.
      Now you get to create a data mart with dimensions, hierarchies, and a measure (fact) table. Make sure that you set the PKs. (The foreign keys will be set later when the data mart will be populated.)
    3. Creating the Maximum Miniatures Sales Data Mart Using SQL Server Data Tools (pp. 116-129)
      • Always open SQL Data Tools 'as Administrator' on the VM (by right clicking on the icon, selecting "More >", then "Run as administrator")
      • Use MaxMinSalesDM-<yourInitials> for both the database name and the project name.
      These steps will create a MinMaxSales-<yourInitials> data cube with dimensions. The Schema Generation will create tables in the MaxMinSalesDM-<yourInitials> data mart. Lastly, you'll extend the data mart with additional table columns using SQL Server Management Studio.
Note: Grading will be performed on your BICxx server. Consequently, please save files at their default locations.

Rubric:

Points      Item
----------  --------------------------------------------------------------
            Creating MaxMinManufacturingDM-<yourInitials>
_____ / 15  Creating tables
_____ / 15  Creating columns & data types
_____ /  5  Creating PKs

            Creating MaxMinSalesDM-<yourInitials>
_____ / 10  Creating the data cube
_____ / 15  Creating dimensions
_____ / 10  Creating the connection to a DB
_____ / 15  Creating tables in the relational DB

_____ / 85  Total