Discussion for Decision support
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)