WhereScape Developer

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.

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.

You will learn how to install the WhereScape software and setup a new data warehouse along with its scheduler.

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.

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.

You will find out how to load the different types of data into the data warehouse using all the load methods.

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.

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.

You will find out how to create both historical and normal Data Store and 3^rd Normal Form foundation tables and how WhereScape handles slowly changing data as well as adding the relationships for the 3NF tables.

Learn how to build the hub, link and satellite Data Vault tables in the data warehouse.

We will design the data vault, create the DV staging tables, create the hubs, links and satellites.

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.

Learn how to build a data mart.

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.

You will find out how to build the dimension and fact tables that make up a star schema and how to manage metadata change.

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.

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.

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.

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.

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.

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.

Learn how to create and use WhereScape Parameters.

You will create a parameter, use it to do incremental loading and learn how to update the parameters value using a stored procedure.

You will find out how important WhereScape Parameters are in automating your data warehousing processing.

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.

Convert a dimension from normal to slowly changing, create non-snowflake dimensions and the dimensional hierarchies.

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.

To understand the difference between transactional fact tables and aggregated fact tables.

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.

You will find out the difference between base and aggregate fact tables.

Here you will learn how WhereScape integrates with Microsoft Analysis Services (SSAS) to create and update OLAP cubes.

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.

You will be able to use WhereScape to create and update SSAS cubes.

Learn how to use Table Partitioning for large fact tables and we will understand how WhereScape handles updating of partitioned fact tables.

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.

You will find out why and how to create partitioned fact tables.

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.

• 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.

This will help to cement all the things you have learnt about creating data warehouses using WhereScape RED.