Data Store Imports - SQL
The software includes the ability to import data directly from a SQL database.
- Go to Model
- From the menu, select , then
- the import
- For , select the appropriate driver for the database you want to connect to
- sun.jdbc.odbc.JdbcOdbcDriver
- com.mysql.jdbc.Driver
- oracle.jdbc.driver.OracleDriver
- com.microsoft.sqlserver.jdbc.SQLServerDriver
- Enter the , e.g. jdbc:mysql://localhost/<database name>
- Enter the . e.g. root
- Enter the for this user
- Enter the to extract the table containing the data, e.g. select * from <table name>
- Click
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 .
Specifying the Identity
The identity is used to determine how data store records are mapped to FOs. The identity can be account numbers or 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. 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 . 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, account numbers, and FO attributes.