Decision Support System

Pg. 02

 

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

 

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

 

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

 

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)