Loading Stage Data

Import Excel Template

When importing data into the Stage via Excel, a specific template must be used for OneStream to read and load the data. The user must first create Dimension Tokens (e.g. E#, A#, S#, etc.) to organize the data correctly. The Dimension Token specifies the specific type of data in any given column. For example, if the column header is E#, OneStream will read every row in that column as an Entity name when loading into Stage. After the Dimension Tokens are specified, create a Named Range beginning with xfd. There can multiple xfd Named Ranges across multiple tabs within an Excel workbook. The Dimension Tokens must be the Named Range’s first row. 

The following Dimension Tokens are used within an Import Excel Template. Please note these tokens can be in any order on the Excel template.

Dimension Tokens	Meaning
A#	Account: each row below will list the accounts to be imported
AMT#	Amount: using the AMT.ZS# header will automatically apply zero suppression to this import. Tip: Apply zero suppression to a Matrix-style Excel import template by using this same .ZS extension on the AMT column.
F#	Flow
IC#	Intercompany
E#	Entity
C#	Consolidation
S#	Scenario
T#	Time Period
V#	View
O#	Origin
UD1#-UD8#	Each row must have a value even if a User Defined Member is not used in the application. Create a Static Value of None for any UD Members where this applies. Ex. UD5#:[None]  UX# or UDX# can be used for all User Defined Dimensions.
LB#	Label: This is used for an Account description related to a line of data. It is imported just for reference purposes and not stored in the Cube.
SI#	Source ID: This is a key for data imported into Stage. This typically includes a reference to the Entity being loaded but depends on the implementation. It is a best practice to have only one Source ID per Named Range and these can be the same or different for every Named Range imported for one Excel workbook.
TV#	Text Value: this is used to store large amounts of textual data.
A1# through A20#	Attribute Dimensions: these 20 Dimensions can each store 100 characters of text.
AV1# through AV12#	Attribute Value Dimensions: these 12 Dimensions can store numeric data.

Header Abbreviations

Static Value

Use  :[] to fix a specific Member to the entire column creating a Static Value for the specified Source Dimension. For example, F#:[None] imports the None Flow Member for every Flow row within the Named Range. This syntax applies to all Dimension Tokens.

Data Sources allow text values to be loaded as a View Member from the same row as the numeric value. Specify #Annotation, #VarianceExplanation, #AuditComment, #Footnote, or #Assumption as the Static Text Value of the TextValue Source Dimension and a new row will be created for the comment row. For example, use TV#:[#Annotation] to add an additional Annotation row.

Business Rule

Pass a Business Rule for any specified Source Dimension to set a specific value.

AMT#:[]:[BusinessRuleNameThatSetsAValue]

Matrix Member

This repeats for each Member. For example, if there were twelve time periods in the named range the syntax would be as follows:

T#:[]:[]:[2012M3]

To use Current/Global Scenario and Time, use .C# and .G# which creates a Static Value for the Time and Scenario within the Named Range. T.C# and S.C# returns the current Workflow Time and Scenario.  T.G# and S.G# returns the Global Time and Scenario.

Import Data Extracted via Data Management

Any type of data (Import, Forms, or Journals) extracted to a CSV file through a Data Management Job can be imported into Stage via an Extensibility Business Rule. This simplifies the migration of data between applications.

Example

Dim objXFResult As XFResult = BRApi.Finance.Data.SetDataCellsUsingCsvFile(si,  filePath, delimiter, 
	originFilter, targetOriginMember, loadZeros)

When using this BRApi make sure to specify the Origin Filter which determines the type of data desired from the file (Import, Forms or Adjustments), and the Target Origin Member which determines where the data will be stored upon loading the file.