[SOLVED] chain data mining ROYAL HOLLOWAY

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 banks Data Warehouse contains data concerning the number of customers loans

applications performed in the banks 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 ofMeasurements

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