DATA WAREHOUSE AND BUSINESS INTELLIGENCE
Knowledge of the theory and practice of relational databases fundamentals and the SQL language (fundamentals).
Course Objectives and Expected Outcomes
The course aims to provide students with basic and concrete knowledge about the implementation process of a Data Warehouse and Business Intelligence system.
Evaluation procedure
Students, individually or in groups of up to two people (usually the same groups that carried out the course exercises), will implement a project with the aim to verify the knowledge and skills expected. The project will be based on a context chosen by the students but with a set of constraints and design requirements.
The examination project requirements document will be available to the students before the end of the course lectures through the e-learning website.
The project generally involves the implementation, using the same tools, of an information system with the following modules:
• A Data Mart deployed on an RDBMS
• An ETL process to Extract, Transform and Load data from the Staging Area to the Star Schema
• A Business Intelligence metadata repository that includes the physical mapping of data, the semantic model and data presentation (dimensions, measures, hierarchies, calculated fields, etc.)
• An interactive dashboard including data views and performance indicators (KPI)
A detailed report is requested to evaluate the project which explain the processes and the choices the students made during the implementation.
The report should include screenshots documenting the correct settings and the implemented query results (also by analyzing the BI system log files) against the project required measures and input data sources.
The report will be used as a guideline, during the exam, to concrete demonstrate what the students have been achieved and documented.
The report discussion, during the exam, will focus on verifying the knowledge gained by showing the ability to independently identify the theoretical foundations used to find solutions to the project issues and to develop a strategy.
The exam duration is about 1,5 hours.
The result of the exam will be in thirtieths: the test is considered successfully passed with a rating of at least 18/30.
• Basic Data Warehouse concepts and terminology ("If you know the enemy and know yourself, you need not fear the result of a hundred battles" SUN TZU on The art of war, V century BC)
Fundamentals of Data Warehouse modeling: Defining the Business Model (Conceptual Model)
4 hours lesson
• Data Warehouse modeling fundamentals: Creating a Dimensional Model (Kimbal methodology) and a Physical Data Model. Exercise: Creating a Data Mart for a sales analysis process (Sales Data Mart).
4 hours lesson
• Data Warehouse modeling fundamentals: Creating a Physical Data Model and performance optimization techniques.
Theory and practice of indexing techniques.
ETL fundamentals: Concepts and terminology of the Data Extraction, Transformation and Loading systems and processes.
4 hours lesson
• Integrate ETL procedures with date investigation and predictive analytic functions.
How to use an open source tool to create and manage Data Access, Data Transformation, Data Investigation and Predictive Analytics processes.
4 hours lesson
• Exercise: How to create and manage an ETL / Analytics system using the Sales Data Mart.
4 hours lesson
• Exercise: How to create and manage an ETL / Analytics system using the Sales Data Mart.
4 hours lesson
• Data Warehouse modeling fundamentals: performance optimization techniques involving hardware, software, data partitioning and pre-aggregated data views.
Exercise: How to implement optimization techniques to the Sales Data Mart.
4 hours lesson
• Business Intelligence fundamentals: concepts and terminology.
Exercise: How to implement a Business Intelligence system using the Sales Data Mart.
4 hours lesson
• Exercise: How to implement a Business Intelligence system using the Sales Data Mart.
4 hours lesson
• Advanced Business Intelligence techniques.
How to create an interactive dashboard to analyze the Sales Data Mart.
4 hours lesson
• Visual Analytics fundamentals: human perception and cognitive processes, data visualization best practices. 4 hours lesson
• Exercise: How to implement a Data Visualization/ Data Exploration system using the Sales Data Mart to quickly create data views and dashboard.
Best Practices for Data Visualization.
4 hours lesson
Lectures slides and other support materials (manuals, SQL code, demo projects) will be available through the e-learning website.
Recommended books to deepen study:
• Data Warehouse, The Complete Guide by Ralph Kimball and Margy Ross, 2003 Hoepli Computing
• Information Dashboard Design: The Effective Visual Communication Of Data by Stephen Few, 2005 Oreilly & Associates Inc