BI5502: Business Intelligence
Assignment
Announced: Friday 24th February 2017
Due: Monday 13th March 2017, 2 p.m.
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.
Notes: a. You may use whatever tool (e.g. Word, PowerPoint, Visio, a UML tool) in order
to design such schema. . If you wish, you may design on paper your model and/or cubes
and include them as scanned images in your answer. b. The schema you design should
be optimal in the sense that it should be at the appropriate granularity and include only
the entities needed in order to support the questions above. Therefore, you should not
define a granularity or include other entities which are not required (marks may be
deducted if you include unnecessary entities). For instance, the granularity of your
model should not be at the level of the individual ATM transaction.
Provide a set of at least 2 sample rows of all tables of the proposed schema illustrating
the respective data interrelationships. Where applicable, the rows you provide should
use/include data from the operational data subset given to you.
Suggest 2 additional business questions (similar to 1 to 6 above) that your schema is
able to support.
[80% of marks]
2
2. Design two suitable Cubes that show data corresponding to the following:
?
Number of Transactions, per card network type (e.g. Visa/Mastercard), per card
type (credit, debit), per month.
?
Total Amount of all Transactions, per day, per transaction type (e.g. withdrawal,
balance enquiry), per card type (credit, debit), performed on the Bank XYZ (“us”)
terminals.
Where applicable, the cubes you design should also illustrate in their visible parts some
corresponding data from the subset given to you (i.e. data for one day).
[10% of marks]
Additionally, in the first cube above, perform the following OLAP operations and design
the results of these operations.
?
?
Roll up on time (from month to year)
Slice, for card network type = “Visa”
Notes: (i) In the facts part of the dimensional schema you design, some measures/facts may be
derived facts. (iii) the dimensional schema may have more than one fact tables (iii) ignore the
need for currency conversions as part of your model.
Submission guidelines
The deadline for submission is Monday the 13th of March 2017, at 2 p.m. The work should be
submitted electronically via Moodle in .pdf format.
Plagiarism
All work submitted by students as part of the requirements for any examination or other
assessment must be expressed in their own words and incorporate their own ideas and
judgments. Plagiarism that is, the presentation of another persons thoughts or words as though
they were one’s own must be avoided, with particular care in coursework and essays and
reports written in students own time. Deliberate plagiarism in coursework is as serious as
deliberate cheating in an examination. Failure to observe these rules may result in an allegation
of cheating. Students should therefore consult their tutor or course director if they are in any
doubt about what is permissible.
PLACE THIS ORDER OR A SIMILAR ORDER TODAY AND GET AN AMAZING 20% DISCOUNT 🙂
