Assignment Specification

Imagine you work as a BI expert in Bank XYZ. You are given a spreadsheet file (Excel) which contains a subset/sample of data from the Banks operational (transactional) system that manages and authorizes the following transactions:

i. Transactions performed at the ATMs of the Bank by its customers or by customers of other banks.

ii. Transactions performed 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 the 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 be based on the following:

The data contained in the sample spreadsheet.

Additional information on the Bank XYZ terminals that the above system has, i.e. . Note: the system does not maintain such information on ATMs/terminals which belong to other banks.

Bank customers information that is stored and maintained in the Bank XYZ Core Banking System, e.g. . Note: the system does not maintain such information on customers of other banks.

After a series of requirements analysis sessions with 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, per Transaction Type (i.e. withdrawal, deposit, transfer etc.) performed in each of the Us (Bank XYZ) terminals, 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 (total, weekdays, weekends), month, quarter and year.

3. Number and Total Amount of Transactions, in each of the Bank XYZ terminals, per Transaction Type, per day, month, year, distinguishing the time period (i.e. morning (06.00- 13.00h), lunch time (13.00-14.00h), afternoon (14.00-17.00h), early night (17.00-22.00h, late night (22.00-06.00h)).

4. Daily and Monthly Number of Transactions, per card type (credit/debit), per transaction type, per city, on the Bank XYZ (Us) terminals(ATMs).

5. Number, Total Amounts and types of Transactions (i.e. withdrawal, deposit, transfer etc.) performed on Us terminals, with cards issued by other banks (i.e. FIID Card Issuer ID != Us), per day, week, month, quarter and year.

6. Number and Total Amount of Transactions, per Bank XYZ Customer, per Transaction Type (i.e. withdrawal, deposit, transfer etc.), per month and year.

7. 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 Card type, per week, month, quarter and year.

8. Number and Total Amounts of Transactions per Card Network type (e.g. Visa, Mastercard), performed in the Bank XYZ terminals, per day, week, month, quarter and year.

9. Number of ATM device failures per Manufacturer of Terminals in the ATM network of Bank XYZ, per month, quarter and year.

10. Number and Total Amounts of Credit Card type 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.

Study carefully the subset of data given to you as well as the above additional information and business needs. Assume that the BI solution to be implemented includes a Data Mart and a MOLAP server. Then perform the following tasks:

QUESTION 1. Design an appropriate dimensional model (in a graphical form) to support the business questions above. The granularity of the fact table(s) of the model should be at the level of the individual ATM transaction. Explain any assumptions made in your design. Provide a set of at least 2 sample rows of all table(s) of the proposed model. Where applicable, the rows you provide should use/include data from the operational data subset given to you. [45 marks]

QUESTION 2. Design a second dimensional model (also in a graphical form) to support the business questions above. In the second model, the granularity of the fact table(s)should not be at the level of the individual ATM transaction. Moreover, the model should be optimal, in the sense that it should be at the appropriate granularity and include only the entities that are necessary 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 do so). Explicitly state the granularity of the fact table (or tables, if multiple) of your schema and include as many details as possible concerning the schemas attributes. Explain any assumptions made in your design. Provide a set of at least 2 sample rows of the fact 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.

A Timestamp B Transaction ID D Terminal Manufacturer ID E FIID Terminal ID Terminal ID F Card ID G CardTypeID H FIID Card Issuer ID 1 Network ID Transaction Ty The date and time 2 of the transaction 3 4 The ID of the transaction (a a unique transaction identifier) The manufacturer of the terminal (Diebolt, Wincor The ID of the etc). "Undisclosed" if such terminal (ATM) info is not available in cases the transaction takes place in an ATM of another bank The FIID (Financial Institution ID) of the Bank that owns the terminal - "Us" means the terminal belongs to Bank XYZ. The ID of the The type of the card card that that perfromed the performed the transaction transaction (Debit/Credit) The ID of the "payment" The FIID (Financial network e.g. Institution ID) of the Visa, Mastercard, Deposit/Withdrawal/Depo Bank that issued the InterCountry etc card. "Us" means it (Local), or "US" is a Bank XYZ card (inside the XYZ bank) 5 6 121120201644 501121117 7 121120201647 501121118 8 121120201653 501121119 9 121120201655 501121120 10 121120201701 501121121 11 121120201705 501121122 12 121120201646 501121123 13 121120201648 501121124 14 121120201649 501121125 15 121120201653 501121126 Sheet1 Sheet2 501 501 501 501 501 501 502 502 502 502 Diebolt Diebolt Diebolt Diebolt Diebolt Diebolt Wincor Wincor Wincor Wincor Us Us Us Us Us Us Us Us Us Us 23344478 45621112 11141114 11111111 43211225 11111116 11181117 11111111 56732456 11161120 Credit Credit Debit Debit Debit Debit Debit Debit Credit Credit dississis Bank A Bank B Us Us Bank E Us Us Us Bank C Us Visa MC Us Us Local Us Us Us Visa US Withdrawal Withdrawal Withdrawal Withdrawal Withdrawal Transfer Deposit Withdrawal Withdrawal Withdrawal J Transaction Type K Transaction Amount M Transaction Response Code N Card Returned 0 Error Code Currency 00: Approved 0: card was returned 00: No error Deposit/Withdrawal/Deposit/Transfer etc 01: Denied, wrong PIN 02: Denied, insufficient balance 1: card was retained by ATM 01: Processing Error 03: Line Error 04: Device Failure 0 Withdrawal Withdrawal Withdrawal Withdrawal Withdrawal Transfer Deposit Withdrawal Withdrawal Withdrawal 100 150 300 80 60 200 100 180 240 280 Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds 00 00 01 00 00 02 00 00 00 00 0 1 1 0 0 0 0 0 0 0 00 00 04 00 00 00 00 00 00 00 A Timestamp B Transaction ID D Terminal Manufacturer ID E FIID Terminal ID Terminal ID F Card ID G CardTypeID H FIID Card Issuer ID 1 Network ID Transaction Ty The date and time 2 of the transaction 3 4 The ID of the transaction (a a unique transaction identifier) The manufacturer of the terminal (Diebolt, Wincor The ID of the etc). "Undisclosed" if such terminal (ATM) info is not available in cases the transaction takes place in an ATM of another bank The FIID (Financial Institution ID) of the Bank that owns the terminal - "Us" means the terminal belongs to Bank XYZ. The ID of the The type of the card card that that perfromed the performed the transaction transaction (Debit/Credit) The ID of the "payment" The FIID (Financial network e.g. Institution ID) of the Visa, Mastercard, Deposit/Withdrawal/Depo Bank that issued the InterCountry etc card. "Us" means it (Local), or "US" is a Bank XYZ card (inside the XYZ bank) 5 6 121120201644 501121117 7 121120201647 501121118 8 121120201653 501121119 9 121120201655 501121120 10 121120201701 501121121 11 121120201705 501121122 12 121120201646 501121123 13 121120201648 501121124 14 121120201649 501121125 15 121120201653 501121126 Sheet1 Sheet2 501 501 501 501 501 501 502 502 502 502 Diebolt Diebolt Diebolt Diebolt Diebolt Diebolt Wincor Wincor Wincor Wincor Us Us Us Us Us Us Us Us Us Us 23344478 45621112 11141114 11111111 43211225 11111116 11181117 11111111 56732456 11161120 Credit Credit Debit Debit Debit Debit Debit Debit Credit Credit dississis Bank A Bank B Us Us Bank E Us Us Us Bank C Us Visa MC Us Us Local Us Us Us Visa US Withdrawal Withdrawal Withdrawal Withdrawal Withdrawal Transfer Deposit Withdrawal Withdrawal Withdrawal J Transaction Type K Transaction Amount M Transaction Response Code N Card Returned 0 Error Code Currency 00: Approved 0: card was returned 00: No error Deposit/Withdrawal/Deposit/Transfer etc 01: Denied, wrong PIN 02: Denied, insufficient balance 1: card was retained by ATM 01: Processing Error 03: Line Error 04: Device Failure 0 Withdrawal Withdrawal Withdrawal Withdrawal Withdrawal Transfer Deposit Withdrawal Withdrawal Withdrawal 100 150 300 80 60 200 100 180 240 280 Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds Pounds 00 00 01 00 00 02 00 00 00 00 0 1 1 0 0 0 0 0 0 0 00 00 04 00 00 00 00 00 00 00