Using the requirements of the Final Project( see the attachment)
Complete:
Task 2: Select Analysis
1. Select a Line of Analysis
2.Review the AW data to determine the Dim and Fact tables required to solution the operation
Complete an SSAS solution for theLine of Analysisselected
What to Submit:
1.A Word document with a list of the Dim and Fact tables used to create the solution and a copy of the Cube schema
2. A Zipped folder of the SSAS solution
Naming convention applies
BCS 425 – Final Project – Spring 2020 Prof. S. Lees 1 Final Project Due: May 10th. 2019 @ 11:59 pm Company Overview: Adventure Works Cycles Adventure Works Cycles is a large multinational bicycle manufacturer, with headquarters located in Bothell, Washington. The company has approximately 300 employees, 29 of which are sales representatives. The primary distribution channel for Adventure Works Cycles through the retail stores of their resellers. These resellers are located in Australia, Canada, France, Germany, the United Kingdom, and the United States. Adventure Works Cycles also sells to individual customers worldwide by means of the Internet. Adventure Works Cycles has five major product offerings: _ Bikes – Three primary bike product lines: Mountain, Road, and Touring. _ Accessories – Examples include helmets and water bottles. _ Clothing – Examples include jerseys and biking shorts. _ Components – Examples include bottom brackets and frames. _ Services – Examples include premium service and standard service. BUSINESS PROBLEMS: Product/Sales Growth Inability to determine trends. Unable to market its product and reach customers Info Restricted Available information is restricted by the current enterprise resource planning systems’ functionality. Difficult Difficult for end users to access operational information. Slow and Costly Building new reports is slow and costly. Computer Science Department BCS425 – Spring 2020 Decision Support Systems and Data Warehousing Dr. Shellon Blackman-Lees Email:
[email protected] BCS 425 – Final Project – Spring 2020 Prof. S. Lees 2 TABLE 1: LINES OF ANALYSIS The following are anticipated lines of analysis for sales and marketing at Adventure Works Cycles Line of Analysis Description Product profitability analysis Analyzing product sales margin by monitoring costs, discounts, and selling prices Variance Analysis Comparing forecast sales to actual sales Trend and growth analysis Understanding how the current period compares to prior periods Industry Market share analysis Analyzing market share data for the sellers of Adventure Works Cycles bicycles Promotion effectiveness Identifying how promotions influence sales performance SOLUTION Produce a simple consistent model that is expressed in the users’ own language and will enable them to easily access information using the tasks outlined in the requirements below. BI REQUIREMENTS Task 1: Load AW Data (SSIS) – this should have been completed already Load company data to SQL Data files have been posted to Blackboard Task 2: Select Analysis From the Table 1, identify ONE business process for analysis (like sales) Review and identify the data (in SQL) that you would require to provide the corresponding business intelligence o Identify measure or facts (sales dollar) o Identify dimensions for facts (product dimension, location dimension, etc) o List the columns that describe each dimension (region name, branch dimension, etc) o Determine the lowest level of summary in a fact table (sales dollar) o Based on the analysis selected, list at least 5 questions the you intend to provide intelligence for Task 3: Build and Deploy Analysis (Cube) – SSAS Task 4: Visio Diagram o Using Visio, design the schema for the data analysis o ***Note***: This file is in addition to your project solution BCS 425 – Final Project – Spring 2020 Prof. S. Lees 3 Task 5: Analysis Report (SSRS)/ or TABLEAU o Build a report to present the information/intelligence of the analysis to your client – Adventure Works o In a Word document, include the SQL queries applied to the Cube to build form your analysis PRESENTATION REQUIREMENTS 1. Executive Summary 2. Line of Analysis 3. Overview of Business Problems 4. Identify the Solution Focus (questions that need answers) 5. Data Schema Design 6. Analytical Report (Screenshots acceptable) BCS425 – Spring 2020 Computer Science Department Computer Science Department