Pg. 02

Question Four

Assignment 2

Deadline: Day 21/2/2021 @ 23:59
[Total Mark for this Assignment is 5]

Decision Support Systems

IT445

College of Computing and Informatics

Question One
1 Marks

Learning Outcome(s):

Develop student’s Knowledge on how EDW can contribute to Business Intelligence

Discuss, analytically yet briefly, the role of an enterprise data warehouse (EDW) in the decision-making process. (0.5 marks) Relate your discussion to operational databases and the type of decisions they support as well as their insufficiency for effective business intelligence strategy compared to EDW. (0.5 marks)
Question Two
1 Marks

Learning Outcome(s):

Develop student’s knowledge on performance measurement and management systems

What does a Performance Measurement System consist of? (0.3 marks) Which Performance Management System is considered a management as well as measurement methodology? (0.3 marks) What is its role as a performance measure and as a management tool separately. (0.4 marks)

Question Three
1.5 Marks

Learning Outcome(s):

Develop students’ knowledge for design principles of Information Visualization

Decision makers in XYZ company need to analyze the organization’s financial performance in order to determine whether departments’ expenditures are over or under budget, thus, deriving actionable decisions. Table 1 provides information extracted from the organization’s data warehouse on the budgeted expenses against actual expenses of the company’s departments over one year period of time.

Dept.

Q1 Budget

Q1 Actual

Q2 Budget

Q2 Actual

Q3 Budget

Q3 Actual

Q4 Budget

Q4 Actual

Distribution

390,000

375,000

395,000

382,000

400,000

390,000

410,000

408,000

Facilities

675,000

693,000

800,000

837,000

750,000

713,000

750,000

790,000

HR

350,000

346,000

350,000

342,000

350,000

340,000

350,000

367,000

IS

950,000

925,000

850,000

890,000

875,000

976,000

900,000

930,000

Table 1: Departments’ yearly performance
A) Use the most appropriate basic visual chart covered in class to show, for each department, the percentage variance between the budgeted and actual expences over each of the four quarters. (0.5 marks)
Variance% = ((budgeted expense – actual expense) / actual expense) * 100
(a positive value indicates that the department is spending less than the budgeted amount, whereas negative values indicate that expenses are more than anticipated).
B) Use the most appropriate basic visual chart covered in class to visualize the difference between the departments’ overall budget values against overall actual values over the four quarters. (0.5 marks)
C) As a decision maker, draw one informative conclusion from each chart (A) and (B) using your own words. (0.5 marks)
Question Four
1.5 Marks

Learning Outcome(s):

Develop student’s knowledge on data warehouse OLAP operations

Suppose that a data warehouse consists of the three dimensions, supplier[sup_name, type] customer[cust_name, email, mobile], location[street, city, country], and the two measures units_sold, and sales_amount (in SAR).
A) Draw a Star schema diagram for the above data warehouse. Include the appropriate attributes for dimension and factor tables (DT and FT). (0.5 marks)
B) Explain how Snowflake schema can provide better representation over Star schema. In the given 3D warehouse above, determine which DT will be affected when implementing the Snowflake model? (0.5 marks)
C) What specific OLAP operations (roll-up, drill down, dice, slice) should be performed (based on the above schema (A)) in order to list the total sales of each supplier in Saudi Arabia? (0.5 marks)