Setup a Metadata Database - SuperWEB2

You can add metadata about databases, tables, and fields within tables to SuperWEB2. Once the metadata is set up, end users can click the links in the SuperWEB2 interface to read the additional information about the data they are working with.

Metadata links are available in SuperWEB2's Table View mode, for any fields that have been added to the table. The links are denoted by the

This page describes how to set up metadata for SuperWEB2 using a database repository.

It is also possible to add metadata using static HTML pages, instead of using a database. See Setup Static Metadata Pages - SuperWEB2 for more information.

To set up a metadata database you need to complete the following steps:

Step 1 - Create the Database

The first step is to create a database for storing the metadata information.

You do not need to create any tables in the database; these will be created by the scripts you run in the next step. Simply create a new empty database in your preferred relational database system.

This example will use Microsoft SQL Server. We have created a new empty database called Metadata:

You will also need a JDBC driver so that SuperSTAR can connect to your database. Download a JDBC driver for your chosen relational database system.

Step 2 - Edit and Run the Configuration Scripts

SuperSTAR is supplied with some configuration files for setting up metadata. You need to edit these files to suit your system.

Edit BuildMetadataTemplate.bat

The first file you need to modify is a windows batch file called BuildMetadataTemplate.bat. In a default installation, this file is located in C:\ProgramData\STR\SuperADMIN\MetaData\MetaDataUtilities

This is a script that will connect to both SuperSTAR and your new metadata database. It copies the structure of the SuperSTAR database to your metadata database, ready for you to start populating it with your metadata.

Open BuildMetadataTemplate.bat in a text editor.

Make a backup copy of this file before making any changes.

At the top of the file there are a number of lines that define various parameters the script will use. You need to modify the following lines to provide the appropriate details for your system:

SET DB_DRIVER_CLASS=

SET DB_DRIVER_CLASS="com.microsoft.sqlserver.jdbc.SQLServerDriver"

SET DB_DRIVER_LOCATION=

SET DB_DRIVER_LOCATION="C:\drivers\SQLServer\sqljdbc_4.0\sqljdbc4.jar"

SET DB_URL=

SET DB_URL="jdbc:sqlserver://MYSERVER;databaseName=Metadata;user=mydbuser;password=myuserpassword;"

SET REPOSITORY=

SET REPOSITORY=metadatadbid

You can also make the following changes if you wish, or just leave these parameters set to the default values:

SET VALUESET=20

SET OVERWRITE=true

SET EXPORT_VALUESET=true

When you have finished, save your changes to BuildMetadataTemplate.bat.

Edit databases.txt

This is a text file that specifies which SuperSTAR databases you want to create metadata for. In a default installation, this file is located in C:\ProgramData\STR\SuperADMIN\MetaData\MetaDataUtilities

Edit the file so that each line contains the details of a database you want to create metadata for. You can either create this file manually or use the createdatabaselist command in SuperADMIN.

The database list file must use the following format:

<database_id>|<display_name>|<full_path_to_SXV4>

Where:

• <database_id> is the ID of the database in the SuperSTAR catalogue.
• <display_name> is the database display name.
• <full_path_to_SXV4> is the full path to the .sxv4 file that contains the database but without the .sxv4 file extension.

For example, the shipped databases.txt file is as follows. This would create metadata tables and columns for the sample People and Retail Banking databases:

people|people|C:\ProgramData\STR\SuperSERVER SA\databases\People
bank|bank|C:\ProgramData\STR\SuperSERVER SA\databases\RetailBanking

When you have finished editing databases.txt, save the file.

You are recommended to save this file in the same location as the standard shipped file. If for any reason you want to save the file to a different location, you will have to find the line SET DB_FILE_LIST="databases.txt" in BuildMetadataTemplate.bat and update it to point to your new filename and location.

Edit metacolumns.txt

This is a text file that specifies the columns that will be created for metadata in the repository. In a default installation, this file is located in C:\ProgramData\STR\SuperADMIN\MetaData\MetaDataUtilities

Each line in the file defines a column to create in the metadata database, in the following format:

<column_name> <language> <type> <length>

Where:

• <column_name> is the name of the column to create. You can chose whatever name you like for the column; it will not be displayed to the end user in SuperWEB2, but you will see this in the RDBMS when you are populating your metadata, so you should choose something that will make it easy to remember which column is which (e.g., the default column name, en_desc is a good choice of column name to represent the English language description).
• <language> is a language code (e.g. en, fr, de, etc).
• <type> is the column type, either name or desc. (The name column type is not currently used by SuperWEB2).
• <length> is the maximum length of the column in characters.

For example, the following sample shows how to create columns to store English and French metadata with up to 250 characters in the name and 500 characters in the description:

en_name en name 250
en_desc en desc 500
fr_name fr name 250
fr_desc fr desc 500

When you have finished editing metacolumns.txt, save the file.

You are recommended to save this file in the same location as the standard shipped file. If for any reason you want to save the file to a different location, you will have to find the line SET META_COLUMNS="metacolumns.txt" in BuildMetadataTemplate.bat and update it to point to your new filename and location.

Run BuildMetadataTemplate.bat

Once you have finished editing all three files, you run the configuration batch file. It will use the information from your SuperSTAR databases to populate your metadata database:

1. Open a Windows Command Prompt and change to the directory where the batch file is located.
2. Run BuildMetadataTemplate.bat
3. Wait for the file to complete and advise that it has "Finished populating RDBMS":

2013-07-29 14:28:40,596 [main] INFO  au.com.str.metautilities.builders.BuildMetadataTemplate - Finished populating RDBMS. It took 3 seconds

Check your metadata database to confirm that the script has created the database tables:

There should be one new table for each SuperSERVER classification table.

Step 3 - Configure SuperWEB2 to Connect to the Database

The next step is to configure SuperWEB2 to connect to the metadata database.

1. Copy the JDBC driver (.jar file) for your relational database to the <tomcat_home>\lib directory.
2. Open <tomcat_home>\webapps\webapi\WEB-INF\faces-config.xml in a text editor.

3. Locate the following section:

   XML

   <managed-property>
       <property-name>metaInfoBaseURL</property-name>
       <value>/webapi/help/not-configured.jsp</value>
   </managed-property>

4. Change the value to /webapi/help/sa-metadata.jsp

   XML

   <managed-property>
       <property-name>metaInfoBaseURL</property-name>
       <value>/webapi/help/sa-metadata.jsp</value>
   </managed-property>

5. Save your changes to faces-config.xml.

6. Open <tomcat_home>\conf\context.xml in a text editor.

7. Add the following section (before the closing </Context> tag).

   XML

       <Resource name="ds/superstar-metadata" auth="Container"
           type="javax.sql.DataSource"
           maxActive="100"
           maxIdle="30"
           maxWait="10000"
           username="mydbuser"
           password="myuserpassword"
           scope="Shareable" 
           driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
           url="jdbc:sqlserver://SERVERNAME;databaseName=Metadata;"/>
   

   Replace the database and user details with the appropriate values for your system:

   Parameter	Set This To...
   username	The user account for connecting to the database.
   password	The password for connecting to the database.
   driverClassName	The database driver to use to connect to your database (see the Driver Class column in the table of JDBC drivers above for the appropriate value to use).
   url	The connection string for connecting to the database. The following are some examples for different database types: Database Connection String MySQL jdbc:mysql://SERVENAME:3306/Metadata Oracle jdbc:oracle:thin:@SERVERNAME:1521:Metadata PostgreSQL jdbc:postgresql://SERVERNAME:5432/Metadata SQL Server jdbc:sqlserver://SERVERNAME;databaseName=Metadata Sybase jdbc:jtds:sybase://SERVERNAME:7100/Metadata;useCursors=true Teradata jdbc:teradata://SERVERNAME/DATABASE=Metadata Make sure you replace SERVERNAME with the name of your database server and Metadata with the name of the database you created in the first step. Some of these strings contain a port number (e.g. 3306, 1521). The examples shown here are the default port numbers for these database servers. If your instance is using a different port number then you will need to substitute the appropriate value for your system.

8. Save your changes to context.xml.
9. Open <tomcat_home>\webapps\webapi\WEB-INF\web.xml in a text editor.

10. Locate the following section:

    XML

    <context-param>
        <description>SQL to be executed against a field meta data request</description>
        <param-name>metadata_crossTabField_SQL</param-name>
        <param-value>SELECT EN_DESC FROM COL_{db}_{fact} WHERE SS_CODE ='{field}'</param-value>
    </context-param>
    <context-param>
        <description>SQL to be executed against a summation meta data request</description>
        <param-name>metadata_count_SQL</param-name>
        <param-value>SELECT EN_DESC FROM FACT_{db}</param-value>
    </context-param>
    <context-param>
        <description>SQL to be executed against a database meta data request</description>
        <param-name>metadata_database_SQL</param-name>
        <param-value>SELECT EN_DESC FROM DB_DOMAIN WHERE SS_CODE ='{db}'</param-value>
    </context-param>

    This section contains the SQL statements that SuperWEB2 will use to retrieve your metadata from the database. Make sure that the column name (EN_DESC in the above example) matches the name of the column that contains your descriptions in your metadata database.

    For example, if you are creating metadata descriptions in a language other than English, you might need to change this to the corresponding column name (e.g., FR_DESC, ES_DESC, etc.)

11. Save your changes to web.xml, if any.

12. Restart Tomcat or the SuperWEB2 service.

Step 4 - Verify Metadata

To verify that the metadata database is set up correctly:

1. Login to SuperWEB2 and create a table.
2. View the metadata for one of the fields in the table. For example:
   

   

3. Go to the metadata database in your relational database system and edit the metadata description for this field. For example:
   

   

4. View the metadata in SuperWEB2 again and check that you can see your updated description. For example:
   

   

Now that you have verified that SuperWEB2 is accessing the metadata from your database, you can proceed to add appropriate descriptions for all the fields to your database.