Essay BIAM 530 Week 6 Business Intelligence DeVry

BIAM 530 Week 6 Business Intelligence DeVry

BIAM 530 Week 6 Business Intelligence, Data Warehousing, and Key Performance Indicators (KPIs)

BIAM 530 Week 6 Discussions

DQ 1 BUSINESS INTELLIGENCE AND DATA WAREHOUSING & KEY PERFORMANCE INDICATORS (KPIS)

Discuss how a business organization might use data marts as a part of their management and decision support strategy.
You are a business analyst for a company that is launching a major initiative to improve the quality of its customer service. How would you go about selecting KPIs for this initiative?

DQ 2 WORKOUT ROOM

Please share your expertise on this Week’s Lab Assignment. Give examples or discuss topics you might consider using and help a classmate. Please post two (2) times throughout the week. (On two different days.)

BIAM 530 Week 6 Lab Building and Using a Data Warehouse

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.

Be sure the right database is selected!

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.