Business Intelligence dimensional schema
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 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.
[50% of marks]
2. 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.
[20% of marks]
3. Design two suitable Cubes that show data corresponding to the following:
Number of Transactions, per card type (credit/debit), per transaction type (withdrawal, deposit, balance, etc.), per month.
Total Amount of all Transactions, per day, per transaction type, per network (Visa, Mastercard, Local, Us), performed on 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 second cube above, perform the following OLAP operations and design the results of these operations.
Roll up on time (from day to month) [5% of marks] Slice, for transaction type = “withdrawal” [5% of marks]
4. Answer the following question: Apart from the identification of facts/measures, in order to be comprehensive on the proposed dimensional model, it is necessary to discuss and agree with the business stakeholders on at least one additional important aspect of the model. Identify this aspect and briefly explain its importance.
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.
For a custom-written paper on the above topic, place your order now!
What We Offer
• On-time delivery guarantee
• PhD-level professionals
• Automatic plagiarism check
• 100% money-back guarantee
• 100% Privacy and Confidentiality
• High Quality custom-written papers