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

Versions: 8.0.1 and 6.8.6

Prerequisites: None

COURSE CONTENT

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

Scheduled Courses
No event found!