This video is best viewed at 1080.
Best Regards.
Peter.
This is the code needed to transfer documentation from the SeETL Dictionary to the Meta5 Workstation Tools Data Dictionary.
--- This SQL Updates the WTDD in Meta5 with the data from the SeETL Data Models Documentation Tables.
--- Date: 2020-01-01.
---
---
update bi4all3000_m5.dbo.wt_tables
set printname = substring ( view_short_description , 1 , 128 )
from bi4all3000.dbo.ctl_dw_views_doc_03
where 1=1
and pk_spreadsheet_name = ‘BI4ALL Models All.xml’
and pk_spreadsheet_version = ‘V3.01.01’
and tablename = view_owner + ‘.’ + view_name
and pk_language_number = 1
;
update bi4all3000_m5.dbo.wt_tables
set description = substring ( view_long_description , 1 , 255 )
from bi4all3000.dbo.ctl_dw_views_doc_04
where 1=1
and pk_spreadsheet_name = ‘BI4ALL Models All.xml’
and pk_spreadsheet_version = ‘V3.01.01’
and tablename = view_owner + ‘.’ + view_name
and pk_language_number = 1
;
update bi4all3000_m5.dbo.wt_tables
set tabletype = 1
where 1=1
and tablename like ‘dbo.vf%’
and not (tabletype = 1)
;
update bi4all3000_m5.dbo.wt_tables
set tabletype = 2
where 1=1
and tablename like ‘dbo.vm%’
and not (tabletype = 2)
;
update bi4all3000_m5.dbo.wt_columns
set keyusage = 1
where
(
substring (wt_columns.columnname,1,3) = ‘dk_’
or substring (wt_columns.columnname,1,3) = ‘pk_’
)
;
update bi4all3000_m5.dbo.wt_columns
set
printname = ‘<-‘ + substring (wt_tables.printname, 1 , 128) ,description = ‘This field contains a pointer to the dimension/fact table called: ‘ + substring (wt_tables.printname, 1 , 255) from bi4all3000_m5.dbo.wt_tables wt_tables where 1=1 and substring (wt_columns.columnname,1,3) = ‘dk_’ and substring (wt_columns.columnname,4,99) = substring(wt_tables.tablename,5,99) ; update bi4all3000_m5.dbo.wt_columns set printname = ‘->’ + substring (wt_tables.printname, 1 , 128)
,description = ‘This field contains a pointer to THIS table called: ‘ + substring (wt_tables.printname, 1 , 255)
from bi4all3000_m5.dbo.wt_tables wt_tables
where 1=1
and substring (wt_columns.columnname,1,3) = ‘pk_’
and substring (wt_columns.columnname,4,99) = substring(wt_tables.tablename,5,99)
;
update bi4all3000_m5.dbo.wt_columns
set
printname = substring (view_column_name_sdesc, 1 , 128)
,description = substring (view_column_name_ldesc, 1 , 255)
from bi4all3000.dbo.ctl_dw_views_doc_05
where 1=1
and pk_spreadsheet_name = ‘BI4ALL Models All.xml’
and pk_spreadsheet_version = ‘V3.01.01’
and tablename = view_owner + ‘.’ + view_name
and columnname = view_column_name
and pk_language_number = 1
and not (substring (wt_columns.columnname,1,3) = ‘pk_’)
and not (substring (wt_columns.columnname,1,3) = ‘dk_’)
–and tablename = ‘dbo.vf_ap_allocation_to_gl_txn’
;
delete from bi4all3000_m5.dbo.wt_columns
where
columnname in
(
‘audit_timestamp_01′
,’batch_number’
,’dim_char_ky_fld’
,’file_cycle_number’
,’level_col’
,’partitioning_column’
,’partitioning_column_part_01′
,’partitioning_column_part_02′
,’ss_number’
,’table_number’
,’view_number’
,’dummy_join_col’
)
;
delete from bi4all3000_m5.dbo.wt_djoins
where table1 like ‘dbo.vm%’ or table1 like ‘dbo.vf%’
;
insert into bi4all3000_m5.dbo.wt_djoins
(
table1
,column1
,table2
,column2
,jointype
,joinop
,inout1
)
select
Left_Table_Owner + ‘.’ + left_table
,left_table_column_name
,right_table_owner + ‘.’ + right_table
,right_table_column_name
,1
,0
,1
from bi4all3000.dbo.ctl_data_model_joins
where 1=1
and pk_spreadsheet_name = ‘BIDAModels Joins.xml’
and pk_spreadsheet_version = ‘V3.0.01’
;
Best Regards
Peter
