How to generate a Data Model or Data Dictionary

This article provides two ways of generating a data model or data dictionary of your Adobe Campaign database.

Description description

Environment

Adobe Campaign Classic v7

Issue/Symptoms

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

Resolution resolution

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

  1. By generating an Open Office document (readable in Microsoft Word), or
  2. 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.

A. 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)

B. 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.

C. 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.

database-description

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.
recommendation-more-help
3d58f420-19b5-47a0-a122-5c9dab55ec7f