Invalid Source Layers

Introduction

We define the term “layer” as an object type in WhereScape RED such as “Load Table”, “Dimension”, “Fact” and others that are usually represented as a folder in the tree navigation of WhereScape RED.

Each column of a specific layer in RED can be sourced by a column from another layer. One can make a mess with hopping over specific layers or cross-referencing different layers to each other. This test case has the goal to force architectural rules regarding the source-layers of each column or field.

The test case checks whether any columns in specific layers are sourced from invalid layers by defining some rules in a parameter list.

How does it work?

The parameterlist WhereScape RED layers (ws-red-layers) maintains a list of several architectural layers like “Source”, “Stage”, “Dimension” etc. In fact these are the objects within the RED repository. The layers are defined by a name, a shortcode (the one used by WhereScape’s meta data) a repo-type and a prefix for the repo-table. You find examples in the parameter list installed with the pack.

For each of these layers, the parameterlist also contains a list of layers in the “Allowed Source Layers” column, where the actual layer can be sourced from. The values are comma separated short codes of the layers that are allowed as a source. “S” means that the current layer can only be based on a stage table. And “S,D” means that the current layer can be based either on a stage table or on a dimension table.

The test case iterates through this parameterlist and runs itself for each layer.

The script assigns a dynamic SQL query to the first probe, which extracts all column-definitions of all objects of the current layer, that are not sourced on one of the allwowed source layers.

The dynamic SQL query is maintained in the snippet “ws-red-invalid-layer-source-dedicated-repo” or in the snippet “ws-red-invalid-layer-source-normal-repo” depending on whether the current layers objects are stored in a dedicated or a common metadata-table.

The allowed-layers get passed to the queries WHERE clause as a NOT IN (…) filter.

Configuration

All configurations are done in the parameter list WhereScape RED Layers (ws-red-layers).