FO Set Import - SQL

This style of import is designed to work with a specified Financial Object . Rather than reading in data that varies over time, this import reads in data for a single period, but can include Constants, Line Items, FO Attributes, and titles.

An import of this type is treated as a one-time event. Subsequent imports will replace the value of the original import if it is for the same Layer Start Date (see below), including value layers. However if the Start Date is changed, the data will be re-imported (Note: if the re-import is in the same Overlay used in a previous import, you will need to make a Priority Adjustment (see below)).

Preparing to Import

Go to Model Content/Data Imports
From the right-click Add menu, select New FO Set Import, then from database using SQL
Title the import
From the FO Type list, select the FO that will receive the data. Click Save.
For Database Driver, select the appropriate driver for the database you want to connect to
1. sun.jdbc.odbc.JdbcOdbcDriver
2. com.mysql.jdbc.Driver
3. oracle.jdbc.driver.OracleDriver
4. com.microsoft.sqlserver.jdbc.SQLServerDriver
Enter the Database URL, e.g. jdbc:mysql://localhost/webadmin
Enter the Database Username. e.g. root
Enter the Database password for this user
Enter the SQL code to extract the table containing the data, e.g. select * from <table name>
Select the Group that the newly created FO Instances will be placed. (Note: If the FO is based on a Basic Duration Line Item FO or Basic Instant Line Item, you will need to create a group in User Defined if none exist)
If you want to automatically create a new group within the group selected in Step 4 above, type in a name for the new group in New Group Title.
Select the Identity to be used for mapping data store records to FOs.
Re-title FO by clicking on title components to change, add or delete text, or FO Attributes
Select the Overlay that will receive the data.

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.

Select the Date for which the incoming data applies for LAYER Start Date

For Skip first line? select yes if the first line CSV file has column head information rather than data that is to be imported.
If any of the parameters you will be reading in include dates, select the date format of the incoming data.
Click Read-Database from the menu.

Mapping the data

Now that the data has been read in, you will need to map the columns in the file to the parameters in the FO Instances; Each column represents an attribute or parameter and each row represents an FO Instance An FO Instance inherits all parameters and any logic(formulas) contained in the FO. Any change in the FO is immediately updated in all instances.. Another way to think about the data being read in is as a database record.

Example: If you created an employee object.that include the employee's name, employee number, and salary and want to automatically create the FO Instances and populate the values, an FO Set import is the solution. Create a SQL table with a column each for the name, number, and salary.

When the mapping is complete, click Save. Then click Import FO data.

See FO Tables as the preferred method for importing FO data.