Creating a Data Source

  To create a data source:

1. On the Object Browser View menu, select Data Sources.

2. Click Actions and select Add.

3. In the Data Source Wizard, enter data source Properties information.

   • Name—Data source name

   • Description—Description of the data source

   • Type—Flat File, Excel File, Database, or Essbase

   • Create Row Source and Stagemap—Select to automatically create a row source and a stagemap from the data source. The default value is set by the datadesigner.default.rowsource.create parameter in ISServer.properties.

   • Details—Information about the file

     • In Flat File, enter a file name and a file type (delimited or fixed width).

     • In Excel File, enter a file name and an Excel sheet name.

     • In Database, define a database connection.

     • In Essbase, select an Essbase connection or click New to define a connection.

       When entering a file name for Flat Files or Excel Files, peform an action:

       • Enter the file name.

       • Select Browse to display the files on the server data tray.

       • Upload to upload a file from your computer. When you select a file after clicking Upload, the file is uploaded to the server.

4. Enter data source Configuration information and click Preview.

   In Flat File, enter:

   • Delimiter—Separator between fields in a data source. Select Tab, Comma, Semicolon, or Pipe.

   • Start Row—Row on which to start importing data (The Start Row is usually 2.)

   • Header Row—Row that contains the column titles (The Header Row is usually 1.)

   • Missing Trailing Values—How to treat rows or columns that are missing values. Select Treat as Error or Treat as Null.

   • Text Qualifier—Whether to display quotation marks around text values:None, Double Quote, or Single Quote.

   • End Row—Row on which to stop importing the data (Leave blank to import all rows.)

   • Row Increment—Number of rows to advance when importing data (Default is 1)

   • Pivot Data—If the underlying Excel data is tabular, select False. If the underlying Excel data is in a pivot format, select True. Pivoting implies that the file is anchored vertically along a few columns and then horizontally along other columns, with data at the intersection of the vertical and horizontal columns.

   For Excel File, enter:

   • Start Row—Row on which to start importing data (Usually 2.)

   • Header Row—Row that contains the column titles (Usually 1.)

   • Missing Trailing Values—How to treat rows or columns that are missing values. Select Treat as Error or Treat as Null.

   • End Row—Row on which to stop importing the data (Leave blank to import all rows.)

   • Row Increment—Number of rows to advance when importing data (Default is 1)

   • Pivot Data—If the underlying Excel data is tabular, select False. If the underlying Excel data is in a pivot format, select True. Pivoting implies that the file is anchored vertically along a few columns and then horizontally along other columns, with data at the intersection of the vertical and horizontal columns.

   • Skip Hidden Rows—Whether to skip hidden rows.

   For Database, enter the SQL for the data source configuration.

   For Essbase, enter:

   • Query Type—Report Script or MDX

   • Query—Query to be sent to Essbase

     Integrated Operational Planning internally flattens the results returned from Essbase and displays the results under Data Source Preview.

5. Review the data Fields.

   • Refresh Fields—Refresh the defined data fields. Click this button if no fields are displayed.

   • Create—Create a data field

     Enter this information:

     • Name—Data field name

     • Type—String, Double, Integer, Date, or Duration

     • Nullable—Whether a value can be empty. True or False.

     • Orientation—For pivot formatted data, select Vertical or Horizontal to define how to display the data field in relation to other data fields

       If vertical, enter the column in which the data field will be displayed.

       If horizontal, enter a column range, row number and row type. A row type can be absolute or relative. Absolute rows are horizontal header columns, and you must specify the actual row numbers. Relative rows indicate the data columns, and the row number must be zero.

     • Default—Default data field value

     • Date Format—For date fields, select the format in which the date will appear.

     • Null Values—How to treat null values. Select Remain as Null or Default to Previous Non-Null Value.

   • Edit—Edit a data field

   • Delete—Delete a data field

6. Click Save to create the data source and map it to a row source.

   The data source is displayed in the Object Browser and is automatically mapped to a row source.

   See Data Flow and Mapping for details on how data is staged and loaded into the Integrated Operational Planning database.

   Tip:

   Review the row source generated through automatic mapping for key values. The automatic generation assigns the first column as the key column of the row source. If this assignment is not valid, you must modify the key columns of the row source. Similarly, when the stagemap is automatically generated for the mapping between a data source and a row source, a default group name is assigned to the stagemap. Review this default group name for accuracy.