WhereScape Developer
WhereScape Developer course is designed for new and existing data warehouse practitioners who wish to use WhereScape to design and build data warehouses and reporting databases.

ABOUT THIS COURSE
The course teaches WhereScape features, and how to apply them to real life data scenarios using the rapid, iterative capability of WhereScape. This 3 day instructor-led course includes two and a half days training on the usage of WhereScape, and culminates with a half day hands on data warehouse practice which guides the delegate through design and build of a data mart from start to finish using WhereScape.
The course uses a transactional operational data source, through star schema or normalised models, to analysis cubes and data delivery, built using WhereScape and an iterative approach.
Audience
Data Warehouse Developers.
Duration: 3 days
Prerequisites: None
Lesson
Introduction to the concepts of Data Warehousing including Data Store, Third Normal Form, Data Vault, Star Schema and OLAP Cube. Understand the different modelling types Conformed dimensions by Ralph Kimball, Normalized by Bill Inmon, Data vault by Dan Linstedt and Big Data.
Outcome
You will understand data warehousing concepts and find out the differences between the modelling types.
Lesson
Find out the prerequisites to installation of WhereScape, how to install the client software, update the licence key, how to install the Metadata Repository, create the source database, update data warehouse options and user preferences and install the Scheduler.
Lab
For the lab you will install WhereScape RED, then using the Admin Application you will use Quick Start to create a Data Warehouse with Metadata Repository along with the Tutorial source database. This will start the Database Application where you will modify the data warehouse options and user preferences and validate the install. Finally, using the Admin Application again you will create the WhereScape Scheduler in services.
Outcome
You will learn how to install the WhereScape software and setup a new data warehouse along with its scheduler.
Lesson
Learn how to load data using different load methods depending on the data source into the data warehouse. This includes database link, ODBC, Native ODBC, Excel spreadsheet, text files and SSIS. You will also learn about trigger files to validate loading text data.
Lab
The lab includes creating Connections to the data, creating the load tables in the data warehouse and using the different methods to load the data. You will also create a Post Load Procedure to check the validity of the load using the trigger files.
Outcome
You will find out how to load the different types of data into the data warehouse using all the load methods.
Lesson
The objective is to understand the features supported by WhereScape when building and maintaining both Data Store and 3rd Normal Form data warehouse tables. This includes both non-history and historical tables and as such you will find out how WhereScape handles slowly changing data. For the normalized tables we also find out how to include the join information in the documentation.
Lab
You will create Data Store historical tables and both history and non-history 3NF tables including joining multiple input tables. To assist in the documentation of your data warehouse you will add the relationship information at the 3NF level.
Outcome
You will find out how to create both historical and normal Data Store and 3rd Normal Form foundation tables and how WhereScape handles slowly changing data as well as adding the relationships for the 3NF tables.
Lesson
Learn how to build the hub, link and satellite Data Vault tables in the data warehouse.
Lab
We will design the data vault, create the DV staging tables, create the hubs, links and satellites.
Outcome
You will understand how to design a Data Vault and how to create the different tables in the Data Vault layer including the hash keys required to uniquely identify rows of data.
Lesson
Learn how to build a data mart.
Lab
You will create dimensions, dimension views, join the fact data and dimension surrogate keys in a stage table prior to creating the fact table. We use the diagrammatic views to understand relationships and impact analysis. You will also create and display complete data warehouse documentation and manage metadata change where you will find out about versioning and comparing code from different versions.
Outcome
You will find out how to build the dimension and fact tables that make up a star schema and how to manage metadata change.
Lesson
Learn how to organise data warehouse objects into projects and groups which will allow you to perform actions on multiple objects at the same time. You will also learn how to create a production data warehouse and deploy objects from development to the production environment via application packages.
Lab
The exercises include creating projects and a group and you will see how you can document all the objects in a single project. Next you will create a production style data warehouse, create an application package in the development environment, install the objects into the production environment and compare objects between the two metadata repositories.
Outcome
You will find out how to manage objects within a data warehouse, how to migrate application packages containing multiple objects between development and production environments and to compare the metadata between environments.
Lesson
Learn all about the WhereScape Scheduler, including how to create a job with the tasks necessary to process the entire data warehouse, both manually and using the diagrammatic Joins Diagram. You will also get to understand the Scheduler screens and options.
Lab
We create a job, both manually and via the diagrammatic window. After using the diagrammatic window to view the tasks within the job you will see the task dependencies. You will use the scheduler window and options to run the job and check the task results.
Outcome
You will find out how best to create a job with all the tasks and dependencies using the diagrammatic Joins Diagram and how to run and check the results.
Lesson
Learn how to create and use WhereScape Parameters.
Lab
You will create a parameter, use it to do incremental loading and learn how to update the parameters value using a stored procedure.
Outcome
You will find out how important WhereScape Parameters are in automating your data warehousing processing.
Lesson
Understand the different types of dimensions that you can create, normal, changing, previous, ranged and time dimensions. You will also find out how to change a normal to a historic dimension and how to eliminate snowflake dimensions. There is a section on creating dimensional hierarchies which will be used when building OLAP cubes.
Lab
Convert a dimension from normal to slowly changing, create non-snowflake dimensions and the dimensional hierarchies.
Outcome
You will find out about the different types of dimensions, how to change from a normal to slowly changing dimension, how to combine multiple levels of data into a dimension to avoid creating a snowflake dimension and how to create dimensional hierarchies.
Lesson
To understand the difference between transactional fact tables and aggregated fact tables.
Lab
The exercise builds base fact tables from stage tables, then combines the facts into a rollup fact table and view the resulting data using Excel. You will also create an aggregate fact table from a base fact table and compare the results by again viewing the data in Excel.
Outcome
You will find out the difference between base and aggregate fact tables.
Lesson
Here you will learn how WhereScape integrates with Microsoft Analysis Services (SSAS) to create and update OLAP cubes.
Lab
Firstly, you will need to ensure that all dimensions have at least one hierarchy. You will need to create a connection to SSAS, update the data warehouse connector, created the cube metadata eliminating any numeric fields that shouldn’t be aggregated before creating the cube and cube dimensions and updating the cube with data from WhereScape. Next, you will query the cube using Excel and visualising with a PivotChart. Finally, you will add a calculated member, update the cube and the data and re-visualise.
Outcome
You will be able to use WhereScape to create and update SSAS cubes.
Lesson
Learn how to use Table Partitioning for large fact tables and we will understand how WhereScape handles updating of partitioned fact tables.
Lab
You will convert a normal fact table into a partitioned fact table using the wizard which includes re-building the update stored procedure. Using the code viewer compare, you will also see the difference between the old and new code. Next we copy the data from the partitioned table back to the original fact table before deleting the rows from the partitioned table and finally use the new stored procedure to update the fact table.
Outcome
You will find out why and how to create partitioned fact tables.
Lesson
Using a business requirement and an agile approach you will learn to create a data warehouse end-to-end including creating a scheduler, creating and running a job to process all the source data through the entire data warehouse and create and display the documentation for the data warehouse.
Lab
- Design a data warehouse from a database diagram of the source system.
- Create a new data warehouse including installing the metadata repository and a scheduler.
- Create a connection to the Northwind source database.
- Create the load tables and data store layer tables
- Create dimensions and role playing dimensions.
- Stage the fact data and dimensional surrogate keys.
- Create the fact table.
- Create a job schedule where the tasks run multi-threaded for the entire data warehouse.
- Create and display the fully documented data warehouse.
Outcome
This will help to cement all the things you have learnt about creating data warehouses using WhereScape RED.
Tell Me, I’ll Forget;
Show Me, I’ll Remember;
Let Me Do It, I’ll Understand
Lesson
Introduction to the concepts of Data Warehousing including Data Store, Third Normal Form, Data Vault, Star Schema and OLAP Cube. Understand the different modelling types Conformed dimensions by Ralph Kimball, Normalized by Bill Inmon, Data vault by Dan Linstedt and Big Data.
Outcome
You will understand data warehousing concepts and find out the differences between the modelling types.
Lesson
Find out the prerequisites to installation of WhereScape, how to install the client software, update the licence key, how to install the Metadata Repository, create the source database, update data warehouse options and user preferences and install the Scheduler.
Lab
For the lab you will install WhereScape RED, then using the Admin Application you will use Quick Start to create a Data Warehouse with Metadata Repository along with the Tutorial source database. This will start the Database Application where you will modify the data warehouse options and user preferences and validate the install. Finally, using the Admin Application again you will create the WhereScape Scheduler in services.
Outcome
You will learn how to install the WhereScape software and setup a new data warehouse along with its scheduler.
Lesson
Learn how to load data using different load methods depending on the data source into the data warehouse. This includes database link, ODBC, Native ODBC, Excel spreadsheet, text files and SSIS. You will also learn about trigger files to validate loading text data.
Lab
The lab includes creating Connections to the data, creating the load tables in the data warehouse and using the different methods to load the data. You will also create a Post Load Procedure to check the validity of the load using the trigger files.
Outcome
You will find out how to load the different types of data into the data warehouse using all the load methods.
Lesson
The objective is to understand the features supported by WhereScape when building and maintaining both Data Store and 3rd Normal Form data warehouse tables. This includes both non-history and historical tables and as such you will find out how WhereScape handles slowly changing data. For the normalized tables we also find out how to include the join information in the documentation.
Lab
You will create Data Store historical tables and both history and non-history 3NF tables including joining multiple input tables. To assist in the documentation of your data warehouse you will add the relationship information at the 3NF level.
Outcome
You will find out how to create both historical and normal Data Store and 3rd Normal Form foundation tables and how WhereScape handles slowly changing data as well as adding the relationships for the 3NF tables.
Lesson
Learn how to build the hub, link and satellite Data Vault tables in the data warehouse.
Lab
We will design the data vault, create the DV staging tables, create the hubs, links and satellites.
Outcome
You will understand how to design a Data Vault and how to create the different tables in the Data Vault layer including the hash keys required to uniquely identify rows of data.
Lesson
Learn how to build a data mart.
Lab
You will create dimensions, dimension views, join the fact data and dimension surrogate keys in a stage table prior to creating the fact table. We use the diagrammatic views to understand relationships and impact analysis. You will also create and display complete data warehouse documentation and manage metadata change where you will find out about versioning and comparing code from different versions.
Outcome
You will find out how to build the dimension and fact tables that make up a star schema and how to manage metadata change.
Lesson
Learn how to organise data warehouse objects into projects and groups which will allow you to perform actions on multiple objects at the same time. You will also learn how to create a production data warehouse and deploy objects from development to the production environment via application packages.
Lab
The exercises include creating projects and a group and you will see how you can document all the objects in a single project. Next you will create a production style data warehouse, create an application package in the development environment, install the objects into the production environment and compare objects between the two metadata repositories.
Outcome
You will find out how to manage objects within a data warehouse, how to migrate application packages containing multiple objects between development and production environments and to compare the metadata between environments.
Lesson
Learn all about the WhereScape Scheduler, including how to create a job with the tasks necessary to process the entire data warehouse, both manually and using the diagrammatic Joins Diagram. You will also get to understand the Scheduler screens and options.
Lab
We create a job, both manually and via the diagrammatic window. After using the diagrammatic window to view the tasks within the job you will see the task dependencies. You will use the scheduler window and options to run the job and check the task results.
Outcome
You will find out how best to create a job with all the tasks and dependencies using the diagrammatic Joins Diagram and how to run and check the results.
Lesson
Learn how to create and use WhereScape Parameters.
Lab
You will create a parameter, use it to do incremental loading and learn how to update the parameters value using a stored procedure.
Outcome
You will find out how important WhereScape Parameters are in automating your data warehousing processing.
Lesson
Understand the different types of dimensions that you can create, normal, changing, previous, ranged and time dimensions. You will also find out how to change a normal to a historic dimension and how to eliminate snowflake dimensions. There is a section on creating dimensional hierarchies which will be used when building OLAP cubes.
Lab
Convert a dimension from normal to slowly changing, create non-snowflake dimensions and the dimensional hierarchies.
Outcome
You will find out about the different types of dimensions, how to change from a normal to slowly changing dimension, how to combine multiple levels of data into a dimension to avoid creating a snowflake dimension and how to create dimensional hierarchies.
Lesson
To understand the difference between transactional fact tables and aggregated fact tables.
Lab
The exercise builds base fact tables from stage tables, then combines the facts into a rollup fact table and view the resulting data using Excel. You will also create an aggregate fact table from a base fact table and compare the results by again viewing the data in Excel.
Outcome
You will find out the difference between base and aggregate fact tables.
Lesson
Here you will learn how WhereScape integrates with Microsoft Analysis Services (SSAS) to create and update OLAP cubes.
Lab
Firstly, you will need to ensure that all dimensions have at least one hierarchy. You will need to create a connection to SSAS, update the data warehouse connector, created the cube metadata eliminating any numeric fields that shouldn’t be aggregated before creating the cube and cube dimensions and updating the cube with data from WhereScape. Next, you will query the cube using Excel and visualising with a PivotChart. Finally, you will add a calculated member, update the cube and the data and re-visualise.
Outcome
You will be able to use WhereScape to create and update SSAS cubes.
Lesson
Learn how to use Table Partitioning for large fact tables and we will understand how WhereScape handles updating of partitioned fact tables.
Lab
You will convert a normal fact table into a partitioned fact table using the wizard which includes re-building the update stored procedure. Using the code viewer compare, you will also see the difference between the old and new code. Next we copy the data from the partitioned table back to the original fact table before deleting the rows from the partitioned table and finally use the new stored procedure to update the fact table.
Outcome
You will find out why and how to create partitioned fact tables.
Lesson
Using a business requirement and an agile approach you will learn to create a data warehouse end-to-end including creating a scheduler, creating and running a job to process all the source data through the entire data warehouse and create and display the documentation for the data warehouse.
Lab
- Design a data warehouse from a database diagram of the source system.
- Create a new data warehouse including installing the metadata repository and a scheduler.
- Create a connection to the Northwind source database.
- Create the load tables and data store layer tables
- Create dimensions and role playing dimensions.
- Stage the fact data and dimensional surrogate keys.
- Create the fact table.
- Create a job schedule where the tasks run multi-threaded for the entire data warehouse.
- Create and display the fully documented data warehouse.
Outcome
This will help to cement all the things you have learnt about creating data warehouses using WhereScape RED.