Using the zData Warehouse [version uncertain]

Owned by Cortlandt _
Oct 04, 2023
4 min read

The purpose of the zData Warehouse

Using the zData Warehouse tables can improve performance when writing more complex SQL queries with multiple tables or using SQL functions for calculated fields. The SQL query can use local data in the SQL database rather than pull from the source ERP/CRM system for the data, which is often slower and more complex. For Sage 100 Providex ERP sources, using the zData Warehouse tables allows you to use standard SQL syntax rather than being constrained by proprietary Providex syntax.

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.

Note the ERP source table names don’t have a schema name. The table names in the zData Warehouse source begin with a schema name, then the table name. This is what indicates that the source is the DataSelf SQL data warehouse, not the ERP.

 

To add a table to the zData Warehouse

Method 1:

Right click on the desired table under the zData Warehouse source and select Add Table.

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. See the SQL Query Statement on the right panel pulling data from the zData Warehouse SQL source instead of from the ERP source.

 

Note the different sources in the FROM section of the ETL SQL STATEMENTS below. The Account table from the Acumatica ERP source says FROM Account. The Account(1) table from the zData Warehouse source says FROM [dataselfcertifiedtraining_acumatica].[Account]. This is the SQL schema & table name source rather than the ERP source with no schema name.

 

Method 2:

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.