Lab 1_Dimensional Modeling-2

.docx

School

California State University, East Bay *

*We aren’t endorsed by this school

Course

BAN 622

Subject

Marketing

Date

Jan 9, 2024

Type

docx

Pages

6

Uploaded by kumarabhinav176212

Report
Lab 1: Dimensional Model Design Name: Identify the dimensions, and fact measurements based on the following description: Question 1. One of the online retail company’s features is an e-wallet service, that holds credit that can be used to pay for products purchased on the platform. Users can receive credit in three different ways: (1). When a product purchase that is paid for is canceled, the money is refunded as cancellation credit. (2). Users can receive gift card credit as a gift. (3). If a user has a poor service experience, sorry credit may be provided. Requirement The Finance department of the company would like to build reporting and analytics on the e- wallet service so they can understand the extent of the wallet liabilities the company has. Some of the questions they would want to answer from this are like below: (1). What is the daily balance of credit in the e-wallet service? (2). How much credit will expire in the next month? (3). What is the outcome (i.e. % used, % expired, % left) of credit given in a particular month? Step 1: What is the business process: Step 2: Define the grain: Step 3: Identify the Dimensions: Step 4: Identify the Fact Tables Step 5: Star Schema Step 1: What is the business process: Step 2: Define the grain:
Step 3: Identify the Dimensions: Step 4: Identify the Fact Tables Step 5: Star Schema Step 1: What is the business process : The usage /spending of e-wallet from the customer Step 2: Define the grain : The product purchased in one transaction by one customer at a certain time using e-wallet credits Step 3: Identify the Dimensions: Date, Product, Customer, E-Wallet, Employee(optional), Payment Method Date Dim Column Name Description Key values DateKey YYYYMMDD Primary Key Holiday In date Format Week Month Day of Week Product Dim Column Name Description Key values ProductKey Surrogate key Primary Key SKU Natural Key Product name Customer Dim Column Name Description Key values
Customer Key YYYYMMDD Primary Key Customer ID Natural Key Customer Name Customer Email E-WalletDim Column Name Description Key values WalletKey Surrogate Key Primary Key WalletID Natural Key Start date Expirationdate IntialCredits Sorry Credits, cancellation , gift card Payment Dim Column Name Description Key values Payment Key Primary Key PaymentID PaymentName PaymentDiscription PaymentStatus Step 4: Identify the fact Tables The product purchased in one transaction by one customer at a certain time using e-wallet credits; Quantity of the Products Unit Price Total Payment amount : quantity of the product * Unit price Total payment amount by other payment methods Dim Column Name Description Key values Datekey Part of the composite key, foreign key ProductKey Part of the composite key, foreign key CustomerKey Part of the composite key, foreign key E-Wallet Key Part of the composite key, foreign key
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help