How to generate a Data Model or Data Dictionary

Description

Environment

Campaign Classic v7

 


Issue/Symptoms
This document shows two ways of generating a data model or data dictionary of your Adobe Campaign database.

Resolution

Adobe Campaign contains hundreds of tables. This document presents two different ways to generate schema documentation:

By generating an Open Office document (readable in Microsoft Word), or
By generating an HTML web page.

Method 1: Creating an OpenOffice document

In the Adobe Campaign’s datakit\nms\fra\package\optional directory, there is a package called dbbBuilder.xml. This package file contains a JavaScript entity that generates an Open Office document detailing the database schema of the Adobe Campaign instance.


Create a JavaScript file


Using the Campaign Console Explorer view, write a simple JavaScript utility in Administration Configuration JavaScript codes, like in the example below.

Change the strOutputFile value to add the destination directory where you would like to create the Open Office document. For example: /tmp/database-description.odt.


File example: cus:build-dbd.js

/* * build-dbd.js/ var
    strXslt = "nl:dbdToOdt.xsl"
    var strTemplate = installDirectory( "datakit/nl/dbd-template.odt" )
    var strOutputFile = "database-description.odt"
    var sql = xtk.sqlSchema.create() sql.Generate()
    var strDbXml = db = sql.toXML().toXMLString()
    var strOdt = applyXsl(strXslt, strDbXml)
    strOdt = strOdt.replace(/\?xml .*\?\r?\n/, '\r\n' )
    var mbContent = new MemoryBuffer mbContent.fromString(strOdt, "utf-8" )
    var zip = new ZipFile(strTemplate)
    zip.open() zip.setEntry( "content.xml" , mbContent) zip.save(strOutputFile)

 

Import dbdBuilder.xml


The dbdBuilder.xml package can be imported by executing the following command in the directory where the package is stored. Replace instance_name by the actual name of the Adobe Campaign instance.

nlserver package - import :dbdBuilder.xml -instance:instance_name


Package import example for Debian

neolane@debian: /nl6/datakit/nms/fra/package/optional $ cd
    /nl6/datakit/nms/fra/package/optionalneolane@debian:/nl6/datakit/nms/fra/package/optional$
    nlserverpackage -import:dbdBuilder.xml
    -instance:10:46:09
    Application serverfor Adobe Campaign Version 6.1.1(build 8537) of 04/16/201410:46:09
    Starting 1 connection(s) on pool'default vmcloud'(PostgreSQL, server='localhost',
    login='user:password')10:46:09 Importing package'/tmp/dbdBuilder.xml'...10:46:11
    Enumerating thefile entities...10:46:11
    Writing entitiesin the database...10:46:11
    Saving entities oftype 'xtk:xslt'...10:46:12
    Saving entities oftype 'xtk:javascript'...10:46:12
    Installation of packages successful.

Package import example for Windows

C: cd "\Program Files (x86)\Adobe\Adobe Campaign v6\bin"
    C:\Program Files (x86)\Adobe\Adobe Campaign v6\binnlserver.exe
    package - import : ..\datakit\nms\fra\package\optional\dbdBuilder.xml
    -instance: 13:52:57 Application server for Adobe Campaign Version 6.1.1 (build 8622) of 01 /19/2015 13:52:57
    Starting 1 connection(s) on pool 'default ' (PostgreSQL, server= 'localhost' , login= 'user:password' ) 13:52:57
    Importing package 'C:\Program Files (x86)\Adobe\Adobe Campaign v6\bin\..\datakit\nms\fra\package\optional\dbdBuilder.xml'...
    13:52:59 Enumerating the file entities...
    13:52:59 Writing entities in the database...
    13:52:59 Saving entities of type 'xtk:javascript' ...
    13:52:59 Installation of packages successful.

Generate the database description
To generate the database description, you can execute the cus:build-dbd.js JavaScript entity with the nlserver module using a command line, as shown below:

neolane@debian: /tmp $ nlserver javascript -instance: cus:build-dbd.js 05:06:12
    Application server for Adobe Campaign Version 6.1.1 (build 8537) of 04 /16/2014
    05:06:12 Starting 1 connection(s) on pool
    'default vmcloud' (PostgreSQL, server= 'localhost' , login= 'user:vmcloud' )
    05:06:12 Executing JavaScript from entity of identifier 'xs:build-dbd.js' ...
    05:06:12 Generating the database structure...
    05:06:12 Element '0' unknown (see definition of schema 'Recipient delivery logs (nms:broadLogRcp)').
    (iRc=16384)
    05:06:12 Element '0' unknown (see definition of schema 'Visitor delivery logs (nms:broadLogVisitor)').
    (iRc=16384) 05:06:12 Element '0' unknown (see definition of schema 'Recipient delivery logs (nms:broadLogRcp)').
    (iRc=16384) 05:06:12 Element '0' unknown (see definition of schema 'Visitor delivery logs (nms:broadLogVisitor)').
    (iRc=16384) 05:06:13 Generating the ODT XML... 05:06:14 Saving the zip file ...
    05:06:14 File saved as 'database-description.odt

An OpenOffice document named 'database-description.odt' is generated in the directory you specified in build-dbd.js. It contains the description of the Adobe Campaign tables. This document can be edited with Microsoft Word or OpenOffice, and can be converted to PDF. Data model file example
This document shows the database tables index, the SQL fields and keys, and the list of schema links.
 Method 2: Create a Web Page Using xsltproc
This alternative approach is based on the Linux xsltproc command line tool. In this scenario, you export the schema by creating an XML document, then transform the XML into HTML using an XSLT file. The output file is viewable in a browser.

For this method, you need:

• A Linux or Macintosh environment to run xsltproc, which is a standard Macintosh Mac OS X command line utility.
• The attached files, database.xsl, and styles.xml (see attachments.zip).

Note:

There is a Windows equivalent, msxsl.exe, which can work fine on that platform, but it has not been tested.

Now msxsl.exe is a part of MS Core XML Services (MSXML).

The MS Core XML Services (MSXML) download can be found in the Microsoft Update Catalog.

DOWNLOAD
Get file 
database.xsl and styles.xml
 
 

1. Download the two attached files database.xsl and styles.xml into a working directory on Linux or Mac. At the top of the XSL there are two variables you can adjust:
   xsl:variable name="unicode"false/xsl:variable
   xsl:variable name="engine"postgresql/xsl:variable
   The supported values for engine are: postgresql, mssql, and oracle. Set the engine variable to the value that corresponds to your marketing instance database.
2. From the Campaign Console, go to Administration Configuration Package Management Package definitions, and create a package.xml file that contains the data schemas (xtk:schema).
3. Export the package, then copy the file to your Linux or Mac working directory.
4. Process the package.xml file by running the following command in the working directory using XSLT: xsltproc database.xsl package.xml database.html.

The document generated is not perfect:

• Some join conditions on links cannot be documented.
• When an enumeration is referenced in a schema but defined in another schema which is not in the package, the enumeration is not documented.