Lab 1_Dimensional Modeling-2
.docx
keyboard_arrow_up
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
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
Related Questions
QUESTION 1: CASE STUDY ON AMAZON: ONLINE SALES
Amazon sells books, music, and many other items over the Internet and is one of the pioneers of online consumer sales. Amazon, based in Seattle, Washington, started by filling all orders using books purchased from a distributor in response to customer orders. As it grew, the company added warehouses, allowing it to react more quickly to customer orders. In 2009, Amazon had about 20 warehouses in the United States and another 30 in the rest of the world. It uses the U.S. Postal Service and other package carriers such as UPS and FedEx to send products to customers. Outbound shipping-related costs at Amazon in 2009 were almost $2 billion.
With the Kindle, Amazon has worked hard to increase sales of digital books. As of 2009, Amazon offered more than 460,000 books in digital form. The company has also added a significant amount of audio and video content for sale in digital form.
Amazon has continued to expand the set of products that it…
arrow_forward
QUESTION 2
In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing the hypothesis (at the 5% level of significance) that the average energy use has decreased from the past, what is the test statistic? (please round your answer to 2 decimal places)
arrow_forward
Case Part A Tesla's Quality Challenge
Should tesla deliver cars to customers on a first come first served basis (i.e. in the order of when deposits were placed)?
arrow_forward
Question 3
In model M/M/ s =4 / FCFS / 0 / 00, with A = 3, and u = 10 the P(j 2s) is?
O a. PU 2s) = 0.03
O b. PU 25) = 0.00
O C. P( 2s) = 0.5
O d. Pj 25) = 4
arrow_forward
Question-1:
A company supplies several types of Ice-cream to consumer market. It has two plants. The data of year
2022 for both plants are given as follows
Plant-1
$10,000
$5,000
$2,000
50000
Quality rate
90%
Average Selling price $5/unit
Labor cost
Material cost
Energy cost
Unit produced
Plant-2
$7,000
$7,000
$3,000
40000
95%
$7/unit
For year-2023, company decided to make an additional investment of $3,000 which would potentially
improve the quality rate by 4% in plant-1 and 2% in plant-2. For budget limitation they can only invest in
any one of the plants. Management decided to invest where multifactor productivity improvement will
be higher compared to year-2022. Perform your analysis and suggest where management should invest
the amount. (You may use MS Excel to perform the calculation.)
arrow_forward
QUESTION 4
In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing the hypothesis (at the 5% level of significance) that the average energy use has decreased from the past, what is your conclusion concerning the null hypothesis?
Reject the null hypothesis
Fail to reject the null hypothesis
arrow_forward
QUESTION 3
In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing whether the average energy use has decreased from the past (with a null hypothesis that the use has increased) (using the 5% level of significance), what is the p-value? (please round your answer to 4 decimal places)
arrow_forward
13. Define Consumer-to-Business transaction (C2B). Give one Example.
arrow_forward
Sanjeevini is a 100 bed hospital established 10 years ago and is performing well. Now the hospital is facing trouble in sourcing inpatients due to various reasons, one of them being improper inpatient nursing care. So the hospital management plans to intervene to improve the inpatient Care Experience.
QUESTION: Develop a questionnaire to study the patient satisfaction in the hospita
arrow_forward
ul T-Mobile ?
22:07
89 %
A coffeyvillecommunitycollege.mrooms.net
Question 7
Not complete Marked out of 2.00 P Flag question
In Norway and Sweden, there are:
Select one:
a. more bisexual relationships than same sex
male relationships
b. more male same sex registered partnerships
than female same sex partnerships
c. more bisexual relationships than same sex
female relationships
d. more female same sex registered partnerships
than male same sex partnerships
Check
Question 8
Not complete Marked out of 2.00 P Flag question
arrow_forward
Question: What should have been done by the government to address this problem?
arrow_forward
Quality assurance.
arrow_forward
Volumetric Glassware: Utility and Calibration (Dry Lab)
Table B1: Calculation of a 100-mL Volumetric Flask.
temperature of the Distilled H2O (°C)
27
density of distilled H2O at calibration temperature
mass (g) of volumetric flask + distilled H₂O
Trial 1
198.5893
Trial 2
198.6114
Trial 3
mass (g) of dry volumetric flask
98.6563
98.7503
198.6001
98.7145
mass (g) of distilled H₂O
m true g
calculated volume of water dispensed, cm³
corrected volume, V20 °c, cm³
average corrected volume, V20 °C, cm³
standard deviation (4 sig figs)
%RSD (2 sig figs)
arrow_forward
Q5.2)
Briefly describethe bottom of the pyramid (BOP) process for developing business specifications.
arrow_forward
Explain : Which of the following poses a challenge to hazard management? Question 17 options: a) cost cutting strategies b) worker reports of concerns c) employee retention d) preventive maintenance programs e) none of the above
arrow_forward
Value Proposition at Brenco GPS Security Solution
A value proportion defines the product or service that an enterprise is producing with the aims at resolving customer pain or fulfilling a need of a customer segment. Brenco is a security and GPS service providers, reputable in the deployment of digital tracking devices. Brenco has proposed to produce and distribute a small GPS tracking device that you can attach to your bicycle, cars, luggage or other valuables and in the event of loss of theft it will locate the items. The value proposition we offer is the ability to track your valuable objects.
Requires:
As a senior business analyst at Brenco, you are required to identify and explain the set of features embedded in
the value proposition of GPS tracking device
arrow_forward
QNO:1 Differentiate following with an example.
Compliance Based Culture VsValue Based CultureVirtue philosophy VsTeleology PhilosophyApathetic Culture VsExacting CultureLegitimate Power Vs Referent Power
arrow_forward
Q1. Explain the benefits and challenges of CRM. Customer relationship management?
arrow_forward
Question 4
The ISO 14000 series is primarily concerned with:
Establishing guidelines to achieve 6-Sigma certification
Environmentally-responsible production and operations
O Total quality management (TQM)
O Automotive manufacturing
Question 5
II Proctorio is sharing your screen.
St
arrow_forward
Question 3
For better quality audits, auditors are to obtain an appropriate level of knowledge of the business
before commencing the audit process.
Required:
In your own words, explain why an auditor is required to obtain a good understanding of the client's
business.
ANSWER:
arrow_forward
• Higher cost of JET A1 fuel resulted in more expenditures which affected the net profit of MAS. Malaysia Airlines reinstated a fuel surcharge on its tickets price from 18 sept, 2018 onwards but their competitor such as AirAsia BHD had introduced a no-fuel-surcharge policy. This will encourage more passengers to travel by AirAsia because they offer much cheaper rates.
• Service quality is one of the main keys of passengers satisfaction such as waiting time , staff enthusiasm in problem solving , boarding time , flight schedule and boarding system. Lack of comfort and satisfaction of passengers may affect the reputation of MAS causing people to be more inclined towards chosing other airlines for travelling.
• Technology has to be in par with other leading airlines. In-flight entertainment can currently act as a differentiating factor, in future it will become a major part of passengers’ expectation especially in long-haul flights. The impact of IFE would be felt indirectly, through…
arrow_forward
QUESTION 2
Searching for ways to cut costs and increase profit, one of the industrial engineers at Home Comfort Furniture Manufacturers, Inc.
determined that the equivalent annual worth of an existing machine over its remaining useful life of 2 years is $-65,000 per year. The IE
also determined that used machines like the one currently in use are not available any longer, but the defender can be replaced with a
challenger that is more advanced. It will have an AW of $-78,550 if it is kept for 2 years or less, $-72,075 if it is kept between 3 and 4
years, and $-65,000 if it is kept for 5 to 10 years. If the company uses a specified 3-year planning horizon and an interest rate of 14%
per year.
Using Replacement Study, select:
a. Replace now with challenger for all 3 years
b. Keep the defender for 2 years and then replace it with the challenger for 1 year
c. Keep the defender for 1 years and then replace it with the challenger for 2 years
arrow_forward
Problem 10-6 (Static)
A medical facility does MRIs for sports Injuries. Occasionally, a test yields inconclusive results and must be repeated. Using the
following sample data and n = 200.
Number of retests
1 2
1
2
UCL
LCL
3
2
b. Is the process in control?
4
The process is in control.
O The process is not in control.
0
5
2
SAMPLE
6 7
1 2
Click here for the Excel Data File
a. Determine the upper and lower control limits for the fraction of retests using two-sigma limits. (Do not round intermediate
calculations. Round your final answers to 4 decimal places. Leave no cells blank - be certain to enter "0" wherever required.)
8
9 10 11 12 13
2 7 3 2 1
arrow_forward
Case study: container shortage challenges Unexpected events have resulted in a global container shortage, disrupting international trade and causing ripple effects in the supply chain. When the Covid-19 pandemic broke out at the beginning of last year, many countries implemented national lockdowns and halted production, disrupting economic growth. The number of cargo ships dispatched started to decline. This hampered not only the normal flow of commodities imported and exported but also the collection of empty containers. As a result, there has been a container shortage since 2020.
1.1 Evaluate the impact of the container shortage and what role freight forwarders play? 1.2 Discuss what activities a freight forwarder can help with in this case? 1.3 Differentiate which activities will be contacted by a traditional freight forwarder and which will fall within a 3PL scope?
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Principles Of Marketing
Marketing
ISBN:9780134492513
Author:Kotler, Philip, Armstrong, Gary (gary M.)
Publisher:Pearson Higher Education,
Marketing
Marketing
ISBN:9781259924040
Author:Roger A. Kerin, Steven W. Hartley
Publisher:McGraw-Hill Education
Foundations of Business (MindTap Course List)
Marketing
ISBN:9781337386920
Author:William M. Pride, Robert J. Hughes, Jack R. Kapoor
Publisher:Cengage Learning
Marketing: An Introduction (13th Edition)
Marketing
ISBN:9780134149530
Author:Gary Armstrong, Philip Kotler
Publisher:PEARSON
Contemporary Marketing
Marketing
ISBN:9780357033777
Author:Louis E. Boone, David L. Kurtz
Publisher:Cengage Learning
Related Questions
- QUESTION 1: CASE STUDY ON AMAZON: ONLINE SALES Amazon sells books, music, and many other items over the Internet and is one of the pioneers of online consumer sales. Amazon, based in Seattle, Washington, started by filling all orders using books purchased from a distributor in response to customer orders. As it grew, the company added warehouses, allowing it to react more quickly to customer orders. In 2009, Amazon had about 20 warehouses in the United States and another 30 in the rest of the world. It uses the U.S. Postal Service and other package carriers such as UPS and FedEx to send products to customers. Outbound shipping-related costs at Amazon in 2009 were almost $2 billion. With the Kindle, Amazon has worked hard to increase sales of digital books. As of 2009, Amazon offered more than 460,000 books in digital form. The company has also added a significant amount of audio and video content for sale in digital form. Amazon has continued to expand the set of products that it…arrow_forwardQUESTION 2 In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing the hypothesis (at the 5% level of significance) that the average energy use has decreased from the past, what is the test statistic? (please round your answer to 2 decimal places)arrow_forwardCase Part A Tesla's Quality Challenge Should tesla deliver cars to customers on a first come first served basis (i.e. in the order of when deposits were placed)?arrow_forward
- Question 3 In model M/M/ s =4 / FCFS / 0 / 00, with A = 3, and u = 10 the P(j 2s) is? O a. PU 2s) = 0.03 O b. PU 25) = 0.00 O C. P( 2s) = 0.5 O d. Pj 25) = 4arrow_forwardQuestion-1: A company supplies several types of Ice-cream to consumer market. It has two plants. The data of year 2022 for both plants are given as follows Plant-1 $10,000 $5,000 $2,000 50000 Quality rate 90% Average Selling price $5/unit Labor cost Material cost Energy cost Unit produced Plant-2 $7,000 $7,000 $3,000 40000 95% $7/unit For year-2023, company decided to make an additional investment of $3,000 which would potentially improve the quality rate by 4% in plant-1 and 2% in plant-2. For budget limitation they can only invest in any one of the plants. Management decided to invest where multifactor productivity improvement will be higher compared to year-2022. Perform your analysis and suggest where management should invest the amount. (You may use MS Excel to perform the calculation.)arrow_forwardQUESTION 4 In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing the hypothesis (at the 5% level of significance) that the average energy use has decreased from the past, what is your conclusion concerning the null hypothesis? Reject the null hypothesis Fail to reject the null hypothesisarrow_forward
- QUESTION 3 In an effort to reduce energy costs, a major university has installed more efficient lights as well as automatic sensors that turn the lights off when no movement is present in a room. Historically, the cost of lighting an average classroom for 1 week has been $265. To determine whether the changes have signficantly reduced costs, the university takes a sample of 50 classrooms. They find that the average cost for 1 week is $247 with a standard deviation of $60. When testing whether the average energy use has decreased from the past (with a null hypothesis that the use has increased) (using the 5% level of significance), what is the p-value? (please round your answer to 4 decimal places)arrow_forward13. Define Consumer-to-Business transaction (C2B). Give one Example.arrow_forwardSanjeevini is a 100 bed hospital established 10 years ago and is performing well. Now the hospital is facing trouble in sourcing inpatients due to various reasons, one of them being improper inpatient nursing care. So the hospital management plans to intervene to improve the inpatient Care Experience. QUESTION: Develop a questionnaire to study the patient satisfaction in the hospitaarrow_forward
- ul T-Mobile ? 22:07 89 % A coffeyvillecommunitycollege.mrooms.net Question 7 Not complete Marked out of 2.00 P Flag question In Norway and Sweden, there are: Select one: a. more bisexual relationships than same sex male relationships b. more male same sex registered partnerships than female same sex partnerships c. more bisexual relationships than same sex female relationships d. more female same sex registered partnerships than male same sex partnerships Check Question 8 Not complete Marked out of 2.00 P Flag questionarrow_forwardQuestion: What should have been done by the government to address this problem?arrow_forwardQuality assurance.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Principles Of MarketingMarketingISBN:9780134492513Author:Kotler, Philip, Armstrong, Gary (gary M.)Publisher:Pearson Higher Education,MarketingMarketingISBN:9781259924040Author:Roger A. Kerin, Steven W. HartleyPublisher:McGraw-Hill EducationFoundations of Business (MindTap Course List)MarketingISBN:9781337386920Author:William M. Pride, Robert J. Hughes, Jack R. KapoorPublisher:Cengage Learning
- Marketing: An Introduction (13th Edition)MarketingISBN:9780134149530Author:Gary Armstrong, Philip KotlerPublisher:PEARSONContemporary MarketingMarketingISBN:9780357033777Author:Louis E. Boone, David L. KurtzPublisher:Cengage Learning
Principles Of Marketing
Marketing
ISBN:9780134492513
Author:Kotler, Philip, Armstrong, Gary (gary M.)
Publisher:Pearson Higher Education,
Marketing
Marketing
ISBN:9781259924040
Author:Roger A. Kerin, Steven W. Hartley
Publisher:McGraw-Hill Education
Foundations of Business (MindTap Course List)
Marketing
ISBN:9781337386920
Author:William M. Pride, Robert J. Hughes, Jack R. Kapoor
Publisher:Cengage Learning
Marketing: An Introduction (13th Edition)
Marketing
ISBN:9780134149530
Author:Gary Armstrong, Philip Kotler
Publisher:PEARSON
Contemporary Marketing
Marketing
ISBN:9780357033777
Author:Louis E. Boone, David L. Kurtz
Publisher:Cengage Learning