A consortium of banks wants to develop a data warehouse for effective decision-making about their loan schemes. The banks provide loans to customers for various purposes, like, House Building Loan, Car Loan, Educational Loan, Personal Loan, etc. The whole country is categorized into a number of regions, namely, North, South, East and West. Each region consists of a set of states. Loan is disbursed to customers at interest rates that change from time to time. Also, at any given point of time, the different types of loans have different rates. The data warehouse should record an entry for each disbursement of loan to customer.

With respect to the above business scenario, answer the following questions. Clearly state any reasonable assumptions you make.

a.Design a star schema for the data warehouse clearly identifying the fact table(s), dimensional table(s), their attributes and measures along with the primary key and foreign key relationships.

b.Starting with the base cuboid, if we want to see the amount of loan disbursed during the year 2000 for the state of Maharashtra, which sequence of OLAP operations would you need to perform?