PUBLISHED FEBRUARY 19, 2021
Using Existing Excel Files to Load
Data in OneStream
By Shrushti Patil
Business Analyst, B-eye Solutions
Most businesses use Excel to work on their Planning, Budgeting, Forecasting and Reporting needs. The source data for these statements is an output of another system like an ERP. It is usually in the form of flat files or Excel files.
In a OneStream implementation, we can use many of the existing Excel files with minimal changes and convert them into loadable files or input forms; thereby reducing the implementation time and increasing user comfort.
Let us look at some ways in which we can work with existing excel files.
Excel files converted to CSV format
We can load Fixed width and Delimited flat files using the Import workflow in a OneStream application.
An Excel file can be converted to a CSV file. Then we can map data fields in the csv file to appropriate dimensions in OneStream through the data source.
The next step is to set up mapping rules for the data to be loaded.
Assign the data source and transformation rules to Workflow profile to complete data load in OneStream.
Excel Files converted to Excel Templates
We can also convert existing Excel files to OneStream readable Excel formats. These also can be loaded using the Import workflow in OneStream.
Here are some important elements in creating a template using an existing file:
The template area can be defined either on the same sheet as the existing data or a new sheet in the same file.
Next, we need to represent all dimensions in the columns of the table. Each dimension token is represented by a predefined keyword. These tokens define the type of data in that column.
Use : to fix a specific member by creating a static member for source dimension. For e.g. T#:[2019M1] sets time 2019M1 as static value for every row of the table. Business rules can also be used. Multiple time periods can also be loaded.
To create the data set in the table, cells can be populated from the existing data sheet using excel functions.
Once the table is created, we need to define a Named Range beginning with XFD, where the dimension token is the first row.
The steps to attach the data source and transformation rules to the workflow profile follow.
Excel File converted to Input Forms
An existing Excel file/template (e.g. Planning templates or Supplemental data templates) can be used as a form in OneStream using the Spreadsheet tool.
Let us take an example of an existing Excel file for Supplemental data. We can create an Excel form which can then be attached to the workflow and can be available for Form input in Workflow.
Here are the steps:
Create a Cube view for the Supplemental data
Open the Cube View in Spreadsheet. Navigate to Spreadsheet -> Cube Views and select the Cube View. Click Ok.
Select the File Ribbon, then save the spreadsheet file.
Navigate to Forms templates and create a Form group and Form template (named as SupplementalData_Jan2020 in this example).
Configure Supplemental form template as follows:
Navigate to workflow and do the setting as follows:
The Excel Form is available for input in Workflow -> Forms.
Existing Excel Budget/Forecast file converted to Matrix load file
In a Budget file, we have data columns for multiple time periods. And each month column has to be mapped to the respective period in OneStream. Loading the entire file at one go is possible using Matrix Load type.
The existing Budget file can be converted to a CSV and the columns can be set up in Data Source using Matrix Load type.
If the file has multiple tabs for separate Entities, a Macro can combine all the tabs to create a single CSV.
To learn more about the Matrix Load in OneStream, please read through our blog, Multi Columnar Data Load In OneStream.
Knowing that Finance revolves around Excel, we can leverage the Excel-accepting capabilities of OneStream to our advantage by using existing user files and seeing how quickly and easily we can get them OneStream-ready.