BI4ALL-006-New Documentation Technique Demo

0
88

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


IBI Downloads

IBI On You Tube


Carphone Warehouse Reference Video:


Comments

comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here