Ask a Question

DBM 298 ENTIRE COURSE

Regular price $100.00

DBM 298 ENTIRE COURSE

 

DBM 298 Week 1 Individual Assignment Design a Data Mart for University of Phoenix

 

Imagine that you are designing a data mart that will be used to analyze post activity for University of Phoenix (UOPX) online courses.

Use the data mart to answer at least three of the following sample questions:

  • How many total posts were posted by University of Phoenix students in a given course over the past six months?
  • What is the average number of posts per section of a given course?
  • What is the total word count of all posts made by a given student?
  • How many total posts were made on Saturdays?
  • What percentage of posts in Database Management (DBM) courses were marked substantive?
  • How does the average count of posts per section for students who enrolled in the last year compare to the average count of posts per section for students who enrolled more than three years ago?
  • What percentage of overall posts come from students who were ever in the military?

Assume that each record in your fact table represents a single post by a student. (Facilitator posts are out of scope for this data mart.)

Include three separate dimension tables for date/time, student, and course section. 

Ensure you have the correct data types for each field.

Design your data mart in SQL Server.

Note: You do not have to load real data into the data mart for this exercise, we are only looking for a design.

Collect all of your deliverables into a single .zip file for submission.

Submit the Data Definition Language (DDL) scripts needed to create the fact and dimension tables used to answer at least three of the questions above, to the Assignment Files tab above.

 

 

 

DBM 298 Week 2 Individual Assignment Data Mart Practical Tips Guide Project, Part I

 

Based on the discussion you had in your learning team, create a quick reference guide that describes two practical tips you anticipate users will find valuable when building a data mart.

Address the following:

  • Choose two practical tips from your learning team collaboration.
  • Use a template available in a software that allows for text and visual graphics to explain the practical tips. Examples of software you may use include Microsoft® PowerPoint® or Piktochart.
  • Include text, graphics and/or charts, as needed, to clearly convey the information.
  • Create at least one slide/page per practical tip. You should have two slides in total.

Save your file in a format you can submit such as the original software file format or PDF file format. You will add to this file in the following weeks and submit the final guide in Week Five.

Submit what you have completed up to this point to the Assignment Files tab above.

 

 

DBM 298 Week 2 Individual Assignment Extract, Transform and Load (ETL) Hands-On Project

 

Complete the demonstration video available in the Student Materials showing how to use the Toolwire® environment to create a database, load one of the files into a table, generate the associated DDL for the table, and detach/reattach the database. You can follow the same approach for the other two tables.

Use the Toolwire® SQL Server® environment to build a small data mart for air traffic analysis.

Complete the following steps:

  1. Upload the data files available in the Student Materials to the Toolwire® SQL Server®.
  2. Create a database in SQL Server® to hold your data.
  3. Load the data files into your SQL Server® database using SQL Server® Integration Services (SSIS). You will load them into tables named "src_<filename>". For example, airline.txt should be loaded into a table called src_airline.
  4. Detach your SQL Server® database so that the data file can be submitted as part of your assignment.

Note:  All three tables will go into the same database, so you will create the database and perform the detachment only once (not once per table).

Submit the following in a single .zip file, to the Assignment Files tab above:

  • Your DTSX files from SSIS - submit a separate DTSX file for each data file being loaded.
  • The DDL scripts needed to create your tables.
  • A detached version of your database.

 

 

DBM 298 Week 3 Individual Assignment Data Mart Practical Tips Guide Project, Part II

 

Based on the discussion you had in your learning team,create a quick reference guide that describes two practical tips you anticipate users will find valuable when building a data mart.

Address the following:

  • Add to the file you created in Week Two's practical guide by choosing two practical tips from your learning team collaboration.
  • Use a template available in a software that allows for text and visual graphics to explain the practical tips. Examples of software you may use include Microsoft® PowerPoint® or Piktochart.
  • Include text, graphics and/or charts, as needed, to clearly convey the information.
  • Create at least one slide (page) per practical tip. In total, your file should have at least four slides or pages when this week's assignment is complete.

Save your file in a format you can submit such as the original software file format or PDF file format. You will add to this file in the following weeks and submit the final guide in Week Five.

Submit what you have completed up to this point to the Assignment Files tab above.

 

 

DBM 298 Week 3 Individual Assignment Stored Procedure Hands-On Project

 

This week, you will be taking the raw data you loaded last week using SQL Server Integration Services (SSIS) and loading it into fact and dimension tables using stored procedures.

Create the fact and dimension tables described in the attached table specifications document. Note that you must make your table names, data types, and foreign key relationships match these specifications exactly. You must save and submit the Data Definition Language (DDL) statements needed to create each table as part of your assignment.

Create a stored procedure that loads each of the tables from its corresponding source table. The three procedures you will create are:

  • Load_Dim_Airlines: This stored procedure will take the data from src_Airlines and load it into Dim_Airlines. 
  • Load_Dim_Airports: This stored procedure will take the data from src_Airports and load it into Dim_Airports. Note that you will have to do some special conversions to turn the latitude and longitude in the source data into a geography data type, and additional conversions to coerce the character-based timezone into an integer type. This may require outside research; however there are plenty of examples of doing this on the web, and sample code is suggested below.
  • Load_Fact_Routes: This stored procedure will take the data from src_Routes and load it into Fact_Routes. It should only load routes that have a corresponding airline and airport in the respective dimensions tables (not all routes in the source data meet these criteria).

Below are general notes that may be useful:

  • You will need to cast a negative character string to a numeric value. The following code should provide ideas:
    • SELECT @NegativeNumeric = -1 * CAST ( REPLACE(@NegativeCharString ,'-','') AS DECIMAL (10,2)
  • You will need to cast A VARCHAR to SMALLINT. The following code should provide ideas:
    • SELECT CAST(CAST('2.00' AS DECIMAL) AS SMALLINT)
  • At some point, you will find that you need to specify an SRID for working with the Geography data type. Use the value 4326.

Create a backup statement (in SQL) to ensure your database has a proper backup.

Submit all of the statements you used to create your tables and stored procedures in a single .zip file to the Assignment Files tab above.

 

 

DBM 298 Week 4 Individual Assignment Data Mart Practical Tips Guide Project, Part III

 

Based on the discussion you had in your learning team,create a quick reference guide that describes two practical tips you anticipate users will find valuable when building a data mart.

Address the following:

  • Add to the file you created in Week Two's practical guide by choosing two practical tips from your learning team collaboration.
  • Use a template available in a software that allows for text and visual graphics to explain the practical tips. Examples of software you may use include Microsoft® PowerPoint® or Piktochart.
  • Include text, graphics and/or charts, as needed, to clearly convey the information.
  • Create at least one slide/page per practical tip. In total, your file should have at least six slides or pages when this week's assignment is complete.

Save your file in a format you can submit such as the original software file format or PDF file format. You will add to this file in the following week and submit the final guide in Week Five.

Submit what you have completed up to this point to the Assignment Files tab above.

 

DBM 298 Week 4 Individual Assignment Automate Extract, Transform and Load (ETL) Process

 

This week, you will automate the load process you began in previous weeks.

Create a stored procedure called "sp_trunc_tables" that truncates all six of the tables you created for the assignments in previous weeks.

Create an operator in your database that has an email address.

Create a job named "LoadMart" that takes the following steps:

  • Executes sp_trunc_tables to clear out all of the tables
  • Executes the SQL Server Integration Services (SSIS) packages you created in Week Two to load the "source" (src_) tables. Each package can be executed as a separate step. Use the "File System" package source to execute your DTSX files from Week Two.
  • Execute the stored procedures you created in Week Three to load data from the source tables into the fact and dimension tables.

Schedule your job to run once an hour throughout the week.

Configure your job to send notices to the operator you created when it completes successfully. The operator will not get all of the emails because we have not set up Database Mail yet.

Write a 700-word (minimum) paper that describes three stored procedures and explain why you think they would be essential for your flight data mart. 

Format your paper consistent with APA guidelines.

Submit the following to the Assignment Files tab above:

  • The paper
  • The code for sp_trunc_tables
  • Screenshots of the following pages from your "LoadMart" job, after the associated window has been made full screen:
    • General
    • Steps
    • Schedules
    • Notifications

 

 

DBM 298 Week 5 Individual Assignment Data Mart Practical Tips Guide Final Project

 

Based on the discussion you had in your learning team, create a quick reference guide that describes two practical tips you anticipate users will find valuable when building a data mart.

Address the following:

  • Add to the file you created in Week Two's practical guide by choosing two practical tips from your learning team collaboration.
  • Use a template available in a software that allows for text and visual graphics to explain the practical tips. Examples of software you may use include Microsoft® PowerPoint® or Piktochart.
  • Include text, graphics and/or charts, as needed, to clearly convey the information.
  • Create at least one slide (page) per practical tip. In total, your file should have at least eight slides or pages when this week's assignment is complete.

Save your file in a format you can submit such as the original software file format or PDF file format.

Submit your Data Mart Practical Tips Guide file as your deliverable for this assignment, to the Assignment Files tab above. In total, the file should have at least eight slides (two slides from each of the Weeks Two through Five).

 

 

DBM 298 Week 5 Individual Assignment Final Flight Data Mart Project with Reports

 

This is the final portion of a cumulative database project.  You will need to include your entire flight data mart files from the previous weeks.  In Week Five, you will enhance your data mart by creating the following queries:

Write three SQL queries from your flight data mart that would be useful for management on a monthly basis.  Write three SQL queries that would be useful for management on a yearly basis.

Submit a single document that contains the text of each query (suitable for copying and pasting - no images) and a screenshot of the output from the query.  Write an explanation of why these queries would be essential.  

Review your Flight Data Mart that you have created throughout Weeks Two through Five, along with any feedback you received from your instructor.

Ensure you have successfully completed the following:

  • Selected an appropriate hardware platform for a data warehouse
  • Designed and implementeda data warehouse
  • DevelopedTools for data extraction, transformation & loading
  • Cleansed Data by using Data Quality Services
  • Created databases & database objects
  • Demonstrated an understanding of SQL Server Security
  • Secured a SQL Server and objects
  • Created a backup for databases
  • Created databases and objects using Data Manipulation Language (DML) statements
  • Modified data and created indexes and triggers

Submit all of your files, scripts, SQLs and screenshots for your Flight Data Mart with Reports in a single .zip file, to the Assignment Files tab above.

 

Questions & Answers

Have a Question?

Be the first to ask a question about this.

Ask a Question