Databse warehouse help
Database warehouse help
Project 2 (Practice)
Adventure Works Cycles is a large, multinational manufacturing company. The company manufactures and sells metal and composite bicycles to North American, European and Asian commercial markets. While its base operation is located in Bothell, Washington with 290 employees, several regional sales teams are located throughout their market base. As a bicycle manufacturing company, Adventure Works Cycles has the following four product lines:
Bicycles that are manufactured at the Adventure Works Cycles. Bicycle components that are replacement parts, such as wheels, pedals, or brake assemblies. Bicycle apparel that is purchased from vendors for resale to Adventure Works Cycles customers. Bicycle accessories that are purchased from vendors for resale to Adventure Works Cycles customers.
The AdventureWorks2012 OLTP database supports the following typical sales and marketing areas:
Customer and sales-related information is a significant part of the AdventureWorks database. This topic provides details about the customers that are represented in the database. As a bicycle manufacturing company, Adventure Works Cycles has two types of customers: Individuals. These are consumers who buy products from the Adventure Works Cycles online store. Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives.
The following is the subset of the structure of the AdventureWorksDW2012 data warehouse that you need to use for this project.
Your first task is to develop an OLAP Cube based on the existing data warehouse. It should contain all dimensions, their attributes and measures from the above diagram. In addition, create named calculations for month and year based on the DateKey in the FactProductInventory table. Since DateKey is an integer, you would need to convert it into “2005” for year using the following statement:
You need to extract “200501” for month from DateKey using the similar statement as the above one.
DimProduct ProductKey ProductAlternateKey ProductSubcategoryKey WeightUnitMeasureCode SizeUnitMeasureCode EnglishProductName SpanishProductName FrenchProductName StandardCost FinishedGoodsFlag Color SafetyStockLevel ReorderPoint ListPrice Size SizeRange Weight
FactProductInventory ProductKey DateKey MovementDate UnitCost UnitsIn UnitsOut UnitsBalance
DimProductSubcategory ProductSubcategoryKey ProductSubcategoryAlternateK EnglishProductSubcategoryNa SpanishProductSubcategoryN FrenchProductSubcategoryNa ProductCategoryKey
Once you create new cube:
Make sure that you properly format measure (AggregateFunction & FormatString). Add new calculated measure named “TotalCost” as “UnitCost” * “UnitBalance”. Add new dimension named “Dim Date”. When you specify the source of information, select FactProductInventory table and DateKey as a key. Don’t forget to change the Attribute Types! Rename “DateKey” to “Date”. Make attributes invisible. Create a date hierarchy (Date-> Month -> Year) and make their relationships “rigid”. Add attributes for Product and ProductSubcategory. Make product attributes invisible. Crate product hierarchy. Make their relationships “rigid”. Add attributes for product. Define dimension usage by adding Dim Date dimension and verify corresponding relationship. Deploy the project (set the deployment properties if needed). Include the snapshot of the cube view into your project report.
Open SQL Server Management Studio and demonstrate that your cube is created and populated. Put Product Name and Years in the rows, and include UniteBalance and TotalCost measures. Include the snapshot of this view into your project report.
Create a KPI named “Availability” that indicates the level of profit as follows:
For UnitBalancet < 0 display red traffic light. For UnitBalancet = 0 display yellow traffic light. For UnitBalancet > 0 display green traffic light.
Use the Cube Browser to demonstrate its functionality. Display Availability KPI for one product during a given year and during one month of that year. Include the snapshots for the year and month into your project report.
Please create a report with the above indicated screen shots. Your report needs to have a title page with your name. Please upload your report to the class website.