Using the zData Warehouse

The purpose of the zData Warehouse

Many times, users will need to take data already in the data warehouse and perform additional data manipulation for faster or easier reporting. For instance, taking a table that has detailed sales transactions and grouping it to show sales by year in the data warehouse. An option is trying to do that transformation during the import of the source system, however, many source systems won’t allow transformations during the extraction or performance extraction can be a problem (ex.: many ODBC sources have limited transformation capabilities).

When the data is already in the data warehouse, it’s easy and fast to leverage SQL power to do data manipulation directly in the data warehouse. The idea is to re-import a table or tables from the data warehouse into a new table.

Example 1. Maybe you formatted a date-time field from the data source to be a date only field in the SQL data warehouse. If you want to use that data in another query, it is much easier to pull it from the already formatted SQL table than do another query of the source and have to format the data again.

Example 2. If you are doing a recursive join to a table, it is much faster using the ‘local’ zData Warehouse tables. This is common in multi-level Bill of Material queries where you refer to the same Bill of Material table multiple times to show which level of the BOM the Bill Item is on.

Example 3. If you are doing a SQL UNION query it is much simpler to pull from the zData Warehouse tables.

Note that zData Warehouse tables must have a schema name built into FROM statement.
The ERP source tables often do not need a schema name.

How the zData Warehouse (Source) gets populated

When you refresh the zData Warehouse source in the left panel (Source) , it lists all the SQL tables from the center panel (Data Warehouse) for the ERP source. It will only list those tables that have data loaded to SQL. If you created a SQL query from your ERP source but never loaded the data, that would not show in the zData Warehouse source. These SQL tables are now an alternate source for you to use in other ETL+ queries.

Open

To add a table from the zData Warehouse

Method 1: Re-importing a table already in the data warehouse

This is useful for data manipulation.

For instance, ARTran below has been extracted from an OData v4 source into the data warehouse. It contains detailed sales transactions. This table will remain in the data warehouse for detail-level reporting.

Open

Let’s assume that now we need to create a derived table from ARTran showing total sales by CreateDate.

On the ETL Source panel, select zData Warehouse and click the Refresh icon to list the tables already loaded into the data warehouse. Then select ARTran and click the Add Table icon (or Right-click on the table and select Add Table). See below.

A message will display stating that the table name already exists, Add a copy? Click on OK

ETL+ does not allow duplicate table names. The new table name will add (1) at the end to distinguish it from the original. You can modify the table name as desired.

In the example below, see the SQL Statement on the right panel. It pulls data from the zData Warehouse source; indicated by this table being under the zData Warehouse (Reimports) header on the center panel. When you select this table on the center panel, the right panel’s header shows source = zData Warehouse. Note that this SQL Statement has been customized to sum sales by CreateDate.

Method 2: Create a new table from scratch

Right click on the zData Warehouse icon in the center panel and select create table.

This will create a "NEW TARGET TABLE" with no SQL statement. Note the blank ETL SQL Statement. You can copy/paste or manually write the SQL query statement. Make sure to add the schema name in the FROM clause to point to the existing SQL table as the source rather than the ERP data source.

v2022.08