GET GUARANTEED SATISFACTION OR MONEY BACK UNDER CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT HELP SERVICES OF EXPERTSMINDS.COM - ORDER TODAY NEW COPY OF THIS ASSIGNMENT!
CSE5DWD - Data Warehouse Concepts and Design Assignment - Dimensional Modelling Business Case, La Trobe University, Australia
Objectives
- To create a Data Warehouse conceptual design using Star Schema Modelling
- To document all steps during the design process
Task - Design a data warehouse for Chemist Warehouse using multidimensional Modelling.
Answer - Dimensional Modeling Business Case
(i) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts.
Data Warehouse Bus Matrix
Data warehouse bus matrix shows all the process which is required for the development of the system and we can easily update each process at any time. Bus matrix is components of data warehouse architecture and can be used for the development of the system. It gives us visual picture of all the business process and it is not related to any database technologies.
As per the case study we have created a data warehouse bus matrix and shown below:
|
Dimension →
Bus. Process ↓
|
Date
|
Customer
|
Product
|
Retailer
|
Employee
|
Store
|
Promotion
|
Warehouse
|
Receipt
|
|
Purchase
|
X
|
|
X
|
X
|
|
X
|
X
|
X
|
|
|
Inventory Analysis
|
X
|
|
X
|
X
|
X
|
|
|
X
|
|
|
Service Activation
|
X
|
X
|
X
|
|
X
|
X
|
X
|
|
|
|
Product Sales
|
X
|
X
|
X
|
X
|
|
|
|
|
|
|
Promotion
|
X
|
X
|
X
|
X
|
|
X
|
X
|
|
|
|
Store sales
|
X
|
X
|
X
|
|
X
|
X
|
|
X
|
|
|
Customer receipt
|
X
|
X
|
X
|
X
|
|
X
|
X
|
|
X
|
|
Patient Support
|
X
|
X
|
X
|
X
|
X
|
X
|
|
|
X
|
|
Medicine Order
|
X
|
X
|
X
|
|
|
X
|
|
|
X
|
|
Payment from Customer
|
X
|
X
|
X
|
|
|
X
|
|
|
X
|
|
Return
|
X
|
X
|
X
|
X
|
X
|
X
|
X
|
|
X
|
DO WANT TO HIRE TUTOR FOR ORIGINAL CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT SOLUTION? AVAIL QUALITY CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT WRITING SERVICE AT BEST RATES!
(ii) Design the star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that your attributes are named using verbose textual descriptions.
Star Schema
By the given case study we need to design a start schema which will have all the details of dimension table and fact tables. The purpose of Chemist Warehouse is to store the details of product and buyers with their sales record in the system. All the customer details who purchase the medicine from any store is recorded for further use and we can easily retrieve the data from the database as per the requirement of the user. All receipts are created for each customer who have amount to be paid for each product and date of order. Store management - Billing and payments are these details are recorded in the system. Customer can pay the bills by cash, credit card or debit cards as per the choose.
(iii) Create the following table with a row for each fact table in your design, indicating the granularity of each fact and a brief justification for choosing that granularity.
|
Fact table Name
|
Fact granularity
|
Fact table type
|
Brief justification
|
|
Fact Sales
|
Receipt No
|
INT
|
It will store all details of receipt number given to any customer.
|
|
Sold Count
|
INT
|
All sold product count
|
|
Total Promotion Count
|
INT
|
No of promotion for particular product
|
|
Total Sales
|
INT
|
Total cost value for the product.
|
|
Brand
|
String
|
Brand name of each product
|
|
Category
|
String
|
Category name for each product
|
|
Supply Month
|
INT
|
Month of supply for the product
|
|
Store
|
String
|
Store details of each product
|
|
Time
|
Time
|
Time details
|
|
Customer
|
INT
|
Customer who buy each product
|
|
Employee
|
INT
|
Employee who sold each product
|
|
Sales
|
INT
|
sales details of each product
|
|
UPC
|
INT
|
Product code details of each product
|
|
Fact_supply
|
Key ID
|
INT
|
key code details of each product
|
|
Product
|
INT
|
Product code details of each product
|
|
Retailer
|
INT
|
retailer details of each product
|
|
Supply Date
|
INT
|
Supply date details of each product
|
|
Fact_Stock
|
Stock ID
|
INT
|
Stock id details of each product
|
|
Store No
|
INT
|
Store number details of each product
|
|
Inventory
|
INT
|
inventory details of each product
|
|
Date
|
INT
|
Date of checking od stock details of each product
|
|
Fact_promotion
|
Key ID
|
INT
|
Key id details of each product
|
|
Promotion
|
INT
|
Promotion details of each product
|
|
Product
|
INT
|
Product code details of each product
|
|
Promotion Date
|
INT
|
Promotion date of each product
|
GETTING STUCK WITH SIMILAR CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT? ENROL WITH EXPERTSMINDS'S CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT HELP SERVICES AND GET DISTRESSED WITH YOUR ASSIGNMENT WORRIES!
(iv) Create the following table with a row for each dimension table in your design, giving a brief justification for choosing that dimension, and indicating any attribute hierarchies that exist within the dimension.
|
Dimension table name
|
Brief justification
|
Attribute hierarchies
|
|
DimProduct
|
Product Dimension stored all details for any product
|
UPC, Product name, Brand, Category , Price , Description
|
|
DimCustomer
|
Customer Dimension
|
Customer ID, Name, Address, Phone No, Zip code
|
|
DimSales
|
Sales Dimension
|
Sales code, Sales Region, Sales description
|
|
DimPromotion
|
Promotion Dimension
|
Promotion Id, Scope , Category , Start date , End date , Discount level
|
|
DimTime
|
Calendar Date Dimensions
|
Time Key, Date ,Month, year
|
|
DimEmployee
|
Employee Dimension
|
Employee ID, Name, Address, Phone No, Zip code
|
|
DimRetailer
|
Retailer Dimension
|
Retailer ID, Name, Address, Phone No, Zip code
|
|
DimStore
|
Store Dimension
|
Store No, name, Location, Phone No
|
|
Inventory
|
Inventory will keep all details of product stock
|
Inventory , Product , Stock ,date
|
NEVER BE CAUGHT IN PLAGIARISM, AVAIL CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM AND SAVE HIGHER MARKS!
(v) Create the following table with a row for each design feature you have used, such as handling of possible null foreign keys, and the inclusion of any fact-less fact tables, degenerate dimensions, role playing dimensions, junk dimensions, outriggers, minidimensions, or any other design techniques discussed in the lectures. Provide a brief description of each design feature used (how and where it is used - not the theory behind the concept), any possible problems & solutions with a justification for their use.
|
Design feature
|
Brief description
|
Brief justification
|
|
Dim Store and Inventory
|
Outrigger Dimensions
|
The store dimension can contain a reference to Inventory dimension table.
|
|
Dim Product and Dim Store
|
Outrigger Dimensions
|
The Product dimension can contain a reference to Store dimension table.
|
|
DimProduct and fact_sales
|
Role-Playing Dimensions
|
The Product dimension will be referred to fact_sales many times.
|
|
DimCustomer and fact_sales
|
Role-Playing Dimensions
|
The Customer dimension will be referred to fact_sales many times.
|
|
DimSales and fact_sales
|
Role-Playing Dimensions
|
TheSales dimension will be referred to fact_sales many times.
|
|
DimPromotion and fact_sales
|
Role-Playing Dimensions
|
The Promotion dimension will be referred to fact_sales many times.
|
|
DimTime and fact_sales
|
Role-Playing Dimensions
|
The Time dimension will be referred to fact_sales many times.
|
|
DimEmployee and fact_sales
|
Role-Playing Dimensions
|
The Employee dimension will be referred to fact_sales many times.
|
|
Dim Retailer and fact_sales
|
Role-Playing Dimensions
|
The Retailer dimension will be referred to fact_sales many times.
|
|
DimStore and fact_sales
|
Role-Playing Dimensions
|
The Store dimension will be referred to fact_sales many times.
|
EXPERTSMINDS.COM GIVES ACCOUNTABILITY OF YOUR TIME AND MONEY - AVAIL TOP RESULTS ORIGINATED CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT HELP SERVICES AT BEST RATES!
(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed above.
Business Questions
1. What category and brand of products are the most popular in this year? Has this always been the case or has there been a shift in recent years? What particular product is most popular across nation, what are the total sales in past 3 years?
Answer - For this question we need to select the dimensional tables Product and fact table sales .So that we can calculate the value of Total sales for particular product for last 3 years.
2. Are there certain times of the year when more products are sold? Which day of the week more products sold?
Answer - For this question we need to select the dimensional tables Product, time fact table and fact table sales .So that we can know the month or time in which that particular product is in high demand .
3. What products are short of supply in this month? Has this always been the case in whole year?
Answer - We need to check the dimensional inventory to know the shortage of product.
4. What areas of customers are frequently buyers at our stores in last 12 months?
Answer - To answer this question we need to select customer table and fact sales for retrieving all records of last 12 months.
5. What are top 3 stores have the highest sales across the country?
Answer - To answer this question we need to select store table and fact sales for retrieving all records of stores who's having highest sales.
6. How many patients are regular using our medications? Which medication is the most selling product?
Answer - The patients dimension and fact sales will select and the we need retrieve the medicine detail which is purchased by customers frequently.
7. Which age group of customers is most likely use our Vitamins across nation? Does this vary across different location or times of the year?
Answer - We need to select the customer dimension and fact table so that we can which age group customers purchase most products.
ENROL WITH CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT HELP AND HOMEWORK WRITING SERVICES OF EXPERTSMINDS.COM AND GET BETTER RESULTS IN CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENTS!
8. Does the promotion activity provide the benefits to the business?
Answer - To know the promotion activity for each product we have to retrieve the value of number of promotion for each product in each month.
9. The promotion period in Christmas month has increased the sales comparing with the same period in last year?
Answer - To know the promotion activity for each product we have to retrieve the value of number of promotion for each product in December month.
10. Any products have not been sold out during the promotion period?
Answer - To know about the product which is sold in the period of promotion then we have to retrieve value from promotion dimension and fact table.
11. Who are the most loyal customers (top 5) to our business? What is the main location of those people purchasing the most expensive cosmetics products?
Answer - By selecting particular category of product and select data from customer and fact table for retrieving top 3 customer purchaser of cosmetic product.
12. Do customers prefer to go online purchases or buy product physically at local store? 13. Do VIP members bring more business to our company?
Answer - To answer this question we have select particular customer who have VIP membership and method of purchasing.
24/7 AVAILABILITY OF TRUSTED CSE5DWD - DATA WAREHOUSE CONCEPTS AND DESIGN ASSIGNMENT WRITERS! ORDER ASSIGNMENTS FOR BETTER RESULTS!
Get our La Trobe University, Australia Assignment Help services for below mentioned courses like:-
- CSE5DSS Decision Support Systems Assignment Help
- CSE5ADB Advanced Databases Assignment Help
- CSE5BIO Bioinformatics Technologies Assignment Help
- CSE5ALG Algorithms and Data Structures (PG) Assignment Help
- CSE5BDC Big Data Management on the Cloud Assignment Help
- CSE5BIO Bioinformatics Technologies Assignment Help
- CSE5DEV Data Exploration and Visualisation Assignment Help
- CSE5DMI Data Mining Assignment Help