Data Warehouse - Dimensional Modelling - Use Case Study: Ewallet

Data Warehouse |
Dimensional Modelling |
Use case study: eWallet
Explaining technical concepts through use case will always
make more sense.
Data Warehouse
A data warehouse is a large collection of business-related
historical data that would be used to make business decisions.
Data warehouse stores aggregated transactional data,
transformed and stored for analytical purposes.
• Data warehouses store data from multiple sources, which
makes it easier to analyze.
"Simply speaking, the database (operational) systems are

where you put the data in, and the Data warehouse (Business
Intelligence) system is where you get the data out.” — Ralph
Kimball
Dimensional Modeling
Dimensional modeling is the widely used technique to design
data warehouse mainly because it addresses below two
requirements simultaneously:
1. Delivers the data that is understandable by business
users.
2. Deliver fast query performance.
Source <- Back Room

Transactions
Presentation Area:
• Dimensional (star
schema or OLAP
ETL System:
cube)
• Transform from
• Atomic and summary
source-to-target
data
• Conform
• Organized by business
dimensions Bl Applications:
process
• Normalization • Ad hoc queries
• Uses conformed
optional • Standard reports
dimensions
• No user query • Analytic apps
support Design Goals: • Data mining and
• Ease of use models
Design Goals:
• Query performance
• Throughput
• Integrity and
consistency
Enterprise DW Bus
Architecture
Core elements of the Kimball DW/BI architecture
The figure shows the major components involved in building the

Data warehouse from operational data sources to analytical
tools to support business decisions through ETL (Extract,
Transformation, Load) process.
Now let’s take the use case of e-Wallet to build a data warehouse
using dimensional modeling technique.
Use case
Background
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, soo-sorry credit

may be provided.
Credit in the e-wallet expires after 6 months if it is gift card

credit and soo-sorry credit, but in 1 year if it is cancellation
credit.
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:
• What is the daily balance of credit in the e-wallet service?
. How much credit will expire in the next month?
• What is the outcome (i.e. % used, % expired, % left) of credit

given in a particular month?
Solution Design
The four key decisions made during the design of a dimensional

model include:
1. Select the business process. 2. Declare the grain. 3. Identify

the dimensions. 4. Identify the facts.
Let’s write down this decision steps for our e-Wallet case:
1. Assumptions: Design is developed based on

the background (Business Process) given but also keeping
flexibility in mind. All the required fields are assumed to be
available from the company’s transactional database.
2. Grain definition: Atomic grain refers to the lowest level

at which data is captured by a given business process.
The lowest level of data that can be captured in this context is

wallet transactions i.e., all the credit and debit transactions on
e-wallet.
3. Dimensions: Dimensions provide the "who, what,
where, when, why, and how” context surrounding a business
process event.
Even though a wide number of descriptive attributes can be

added designing dimensions are restricted to the current
business process but the model is flexible to add any more
details as and when required. (Tables name prefixed with Dim)
Dimension Tables:
. DimWallet . DimCustomer
. DimDate: This dimension has all the date related parsed
values like Month of the date, Week of the date, Day of the
week, etc. This will be very handy to get reports based on time.
4. Facts: Facts are the measurements that result from a

business process event and are almost always numeric.
Facts are designed such that focusing on having fully additive

facts. Even though some business process requirements want
facts that are non-additive (% used, % expired, % left, etc).
These values can be achieved effectively by calculating the
additive facts separately. Each row in fact table represents the
physical observable events not only focused on the demands of
reports required.
Fact Table:
• FactWallet
STAR schema model

Below is the logical diagram of the dimensional model for the
eWallet service.
eWallet — Star schema Dimensional Model
Ralph Kimball who is the pioneer in Data warehouse

technologies has always shown the importance of Business
value in his books. Star schema is preferred over snowflake
schema because of more analytical capabilities.
References
[1] Ralph Kimball, Margy Ross, The Data Warehouse Toolkit,
2nd Edition, The complete guide to dimensional modeling

Data Warehouse - Dimensional Modelling - Use Case Study: Ewallet

Uploaded by

Copyright:

Available Formats

Data Warehouse - Dimensional Modelling - Use Case Study: Ewallet

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Warehouse - Dimensional Modelling - Use Case Study: Ewallet

Uploaded by

Copyright:

Available Formats

What are the main components of a data warehouse?

What are the main components of a data warehouse?

What are the key steps in dimensional modeling?

What are the key steps in dimensional modeling?

Data Warehouse |

"Simply speaking, the database (operational) systems are

2. Deliver fast query performance.

Source <- Back Room

Core elements of the Kimball DW/BI architecture

The figure shows the major components involved in building the

One of the online retail company’s features is an e-wallet

Users can receive credit in three different ways:

1. When a product purchase that is paid for is canceled, the

3. If a user has a poor service experience, soo-sorry credit

Credit in the e-wallet expires after 6 months if it is gift card

The Finance department of the company would like to build

. How much credit will expire in the next month?

• What is the outcome (i.e. % used, % expired, % left) of credit

The four key decisions made during the design of a dimensional

1. Select the business process. 2. Declare the grain. 3. Identify

1. Assumptions: Design is developed based on

2. Grain definition: Atomic grain refers to the lowest level

The lowest level of data that can be captured in this context is

Even though a wide number of descriptive attributes can be

4. Facts: Facts are the measurements that result from a

Facts are designed such that focusing on having fully additive

STAR schema model

Ralph Kimball who is the pioneer in Data warehouse

You might also like