Creating a New Data Store Import

How to Create a New Data Store Import

Create the import

Open the "Model Content" section in the Navigation Tree and right-click on the "Data Imports" group.
Click on "Add" to expand the selection list, click on "New Data Store" and select "from CSV File".
Give the import a Title and Click "Save".
1. Optionally, type in any Annotations to add notes about the import or to attached a document. Attached documents will be accessible by anyone the model is shared with.

Upload Data FIle

Click the "Import CSV FIle" button and from the File Selection popup, click "Choose FIle".
Navigate to the .csv file you want to import and click "Upload CSV File".
Once the upload is done, click the "OK" button.

Now that the import data file has been uploaded, we need to tell the software how to read it.

Specify Import Mapping Details

See the Structure section below for detailed help on specifying these details.

Usually, a CSV file will contain column headers. If so, make sure that the box for “Skip First Line” is checked.
In the Field Mapping table, select the appropriate options to inform Synario how to treat each column.
In the "Identity" list, confirm that the attributes selected are the ones used in the Field Mapping table to uniquely identify data.
Click "Save".
Click "Process Raw Data" and when finished, click "OK"

Specify remainder of Import-Related Settings

See the Remaining Import-Related Settings section below for detailed help on these settings.

Select the first period in which the data will appear in the model. By default, the software assumes that the source data periods in the imported file are the same as the periods in the model (e.g. months in a monthly model). The Source Data First Period will, by default, be selected as the initial balance period. Select the date of the first period of data in the imported file. The system will remember any change as the new default. This selection will be remembered as the default selection when data stores need to be updated.
Select the desired Overlay. Often when importing historical data, this will be the Actuals Overay.
Update the "FO Title Format" to reflect the desired naming convention needed for the import.
Click "Import Data Into Model"
Review the Data Store Import Summary and confirm that the newly imported data looks as expected.

The Data Store Import Summary displays the results of the import grouped into helpful categories. Review this summary for details on how your data was imported or to review details of any errors that were encountered. If corrections to the data in your CSV file are required, repeat the update process.

Structure

The software supports a number of data formats:

Report Style

In this style, each row represents an individual account with time varying data.

Each comma separated value will appear as a column. You need to verify that each column has been read in correctly and that the data within it is consistent.

Types of data:

title - the title of the account e.g. Petty Cash

data - The data to be read in for each period to be imported. Any number of data columns can be used

subgroup - The subgroup the FO belongs to. Any number of subgroup columns can be used to create a hierarchy of FOs.

internal identity - an alternate means of establishing identity

Import Tag - specifies which line item of the FO for storing the data (see Identity).

skip - Instructs the software to skip over (ignore) this column.

FO Attribute

Line Item Import Tag - specifies which secondary line item to store the data. If blank, then the data is stored in the primary line item.

Example: If the comma separated values are 1023-101,Petty Cash,cash,5897,5698,6985,... then the appropriate column heads might be account, title, type, data, data, data,...

Period Credits/Debits (e.g. Microsoft Dynamics)

This style of csv file uses an initial balance and year/period debits and credits by account.

year - e.g. 2010

period - e.g. 1 (January)

type - the title of the account e.g. Petty Cash

credits - credits to this account the stated period and year

debits - debits to this account the stated period and year

subgroup - The subgroup the FO belongs to. Any number of subgroup columns can be used to create a hierarchy of FOs.

internal identity - an alternate means of establishing identity

Import Tag - specifies which line item of the FO for storing the data (see Identity).

skip - Instructs the software to skip over (ignore) this column

FO Attribute

Once the structure of the data has been specified and configured, click Process Raw Data.

Remaining Import-Related Settings

Specifying the Identity

The identity is used to determine how data store records are mapped to FOs. The identity is determined by FO attributes.

Specifying the Source Data Periods

Source Data Periods - When importing data using a Report Style structure, the software report the number of data periods found in the imported data.

Best Practice: Compare the number of identified data periods with the import file to confirm the number of data periods. If there are too many or too few it indicates that there is an inconsistency in how the columns (Report Style) or rows (Period Credit/Debit) are configured. For example, in a Report Style import, too many periods may indicate that there are trailing commas in the csv file (typically caused by an Excel to CSV conversion). Too few may indicate that one of the columns was misidentified as something other than data.

Source Data First Period - By default, the software assumes that the source data periods in the imported file are the same as the periods in the model (e.g. months in a monthly model). The Source Data First Period will, by default, be selected as the initial balance period. Select the date of the first period of data in the imported file. The system will remember any change as the new default. When the data is imported, a read-only Value Layers will be created for each line item that starts on the date you select, with a length equal to the number of Source Data Periods you specify.

Custom Calendars

In the event that the periods in the imported file are different than the model periods (e.g. quarterly data in the imported file in a monthly model), you will need to specify the calendar. To do so, from the Source Data First Period drop down list select Custom Calendar. A table will be displayed with one row for each data period in the imported file. For each data period specify how many days are included in the data period and review the resulting dates.

Specifying Balance Amounts

By default, the software assumes that the data contained in the incoming file are current balances. If the incoming data represents the difference from the previous period, change the selection in the drop down list. If the data is a difference rather than a balance, for each line the software will calculate the new balance as the previous balance plus or minus the difference for Instant Period In a rollup, returns the value of the last period line items. Duration Period In a rollup, sums all included period values line items are unaffected.

Specifying the Destination for the Incoming Data

Now that the data is ready for import, we need to specify where the data will go. All data must be associated with a Financial Object. If the FO already exists all you need to specify is which Overlay is to receive the data and whether you want to make a specific Layer Priority Adjustment.

Specifying the Overlay and Priority Adjustment

Specify the Overlay where the data is to be stored.

Overlays are created by Case Dimensions.

The default priority adjustment is zero. Unless a conflict occurs, it is not necessary to change this setting.

Handling conflicting overlays

If more than one layer is created for the same line item and is assigned to the same overlay (or one with the same priority), a potential conflict could occur. The software handles the potential conflict by using the time of creation for layer, with the layer created last taking priority.

Specify the FO Title format. Click on Title components to add, change or delete components including text, and FO attributes.

Once these remaining mapping details are specified, click Import Data into Model.