ROYAL HOLLOWAY
Page 1 of 6
UNIVERSITY OF LONDON
MSc EXAMINATION 2014
For Internal Students of
Royal Holloway
DO NOT TURN OVER UNTIL TOLD TO BEGIN
BI5502: Business Intelligence
Sample Exam paper
Time Allowed: TWO hours
Attempt THREE questions
No credit will be given for attempting any further questions
Calculators are NOT permitted
BI5502
© Royal Holloway University of London 2014
BI5502
Page 2 of 6 NEXT PAGE
BLANK PAGE
BI5502
Page 3 of 6 NEXT PAGE
Question 1
1. (a) Indicate whether each of the following statements is true or false.
(i) Dimension hierarchies relate to drill-down analysis.
(ii) Type 1 changes for slowly changing dimensions relate to correction of errors.
(iii) The main reason BI solutions are necessary is to support the day-to-day operational
needs of an organization.
(iv) Conforming dimensions is not absolutely necessary in a data warehouse.
(v) Scorecards display information on organizational Key Performance Indicators
(KPIs).
(vi) In dashboard design a flow based structure emphasizes a sequence of events or
actions across time.
[12 marks]
(b) The following cube shows the sales of different products of a bank, per branch, per
quarter of the year.
If you know that branches with codes 100 and 101 belong to Region A and branches with
codes 102 and 103 belong to Region B, perform the following OLAP operations in the
previous cube and design the results of those operations.
(i) Roll up on location (from branches to regions) [6 marks]
(ii) Slice, for Time = “Q1” [6 marks]
In the results of both (i) and (ii) above you may leave blank the parts whose values can
not be illustrated due to the lack of the necessary data in the above cube.
(c) Consider Customer Relationship Management (CRM) technology and applications.
(i) Name the functions/business units of an organization that typically use CRM applications.
[4 marks]
(ii) A company is interested in using a CRM application in order to send, via e-mail, a
promotional message to a group of customers. Which module of the CRM application
will be used for that purpose? [5 marks]
BI5502
Page 4 of 6 NEXT PAGE
Question 2
2 (a) Discuss the main differences between BI Solutions/Data Warehouses and On-Line
Transaction Processing Systems (OLTP). Structure the discussion by comparing the two
approaches according to the following criteria: Purpose, Usage, Data Modifications, DB
Schema Design, Typical DB Operations, Historical Data and DB Size. [13 marks]
(b) In the table below, the first column contains a list of business domains in which Data
Mining is applied. Complete the second column of the table with at least two data mining
examples as applied in these domains. The first entry has been already completed for you
as a sample answer. [6 marks]
Domain Example Data Mining Application
Financial Services
-Credit Scoring/Risk Analysis
-Fraud Detection
Customer Relationship
Management
Retail/Logistics
Manufacturing
(c) A bank’s Data Warehouse contains data concerning the number of customers’ loans
applications performed in the bank’s branches in 3 different geographical regions (A, B,
C) in the 4 quarters of the year (Q1, Q2, Q3, Q4). A sample of the relevant business data
is summarized in the following 3 tables:
Region A
Q1 Q2 Q3 Q4
Mortgages Applications 1100 1200 975 1230
Credit Cards Applications 4125 3835 2746 3010
Consumer Loan Applications 3870 4010 3310 3425
Region B
Q1 Q2 Q3 Q4
Mortgages Applications 456 524 410 428
Credit Cards Applications 748 800 735 801
Consumer Loan Applications 945 875 1011 978
Region C
Q1 Q2 Q3 Q4
Mortgages Applications 223 297 289 300
Credit Cards Applications 330 300 333 377
Consumer Loan Applications 452 508 504 589
(i) Design a suitable Star-schema to satisfy the above data modelling requirements,
showing the schema entities, attributes and respective keys.
[7 marks]
(ii) Design a suitable Cube that represents the previous data model. The cube you design
should also illustrate in its visible parts the corresponding data from the above tables.
[8 marks]
BI5502
Page 5 of 6 NEXT PAGE
Question 3
3. (a) Design a STAR schema for a chain of DVD rental stores whose managers would like to
be informed on the number and revenue of individual DVD items, per store, per day.
Devise at least two attributes for each of the non-key dimensions you include in your
schema; such attributes should make sense. Provide also a sample row of all tables of the
proposed schema illustrating the respective data interrelationships.
[13 marks]
(b) In the context of Data Warehousing alternative architectures, provide a diagram of the
Centralized Data Warehouse architecture. What is the main issue of such architecture?
[9 marks]
(c) For each of the transformation types shown in the table below, give an example to
illustrate the case. One entry has already been completed for you as a sample answer.
Transformation Type Example
Character set conversion From older character set formats to ASCII
Conversion of Units of Measurements
Splitting of Single Fields
Date/Time Conversion
Decoding of Fields
[12 marks]
Page 6 of 6
Question 4
4. (a) What is the notion of granularity in dimensional modelling? How does it impact the
design of a BI solution/Data Warehouse? [10 marks]
(b) Provide a diagram of the typical high-level components of a DB reporting solution and
outline the role of each of such components. [10 marks]
(c) Suppose that a dimensional model consists of the three dimensions date, doctor, and
patient, and a measure “charge”, which is the fee that a doctor charges a patient for a
visit.
(i) Draw a STAR schema for the above. [7 marks]
(ii) In a cube that shows data for a given patient, in a given doctor in a given date, what
specific OLAP operations should be performed in order to list the total fee collected by
each doctor in 2013? [7 marks]
END