BI4ALL-006-New Documentation Technique Demo

0
1212

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:


Previous articleBI4ALL-05 – New Documentation Technique For BI4ALL
Next articleBI4ALL-007-Customer Dimension Associations
Peter Nolan is one of the worlds leading thought leaders in Business Intelligence. Across his 29+ years in BI Peter has consistently invented new and innovative ways of designing and building data warehouses. SeETL now stands alone as the worlds most cost effective data warehouse development tool.

LEAVE A REPLY

Please enter your comment!
Please enter your name here