Coursework -Business Intelligence systems
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 Bank’s 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
ii. Transactions performed at the ATMs of other banks by customers (cardholders) of Bank
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 thefollowing: 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 followingquestions: 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. 3 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: 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 toyou. [45 marks] 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. [30 marks] 4 3. Provide a comparison of the two models you designed. The comparison should be based on the following criteria: (i) the performance of RDBMs queries, (ii) the MOLAP/Data Mart solution storage requirements and (iii) the ability of each model to support additional business questions (similar to the ones identified in 1 to 10 above). Clearly state which of the two models is better in each of the criteria above and justify your answers. [15 marks] 4. Design two suitable Cubes that show data corresponding to the following: Daily Number of Transactions, per card network type (Visa, Mastercard), per transaction type (withdrawal, deposit, etc.). Total Amounts of Daily Transactions per card type, per city, on Bank XYZ (“us”) terminals. Where applicable, the cubes should illustrate in their visible parts some corresponding data from the subset given to you (i.e. data for one day). [10 marks] Notes: a. You may use any tool (e.g. Word, PowerPoint, Visio, a UML tool, an RDBMS tool) to design the required models. If you wish, you may design the models and/or cubes on paper and include them as scanned images in your answer. It is your responsibility to ensure that your answer/models are legible. b. Measures/facts may be derived facts. Please state explicitly such cases. Moreover, the dimensional models may have more than one facttables. c. You may ignore the need for currency conversions as part of yourmodels.