Extracting SAP schema (meta data) using Business Object Data Service Designer

Working with SAP BO on data reconciliation, transferring into MS SQL and building a custom reports as anyone else I was struggling with cryptic German acronyms. In order to get some sense out from the data I have to get across also column description and in general schema data as column data types, constraints, etc both for further analysis and documentation. Regretfully BO DS Designer doesn't provide such a feature. However it is possible to export schema either in XMI 1.1 (albeit not complying to the schema or specification) or ERwin XML document. And that is enough for documenting and cleating a data dictionary. In this post I will outline the whole process and one of the first steps namely generating an HTML overview schema document. The chief reason I - generally a very reluctant blogger - am sharing my experience to demonstrate how efficiently could be used a diverse set of tools that in the first have nothing in common. They are Business Object Data Service Designer, MS XML for XSLT transformation, MS Visual Studio for programming and testing XSLT, JavaScript for interacting with MS XML ActiveX components, sqlite for collecting and storing from XML extracted data, Windows BAT for cluing it all together.

This is how the main all together gluing process_xmi.bat script:

@SET ECHO OFF REM Get file name w/o extesion SET FFN=%1 SET FN=%FFN:~0,-4% SET OUTPUT_DIR=outputIF NOT EXIST %OUTPUT_DIR% MKDIR %OUTPUT_DIR% SET DB=%OUTPUT_DIR%\model.sqlite REM Execute trasformations: REM - Generate an HTML report: cscript transform.js %1 xmi-html-summary.xslt %OUTPUT_DIR%\%FFN:~0,-4%.html cscript transform.js %1 xmi-html-summary2.xslt %OUTPUT_DIR%\%FFN:~0,-4%_2.html REM - Generat DML for model update cscript transform.js %1 xmi-sql.xslt %OUTPUT_DIR%\create_model.sql REM Load model data into DB model.sqlite: sqlite3 %DB% < %OUTPUT_DIR%\create_model.sql REM Extract table and column list in CSV format: sqlite3 -header -csv %DB% "SELECT * FROM meta$table;" > %OUTPUT_DIR%\%FN%_tables.csv sqlite3 -header -csv %DB% "SELECT c.*, t.schema, t.name AS table_name FROM meta$table AS t JOIN meta$column AS c ON c.table_id = t.id;"  > %OUTPUT_DIR%\%FN%

The following flowchart will help to understand it better:

The source code of the project you can find at http://j.mp/bo-xmi-transformer