INFS2608 T2 2024 Week 03 Data Warehouse II and Business Intelligence.pdf

UNSW Business School School of Information Systems and Technology Management INFS2608 Data Management & Big Data Infrastructures Week 03 - Data Warehouse II and Business Intelligence Dr Vincent Pang Vincent....

UNSW Business School School of Information Systems and Technology Management INFS2608 Data Management & Big Data Infrastructures Week 03 - Data Warehouse II and Business Intelligence Dr Vincent Pang [email protected]  Week 03 Lecture Agenda ❑Data Warehouse and Business Intelligence ❑The End Goal – Decision Making ❑Business Intelligence Framework ❑Operational Data versus Decision Support Data ❑Online Analytical Processing (OLAP) ❑OLAP Architecture, including Data Marts ❑Retrieving Data from Data Warehouse ❑Evolving in Data Analytics ❑Modern Data Warehouse  Data Warehouse and Business Intelligence Entity Relationship Model (ERM) Star Schema Normalisation (De-Normalised) ETL (Data Cleansing) Relational Data Warehouse Database (Oracle RdB) (Oracle DW) Export Data or Access Data Directly We will not cover PowerBI in depth. You only learn Microsoft PowerBI how to create simple reports and graphs. Reporting (Business Intelligence INFS3603 will cover data and Visualisation) and Business visualisation in depth. Analysis (End Users) Updated by Vincent Pang, Dec. 2021 2 Prepared by Vincent Pang, Feb. 2021  The End Goal – Decision Making ❑ Organisations tend to grow and prosper as they gain a better understanding of their environment ▪ Evaluate through tracking daily transactions and analysing company data ❑ Organisations are always looking for a competitive advantage. Some examples include: ▪ Product development ▪ Market positioning ▪ Sales promotions ▪ Customer service ❑ Business Intelligence (BI) is the common solution used! ▪ Data-driven approach ▪ You should have covered the topics on BI in your readings 3 Business Intelligence Framework (PowerBI) (Excel) (Oracle) Materialized View (Oracle) (Oracle) 4  Business Intelligence Evolution Plus ML and AI ML = Machine Learning AI = Artificial Intelligence 5 Operational Data versus Decision Support Data (1 of 2) ❑ Operational data and decision support data serve different purposes ▪ Operational data is useful for capturing daily business transactions (data stored in a normal Relational DataBase) ▪ Decision support data gives tactical and strategic business meaning to the operational data (data stored in a Data Warehouse) ❑ Decision support data differs from operational data in three main areas ▪ Time span ▪ Granularity (level of aggregation) ▪ Dimensionality Operational Data versus Decision Support Data (2 of 2) Excel Pivot Table in Excel  Online Analytical Processing (OLAP) ❑Online analytical processing (OLAP) refers to an advanced data analysis environment that supports decision making, business modeling, and operations research ❑Online analytical processing (OLAP) is a BI style whose systems share three main characteristics 1. Multidimensional data analysis techniques 2. Advanced database support 3. Easy-to-use end-user interfaces  1. Multidimensional Data Analysis Techniques “Data In” Data Warehouse “Data Out” 9  2. Advanced Database Support and 3. Easy-to-Use End-User Interface 10  OLAP Architecture 11  OLAP and Data Marts 12  Retrieving Data from Data Warehouse Retrieving data from Data Warehouse using Materialized View 13  DW SQL Analytic Functions ❑ The common syntax related to getting data out of Data Warehouse. ❑ The ROLLUP extension ▪ Used with GROUP BY clause to generate aggregates by different dimensions ▪ Enables subtotal for each column listed except for the last one, which gets a grand total ❑ The CUBE extension ▪ Used with GROUP BY clause to generate aggregates by the listed columns ▪ Enables you to get a subtotal for each column listed in the expression, in addition to a grand total for the last column listed ❑ Materialized views ▪ Dynamic table that contains SQL query command to generate rows and stores the actual rows ▪ Created the first-time query is run, and summary rows are stored in the table ▪ Automatically updated when base tables are updated ▪ Requires specified privileges  Rollup Extension 15  Cube Extension 16  Materialized View 17  Evolving in Data Analytics Today, different ways data could be captured and analysed. 18 Image from: Lecture presented by Vinicius Cardoso in INFS5710 Lecture at UNSW  Modern Data Warehouse (1) ❑ Data warehouse is evolving. ❑ Yesterday’s data storage might be outdated ❑ The need to integrate what you currently have with what you are going to get. 19 Image from: Unlock the value in data with Oracle’s Modern Data Warehouse  Modern Data Warehouse (2) ❑ Integrated data warehouse and data analytics tools together. ❑ The addition of Machine Learning (ML) and Artificial Intelligence (AI) ▪ “Customers can load any data, store in a data warehouse or data lakes, and transform, catalog, govern, visualize, analyze, and build ML models.” ▪ “A single solution with built-in support for multimodel data and multiple workloads such as analytical SQL, in- database machine learning, graph, and spatial, eliminates the development and deployment complexity.” 20 Ref: Modern Data Warehouse | Oracle Australia  Modern Data Warehouse (3) Example: Microsoft Azure 21 Ref: Modern data warehouse (microsoft.com)  Microsoft Azure DW - Architecture overview 22  Modern Data Warehouse (4) Another Example: Amazon AWS Redshift 23 Ref: Cloud Data Warehouse – Amazon Redshift – Amazon Web Services  Summary ❑You learn the relationship between Data Warehouse and Business Intelligence ❑The changes to Data Warehouse and Business Intelligence are associated to Decision Making ❑You have examined Online Analytical Processing (OLAP) and how Data Marts are generated ❑You have learned the SQL Analytic functions used to retrieve data from Data Warehouse ❑The evolution of Modern Data Warehouse is discussed. 24  Questions ? 25