Imagine you work as a BI expert in Bank XYZ. You are given an spreadsheet file (Excel) which
contains a subset/sample of data from the Bank’s operational (transactional) system that
manages and authorizes the transactions performed at the ATMs of the Bank (by customers of
the bank or by customers of other banks) or at the ATMs of other banks by customers
(cardholders) of Bank XYZ. A high-level, simplified, diagram of the high-level interactions of the
system (and systems of different banks) is shown in the picture below.

As part of a BI solution implementation your main task is to design a dimensional model which
will primarily be based on the type of information contained in the sample spreadsheet data.
After a series of requirements sessions with relevant business stakeholders, you have identified
that such a BI solution should be able to support the following questions:
1. Number and Total Amount of Transactions performed on “Us” terminals by cardholders
of Bank XYZ (i.e. FIID Card Issuer ID = “Us”), per day, week, month, quarter and year.
2. Number and Total Amount of Transactions performed in each of “Us” terminals (i.e. the
ATM terminals of Bank XYZ), per day, week, month, quarter and year.
1

3. Number and Total Amount of “Withdrawal” Transactions performed in each of “Us” (Bank
XYZ) terminals, per day, week, month, quarter and year.
4. Number and Total Amounts of transactions performed by cardholders of Bank XYZ outside
its network of ATMs (i.e. performed in other banks ATMs), per day, week, month, quarter
and year.
5. Number and Total Amount of Transactions in other FIID terminals (i.e. transactions
performed in the ATMs of other Banks by customers/cardholders of Bank XYZ), per FIID,
per Transaction Type, per Network type, per week, month, quarter and year.
6. Number and Total Amounts of Transactions per Card Type (i.e. Credit, Debit) and per Card
Network type (e.g. Visa, Mastercard), performed in the Bank XYZ terminals, per day,
week, month, quarter and year.
7. Number of Daily and Monthly Captured (Retained) Cards per Manufacturer of Terminals
in the ATM network of Bank XYZ.

Study carefully the subset of data given to you as well as the above business needs. Then
perform the following tasks.
1. Design an appropriate dimensional schema (in a graphical form) that can be used to
implement such a BI solution. Explicitly state the grain of the fact table (or tables, if
multiple) of your schema and include as many details as possible concerning the schema
attributes. Explain any assumptions made in your design.