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.
Questions & Answers
Have a Question?
Be the first to ask a question about this.