OVERVIEW
This two day course is intended to introduce Business Intelligence and Analytics to the students. This course will introduce data warehousing concepts and how it differs from transactional databases. It will describe the differences both in terms of how we gather requirements and how we model the database. Students will use Analytic SQL to aggregate, analyze and report, and model data. Students also learn to use regular expressions and subexpressions to search for, match, and replace strings.
DURATION: 2 Days
OBJECTIVES
- Review relational database terminology
- Define multi-dimensional terminology
- Describe the benefits and uses of the dimensional model, also known as a Star schema.
- Understand the overall design and structure of a Data Warehouse, including the layered architecture
- How to gather requirements for Dimensional Modeling.
- Understand the data integration concepts
- Understanding of reporting tools
- Identify the benefits of using Analytic SQL
- Identify the benefits of using regular expressions
- Review the available SQL for aggregation operators, SQL for Analysis and Reporting functions, and the SQL for Modeling using Group and aggregate data using the ROLLUP and CUBE operators, the GROUPING function, Composite Columns, and the Concatenated
- Analyze and report data using Ranking functions, the LAG/LEAD functions, and the PIVOT and UNPIVOT clauses
- Use the MODEL clause to create a multidimensional array from query results
INTENDED AUDIENCE
This seminar is designed for systems analysts, application developers/analysts, business analysts, project leaders and people involved with Data Warehousing and Business Intelligence.
PREREQUISITES
Basic understanding of Relational Databases, and working knowledge of SQL is required.
FORMAT/MATERIALS
This course includes a combination of lecture, discussion and numerous computer workshops. Students will receive an extensive course manual covering SQL, which will be a valuable reference after class.
CONTENT
- Terminology
- Define staging
- Define an ODS
- Define a mart
- Define Analytics
- Define Data Mining
- Define a data warehouse
- Benefits of dimensional models
- Intuitive to use and understand
- Improved overall performance
- Easier to maintain, and change
- Simplified structure
- Dimensional Model
- What is a dimensional model?
- The Star or Cube Schema
- The Snowflake Schema
- Snowflake Schema Design
- Star vs. Snowflake Schema
- Fact Tables
- Dimension tables
- Database sizing
- Layered Data Architecture
- Transactional layer
- Staging layer
- Operational Data store for relational online analytical processing (ROLAP)
- Analytical layer for online analytical processing (OLAP)
- Database products used for storing data layers
- Tools used for extract, translate and load
- Tools used for Business Intelligence reporting
- Requirements gathering in a data warehouse
- Discuss business and process alignment
- How does business process affect the data warehouse
- Getting from business requirements to data requirements
- Mapping business questions to data
- Addressing business performance, people and process
- Establishing a framework for business questions
- Data Integration
- What is data integration
- Why is it necessary to integrate data?
- When should data integration happen?
- When should data integration be avoided?
- Challenges of data integration
- Data integration architecture examples
- Reporting tools
- What do reporting tools do
- What do all of the tools have in common
- What tasks are reporting tools very good at achieving
- Where are reporting tools lacking
- Short primer on main stream reporting tools
- Grouping and Aggregating Data Using SQL
- What is Analytic SQL?
- Analytic SQL in Data Warehouses
- Generating Reports by Grouping Related Data
- Using the GROUP BY Clause
- Using the ROLLUP and CUBE Operators
- Using the GROUPING Function
- Working With GROUPING SETS
- Working With Composite Columns and Concatenated Groupings
- Analyzing and Reporting Data Using SQL
- Overview of SQL for Analysis and Reporting Functions
- Identifying the SQL Ranking Functions
- Controlling the Ranking Order
- Ranking on Multiple Expressions
- Using the RANK, DENSE_RANK, and PERCENT_RANK Functions
- Ranking Per CUBE and ROLLUP
- Using the LAG/LEAD Functions
- Performing Pivoting Operations Using the PIVOT and UNPIVOT Clauses
- Modeling Data Using SQL
- Overview of SQL for Modeling Data
- Integrating Inter-row Calculations in SQL
- Working With the SQL MODEL Clause
- Cell and Range References
- Using the CV()Function
- Using the FOR Construct with IN List Operator, Incremental Values, and a Subquery
- Using Reference Models
- Cyclic Rules in Models
- Analyzing Data Using Regular Expressions
- The Benefits of Using Regular Expressions
- Using the Regular Expressions Functions and Conditions in SQL
- Performing a Basic Search Using the REGEXP_LIKE Condition
- Finding Patterns Using the REGEXP_INSTR Function
- Extracting Substrings Using the REGEXP_SUBSTR Function
- Replacing Patterns Using the REGEXP_REPLACE Function
-