Moving MultiValue (MV) Data to a SQL Server Data Warehouse

The information contained in this guide outlines the steps required to move a MultiValue (MV) file to Microsoft SQL server using Kore's SQL Accelerator and Kourier products.

Often times, the initial SQL database being populated from the MultiValue database is considered a staging database and is not used for reporting. Instead, data from the Staging Database is moved via trigger or other means to a star schema database  which is then used to build OLAP (On-line Analytical Processing) cubes. This guide is focused on the process of populating the Staging Database (diagrammed below). It is common to produce both operational and analytical reporting directly from the staging database.

tip

WidgetTip

 Before getting started, your team should conduct a planning meeting to determine your specific strategy and conventions for the data warehouse project.

We suggest the following directory structure to organize the various files used in this process:

Assumptions made in the Guide

1. The Quick Start Workbench Configuration has been set up to reflect the installation's requirements.
2. The column names used in the Kourier export must match the column names in the target SQL table.
3. The name of the target SQL table is defined in the Kourier Export definition.
4. The name of the export file will follow the naming convention of:  SrcId_(7digitseqno)_sqltablename.export_mode
5. A DSN has been created that points to the ...\ODS\Inbox.

Defining Quick Start Workbench Defaults

Before starting to move data from the MV environment, the Quick Start Workbench Configuration must be defined. This page may be accessed under the Administration menu.

Exporting a MV File to SQL Server - an overview

The process of moving data from each file in the nested relational model of a MultiValue database to the 1st normal form model of SQL consists of 8 primary steps:

1. Analyze the MultiValue file by generating a Schema.
2. Review the generated Schema, and modify it as needed to reflect the actual data structures
3. Move the final Schema back into Kourier
4. Generate Kourier Exports and Services from the Schema
5. Verify and edit the Exports as necessary
6. Generate SQL Data Definition Language (DDL) statements to create tables in SQL Server which will follow the defined Schema, and create the tables using this DDL
7. Generate SSIS packages which will process the Kourier Exports and load them to SQL Server
8. Run the generated Services

Once these steps have been performed, the data will be available in SQL Server for reporting or analysis.

Using the Quick Start Workbench to Generate and edit a Schema

1. In most MV systems, the dictionaries for a file do not accurately reflect the structure of the data.  Use the Quick Start Workbench to analyze the data in a file and compare it to the data defining dictionaries (D-type) for the file. This generates a Schema, which may be checked out in the form of an Excel workbook which should be saved in the ...\ODS\Schema folder.
2. Review and edit the resulting Schema in Microsoft Excel using this procedure.
3. Once the Schema has been edited and saved, use the Quick Start Workbench to check in the Schema.

Generating Exports, Services and SSIS Packages

1. In the Quick Start Workbench with the source data file selected, click the Generate Exports button. This will generate at one Export for all single-valued fields in the schema, and one Export for each group of associated multi-valued fields. Any multi-valued field with no association will generate its own separate Export. One Kourier Service will also be generated. This Service will run all of the Exports generated from the Schema.
2. Review each export created by the Quick Start Workbench. The overall goal of each export is to take advantage of the power of SQL without carrying along MV database conventions that work against you in the SQL environment.
   1. Delete any unwanted fields from each export.
   2. Review the column headings.
   3. Make sure the selection criteria for each export contains valid dictionary references.
   4. If more than one MV account will be sending data to the same data warehouse, verify that a line item for a Source identifier was added at the beginning of each export. 
   5. If a field (especially the item-id) for a MV file is a compound field, verify that the field was properly split in the Schema.  Verify the conversion and format fields which set data types, data lengths. Verify that primary keys are properly set.
   6. Eliminate any child export that solely contains cross-reference information.  Use the SQL join capability to get at this information.
   7. For multi-valued comment or text fields, delete the generated export and use the @TEXT conversion code to convert the multi-valued text to a single string and add that field to the parent export.
   8. For multi-valued fields that contain a fixed number of multi-values (i.e. 3 address lines) considering adding these fields to the parent export instead of treating them as a child export. Use the conversion, format fields to set data types, data lengths.
3. Review the Service created by the Quick Start Workbench.
   • Remove any export deleted in a prior step.
   • Choose a target DSN.
   • Verify that the Autorun SSIS Package checkbox is set if you want the SSIS packages to be run after each Service run
4. While in the Service, use the DDL button to generate a SQL script to create the SQL tables based upon the Exports called by the Service.  Paste that script into the SQL Query tool of SQL Server Management Studio, verify that the correct database is selected, and execute the script. Verify that the SQL tables have been properly created.
5. While in the Service, use the Generate Package button to access the SSIS configuration screen.  Review the parameters in this screen and click the Generate button to create the SSIS package. NOTE: The first time SSIS packages are generated for a new Integration, The Configuration File must be specified and edited.

Moving data to SQL using generated Exports, Services and SSIS Packages

1. While in the service, run the service in full refresh mode to extract the data from U2 and move it to the ...\ODS\Inbox.
2. If the Autorun SSIS Packages option is not set for the Service, run the SSIS job to load the exported data.
3. Resolve any errors which may be reported by the SSIS packages. The most common errors are:

   • Duplicate primary keys caused by the fact that MV databases are case-sensitive and SQL tables (by default) is case-insensitive.

   • Data truncation caused by the data in MV database exceeding the defined length of a column in the SQL table.

   • Wrong data types.

4. Repeat the above steps on each MV file you plan on moving to SQL Server.

Create the SSIS Master Package

note

WidgetNote

 This step is required only if the Autorun SSIS Packages option is not set in the Service definitions.

Once you have created and tested all the individual SSIS packages create one master SSID package that runs all the individual packages.  This allows you to schedule all jobs as a single unit.  For a master package, make sure to limit the maximum number of tasks executed in parallel to 1.  This setting can be found on the General tab in the package properties.