Understanding Stratum.Connector

Published by

Stratum Under CoversThis is the third in a series of blog entries where I will be discussing the four main integrated components that make up Stratum. My first two blogs covered Stratum.Server and Stratum.Planner. Today I will discuss Stratum.Connector.  And in my next post, I’ll provide an overview of Stratum.Viewer.

Stratum.Connector is the Windows server-based middleware component that connects the Stratum Data Base (DB) to Microsoft Analysis Services. It allows for the selection of any or all of the Stratum DB dimensions/attributes, categories, measures and time periods which are then used by the Connector to automatically create a Microsoft Analysis Services OLAP cube which serves as the data store for analysis with Stratum.Viewer.

Connectivity and processing on the IBMi

The connections to the Stratum DB are determined by the version of Microsoft SQL Server where Stratum.Connector resides. The Standard Edition of SQL Server utilizes the IBM .NET Provider and the IBM OLE DB Provider, both of which are part of IBMi Access for Windows. The Enterprise Edition of SQL Server utilizes the IBM .NET Provider and the Microsoft OLE DB Provider for DB2, which is included with the Enterprise Edition of SQL Server. In both environments Stratum.Connector uses the .NET connection to retrieve the meta data and the OLE DB connection to process all the master file and detail transaction data going from the Stratum DB into the OLAP cube.

There are two Stratum.Server jobs that pre-process Stratum data to prepare it for processing by Stratum.Connector. DALOADOLAP is used to create our MASTERXX tables where XX represents the dimensions from 1-99 defined in the Stratum DB. The DALOADOLAP command creates a dimension value in the appropriate MASTERXX table for each detail transaction stored in the Stratum DB. This process ensures every detail transaction in the Stratum DB has a corresponding master record which is a requirement of Analysis Services. For every record in the MASTERXX tables the DACRTOMAST command creates a corresponding MASTXX table. The MASTXX tables contain the same dimension short description, long description and attributes/PUF’s as the STCSMF10, 11 & 35 tables in the Stratum DB.

The DALOADOLAP and DACRTOMAST, along with the Stratum.Connector .exe, are most commonly contained in a CL program on the IBMi. They are usually executed in sequence after successful completion of the nightly Stratum.Server DALOAD process. DALOADOLAP and DACRTOMAST execute on the IBMi whereas the Connector.exe runs on the Windows Server and is executed using the RUNRMTCMD from the IBMi. The Connector .exe retrieves the MASTXX tables and the corresponding detail transaction data from the Stratum DB along with other relevant meta data. This data is transferred to the Stratum.Connector server where it is then processed by Analysis Services to create the OLAP cube used by Stratum. Viewer.

Alternatively, the DALOADOLAP command can be configured to run in conjunction with the DALOAD command. In this scenario, each time DALOAD is executed the DALOADOLAP will add to the MASTERXX tables(s) any new dimension values it detects in the current set of detail transaction data being loaded to the Stratum DB from the XXImport table(s) by DALOAD.

Connectivity and processing on the Windows server

Stratum.Connector utilizes the same connections to the Stratum DB whether it is running on the Standard or Enterprise Edition of SQL Server. In both cases it uses the Microsoft .NET Framework Data Provider for SQL Server and the Microsoft SQL Server Native Client OLE DB Provider. In both environments the .NET connection is used to retrieve the meta data and the OLE DB connection to process all the master file and detail transaction data going from the Stratum DB into the OLAP cube.

There are two jobs that pre-process the Stratum data to prepare it for processing by Analysis Services. GILOADOLAP on the Windows Server is used to create our MASTERXX tables where XX represents the dimensions from 1-99 defined in the Stratum DB. The GILOADOLAP command creates a dimension value in the appropriate MASTERXX table for each detail transaction stored in the Stratum DB. This process ensures every detail transaction in the Stratum DB has a corresponding master record which is a requirement of Analysis Services. For every record in the MASTERXX tables the GICRTOMAST command creates a corresponding MASTXX table. The MASTXX tables contain the same dimension short description, long description and attributes/PUF’s as the STCSMF10, 11 & 35 tables in the Stratum DB.

The GILOADOLAP and GICRTOMAST, along with the Stratum.Connector .exe, are most commonly run in a .BAT file. They are usually executed sequentially after successful completion of the nightly Stratum.Server GISLSDRV process. Once GILOADOLAP and GICRTOMAST complete the Connecter.exe retrieves the MASTXX tables and the corresponding detail transaction data from the Stratum DB along with other relevant meta data which is used by Analysis Services to create the OLAP cube used by Stratum.Viewer.

Alternatively, the GILOADOLAP command can be configured to run in conjunction with the GISLSDRV executable. In this scenario, each time GISLSDRV is executed the GILOADOLAP will add to the MASTERXX tables(s) any new dimension values it detects in the current set of detail transaction data being loaded to the Stratum DB from the XXImport table(s) by GISLSDRV.

 

Categorized in:

This post was written by Steve Morgan