Notes About Data Conversion

January 7, 2025

General notes below explain some issues related to the Multidev MART (Multidev Assisted Record Translation) spreadsheet and tool (read from bottom dates up).
After the last staffing change, additional work has been done to address Multidev data conversion.
The general outline is this:
- MART is the best method to CREATE NEW RECORDS of any quantity and to ensure consistency with the data in Universe.
- Multidev UI’s can be used to CREATE NEW RECORDS, but they should only be records that do not exist in Universe and/or do not need to be created from Universe data, i.e., no “hand carry” of data between Universe and Multidev.
- MART relies on a large Excel spreadsheet (MART spreadsheet).
- Each tab in the spreadsheet corresponds to a table in Multildev.
- The spreadsheet is heavily macro driven to insure data integrity (required, not required; data types and field lengths) and constraints between tables (foreign key dependencies and links). It is not uncommon for a spreadsheet to take 3 minutes or more to open.
- A clean MART spreadsheet exists at: O:\MISGRP\Multidev\Data Conversion\Multidev Imports\Import Template 3.0 v2.xlsm
- However, due to the dependencies between tabs, it is best to work from a relatively filled out spreadsheet, such as the last one to pass all validations: O:\MISGRP\Multidev\Data Conversion\Multidev Imports\Import Template 3.0 v2 UPDATED 2024-12-19.xlsm

Data from Universe
- Extracts of data from Universe can be done using Perform (Universe utility), BASIC code (Universe) or Informer.
- What seems to have worked best is a combination of a Universe select statement used by a formatted Informer call/dump. See Multidev/Data Conversion tag in Informer.

The columns that are output as part of the Informer extract are built/curated to match the SUPPLIER tab in the MART spreadsheet.
The Informer extract should be run to a CSV or TAB file, opened in EXCEL (protect leading zeros if necessary) and then copied into the MART spreadsheet tab.
Dependencies
- Tabs that have already been loaded from the MART tool to Multidev (or tables built in Multidev UI) can be exported from Multidev (through Informer) to fill out tabs in the MART spreadsheet that may be needed by other tabs that are primary focus of the specific MART spreadsheet but need dependency validation to proceed.
- Example: Multidev MART Tool - MD Output GROUPCODE (MERCH DEPARTMENT)
- The Informer export can be saved to a CSV or TAB file, opened in EXCEL (protect leading zeros if necessary) and then copied into the MART spreadsheet tab.
After building/editing MART spreadsheet tabs, the tabs should be validated.

At this point in the project, we are only updating specific, individual tabs in the MART spreadsheet, but the entire spreadsheet should be validated to reduce errors.

There must be a C:\Multidev\CSV Files directory for this process to write files to.

The Destination Database string needs to be updated to:

It is not saved in the tool.

Click Connect button to connect to the database.
All files in the C:\multidev\CSV Files directory are shown.
Check Import columns for files to be processed
Check/uncheck Allow Insert or Allow Update columns as necessary.

Additional updates
- The MART tool tabs do not contain all of the information needed for data conversion.
- There are also tables that need to be updated that are not part of the MART process.
- ALSO, some, simple tables without table dependencies can be INSERTED using this tool.
- Once the base record, say SALESBRANCH has been built, we may need to add fields that are not part of the MART spreadsheet.
- Example: Multidev - SALESBRANCH (STR.MST) Supplemental #1
- This unload from Universe needs to be loaded into Multidev.
- Use 10.0.5.99 or 10.0.15.96 and the tool at:

Some updates directly related to MART are stored in POST MART Updates (data and scripts).
Other updates are split in Data and Scripts.
Build Updates have been abandoned as a process in favor of the above.
The tool to at
Select SV1020HO for the Database alias The import file should be stored in the Data directorY and needs to be selected from the tool.
Set the Delimiter value as appropriate.

Click Process

November 4, 2024

MD.SUB.CLASS was updated in Universe to associate with different MD.CLASS AFTER initial data was loaded.
All to be fixed if PCLASS, PSCLASS and INVENTORYMASTER to be replaced October 18, 2024
From prior entries here, note that:
- The MART tool process is required to add new records to tables IF the additions are not done through a Multidev UIs.
- Because of the frequency of new records being added to SLSP.MST, INV.MST and VEND.MST the MART tool needs to be used.
- It may be useful for other tables, like class and sub-class, to use the MART tool to limit time spent and data entry errors from dual entry.
Reconcilement of tables in Multidev to Universe
- Test records added to various tables in Multidev will be lost when the new production database is initialized.
- Foreign Key Matched
  - If the key to the Multidev record is the same as the key to the Universe record, a simple Informer query can select from Universe and using a link to Multidev show records that are found/not found.
  - Foreign key matched tables include:
    - SLSP.MST – EMPLOYEE
    - VEND.MST – SUPPLIER
    - MD.CLASS – PCLASS
    - MD.SUB.CLASS – PSUBC
  - See UT919 and Unload Universe Data to MART tool tabs to ADD to Multidev discussions below.
- Other files
  - INV.MST keys are NOT loaded directly into INVENTORYMASTER.
  - The reconciliation process involves unloading records from Multidev and using UT919 in Universe to compare the unloaded records to INV.MST.
  - UT919 also compares MD.CLASS/PCLASS and MD.SUB.CLASS/PSUBC.
  - This is useful since non-inventory SKUs in Universe are NOT being brought directly into Multidev and the class/sub-class structure is not consistent.
  - Looking at Multidev records that are in not in Universe for INVENTORYMASTER, PCLASS and PSUBC allows validation that all required non-inventory SKUs have been completed and are in the correct class/sub-class.
Reconciliation processes
- Compare Multidev to Universe
  - Export Multidev data for use with UT919.

Export files as .txt (tab delimited), do not change the file name (file names are hardcoded in UT919) and save to k:\misgrp (path hardcoded in UT919).
The result of UT919 is a listing for records that are missing from each side.
Unload Universe Data to MART Tool Spreadsheet Tabs

Except from Multidev MART Tool - MD Output INVENTORYMASTER UT919 Only the other unloads can be used to populate the related MART tool tabs:
Export to CSV, open the file in Excel (do not convert numeric values as text to numbers) and copy all by the header row into the MART tool spreadsheet tab.
Unload Universe Data to MART tool tabs to ADD to Multidev
- Informer extracts have been built to pull data out of Universe allowing that data to be added to the MART tool spreadsheet and then export for inserting into Multidev via the MART tool.

Order of operation
- See “Unload Universe Data to MART Tool Spreadsheet Tabs” above to update MART tool spreadsheet tabs for validation purposes.
- See “Export data from Multidev for use with UT919” above and run UT919.
IMPORTANT: Completing this step for INV.MST will update INV.MST with Multidev PRODUCTID for Universe records added to Multidev through previous MART tool uses.
- See “Unload Universe Data to MART tool tabs to ADD to Multidev” above.
  - For ALL but INV.MST a column ON.SV1020 will show key matches between the two systems.
  - Find rows with null values for this column and paste them into the correct MART Tool spreadsheet tab.
  - For INV.MST, the output will only have records that do not have a PRODUCTID (see note above about PRODUCTIDs being updated in UT919).
- Validate the MART tool spreadsheet.
- Export tabs to be updated in Multidev.
- Use MART tool to add records in Multidev.
  - Training on using the MART tool was already provided but should be documented.
- Export Multidev MART Tool - MD Output INVENTORYMASTER UT919 Only again and run UT919 to update productids in INV.MST.
Other Updates
- KSKUIDSKUS
  - In Smartforms, this is the Alternate SKU tab.
  - A row in this tab has the legacy SKU.
  - When the MART tool creates the new record in inventory master, it creates three alternate SKUS – one for the productid, one for the productcode and one our legacy SKU (SKU in the MART tool spreadsheet PRODUCTS tab).
  - In order our SKU to be recognized through the system as “our legacy SKU”, vendorbarcode on KSKUIDSKUS must be “Y”.
  - See Informer: Multidev UPDATES - Legacy SKU Vendor Barcode
  - Export this data to a .txt (tab delimited file) with no field, text delimiters and save to \10.0.8.89\c$\Multidev\SQL Table Updates\data as kskuidskus vendorbarcode UPDATE.txt
  - Use Table update EXCEL tool

And run against the data that was exported. October 8, 2024
There are approximately 60 SKUs in Multidev that were loaded from Universe and are junk (PTP=”D”).
There are approximately 100 SKUs in Multidev that are NOT in Universe and were created during testing.
For several reasons, we are likely to have INVENTORYMASTER in Multidev purged and reload from Universe.
However, if INVENTORYMASTER is to be maintained and we are to update with current Universe values, these SKUs (#1 and #2) will have to be deleted.
UT919 has been written to compare Multidev to Universe and further documentation is forthcoming. October 8, 2024
MART Tool
- This tool needs to be used to add large number of new records to Multidev, i.e., add new inventory records added in Universe not yet in Multidev.
- New records CAN BE added to Multidev through the UI, but that may not be effective for large MAJOR tables (see below).
- Once records are in Multidev, update scripts can be run in order to synchronize other portions of Universe data.
Major, minor and other tables
- Major tables
  - Major tables are those that may change frequently in Universe and we cannot shut off Universe updates or do dual maintenance many weeks in advance of go live.
  - Major tables are:
    - INV.MST – inventorymaster
    - VEND.MST – supplier
    - SLSP.MST – employee; we believe that the UKG tool is keeping the employee table updated with new records and the MART tool isn’t likely to be used in the future to load salesperson records to employee.
- Minor tables
  - Minor tables are those that change less frequently.
  - The tables may have been initially loaded with the MART tool or there could have dual maintenance.
  - There could be records in Multidev that are not in Universe.
  - A verification/comparison of the records keys in each system is likely all that is required.
  - Minor tables are:
    - MD.CLASS - pclass
    - MD.SUB.CLASS – psclass
- Other tables
  - Through integration or manual configuration, other tables in Multidev need to be maintained, but maintenance is likely best done only in the Multidev UI.
  - Other tables are:
    - Mutlidev – BRANDNAMES; we have EAVs for BRAND that are being used as a BRANDNAME in Multidev.
Using the MART tool for MAJOR files has the additional complication of dependency checking that is done in the MART tool. For example, adding a new product requires that class, sub-class and other tabs are complete.
- Informer extracts from Multidev should be used to quickly load into the MART tool dependent tables and when the records loaded don’t account for all of the required values, the dependent table may need to be updated.
Using the MART tool for MAJOR files requires an extract from Universe which can be done in Informer.