Notes

Course Objectives

• The course provides an overview of data management architectures and analytics procedures aimed at organizing, describing and modeling Big Data (structured and unstructured).
• The convents of the course covers both technical aspects of data management/analytics and topics related to analysis managerial evaluation (how to translate the outputs into meaningful business insights).
• Two different approaches are used: theoretical and applicative. A number of data ingestion procedures and machine learning data analysis case histories are shown, on Big and Small Data, using specific data management and machine learning software.
• At the end of the course the students will be able to improve their skills to manage and to take advantages of the huge availability of data nowadays produced by a great variety of sources.

Informal definition Database = Set of tables (and other objects) Table (a.k.a relation) = set of records. It's defined by a set of fields (columns) that have a name and a data type. Record = horizontal group of values within a table. It contains values for multiple fields.

]

Phase 1 - OLTP-SQL Static Report Starting from the 80s the RDBMS (Relational DataBase Management System) and the SQL (Structured Query Languange) allow the creation of static reports (mostly printed reports). All the queries and reports are built on top of the operational databases.

What's a report? A document containing information organized in a narrative, graphic, or tabular form, prepared on ad hoc, periodic, recurring, regular, or as required basis. We can also define a report as the formatted result of database queries that contains useful data for decision-making and analysis.

Many types of reports:

• Strategic Reports:
  • Aggregated Data
  • It's a summary of the company's situation
  • It's built on a weekly or even monthly basis
• Tactical Reports
  • Less aggregated data
  • It's built on a daily or weekly basis
• Operational Reports
  • It contains both aggregated and detail data.
  • It's built on a daily basis.

Operational Databases An operational database is a database that is used to manage and store data in real time. An operational database stores the day-by-day activity of a company. They support the storage,, usage and manipulation of data for a wide range of enterprise application like ERP and CRM.

• ERP (Enterprise Resource Planning)
  • It refers to the systems and software packages used by organizations to manage day-to-day business activities, such as accounting, procurement, project management and manufacturing.
  • ERP systems tie together many business processes and enable the flow of data between them.
  • Some software tools:
    • SAP
    • Microsoft Dynamics AX
    • Oracle JD Edwards
• CRM: Customer Relationship Management is a technology for managing all the company's relationships and interactions with customers and potential customers.
  • The goal is improving the business relationships.
  • A CRM system helps companies stay connected to customers, streamline processes, and improve profitability.
  • Some software tools:
    • Salesforce.com CR
    • SAP
    • Microsoft Dynamics

When analyzing data directly on the operational databases we may come across some problems.

• The operational databases are not structured for massive reads. They are designed for fast inserts or fast updates.
• Data might be replicated in more than one database (i.e., ERP and CRM). If the data is located in different databases:
• The data format could be different
• Or we could have different different versions of the same data.
• The reporting workload might affect performances of insert/update operations and viceversa.

Note: We'll see a more complete definition of RDBMS in lesson 2. In lesson 2 we'll also see the main differences in database design between Operational Databases and Analytical Databases (Data Warehouse).

Phase 2 - Data Warehouse Due to the operational db problems, starting from the '90s, we see the rise of an analytical database, called data warehouse. At the beginning it was just a mere copy of the operational databases: it solved the performance issued caused by different workloads (massive reads vs. writes). Later the data coming from different databases were integrated, made consistent, cleansed and organized in a different data model: the dimensional model, which is more appropriate for the analytics tasks. The data warehouse is the starting point of the Business Intelligence process.

The Data Warehouse A data warehouse (DW) is a collection of corporate information and data derived from heterogeneous operational systems and external data sources. A data warehouse is designed to support business decisions by allowing data consolidation, analysis and reporting at different aggregate levels. Data is populated into the DW through the processes of extraction, transformation and loading. The data warehouse can be considered as the "single source of truth": it contains the enterprise data once they've been integrated from different sources and cleansed using business rules and data quality rules.

Business Intelligence The term Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information. The purpose of Business Intelligence is to support better business decision making.

Elements of a Business Intelligence System

ETL (Extract Transform Load) The ETL acronym identifies both a class software tools and the process of loading the data warehouse. The ETL process:

• Extracts data from the operational sources.
• Transforms the data by applying:
  • Business rules
  • Data quality checks
• Loads the data into the data warehouse.

Phase 3 - Data Warehouse - BI - OLAP

• The data warehouse requires SQL skills to be queried. The user also needs to know the data structures and the relationships between the tables.
• Starting from the late '90s the OLAP (On Line Analytical Processing) systems come to life.
• OLAP engines are databases where the tables are replaced with multidimensional structures.
• OLAP systems combine data & metadata: the user doesn't need to know the relationships when querying the OLAP Database.
• Additionally the OLAP System are much faster than RDBMS when querying aggregated data (SUMS, AVERAGES, COUNTS, ...)

OLAP

• OLAP (Online Analytical Processing) is the technology behind many Business Intelligence (BI) applications.
• OLAP is a powerful technology for data discovery, including capabilities for limitless report viewing and complex analytical calculations.
• OLAP tools enable users to analyze different dimensions of multidimensional data.
• OLAP can be seen as a Semantic Layer that facilitates the access to the data.

OLAP clients behave like a huge pivot table on all the data warehouse data: they allow "drag&drop" analysis.

Phase 4 - Predictive Analytics

• Both the DWH and the OLAP systems provide an historical view of the data.
• Starting from year 2000 we see an increasing need to predict future events (such as the next month's sales or the cost of energy).
• A new term is created: data mining. It means discovering patterns in large data sets and find hidden patterns that can be exploited in the business.
• The data mining has many applications:
  • Customer segmentation, market basket analysis, churn analysis, campaign targeting, demand forecasting, etc.

Phase 5 - Big Data Platforms

• Starting from 2021 we start to talk about Big Data (& Big Data analytics)
• Big Data can be defined as data with the following features:
  • Volume
  • Velocity
  • Variety

Or, better, we can say that Big Data are "data that we cannot analyze (or that we have no convenience to analyze) with traditional tools (i.e., relational databases)".

Big Data Platforms The main tools that allow us to handle massive amounts of data are:

• Hadoop, which is a distributed file system and computation engine (we'll see the definition of both in lesson 6). It is comprised of many tools that cover all the data life-cycle (from the ingestion from the sources to the data presentation to the user). Hadoop can be used both for data storage and for data analysis.
• Spark, which is a fast distributed computation engine. It is also made of many components and is more oriented to the data analysis.

Today In 2019 the trend topics in data analytics are:

• Big Data
  • New versions of Hadoop
  • New versions of Spark
• Predictive Analytics, ... a modern way to call the data mining.
• Cloud Computing
  • Platform as Service/Infrastructure as a Service
  • Scalable resources at low prices!

New Data Sources "Traditional" data sources with a deeper level of detail:

• ERPs
• CRMs
• ...

Emerging data sources:

• Scientific/Medical Equipment
• DCS (Distributed Control System)
• High Frequency Trading Systems
• Web 2.0: Blog posts, Tweets, Facebook comments & likes, images & videos.
• IOT: Internet of Things.

Features of Emerging Data Sources

• Volume
  • Potentially huge
  • Examples:
    • Detail level credit card transactions
    • Sensors data (from industrial plants, power facilities, etc.)
• Velocity
  • Fast data
  • Example: data coming from IOT sensors
• Variety
  • Structured
    • Data that can be conveniently put into a table
    • Example: data coming from an ERP
  • Unstructured
    • Data that can be conveniently put into a table
    • Examples: text data from tweets or blog posts, Image data from medical equipment.
  • Semi-Structured
    • Most of the so-called unstructured data, are actually semi-structured.
    • Example: a tweet contains the text, which is unstructured, but it also contains other data as key/value pairs: author, date and time, location, etc.

Machine Learning / Data Mining

• Machine Learning It's the science of getting computers to act without being explicitly programmed.

• Data Mining / Predictive Analytics Is the process of discovering patterns in large datasets. It involves methods coming from of artificial intelligence, machine learning, statistics, and database systems. It analyzes the current and historical facts to make predictions about future events.

Two types of analytics

• Descriptive Analytics, it summarizes raw data and make it something that is interpretable by humans. It describes the past. The old-style static reporting and the more recent business intelligence techniques can be placed into the descriptive analytics category.
• Predictive Analytics, these analytics are about understanding the future. It provides companies with insights based on data, provides estimates about the likelihood of a future outcome. Comprises a variety of statistical, machine learning, and data mining techniques. It analyzes the current and historical facts to make predictions about future events.