The first step in prepping for a master data import is to collect your data into an Excel file. Familiarize yourself with the following tips and revisit this topic for guidance while setting up import files for Data Imports.
Save your file as an Excel Workbook file type and to a location that you can access while signed onto Stratum.Viewer. Avoid using the Strict Open XML Spreadsheet file type.
Your import file must include dimension key values and master data values.
Target Dimension and Dimension Key Values – One column needs to contain the import dimension’s key values. Use a name at the top of the column that helps you identify the associated Stratum dimension, such as the name that shows up for the dimension in Viewer. Examples of dimensions are Product, Inventory, UPC, and Customer Ship-To.
Note: If your import contains columns of descriptive info about the dimension that you don’t plan to use for the import (such as some of its Corporate Controlled Attributes), you can tell Master Data Import to ignore the columns.
Master Data Values For User Controlled Attributes – Include a column for each User Controlled Attribute where data will be imported. Use a name at the top of each column that helps you identify the associated attribute. If you include columns for more than one User Controlled Attribute, make sure they belong to the same dimension. Imports handle a single dimension at a time.
Identifying information about the data contained in each column of your file can be included in the header row(s) of your import file. They help identity what is in each column – saving you time as you map columns or mark them to be ignored while setting up your import. The example shown in the next image uses one header row. You can use more than one header row if needed. All header rows must be located at the top of your file, above the rows of import data.
The data for your import needs to be in the first worksheet of your import file. Only the first worksheet is considered by Master Data Import and all others are ignored.
Make sure you don’t have a null row interspersed with rows of data in your import file. A Null row is a row without any data (note that a cell with all blank spaces or with zeroes in it is not considered null). The first null row that Master Data Import encounters in an import file will be treated as the end of your file. Master Data Import stops looking for data to import once it finds a null row. In this example, you’d want to remove row 14. If you don’t, the import will stop at that row and not consider any of the rows of data after that point.
Certain types of information should be excluded either because they don’t make sense for an import, aren’t necessary for an import, could disrupt the upload process*, or could throw off the integrity of the import such as bring in unwanted data.
Exclude measure values, grand totals, and subtotals. Only include dimension and master data in import files.
Delete any hidden rows from the Excel file, otherwise they will show up in the data import preview and be included in the data that gets imported. If you don’t want data from hidden rows, delete the rows from the Excel file before you import it.
Delete hidden columns from the Excel file, otherwise they will show up in the data import preview. If you don’t want data from hidden columns included in your import, delete the columns from the Excel file before you import it OR mark the columns as Ignore when mapping the data.
In cases where you use an exported Stratum.Viewer view as the starting point when creating an import file, it is recommended that you disable or exclude the following Viewer features before exporting the view.
“All Others” rows or columns related to filtering.
Conditional format icons or formatting.
Charts.
Hyperlinks.
Calculated measure items that return images.
Pop-up labels on measure items.
Drilldown views linked up to rows, columns, or measure items.
Also consider excluding specialty Excel items like the items listed below.
Images.
Charts.
Cell borders.
Comments or notes.
Excel features such as text boxes.