BIAM 530 Week 6 Lab Building and Using a Data Warehouse DeVry

1. 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.
2. 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.
3. Click the SQL2016 Server Management Studio icon to launch the application.
4. In SQL Server Management Studio, connect to the Database Engine d1w-sqlp00am01\BIAM530.
5. Click to place your cursor in the blank query pane on the right.
6. In the Available Databases list, change the selected database from master to AdventureWorksDW2016. This is the data warehouse for Adventure Works Cycles.

1. 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
2. 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.
3. 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.
4. 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).
5. 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.
6. 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.
7. Expand the list of tables under your individual data warehouse database and verify that the DimCustomer, DimProduct, and FactInternetSales are listed.

1. 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.
2. 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
3. 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]
4. 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
5. 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.
6. 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.
7. 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.

1. Log into the Citrix Lab environment if you are not already logged in.
2. If necessary, locate the SQL Server Data Tools 2015 application and add it to your Citrix menu screen.
3. Click the Data Tools icon to launch the application.
4. Create a new Business Intelligence Project using the Report Server Project Wizard template. Name the project AdventureWorks DW Sales Report.

1. Click past the Welcome page of the report wizard.
2. 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.
3. 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.
4. On the Report Type page of the report wizard, select Matrix.
5. On the Design the Matrix page of the report wizard, use Gender as the columns, ModelName as the rows, and SalesAmount as the details.
6. On the Completing the Wizard page of the report wizard, check the box to preview the report and then click Finish.
7. 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.

1. 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.
2. Also in your Word document, write at least three recommendations you would make to management based on these results.

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

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