Metadata is essential to well documented ETL processes. Ideally, this metadata starts with the source system table and column descriptions and other system specific information. When this information is not available directly inside of Data Services, retrieving it manually can fill in the gaps and help your users get a complete map of how data arrives in the warehouse.
Generally, the lack of metadata is caused by the absence of a native driver to the source database. For example, using ODBC to access a DB2 source on an AS400, the ideal solutions is to use DB2 Connect, a native driver, but that option can be price prohibitive for some clients. Additionally, some third-party vendors only allow connectivity via ODBC.
How do you go about getting the metadata? Most databases have system tables that contain the data we are seeking. A quick Google search for your source database and platform should provide you the system table name(s) you need. For this version of DB2, the table is called SYSIBM.SQLCOLUMNS. Below is a data flow to copy SQLCOLUMNS to the ODS. Qry_Set_Desc just picks out the metadata columns we are interested in.
Then I use a second data flow to limit to just the tables I am interested in and output the results into a format that makes them easy to transport to our target database. In this case I have created a 4 column file, although you could get by with just the last one if your prefer.
To download PDF and Continue Reading…
About Ernie Phelps
Ernie has 15+ years of experience in EIM with client in many verticals, including aviation, construction, finance, and healthcare. He is also a certified SAP instructor. At Decision First Technologies, Ernie specialized in SAP Data Services