Understanding Stratum.Server

Published by

As the persoStratum Under the Coversn responsible for helpline support here at Silvon I’ve decided to try my hand at blogging as a vehicle for providing information to current as well as future users and administrators of Stratum. As we began to discuss this idea here internally, we quickly realized there’d be no shortage of topics. So to start, we’ve decided to begin with a discussion of the various products and technologies that make up Stratum.  A clear understanding of the Stratum architecture will make it easier for me to delve into the details of the many additional topics we have on our list.

Stratum is essentially composed of four main integrated products: Stratum.Server, Stratum.Planner, Stratum.Connector and Stratum.Viewer. These integrated products can be deployed across two different technology stacks as we will see further along in our discussions. I will focus on Stratum.Server today and the other three products in subsequent blog entries.

What is Stratum.Server?

Stratum.Server is Silvon’s relational database design that incorporates predefined dimensions (like customers, products, sales channel, etc.), attributes about each of the dimensions (like customer address, phone number, URL, etc.), and categories of data and measures/values within each category (like sales $$’s, units, budget $$’s units etc.). We commonly refer to dimensions/attributes as master file data and categories/measures as transaction data. This design incorporates years of experience in delivering business intelligence (BI) solutions and provides a starting point for customer implementations while allowing for customization.

Stratum.Server can reside either in DB2 for the IBMi or in SQL Server from Microsoft. Through the years the industry commonly referred to this type of data store as a business intelligence data warehouse, data mart or data repository. Suffice it to say, Stratum.Server fits the description of any or all of those terms and serves as the engine for everything we do with Stratum.

Master file and transaction data for Stratum.Server are most commonly sourced from an ERP system. The process for loading this source data into Stratum.Server differs slightly depending on whether Statum.Server is on the IBMi or Windows.

Master file data

Stratum generates a custom table called MFIMPORT for loading master file data into Stratum.Server. The MFIMPORT table contains the definition of the dimensions and attributes for Stratum including any customization of our base design that has been done as part of each customer’s business intelligence implementation.

  • MFIMPORT on IBMi
    The MFIMPORT table is most often populated from an ERP system on a nightly basis. It is commonly an RPG or set of RPG programs created either by Silvon or the customer and imbedded in a CL program that runs at night once the ERP system has finished processing the day’s transactions. When the process of populating the MFIMPORT table is complete Stratum will execute one of two commands to move the master file data from the MFIMPORT into Stratum.Server. The EXTMST command deletes all existing master file data in Statum.Server and reloads data for all dimensions from the MFIMPORT table. Only a few customers continue to use EXTMST as it was superseded by a new more flexible command, DAMASTLOAD. The DAMASTLOAD command can be configured to load master file data for only certain dimensions each night, thereby eliminating the need to reload master data that has not changed.
  • MFIMPORT on Windows
    The same MFIMPORT table described above for the IBMi exists on the Windows server. In place of the RPG program(s) Silvon provides customizable DTS or SSIS scripts for use in extracting master file data from a source system and populating the MFIMPORT table. The CREATE MFIMPORT DATA script is used to extract master file data from the source system. The LOAD MFIMPORT DATA script is used to load the data extracted by the CREATE MFIMPORT DATA script into the MFIMPORT table. These scripts are usually executed as part of a .BAT file that runs at night once the ERP system has finished processing the day’s transactions. Once the MFIMPORT table has been updated the GIMASTLOAD command is used to load the master file data from the MFIMPORT table into Stratum.Server. The GIMASTLOAD command, similar to the DAMASTLOAD command on the IBMi, can be configured to load master file data for only certain dimensions each night — thereby eliminating the need to reload master data that has not changed.

Transaction data

Similar to the custom MFIMPORT table for loading master file data, Stratum also generates custom table(s) called XXIMPORT and EXTPNDXX for transaction data where XX represents the category of data. These tables contain the definition of each category and the measures within that category — including any customization of our base design that has been done as part of each customer’s BI deployment.

  • XXIMPORT and EXTPNDXX on IBMi
    Like the MFIMPORT, the XXIMPORT table(s) is usually populated with detail transaction records from a source ERP system. It is commonly an RPG or set of RPG programs created either by Silvon or the customer and imbedded in a CL program that runs at night once the ERP system has finished processing the day’s transactions. When the process of populating the XXIMPORT table(s) is complete, Stratum executes the IMPDATA command. The IMPDATA command summarizes the XXIMPORT data by dimension value and time period while copying the data into the EXTPNDXX table(s). The DALOAD command then updates or inserts the data from the EXTPNDXX table(s) into the appropriate data summary level (DSL) tables in Stratum.Server.
  • XXIMPORT and EXTPNDXX on Windows
    The same XXIMPORT table(s) described above for the IBMi exist on the Windows server. In place of the RPG program(s) Silvon provides customizable DTS or SSIS scripts for use in extracting transaction data from a source system and populating the XXIMPORT table(s). The CREATE XXDATA script is used to extract transaction data from the source system. The LOAD XXIMPORT DATA script is used to load the data extracted by the CREATE XXDATA script into the XXIMPORT table(s). These scripts are usually executed as part of a .BAT file that runs at night once the ERP system has finished processing the day’s transactions. Once the XXIMPORT table(s) has been updated the GISLSDRV command summarizes the XXIMPORT data by dimension value and time period into either a temporary .txt file or an EXTPNDXX table(s).  From there, the command updates or inserts the data either into the appropriate data summary level (DSL) tables in Stratum.Server. Initial implementations of Stratum only had the option for GISLDRV to create the temporary .txt file. A newer version of GISLSDRV provides enhanced functionality and improved performance of summarizing the data and updating Stratum.Server from the EXTPNDXX table. We no longer use the.txt file format when implementing new customers and most existing customers who were using the .txt format have since switched to the EXTPNDXX format.

My next posting will discuss Stratum.Planner and how it is used to access the data in Stratum.Server.

Happy reading …. and be sure to share any comments (or ideas for future blog posts) with me!

Categorized in:

This post was written by Steve Morgan