223ism example of dimensional modeling case study with solution PDF

Preview

Summary

dfbgdg...

Description

Business Case Studies for Dimensional Modelling The Logical Design of Data Warehouse

Case Study Suitcase In Incorporated corporated se sells lls a range of produ products cts to customer wo worldwide. rldwide. You nee needd to develop a dat dataa warehouse mo model del based on the fo following llowing requireme requirements: nts: 1. Its marketing division is interested in better understanding its customers. They want to slice-and-dice invoice revenue by customer and product to analyze product penetration and cross sell opportunities. 2. Sales management wants to better understand the performance of their sales organization in terms of both sales orders and invoice revenue – which sale representatives are selling and invoicing which customers for what products under which discount terms. In addition to sales representative performance information, Sales is also interested in customer support cases. They want to understand which customers are experiencing product related support problems Finally, sales management wants the ability to better analyze forecast data. They’d like the ability to manipulate their sales forecasts by sales rep, product and customer characteristics.

3. Technical support currently has a stand-alone system for capturing customer support cases. However, the analytic capabilities are very limited. They would like the ability to analyze support cases by product, customer, and technician. They’d also like to access customer invoice revenue information in order to establish appropriate support case priorities.

223ISM Data Warehousing and Data Mining

Department of Information Systems, College of Computer Science, KKU

Solution of Case Study 1 Step 1 – Identify Data Marts based oonn Business Processes First, we have to determine all Business Processes included in the case study. Based on that, we can identify the data marts. All business processes bellow have been extracted from the case study (text written in red color). Therefore, we summarize them as: 1. Marketing Division: Business Process ##1: 1: Analyze invoice revenues 2. Sales Division: Data Mart 1 Business Process ##2: 2: Analyze invoice revenues Business Process ##3: 3: Analyze Sale Orders Data Mart 2 Business Process ##4: 4: Analyze Customer Support Cases Business Process #5: Sales Forecasting Data Mart 3 3. Technical Support Divisi Division: on: Data Mart 4 Business Process ##6: 6: Analyze invoice revenues Business Process #7 #7:: Analyze Customer Support Cases Hence, we identified 4 Data Marts based on the business processes and we will call them as follows: Data Mart 11: Invoice Revenue Analysis Data Mart 22: Sale Orders Analysis Data Mart 33: Sales Forecasting Data Mart 44: Support Cases Analysis

** in this case study, we will discuss just the first two data marts and the same should be performed for the other two. Step 2 - Declare the Grain  Grai Grainn of Data Ma Mart rt 1 (Invoice revenue Analysi Analysis): s): Invoice revenue by customer and product  Grai Grainn of Data Ma Mart rt 2 (Sales or order der Analysis): Number of sales orders and revenue by reps by customers by products and by discount terms.  Grai Grainn of Data Ma Mart rt 3 ………  Grai Grainn of Data Ma Mart rt 4 …....etc. etc. Step 3 – Identify Dimensions All Dimensions included in the case study are:  Date  Customer  Product  Sales Representative.  Discount Terms  Technician Step 4 – Identify Facts (This st step ep will be repeated for each single Data Mart identified in step 1) 4.1 – Choose a Data Ma Mart rt (choose the 1st Data Mart from the list n step 1)  Invoice Revenue Analysis 4.2 – Declare the Grain (already declared n Step 2)  Invoice revenue by customer and product 4.3 – Choose Dimensio Dimensions ns (from the list identified in Step 3)  Customer  Product  Date 4.4 – Choose the Facts (choose the measurement (numeric data) from the grain in step 4.2)  Total Revenue per Invoice Data Mart Di i

Invoice revenue Sales orders Analysis Sales Forecasting Support cases Analysis

Date

Customer

Product

X

X

X

223ISM Data Warehousing and Data Mining

Sales Reprst.

Discount Terms

Technician

Department of Information Systems, College of Computer Science, KKU

Star Schema of Data Mart 1 Product Product_Key P_Name SKU Brand

Invoice Revenue

Customer Customer_Key Name Address Region

Date_Key Product_Key Customer_Key Revenue per Invoice

Date Date_key Day Month year …

Step 4 - Identify the Facts (this is the 2nd iiteration teration for Data Ma Mart rt 2) St Step ep 4.1 – Choose the Data M Mart: art:  Sales Orders Analysis Step 4.2 – Declare the Grain  Number of sales orders and revenue by reps by customers by products and by discount terms. Step 4.3 – Choose the Dimens Dimensions ions  Customer  Product  Discount terms  Sales Reprst.  Date Step 4.4 – Choose the Facts  Total Revenue per Sales Order Data Mart Di i

Date

Customer

Product

Sales Reprst.

Discount Terms

X X

X X

X X

X

X

Invoice revenue Sales orders Analysis Sales Forecasting Support cases Analysis

Technician

Star Schema of Data Mart 2 Customers Customer_Key Name Address …

Products Product_Key P_Name SKU

Sales Repres. SR_Key Name Region …

223ISM Data Warehousing and Data Mining

Sale Orders

Date Date_Key Day Month Year

SR_Key Product_Key Customer_Key DisTerm_Key Date_Key Revenue per Sales Order

Discount Terms DisTerm_Key Term_Desc

Department of Information Systems, College of Computer Science, KKU...