Posted: March 8th, 2016

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

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 in each of “Us” terminals (i.e. the ATM terminals of Bank XYZ), per day, week, month, quarter and year.
2. Number and Total Amount of Transactions performed on “Us” terminals, with cards of Bank XYZ (i.e. FIID Card Issuer ID = “Us”), per day, week, month, quarter and year.

3. Number and Total Amount of Transactions, per Transaction Type (i.e. withdrawal, deposit, transfer etc.) performed on “Us” (Bank XYZ) terminals, per day, week, month, quarter and year.
4. 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.
5. 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.
6. Number and Total Amounts of Credit card transactions performed with cards 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 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.