WhereScape University

Prerequisites

This course is designed to provide the student with a good understanding of the basic features of ELT automation using WhereScape 3D and RED. The typical student will be a data warehousing practitioner or database designer, though it is not uncommon to have purely business individuals or people new to this space on the course. With this in mind, we will aim to employ business terminology rather than technical terms to ensure a common understanding. No prior experience with WhereScape 3D or RED is required.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• How ELT/ETL data workflows operate
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed

Students with the following optional skills will get additional value from the experience:

• Data Store design (Inmon) and/or Star Schema design (Kimball)
• A few weeks working with WhereScape 3D and/or RED
• Programming/coding with SQL

What you will learn

The following topics are covered in the course:

Introduction

• WhereScape 3D and RED overviews

New Data Source

• Connecting to a Data Source
• Discover the Data Source
• Profile the Data Source

Create an Operational Data Store (ODS) Model

• Auto-generation of a Logical ODS model together with Load tables, Stage tables and SQL Views
• Preparing the Physical ODS model

Publishing a Physical Model for WS RED export

• Export the Physical model to WhereScape RED
• Creating a Scheduler Job to populate ODS from data sources

Constructing a Dimensional Data Mart

• Creating a Project in RED
• Creating a Dimension table
• Creating a Fact table
• Documentation
• Creating a Scheduler Job to populate Data Mart from ODS

Prerequisites

This course is designed to provide the student with a good understanding of the basic features of WhereScape RED. The typical student will be a data warehousing practitioner or database developer, though it is not uncommon to have purely business individuals or people new to this space on the course. With this in mind, we will aim to employ business terminology rather than technical terms to ensure a common understanding. No prior experience with WhereScape RED is required.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• How ELT/ETL data workflows operate
• Be able to read and write basic SQL statements
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed

Students with the following optional skills will get additional value from the course:

• Star Schema design (Kimball)
• A few weeks working with WhereScape RED
• Programming/coding on Microsoft SQL Server

What you will learn

This course is presented on Microsoft SQL Server purely out of ease and convenience. The basic principles taught in this course apply to all of the target platforms supported by WhereScape RED. The typical student would follow up on this training with advanced training specific to the modelling type or target platforms being used.

The following topics are covered in the course:

Getting Started

• WhereScape RED Overview
• Logging in to WhereScape RED
• RED Builder Window
• RED Scheduler Window
• RED Diagram Window
• RED Procedure Editor Window

Connections

• The Data Warehouse (metadata repository) connection
• Creating a Data Source connection
• Browsing a connection

Loading Data

• ODBC Load
• Native ODBC Load
• Linked Server Load
• Data Type Mappings

Loading Data from Files

• Create a Connection to a Text File
• Populate a Load Table from a Text File
• Archiving Text Files
• Multiple Data File Loads
• Load an XML Data Source
• During/After Load Transformations Against Text Files

Staging and Transforming the Data

• Join Multiple Sets of Data
• Fine-Tune Your Table Specifications
• Make the Data Meaningful
• Merge or Join Multiple Sets of Data

Star Schema - Dimensions

• Creating Dimensions
• Create a Dimension to Track Changes
• Create a Dimension View

Star Schema – Facts

• Stage a Fact table
• Create a Fact table

WhereScape Scheduler

• Schedule an Ad-hoc Job Request
• Create a Job
• Managing Tasks in a Job
• Managing Job Dependencies
• Troubleshooting Job Failures
• Job Documentation
• Maintaining the Job Logs

Projects and Project Groups

• Organize Objects into Groups and Projects

Documentation

• Auto-generate documentation
• Diagrams
• Reports
• Impact Reports

Application Deployments

• Configure a new Environment (Metadata Repository)
• Validate an Environment
• Create an Application
• Move Objects Between Environments
• Compare Environments
• Restore an Environment Backup

Parameters, Procedures and Versioning

• Version Options
• Update Procedures
• Custom Procedures
• Using a Parameter for Incremental Loads
• Deleting an Object

Additional Learning

• Create a View
• Create an Export
• Import Object from Another Environment/Repository
• Create a Date Ranged Dimension Table
• Callable Procedures (API)

Prerequisites

This course is designed to provide the student with a good understanding of the basic features of WhereScape 3D. The typical student will be a data warehousing practitioner or database designer, though it is not uncommon to have purely business individuals or people new to this space on the course. With this in mind, we will aim to employ business terminology rather than technical terms to ensure a common understanding. No prior experience with WhereScape 3D is required.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• How ELT/ETL data workflows operate
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed

Students with the following optional skills will get additional value from the course:

• Star Schema design (Kimball)
• A few weeks working with WhereScape 3D
• Programming/coding with SQL

What you will learn

The following topics are covered in the course:

Getting Started

• WhereScape 3D Overview
• Creating a new local repository

New Data Source

• Connecting to a new Data Source
• Discover the new Data Source
• Derive Foreign Keys
• Managing the diagram layout

Profile a Data Source

• Capture a Data Profile
• View the Data Profile
• Adding your own profiling metrics

Documentation

• Documenting a Model
• Compare Models and documenting the differences

Create a Star Schema Logical Model

• Preparing to create a Star Schema
• Model Dimension tables
• Model a Fact table
• Model Role-play Dimension Views
• Generate artificial keys in the Fact table

Publishing a Physical Model for WS RED export

• Adding Load and Stage tables
• Export the model to RED

Logical Data Model (LDM)

• Create a Star Schema LDM
• Copy a Model

Model Conversion Rules (MCR)

• Model Conversions
• Create a MCR set
• Apply MCRs to a model

Additional Learning

• Using Groups to manage large models
• Generate sample data
• Import a model from external format
• Source mapping a model
• Discover and profile data files
• Auto generate a RED ODS history model from data source

Prerequisites

This course is designed to provide the student with a good understanding of the basic features of WhereScape Data Vault Express. Using a combination of WhereScape 3D and WhereScape RED the student will learn how to quickly model and build a Data Vault 2.0 using WhereScape’s tried and tested Data Warehouse Automation technology.

The typical student will be a Data Vault 2.0 practitioner or database developer who is seeking a better, faster and cheaper way to design and build Data Vault 2.0 data models following the Data Vault 2.0 architecture and methodology. Your basic knowledge of Data Vault 2.0 can be quickly translated to constructed and operational DV 2.0 models on a variety of data platforms including RDBMS, Cloud platforms and Hadoop.

To get the most out of this course it is highly recommended that the student has completed both the WhereScape RED Basics and WhereScape 3D Basics training courses. In addition, the following skills will be advantageous:

• Knowledge of how ELT/ETL data workflows operate
• Be able to read and write basic SQL statements
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed
• Have had some experience with Data Vault 2.0 modelling techniques

What you will learn

This course is presented on Microsoft SQL Server purely out of ease and convenience. The basic principles taught in this course apply to all of the target platforms supported by WhereScape RED. The typical student would follow up on this training with advanced training specific to the modelling type or target platforms being used.

The following topics are covered in the course:

New Data Source

• Connect to a new data source
• Discover and Profile a new data source
  • Adding your own profiling metrics
• Derive Foreign Keys
• Manage Diagram layout

Logically Modelling the Data Vault Design (DVD)

• Preparing the DVD logical model
• Manually creating the DVD logical model
• Creating the DVD from source models
• Importing a DVD logical model

Creating the Raw Data Vault (RDV) model

• Auto generating the RDV model

The Loading and Staging (ELT) for our RDV

• Model Conversion Rules (MCR)
• Auto generating the ELT for an RDV model

Publishing the RDV to a physical environment with WS RED

• Preparing the RDV ELT model for deployment
• Preparing the physical environment in WS RED
• Deploying the physical RDV ELT model to WS RED

Additional Learning

• Automating the Business Data Vault (BDV)
• Using object Groups to manage large models
• Documentation

Constructing a Raw Data Vault (RDV) using WS RED only

• Loading the source data
• Staging the data for the RDV
• Creating Hubs
• Creating Satellites
• Creating Links
• Documenting the RDV

Prerequisites

This course is designed to provide the student with a basic understanding of how to use Pebble Templates in WhereScape. The student registering on this course would be expected to have completed the RED Basics and 3D Basics training courses, or have equivalent knowledge of these courses.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• How ELT/ETL data workflows operate
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed

Students with the following optional skills will get additional value from the course:

• Programming/coding with Pebble / Jinja / Java

What you will learn

The following topics are covered in the course:

Pebble Overview

• Syntax Overview
• Use of variables
• Controlling output
• Control structures
• Macros

Pebble Templates in RED

• Types of Templates
• Default WhereScape Templates
• Creating Templates
• Setting default Templates
• Introduction to the Metadata API
• Generating an API Outline Template
• Using an API Template
• Generating and using a DDL Template
• Generating and using a DML Template
• Generating and using a Utility Template and Macros

Pebble Templates in 3D

• How to reference the 3D User Guide for Pebble and API information
• Using the 3D Template Editor and using Help
• Overview of provided Conversion Rules that use Pebble or Templates
• Using a Template for Matching criteria
• Using Pebble and Templates to rename Entities and Attributes
• Using a Template to generate Entity Queries

Prerequisites

As a follow-on from the Basic Templates training, this course will introduce you to some more complex template concepts and examples to further your learning.

In the resources section of the course you will find all of the examples covered during this course so that you can easily make use of them in your own future development efforts.

An assumption has been made in writing this course that you are already very familiar with both WhereScape 3D and RED. Therefore it is not necessary to walk you through the steps of opening the Model Conversion Manager for example in 3D or explaining what “Rebuild the procedure” means in RED. If you are not already familiar with these terms then this course may not be for you just yet.

This course assumes you will use your own WhereScape 3D installation to try out the examples demonstrated in the videos.

The student registering on this course would be expected to have completed the Basic Templates training course, or have the equivalent knowledge of this course.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• Have worked with WhereScape 3D for over 6 months
• How ELT/ETL data workflows operate
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed
• Programming/coding with Pebble

Students with the following optional skills will get additional value from the course:

• Programming/coding with Jinja / Java

What you will learn

The following topics are covered in the course:

• Use templates for table naming by accessing connection documentation
• Create and use custom entity/attribute matchers in model conversions
• Conceptualise and create your templates using the template editor built into 3D
• Generate repeatable pattern-based transformations using templates
• Write to and read from documentation fields using templates with model conversions
• Make use of StringUtils, Java Functions and Regex in your templates
• Create a query generation template and use it to populate entity queries during model conversion

Prerequisites

As a follow-on from the Basic Templates training this whitepaper will introduce you to some more complex Model Conversion Rule (MCR) concepts and examples to further your learning.

The student registering on this course would be expected to have completed the Basic Templates training course, or have the equivalent knowledge of this course.

To get the most out of this course the student should have a good working knowledge of Data Warehouse operations, including:

• Have worked with WhereScape 3D for over 6 months
• How ELT/ETL data workflows operate
• Have a good working knowledge of how Relational Database Management Systems (RDBMS) are designed and constructed
• Programming/coding with Pebble

Students with the following optional skills will get additional value from the course:

• Programming/coding with Jinja / Java

What you will learn

The following topics are covered in the course:

Overview of the Basics

• What are Model Conversion Rules (MCRs)?
• Where do I start?
• The Model Conversion Manager
• Matchers and Actions
  • Entity and Attribute Types
    
    

Getting Started

• Manually building a prototype
• Automating the steps
  • Create a new MCR
  • Create Load Tables
  • Create Data Store Tables
  • Applying Model Conversions to your model

Next Steps

• Setting the default MCRs
• Testing and Debugging
  • Running with Snapshots

Advanced Topics

• Custom Matchers
• Configurable (parameterized) Rules
• Changing standard MCRs
• Adding Model Categories
• Adding Entity Types