Description
BIAM 530 Week 6 Lab Building and Using a Data Warehouse DeVry
Lab Overview
As you may recall from an earlier Lab activity, Adventure Works Cycles is a fictional bicycle manufacturing company headquartered in Bothell, WA, that sells metal and composite bicycles to distributors in North America, Europe, and Asia. Adventure Works also sells bicycles directly to consumers via the company’s website.
Adventure Works uses a data warehouse to provide decision support services to executives. In this Lab activity, to better understand the structure of a data warehouse, you will first create a simple example of a star schema in your individual data warehouse area. You will use SQL commands to transfer data from the Adventure Works data warehouse into your data warehouse. Finally, you will use your simplified data warehouse to generate a business intelligence report on sales, analyze the data shown on the report, and make recommendations to management.
Deliverables
After completing the steps below, submit a single Microsoft Word file named LabWeek6.xxx.docx (where xxx = your initials). This file will contain screen shots for your queries to create and populate your data warehouse, a screen shot of your completed sales report, and your analysis and recommendations to management based on the sales report.
Your deliverable will be evaluated based on the following rubric.
Step | Criteria | Points | % |
---|---|---|---|
2 | CREATE TABLE queries have valid SQL syntax and reference the specified tables, columns, data types, and constraints. Results shown indicate successful execution. | 10 | 20% |
3 | INSERT queries have valid SQL syntax and reference the specified tables and columns. Results show correct number of records were extracted from the source database and loaded into the student’s individual data warehouse. | 10 | 20% |
5 | Sales report correctly shows sales analyzed by specified dimensions with clear, easily readable layout and professional formatting. | 15 | 30% |
6 | Analysis and recommendations are reasonable, show good business judgements based on the report, and are written professionally with no grammar, spelling, or typographical errors. | 15 | 30% |
Total | 50 | 100% |
Required Software
Microsoft Office: Word Use your personal copy of Word or the centralized version available in the Citrix Virtual lab (link below).
Microsoft SQL Server Management Studio 2016
SQL Server Data Tools 2015
Adventure Works 2016 Sample data warehouse database
Access the software at https://lab.devry.edu (Links to an external site.)Links to an external site..
Tutorial
The following tutorial videos will demonstrate, in general, how to create and populate a star schema for a data warehouse using SQL Server Management Studio and how to generate a business intelligence report from a data warehouse using SQL Server Data Tools. IMPORTANT: The data warehouse and report shown in the video may NOT be identical to those you will create in this lab. Please watch the video to gain an understanding of how to work with SQL Management Studio and SQL Server Data Tools, but follow the written steps below to create the correct deliverable for this Lab activity, based on the Scenario/Summary and Deliverables sections at the beginning of this page.
Creating a Data Warehouse and BI Report Part 1
Creating a Data Warehouse and BI Report Part 2
Creating a Data Warehouse and BI Report Part 3
Creating a Data Warehouse and BI Report Part 4
Creating a Data Warehouse and BI Report Part 5
Creating a Data Warehouse and BI Report Part 6
Creating a Data Warehouse and BI Report Part 7
Creating a Data Warehouse and BI Report Part 8
Lab Steps
Step 1: Launch SQL Server Management Studio and Connect to the Database
- Log in to the Citrix Lab environment (this Lab MUST be performed in Citrix to access the Adventure Works database). See Course Resources for information on how to access the Citrix Lab environment.
- If necessary, locate the SQL Server Management Studio 2016 app in the Citrix All Apps menu and click to add it to your Citrix menu screen.
- Click the SQL2016 Server Management Studio icon to launch the application.
- In SQL Server Management Studio, connect to the Database Engine d1w-sqlp00am01\BIAM530.
- Click to place your cursor in the blank query pane on the right.
- In the Available Databases list, change the selected database from master to AdventureWorksDW2016. This is the data warehouse for Adventure Works Cycles.
In a previous Lab, we used the Adventure Works operational database. In this Lab activity, we will begin by examining the Adventure Works data warehouse, so this time, be sure you do select the database with the letters “DW” in the name.
Step 2: Create Dimension and Fact Tables for a Data Warehouse
- Expand the list of tables for the AdventureWorksDW2016 database, right-click on the DimProduct table, and select Script Tables as > CREATE To > New Query Editor Window. This will generate a SQL script in a query window that can be used to create the DimProduct table. In this script, delete the USE command at the beginning and also delete all the ALTER TABLE commands at the end
- Generate a similar script for creating the DimCustomer table. As before, delete the USE command at the beginning of the script and all the ALTER TABLE commands at the end.
- Generate a similar script for creating the FactInternetSales table. As before, delete the USE command at the beginning of the script. At the end of the script, delete all the ALTER TABLE commands except the ones that refer to the DimProduct and DimCustomer tables; leave those commands unchanged.
- Select the query window containing the script to create the DimProduct table. In the Available Databases list, change the selected database from AdventureWorksDW2016 to your individual data warehouse database. This is named Dnnnnnnnn530mmmyyDW, where Dnnnnnnnn = your DeVry student ID number and mmmyy = the month and year for the session you are taking this course. Note that the month is the beginning month for the session, not necessarily the month in which you are doing this Lab activity. For example, for the May 2018 session, you would select Dnnnnnnnn530MAY18DW, even if you are doing this Lab in June 2018. Be sure to select the database ending in DW (data warehouse) and not DB. Execute the script to create a copy of the DimProduct table in your individual data warehouse. Capture a screen shot showing the script and the result, which should be a message indicating the commands completed successfully. Paste this screen shot into a Microsoft Word document named LabWeek6xxx.docx (where xxx = your initials).
- Select the query window containing the script to create the DimCustomer table. Again, change the selected database from AdventureWorksDW2016 to your individual data warehouse database. Execute the script to create your copy of the DimCustomer table. Capture a screen shot showing the script and the successful result and paste this screen shot into your Word document.
- Select the query window containing the script to create the FactInternetSales table. Again, change the selected database from AdventureWorksDW2016 to your individual data warehouse database. Execute the script to create your copy of the FactInternetSales table. Capture a screen shot showing the script and the successful result and paste this screen shot into your Word document.
- Expand the list of tables under your individual data warehouse database and verify that the DimCustomer, DimProduct, and FactInternetSales are listed.
Step 3: Populate the Data Warehouse
- Right-click on the DimProduct table in your individual data warehouse and select Script Table as > INSERT to > New Query Editor Window. This will generate a SQL script that can be used to insert data into the table.
- At the beginning of the script, add the following command to allow inserting data into the table’s primary key:SET IDENTITY_INSERT DimProduct ONGO
- Immediately after the line beginning INSERT INTO . . . , add the primary key to the list of fields to be inserted by changing the line([ProductAlternateKey]to(ProductKey, [ProductAlternateKey]
- Find the line beginning VALUES. Delete this line and all subsequent lines up to (but not including) the line containing the GO command. Instead, enter the following to copy data from the existing DimProduct table in the Adventure Works data warehouse and insert it into your DimProduct table:
SELECT * FROM AdventureWorksDW2016.dbo.DimProduct - Execute the modified script to populate your DimProduct table. You should see a message indicating that the query executed successfully and showing the number of rows that were affected. Capture a screen shot showing the script and the number of rows affected and paste this screen shot into your Word document.
- Follow a similar procedure to create, modify, and execute a script to copy data from the DimCustomer table in the Adventure Works data warehouse to your DimCustomer table. Capture a screen shot showing the script and the number of rows affected and paste this screen shot into your Word document.
- Follow a similar procedure to create, modify, and execute a script to copy data from the FactInternetSales table in the Adventure Works data warehouse to your FactInternetSales table. For this table, it is not necessary to turn on IDENTITY_INSERT or add the primary key to the field list, since this table does not use an automatically generated primary key. Capture a screen shot showing the script and the number of rows affected and paste this screen shot into your Word document.
STEP 4: Launch SQL Server Business Intelligence Development Studio and Create a Report Server Project
- Log into the Citrix Lab environment if you are not already logged in.
- If necessary, locate the SQL Server Data Tools 2015 application and add it to your Citrix menu screen.
- Click the Data Tools icon to launch the application.
- Create a new Business Intelligence Project using the Report Server Project Wizard template. Name the project AdventureWorks DW Sales Report.
STEP 5: Create a Sales Report
- Click past the Welcome page of the report wizard.
- On the Select the Data Source page of the report wizard, create a new Microsoft SQL Server data source named AdventureWorksDW. Edit the data source properties to select your individual data warehouse as the data source. Recall that your individual data warehouse is named Dnnnnnnnn530mmmyyDW, where Dnnnnnnnn = your DeVry Student ID number and mmmyy = the month and year of the session.
- On the Design the Query page of the report wizard, use the Query Builder to create a query that includes all three tables (DimProduct, DimCustomer, and FactInternetSales). Select the ModelName field from the DimProduct table, the Gender field from the DimCustomer table, and the SalesAmount field from the FactInternetSales table.
- On the Report Type page of the report wizard, select Matrix.
- On the Design the Matrix page of the report wizard, use Gender as the columns, ModelName as the rows, and SalesAmount as the details.
- On the Completing the Wizard page of the report wizard, check the box to preview the report and then click Finish.
- After a short delay, the report will be displayed in the Preview tab. Capture a screenshot showing the report preview. Paste this screen shot into your Word document.
STEP 6: Analyze the Sales Report and Make Recommendations to Management
- Below the screen shot of the sales report in your Word document, write a three to five paragraph analysis of what these results tell you about Internet sales for Adventure Works.
- Also in your Word document, write at least three recommendations you would make to management based on these results.
STEP 7: Submit the Deliverable
Submit your completed Word document, including screenshots, analysis, and recommendations.
Course Tutor helps in providing the best essay writing service. If you need 100% original papers for BIAM 530 Week 6 Lab Building and Using a Data Warehouse DeVry, then contact us through call or live chat.
BIAM 530 Week 6 Lab Building and Using a Data Warehouse DeVry
![Best BIAM 530 Week 6 Lab Building and Using a Data Warehouse DeVry](http://coursetutor.us/wp-content/uploads/2018/05/eb-10.png)
ACCT 553 DeVry, BIAM 500 DeVry, CIS 500 STR, CIS 558 STR, ENG 105 GCU, FIN 390 DeVry, FIN 504 GCU, HCA 545 GCU, HCA 699 GCU, HLT 306 GCU, HLT 362 GCU, HLT 555 GCU, HLT 610 GCU, HLT 665 GCU, HOSP 594 DeVry, HRM 600 DeVry, MAT 144 GCU, MGMT 600, MGT 599 STR, MGT 655 GCU, MKT 373 GCU, PSY 362 GCU, PSY 565 GCU, PSY 575 GCU, PSY 665 GCU, SOC 102 GCU, SOC 320 GCU, SOC 372 GCU, SOC 412 GCU, NSG 6440 SU, HIM 515 GCU, NSG 4029 SU, HIM 615 GCU, NSG 3029 SU, NSG 4055 SU, NSG 6630 SU, NSG 6005 SU, CRMJ 310, PSY 510 GCU, CRMJ 300 Devry, CRMJ 425, SPD 200 GCU, HLT 490 GCU ,ECH 340 GCU , ECH 440 GCU , ECH 355 GCU ,ECH 350 GCU ,LDR 461 GCU ,ECH 425 GCU ,REL 212 STR ,SCI 115 STR ,CIS 505 STR ,JUS 652 GCU, MGT 640 GCU, CIS 527 STR, SOC 436 GCU,ACC 502 GCU, FIN 504 GCU , MATH 260 DeVry ,ETHC 445 DeVry ,ECET 220 DeVry , CARD 405 DeVry, NETW 203 DeVry, NETW 205 DeVry, ECET 365 DeVry ,MATH 270 DeVry ,PHYS 310 DeVry, BIB 106 GCU ,CIS 512 STR ,SYM 506 GCU ,ECN 601 GCU ,BIAM 570 DeVry, PSY 402 GCU , SOC 480 GCU , BIAM 530 DeVry
Reviews
There are no reviews yet.