BUS5WB – Data Warehousing and Big Data

Assignment 02: OLAP, Cubes, and Insights

Marks: 30%

Assignment Type: Individual

Release Date: Monday 29^th April 2019.

Due Date: 11:59PM Monday 27^th May 2019.

The second assignment focuses on the insight generating capacity of a data warehouse. It requires you to apply dimensional model design expertise to improve an existing model and the application of OLAP, cube building and analysis tools, to generate business insights. Your ability to correctly apply these tools to a business use case will be assessed.

Business Case

The CIO of Valeur, a large national supermarket/retail store chain has recently introduced a loyalty card program and keen to gain a more comprehensive understanding of the organisation’s clientele through traditional transactional data as well as the newer and effective demographics analysis techniques.  A close friend, the CEO of Vigour has highly recommended you as an accomplished business analyst/consultant for data warehousing and OLAP projects based on the outstanding work completed on the dimensional modelling and design phase of a data warehouse at Vigour.

The CIO of Valeur, impressed by the commendation, has approached you with her interests in customer analytics. The customer relationship management (CRM) team working under her supervision have already put together a summarised data warehouse containing some transaction data and customer demographics. Although the data is accurate and complete, the CIO believes the structure is not. She feels there’s more information that can be captured by the warehouse. She’s very impressed with the power of demographics as it is directly applicable to his client base.

She has two main business problems in mind. Firstly, improving the current dimensional model/ warehouse to capture all relevant information that applies to customers, such as demographics. Secondly, using current data in the warehouse to better understand the customer base by identifying customer behaviours and thereby potential sales and marketing opportunities. You have agreed to take on this responsibility and deliver a complete report documenting your recommendations to improve the dimensional model and your findings from the current warehouse.  

What you are required to do

1. Dimensional model redesign

Study the given dimensional model to determine what is lacking in response to the business need. Identify potential improvements to the dimensional model. This can be in the form of new attributes, new dimensions, new measures or the use of dimension design techniques. Present your recommendations with supporting evidence and the new dimensional model.

2. Demonstration of Analysis Tools

The data warehouse (ValeurDW) is accessible from the WB server.

Use the knowledge and experience gained from tutorials 7-10, to demonstrate each of the seven analysis technique/tool – 1) SSAS, 2) Cube Features, 3) SSRS, 4) MDX, 5) SSDT Data mining, 6) Excel Power Pivot, and 7) PowerBI, by conducting a comprehensive analysis of customer behaviours and thereby potential sales and marketing opportunities.

It is anticipated that you will demonstrate your proficiency with advanced analysis approaches rather than simple and straightforward querying. Each demonstration should lead towards an insight/recommendation of business value. A minimum of seven demonstrations is expected, preferably in increasing complexity and increasing decision value.

Deliverables

A report on the three activities undertaken for Valeur.

• The report should be compiled in Microsoft Word only, font size 11.
• Should not exceed 10 pages. Diagrams, tables and any other visualisations/ screen captures should be in the main body of the report.
• Should contain a reference to project files (noting project file names) on the server.
• Make realistic assumptions on any information (schema or business requirements) that may be missing in the above description. Mention all assumptions in the report.

Dimensional Model

Rubric