Part of what makes WhereScape Red such a powerful tool is how it treats metadata. The metadata is used to drive every component of WhereScape Red’s GUI and often that is sufficient for business needs.
However!
Moving beyond the GUI and opening up the WhereScape Red metadata database opens up possibilities like
Custom object management Bulk updates
Detailed lineage information New automation possibilities
We’ll cover the whats and hows of each idea in forthcoming blogs. In the meantime, here’s a simple lineage query that can be applied against the Red database and serves as a primer. By examining the tables and logic used by the query, WhereScape’s open approach to metadata management quickly becomes apparent. This script can also serve as the foundation to some automated end-to-end data testing!
SELECT * /*Load tables*/
FROM ( SELECT
CASE WHEN ws_load_col.lc_src_table = ws_load_tab.lt_table_name THEN NULL ELSE ws_load_col.lc_src_table END AS lc_src_table,
ws_load_col.lc_src_column,
ws_load_tab.lt_table_name,
ws_load_col.lc_col_name,
CASE WHEN PATINDEX('%DataWarehouse;%',ws_dbc_connect.dc_attributes) =
0 THEN 'Y' ELSE 'N' END AS rb_remote_flag
FROM ws_load_tab
INNER JOIN ws_load_col
ON ws_load_tab.lt_obj_key = ws_load_col.lc_obj_key
INNER JOIN ws_dbc_connect
ON ws_load_tab.lt_connect_key = ws_dbc_connect.dc_obj_key
WHERE lt_table_name <> lc_src_table
) ld
/*Stage tables*/
INNER JOIN (
SELECT
CASE WHEN sc_src_table = st_table_name THEN NULL ELSE sc_src_table END
AS st_src_table,
ws_stage_col.sc_src_column,
ws_stage_tab.st_table_name,
ws_stage_col.sc_col_name
FROM ws_stage_tab
INNER JOIN ws_stage_col
ON ws_stage_tab.st_obj_key = ws_stage_col.sc_obj_key
WHERE ISNULL(ws_stage_col.sc_join_flag,'N') <> 'Y'
AND ws_stage_tab.st_table_name <> ws_stage_col.sc_src_table
) stg
ON stg.st_src_table = ld.lt_table_name
AND stg.sc_src_column = ld.lc_col_name
INNER JOIN (
/*Single source DV Objects*/
SELECT
ws_normal_col.nc_src_table,
ws_normal_col.nc_src_column,
ws_normal_tab.nt_table_name,
ws_normal_col.nc_col_name
FROM dbo.ws_normal_col
INNER JOIN ws_normal_tab
ON ws_normal_tab.nt_obj_key = ws_normal_col.nc_obj_key
WHERE nc_src_table IS NOT NULL
AND nc_src_table <> ''
AND nc_src_column IS NOT NULL
/*Multi-source DV mappings*/
UNION SELECT
ws_source_mapping_col.smc_src_table,
ws_source_mapping_col.smc_src_column,
ws_normal_tab.nt_table_name,
ws_normal_col.nc_col_name
FROM ws_source_mapping_col
INNER JOIN ws_source_mapping_tab
ON ws_source_mapping_col.smc_source_mapping_key = ws_source_mapping_tab.smt_source_mapping_key
INNER JOIN ws_normal_tab
ON ws_normal_tab.nt_obj_key =
ws_source_mapping_tab.smt_parent_obj_key
INNER JOIN ws_normal_col ws_normal_col
ON ws_normal_col.nc_col_key = ws_source_mapping_col.smc_parent_col_key
) dvobj
ON dvobj.nc_src_table = stg.st_table_name
AND dvobj.nc_src_column = stg.sc_col_name
WHERE ld.lc_col_name NOT LIKE 'dss%'
ORDER BY nc_src_table, lc_src_column
Recent Comments