The OneStream DataFrame is an in-memory, tabular data structure designed for flexible data manipulation outside of the cube. It provides a powerful way to retrieve, transform, and analyze data from multiple sources, including SQL queries, Data Adapters, and dynamic system objects. DataFrames support operations similar to traditional tables but optimized for OneStream’s analytics workflows, allowing large datasets to be processed efficiently. DataFrames can also be converted to legacy types such as DataTable or DataSet for compatibility with existing and legacy processes.
This topic covers the following:
For information about IDataFrame, see IDataFrame Interface.
DataFrame Namespace
To use a DataFrame in a rule, make sure the OneStream.Data.DataFrame and OneStream.Data.DataFrame.Abstractions Namespaces are included.
C#:
Copy
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;VB:
Copy
Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.AbstractionsBasic DataFrame Functions
The following BRApi.Database functions are available to create DataFrame objects:
- GetDataFrame: Creates a DataFrame and populates it using a database query. See GetDataFrame.
- CreateDataFramewithColumns: Creates an empty table with column names. See CreateDataFramewithColumns.
- CreateDataFrame: Creates an empty table in memory that can be populated. See DataFrame Methods.
- CreateEmptyDataFrame: Creates an empty table. See CreateEmptyDataFrame.
- SaveDataFrame: Save a DataFrame to a database table. See SaveDataFrame.
GetDataFrame
BRApi.Database.GetDataFrame(dbConn, dataFrameName, sqlStatement, dbParamsInfos, useCommandTimeoutLarge)
Execute an SQL query and return a DataFrame instance. These parameters are used:
| Parameter | Type | Description |
|---|---|---|
| dbConn | DbConnInfo | Database connection to run the SQL select statement against. |
| dataFrameName | string | Name of the DataFrame. |
| sqlStatement | string | SQL statement to be executed. |
| dbParamInfos (optional) | List<DbParamInfo> | A list of DbParamInfo objects that can be used to substitute SQL parameter names prefixed with @ using the associated parameter value. |
| useCommandTimeoutLarge | bool | Switch used to indicate if the standard system large timeout value should be used for the statement execution. |
Example in C#
Copy
// get DataFrame using a query
var myFrame = BRApi.Database.GetDataFrame(dbConn, "myFrame", "SELECT * FROM MyTable", useLargeTimeout);
var myFrame = BRApi.Database.GetDataFrame(dbConn, "myFrame", "SELECT * FROM MyTable where E=@entity", myDbParamInfos, useLargeTimeout);Example in VB:
Copy
' get DataFrame using a query
Dim myFrame = BRApi.Database.GetDataFrame(dbConn, "myFrame", "SELECT * FROM MyTable", useLargeTimeout)
Dim myFrame = BRApi.Database.GetDataFrame(dbConn, "myFrame", "SELECT * FROM MyTable WHERE E=@entity", myDbParamInfos, useLargeTimeout)CreateDataFramewithColumns
BRApi.Database.CreateDataFramewithColumns(dataFrameName, columnsInfo)
Create a DataFrame with a specified set of columns. If the type of column data is not specified, it defaults to String. These parameters are used:
| Parameter | Type | Description |
|---|---|---|
| dataFrameName | string | Name of the DataFrame. |
| columnsInfos | List<string> or Dictionary<string, XFDataType> | A list of column names used for the DataFrame, or a dictionary where keys are column names and values are XFDataType constants that specify the type of data for each column. |
Example in C#:
Copy
// create empty DataFrame with columns
var myFrame = BRApi.Database.CreateDataFrameWithColumns("myFrame", colNames);
var myFrame = BRApi.Database.CreateDataFrameWithColumns("myFrame", colNamesAndTypesDict);
Example in VB:
Copy
' create empty DataFrame with columns
Dim myFrame2 = BRApi.Database.CreateDataFrameWithColumns("myFrame", colNames)
Dim myFrame2 = BRApi.Database.CreateDataFrameWithColumns("myFrame", colNamesAndTypesDict)
CreateEmptyDataFrame
BRApi.Database.CreateEmptyDataFrame(dataFrameName)
Produce an empty DataFrame with default properties. These parameters are used:
| Parameter | Type | Description |
|---|---|---|
| dataFrameName | string | Name of the DataFrame. |
Example in C#:
Copy
// create empty DataFrame
var myFrame = BRApi.Database.CreateEmptyDataFrame("myFrame") ;Example in VB:
Copy
' create empty DataFrame
Dim myFrame1 = BRApi.Database.CreateEmptyDataFrame("myFrame")SaveDataFrame
BRApi.Database.SaveDataFrame(si, dbLocation, tableName, dataframe, useBulkInsert, useCommandTimeoutLarge, options)
Save a DataFrame to a database table. Replaces all content of the specified table (performs a DELETE), then either writes the new rows with SqlBulkCopy when useBulkInsert is true or updates row-by-row via DbSql.UpdateDataTable. Validates schema/table safety, supports identity insert when options.IdentityColumnMode == IdentityColumnMode.InsertExplicitValues, and throws when the target connection is a Gateway provider. These parameters are used:
| Parameter | Type | Description |
|---|---|---|
| si | SessionInfo | Session information used to create DB connections and for error/log context. |
| dbLocation | String | Database location name: "Application", "System" (Framework), or a named external database. |
| tableName | string | Target table name (optionally schema-qualified, e.g. dbo.MyTable). |
| dataframe | IDataFrame | Source DataFrame to save (converted to a DataTable before save). |
| useBulkInsert | bool | If true, use SqlBulkCopy to insert rows; otherwise perform row-by-row updates/inserts. |
| useCommandTimeoutLarge | bool | If true, use the large command timeout for DB operations. |
| options | SaveDataFrameOptions | Options controlling column mapping/exclusions and identity column handling (may be null). |
Example in C#:
Copy
// save DataFrame using bulk insert and large timeout, explicitly inserting identity values
SaveDataFrameOptions opts = new SaveDataFrameOptions {
IdentityColumnMode = IdentityColumnMode.InsertExplicitValues,
IdentityColumnNames = {"ID"}
};
BRApi.Database.SaveDataFrame(si, "Application", "dbo.MyTable", myDataFrame, useBulkInsert: true, useCommandTimeoutLarge: true, options: options);Example in VB:
Copy
Dim options As New SaveDataFrameOptions With {
.IdentityColumnMode = IdentityColumnMode = IdentityColumnMode.InsertExplicitValues,
.IdentityColumnNames = New HashSet(Of String) From {"ID"}
}
BRApi.Database.SaveDataFrame(si, "Application", "dbo.MyTable", myDataFrame, True, True, options)
Quickstart DataFrame Examples
The following snippets show a set of common techniques to work with DataFrames. Consult the class reference for the complete API description.
Basic Conversion (No Options)
Use toDataTable() or SaveCustomDataTable() as a basic means of converting a DataFrame to a DataTable. All DataFrame columns are included without modification, and underlying .NET data types are preserved during conversion.
Example in C#:
Copy
// Convert the entire DataFrame to a DataTable using its natural schema.
// All columns and rows are included with no filtering or renaming.
DataTable dt = df.ToDataTable();
// Hand off to the legacy save API — useful when the calling code
// still expects a DataTable rather than the new SaveDataFrame overload.
BRApi.Database.SaveCustomDataTable(si, "Application", "InventoryItems", dt, useBulkInsert: true, useCommandTimeoutLarge: false);
Example in VB:
Copy
' Convert the entire DataFrame to a DataTable using its natural schema.
Dim dt As DataTable = df.ToDataTable()
' Hand off to the legacy save API.
BRApi.Database.SaveCustomDataTable(si, "Application", "InventoryItems", dt, True, False)
Conversion with SaveDataFrameOptions
Use SaveDataFrameOptions when the DataFrame schema requires modification before conversion to a DataTable. Common scenarios include target tables with identity columns, mismatched column names between the DataFrame and database table, or DataFrame columns that are intended for display or intermediate calculations and should not be converted.
Example in C#:
Copy
var saveOptions = new SaveDataFrameOptions
{
// SkipAndAutoGenerate (default): omits the identity column from the INSERT statement
// so SQL Server generates the value. Use InsertExplicitValues when restoring data
// with known IDs (e.g. a migration or backup restore scenario).
IdentityColumnMode = IdentityColumnMode.SkipAndAutoGenerate,
// Explicitly declare which column is the identity column.
// If left empty, the engine queries INFORMATION_SCHEMA to detect it automatically.
IdentityColumnNames = new HashSet<string> { "SKU" },
// Exclude columns that exist in the DataFrame but should not be written to the table,
// such as calculated fields or grid-only display columns.
ExcludeColumns = new HashSet<string> { "DisplayLabel" },
// Rename DataFrame columns to match the target table's actual column names.
// Key = DataFrame column name, Value = database column name.
ColumnNameMappings = new Dictionary<string, string>
{
{ "RefreshFreq", "RefreshFrequency" }
}
};
// Apply all options at conversion time — the resulting DataTable is already
// shaped to match the target table before SaveCustomDataTable is called.
DataTable dt = df.ToDataTable(saveOptions);
BRApi.Database.SaveCustomDataTable(si, "Application", "InventoryItems", dt, useBulkInsert: true, useCommandTimeoutLarge: false);
Example in VB:
Copy
Dim saveOptions As New SaveDataFrameOptions() With {
.IdentityColumnMode = IdentityColumnMode.SkipAndAutoGenerate,
.IdentityColumnNames = New HashSet(Of String) From { "SKU" },
.ExcludeColumns = New HashSet(Of String) From { "DisplayLabel" },
.ColumnNameMappings = New Dictionary(Of String, String) From {
{ "RefreshFreq", "RefreshFrequency" }
}
}
' Apply all options at conversion time.
Dim dt As DataTable = df.ToDataTable(saveOptions)
BRApi.Database.SaveCustomDataTable(si, "Application", "InventoryItems", dt, True, False)
Limit the Number of Rows (maxRows)
Use maxRows parameter to limit the number of rows included during DataFrame‑to‑DataTable conversion. This parameter applies only at conversion time, defaults to 0 (all rows).
Example in C#:
Copy
// Convert only the first 1,000 rows — useful for previewing data
// or feeding a bounded grid without loading the full dataset.
DataTable dt = df.ToDataTable(options: null, maxRows: 1_000);
Example in VB:
Copy
' Convert only the first 1,000 rows — useful for previewing data
' or feeding a bounded grid without loading the full dataset.
Dim dt as DataTable = df.ToDataTable(Nothing, maxRows:=1000)DataFrame to TableView Conversion (ToTableView)
Use the toTableView() extension method on a DataFrame to convert it into a TableView in a single method call without programming manual loops, column mapping, or converting to an intermediate data structure.
Example in C#:
Copy
// column names are derived automatically from the DataFrame schema
var myFrame = BRApi.Database.GetDataFrame(dbConn, "SELECT * FROM myTable", ...);
TableView myTableView = myFrame.ToTableView();
myTableView.CanModifyData = true;
Example in VB:
Copy
' column names are derived automatically from the DataFrame schema
Dim myFrame = BRApi.Database.GetDataFrame(dbConn, "SELECT * FROM myTable", ...)
Dim myTableView As TableView = myFrame.ToTableView()
myTableView.CanModifyData = True
Convert DataFrame to Legacy Types
Use toDataTable(), toXFDataTable(), toDataSet(), toTableView(), and toRowsArray() to convert data to a DataTable, XFDataTable, DataSet, TableView, or RowsArray.
Example in C#:
Copy
// convert to DataTable
DataTable myDataTable = myDataFrame.toDataTable();
// convert to XFDataTable
XFDataTable myXFDataTable = myDataFrame.toXFDataTable();
// convert to DataSet
DataSet myDataSet = myDataFrame.toDataSet();
// convert to RowsArray
object[][] myNestedArray = myDataFrame.toRowsArray();Example in VB:
Copy
' convert to DataTable
Dim DataTable As myDataTable = myDataFrame.toDataTable()
' convert to XFDataTable
Dim XFDataTable As myXFDataTable = myDataFrame.toXFDataTable()
' convert to DataSet
Dim DataSet As myDataSet = myDataFrame.toDataSet()
' convert to RowsArray
object[][] myNestedArray = myDataFrame.toRowsArray()Get Metadata from DataFrame
Copy
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
// metadata
int numColumns = myDataFrame.ColumnsCount;
int numRows = myDataFrame.RowCount;
string[] columnNames = myNewFrame.GetAllColumnNames();Get DataFrame Column by Position
Get column data by name or ordinal position.
Copy
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
// get columns by name or by ordinal position
DataFrameColumn myColumn = myNewFrame.GetColumn("Price");
DataFrameColumn myColumn = myNewFrame.GetColumn(3);Assign Metadata to Columns
Use addColumn() to write metadata to columns.
Example in C#
Copy
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
// columns carry metadata
Type colType = myColumn.Type;
string colName = myColumn.Name;
// define columns
myFrame.AddColumn(new DataFrameColumn<string>("colA"));
myFrame.AddColumn(new DataFrameColumn<int>("colB"));
Example in VB:
Copy
Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.Abstractions
' columns carry metadata
Dim colType As Type = myColumn.Type
Dim colName As String = myColumn.Name
' define columns
myFrame.AddColumn(New DataFrameColumn(Of String)("colA"))
myFrame.AddColumn(New DataFrameColumn(Of Integer)("colB"))
Add and Retrieve Data
Use addRow() to write new rows with values to a DataFrame or GetColumn(), GetValue(), GetValueAsString(), or GetColumnOrdinal() to retrieve data from a DataFrame.
Example in C#
Copy
// add and retrieve data
object[] inValues = { "valueField1", 23};
myFrame.addRow(inValues);
for(int c = 0; c < df.ColumnsCount; c++){
for(int r = 0; r < df.RowCount; r++){
int myInt = (int) myFrame.GetColumn(c).GetValue(r)
string myStr = (string) myFrame.GetColumn(c).GetValueAsString(r);
}};
int myInt = (int) myFrame.GetValue(0, myFrame.GetColumnOrdinal("colB"));Example in VB:
Copy
' add and retrieve data
Dim inValues As Object() = {"valueField1", 23}
myFrame.addRow(inValues)
For c As Integer = 0 To df.ColumnsCount - 1
For r As Integer = 0 To df.RowCount - 1
Dim myInt As Integer = CType(myFrame.GetColumn(c).GetValue(r), Integer)
Dim myStr As String = CType(myFrame.GetColumn(c).GetValueAsString(r), String)
Next
Next
Dim myInt As Integer = CType(myFrame.GetValue(0, myFrame.GetColumnOrdinal("colB")), Integer)DataFrame Conversion
DataFrames can be sliced through conversion.
Example in C#:
Copy
var myFrame = BRApi.Database.GetDataFrame(...);
var options = DataFrameConversionOptions();
options.MaxRows = 100;
options.StartRow = 10;
var mySliceOfFrame = myFrame.ConvertFrame(options);Example in VB:
Copy
Dim myFrame = BRApi.Database.GetDataFrame(...)
Dim options As New DataFrameConversionOptions()
options.MaxRows = 100
options.StartRow = 10
Dim mySliceOfFrame = myFrame.ConvertFrame(options)Logs
Use ErrorLog.LogMessage() to write DataFrame content to the log.
Example in C#:
Copy
// Convert the first 10 rows to string for logging
BRApi.ErrorLog.LogMessage(si, "DataFrame Test Output", myFrame.Peek(10));Example in VB:
Copy
' Convert the first 10 rows to string for logging
BRApi.ErrorLog.LogMessage(si, "DataFrame Test Output", myFrame.Peek(10))Create DataFrame from Data Adapter
Example in C#:
Copy
// Look up a Data Adapter and use its query – 9.1+
// note: requires PowerBI Connector support enabled
var myFrame = BRApi.Analytics.GetDataFrameForAdapter(si, isSystemLevel,
"myWorkspace", "myAdapter", "myResultTable", customSubstVars, null);Example in VB:
Copy
'Look up a Data Adapter and use its query – 9.1+
'note: requires PowerBI Connector support enabled
Dim myFrame = BRApi.Analytics.GetDataFrameForAdapter(si, isSystemLevel,
"myWorkspace", "myAdapter", "myResultTable", customSubstVars, null)Create a Dashboard DataSet Service
The following example implements a Dashboard DataSet Service using a few different techniques. The first and second datasets are generated by retrieving a table using GetDataFrame() and calling ToDataSet() to return a DataSet object. The last one creates a DataFrame using CreateDataFrameWithColumns() and then uses ToDataTable() to export a data table.
Example in C#:
Copy
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
public class DatasetWithDataFrame : IWsasDataSetV800
{
public object GetDataSet(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDataSetArgs args)
{
try
{
if ((brGlobals != null) && (workspace != null) && (args != null))
{
if (args.DataSetName.XFEqualsIgnoreCase("HelloDataFrame")){
// simple query
bool useLargeCommandTimeout = false;
string frameName = "MyOutFrame";
string sql = "SELECT * FROM Dim"; // list all Dimensions
using(DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si)){
// *** main call - execute SQL and return a dataframe ***
var myFrame = BRApi.Database.GetDataFrame(dbConn, frameName, sql, useLargeCommandTimeout);
// once we get the dataframe, convert it to legacy type that the rule expects
// Here you can use .ToDataTable() or .ToDataSet()
return myFrame.ToDataSet();
}
} else if(args.DataSetName.XFEqualsIgnoreCase("QueryWithParams")){
// query with parameters
// basic settings
bool useLargeCommandTimeout = false;
string frameName = "MyOutFrame";
string dimTypeStr = args.NameValuePairs.XFGetValue("Dim", "A");
bool isParent, isCube, isBRFN;
DimType dimTypeObj = DimType.GetItemFromAbbreviation(dimTypeStr,
out isParent, out isCube, out isBRFN);
// define parameters as names starting with @
string sql = "SELECT * FROM Dim WHERE DimTypeId = @dTypeId";
// associate your values to the param names
var myParams = new List<DbParamInfo>{
new DbParamInfo("dTypeId", dimTypeObj.Id)};
// execute
using(DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si)){
// *** main call - execute SQL and return a dataframe ***
var myFrame = BRApi.Database.GetDataFrame(dbConn, frameName, sql, myParams, useLargeCommandTimeout);
// convert to expected legacy type
return myFrame.ToDataSet();
}
} else if(args.DataSetName.XFEqualsIgnoreCase("FromScratch")){
// specify type for each column (optional)
var columns = new Dictionary<string, XFDataType>{
{"Strings", XFDataType.Text},
{"Ints", XFDataType.Int32},
{"Bools", XFDataType.Boolean},
{"Datetimes", XFDataType.DateTime},
{"Decimals", XFDataType.Decimal},
{"Guids", XFDataType.Guid},
{"WeirdosAsString", XFDataType.WorkflowTrackingFrequency}
};
// create and populate dataframe
var myDf = BRApi.Database.CreateDataFrameWithColumns("Output Frame", columns);
myDf.AddRow(new object[]{ "My Text ", 42, true, DateTime.Now, 10.3m, Guid.Empty, WorkflowTrackingFrequency.HalfYearly });
// convert to expected legacy type
return myDf.ToDataTable();
}
}
return null;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
}
}
Example in VB:
Copy
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.Abstractions
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800
Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
Public Class DatasetWithDataFrame
Implements IWsasDataSetV800
Public Function GetDataSet(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, _
ByVal args As DashboardDataSetArgs) As Object Implements IWsasDataSetV800.GetDataSet
Try
If (brGlobals IsNot Nothing) AndAlso (workspace IsNot Nothing) AndAlso (args IsNot Nothing) Then
' simple query
If args.DataSetName.XFEqualsIgnoreCase("HelloDataFrame") Then
Dim useLargeCommandTimeout As Boolean = False
Dim frameName As String = "MyOutFrame"
Dim sql As String = "SELECT * FROM Dim" ' List all Dimensions
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
' *** main call - execute SQL and return a dataframe ***
Dim myFrame = BRApi.Database.GetDataFrame(dbConn, frameName, sql, useLargeCommandTimeout)
' Once we get the dataframe, convert it to legacy type that the rule expects
' Here you can use .ToDataTable() or .ToDataSet()
Return myFrame.ToDataSet()
End Using
ElseIf args.DataSetName.XFEqualsIgnoreCase("QueryWithParams") Then
' query with parameters
' basic settings
Dim useLargeCommandTimeout As Boolean = False
Dim frameName As String = "MyOutFrame"
Dim dimTypeStr As String = args.NameValuePairs.XFGetValue("Dim", "A")
Dim isParent, isCube, isBRFN As Boolean
Dim dimTypeObj As DimType = DimType.GetItemFromAbbreviation(dimTypeStr, _
isParent, isCube, isBRFN)
' define parameters starting with @
Dim sql As String = "SELECT * FROM Dim WHERE DimTypeId = @dTypeId"
' associate your values to param names
Dim myParams = New List(Of DbParamInfo) From {
New DbParamInfo("dTypeId", dimTypeObj.Id)
}
' execute
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
' *** main call - execute SQL and return a dataframe ***
Dim myFrame = BRApi.Database.GetDataFrame(dbConn, frameName, sql, myParams, useLargeCommandTimeout)
' convert to expected legacy type
Return myFrame.ToDataSet()
End Using
ElseIf args.DataSetName.XFEqualsIgnoreCase("FromScratch") Then
' specify type for each column (optional, defaults to string otherwise)
Dim columns = New Dictionary(Of String, XFDataType) From {
{"Strings", XFDataType.Text},
{"Ints", XFDataType.Int32},
{"Bools", XFDataType.Boolean},
{"Datetimes", XFDataType.DateTime},
{"Decimals", XFDataType.Decimal},
{"Guids", XFDataType.Guid},
{"WeirdosAsString", XFDataType.WorkflowTrackingFrequency}
}
' create and populate dataframe
Dim myDf = BRApi.Database.CreateDataFrameWithColumns("Output Frame", columns)
myDf.AddRow(New Object() {"My Text ", 42, True, DateTime.Now, 10.3D, Guid.Empty, WorkflowTrackingFrequency.HalfYearly})
' convert to expected legacy type
Return myDf.ToDataTable()
End If
End If
Return Nothing
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
End Class
End Namespace
Create a Dynamic Grid
The following example shows how to create a Dynamic Grid Service by:
-
Using a custom table.
-
Populating default values if the table does not exist.
-
Writing data back to the table on edit.
Example in C#:
Copy
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;
namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
public class DynamicGridWithDataFrame : IWsasDynamicGridV800
{
private string customTable = "XXX_DataFrameSamples";
public XFDynamicGridGetDataResult GetDynamicGridData(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDynamicGridArgs args)
{
try
{
if ((brGlobals != null) && (workspace != null) && (args != null))
{
// create our dataframe
var df = GetOrCreateCustomTable(si, brGlobals, workspace, args);
// small hack to force formatting on Decimal column
// ** only necessary if dataframe is manually populated and used with Dynamic Grid
var amountColumnFormat = new XFDynamicGridColumnDefinition();
amountColumnFormat.ColumnName = "Amount";
amountColumnFormat.DataFormatString = "{0:C2}"; // currency, culture-specific symbol, 2 decimals
//amountColumnFormat.DataFormatString = "{0:N2}"; // generic number, no currency symbol, 2 decimals
var columnDefs = new List<XFDynamicGridColumnDefinition>{amountColumnFormat};
// convert to XFDataTable to set extra properties for our grid
XFDataTable xfDt = df.ToXFDataTable();
xfDt.Columns[0].IsPrimaryKeyColumn = true;
xfDt.HasPrimaryKeyColumns = true;
XFDynamicGridGetDataResult result = new XFDynamicGridGetDataResult(
xfDt, columnDefs, DataAccessLevel.AllAccess );
return result;
}
return null;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
public XFDynamicGridSaveDataResult SaveDynamicGridData(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDynamicGridArgs args)
{
try
{
if ((brGlobals != null) && (workspace != null) && (args != null))
{
DashboardDynamicGridGetDataArgs getDataArgs = args.GetDataArgs;
DashboardDynamicGridSaveDataArgs saveDataArgs = args.SaveDataArgs;
if ((getDataArgs == null) || (saveDataArgs == null))
{
return null;
}
List<XFEditedDataRow> editedDataRows = saveDataArgs.EditedDataRows;
if (editedDataRows != null)
{
using (DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si))
{
XFDataTableHelper.SaveRows(dbConn, "dbo", customTable, args.SaveDataArgs.Columns, true,
editedDataRows, true, true, true);
}
}
XFDynamicGridSaveDataResult result = new XFDynamicGridSaveDataResult();
result.DataTable = GetDynamicGridData(si, brGlobals, workspace, args)?.DataTable;
result.PageIndex = (getDataArgs.StartRowIndex / getDataArgs.PageSize);
result.IndexOfSelectedRowOnPage = 0;
result.SaveDataTaskResult = new XFDynamicGridSaveDataTaskResult()
{
IsOK = true,
ShowMessageBox = false,
Message = "Save Finished"
};
return result;
}
return null;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
/** utility method - Retrieve the contents of our custom table, or create and populate it with default values */
private IDataFrame GetOrCreateCustomTable(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDynamicGridArgs args)
{
string sqlCheck = @"SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = DB_NAME()
AND TABLE_NAME = @customTableName
) THEN 1
ELSE 0
END as TableExists";
var sqlCheckParams = new List<DbParamInfo>{
new DbParamInfo("customTableName", customTable)
};
using(DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si)){
// check if table already exists
var dfCheck = BRApi.Database.GetDataFrame(dbConn, "tableCheck", sqlCheck, sqlCheckParams, false);
if(Convert.ToBoolean(dfCheck.GetValue<int>(0,0))){
// table exists, return contents
return BRApi.Database.GetDataFrame(dbConn, "tableData", $"SELECT * FROM {customTable}", false);
} else {
// table does not exist, create it
var df = BRApi.Database.CreateDataFrameWithColumns(customTable,
new Dictionary<string, XFDataType>{
{"SKU", XFDataType.Int32},
{"Item", XFDataType.Text},
{"Amount", XFDataType.Decimal},
{"Added On", XFDataType.DateTime},
{"Available", XFDataType.Boolean},
{"RefreshFreq", XFDataType.WorkflowTrackingFrequency}
}
);
string sqlCreate = BuildSqlCreate(df, "SKU");
var createResult = BRApi.Database.ExecuteSql(dbConn, sqlCreate, false);
// populate it in memory
df.AddRow(1, "Lightsaber (green)", 1000m, DateTime.Now, true, WorkflowTrackingFrequency.Monthly);
df.AddRow(2, "Darth Helmet", 10000m, DateTime.Now, false, WorkflowTrackingFrequency.Yearly);
df.AddRow(3, "Bounty Hunter JetPack", 500m, DateTime.Now, true, WorkflowTrackingFrequency.Quarterly);
df.AddRow(4, "X-Wing", 200000000m, DateTime.Now, true, WorkflowTrackingFrequency.AllTimePeriods);
df.AddRow(5, "Sleaveless Vest", 50m, DateTime.Now, false, WorkflowTrackingFrequency.HalfYearly);
// convert it in order to use convenience calls for saving.
var dt = df.ToDataTable();
BRApi.Database.SaveCustomDataTable(si, "Application", customTable, dt, true, false);
// return contents
return df;
}
}
}
/** utility method - given a DataFrame, generate a CREATE statement */
private string BuildSqlCreate(IDataFrame df, string primaryColumn = null)
{
var sb = new StringBuilder();
sb.AppendLine($"CREATE TABLE [{df.Name}] (");
string primaryKeyCol = null;
foreach(var col in df.Columns)
{
// probably incomplete mapping, but covers the basics
string sqlType = col.Type == typeof(int) ? "INT"
: col.Type == typeof(string) ? "NVARCHAR(MAX)"
: col.Type == typeof(decimal) ? "DECIMAL(18,2)"
: col.Type == typeof(DateTime) ? "DATETIME"
: col.Type == typeof(bool) ? "BIT"
: "NVARCHAR(MAX)"; // default fallback
sb.Append($" [{col.Name}] {sqlType}");
// If this is the primary key column and integer, set as IDENTITY and NOT NULL
if ((primaryColumn != null) && col.Name.XFEqualsIgnoreCase(primaryColumn))
{
sb.Append(" IDENTITY(1,1) NOT NULL");
primaryKeyCol = col.Name;
}
else if (!col.Nullable)
{
sb.Append(" NOT NULL");
}
sb.Append(",");
sb.AppendLine();
}
// remove last ",\n" -- cross-platform way
int trimLength = Environment.NewLine.Length + 1;
sb.Remove(sb.Length - trimLength, trimLength );
if (!string.IsNullOrEmpty(primaryKeyCol))
{
sb.AppendLine($", CONSTRAINT [PK_{df.Name}] PRIMARY KEY ([{primaryKeyCol}])");
}
sb.Append(");");
return sb.ToString();
}
}
}Example in VB:
Copy
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.Abstractions
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800
Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
Public Class DynamicGridWithDataFrame
Implements IWsasDynamicGridV800
Private customTable As String = "XXX_DataFrameSamples"
Public Function GetDynamicGridData(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, _
ByVal args As DashboardDynamicGridArgs) As XFDynamicGridGetDataResult Implements IWsasDynamicGridV800.GetDynamicGridData
Try
If (brGlobals IsNot Nothing) AndAlso (workspace IsNot Nothing) AndAlso (args IsNot Nothing) Then
' create our dataframe -- see function further down
Dim df = GetOrCreateCustomTable(si, brGlobals, workspace, args)
' small hack to force formatting on Decimal column
' ** only necessary if dataframe is manually populated and used
Dim amountColumnFormat = New XFDynamicGridColumnDefinition()
amountColumnFormat.ColumnName = "Amount"
amountColumnFormat.DataFormatString = "{0:C2}" ' currency, culture-specific symbol, 2 decimals
' amountColumnFormat.DataFormatString = "{0:N2}"; ' generic number, no currency symbol, 2 decimals
Dim columnDefs = New List(Of XFDynamicGridColumnDefinition) From {
amountColumnFormat
}
' Convert to XFDataTable to set extra properties for our grid
' Hopefully unnecessary in future releases...
Dim xfDt As XFDataTable = df.ToXFDataTable()
xfDt.Columns(0).IsPrimaryKeyColumn = True
xfDt.HasPrimaryKeyColumns = True
Dim result As XFDynamicGridGetDataResult = New XFDynamicGridGetDataResult(xfDt, columnDefs, DataAccessLevel.AllAccess)
Return result
End If
Return Nothing
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
Public Function SaveDynamicGridData(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, _
ByVal args As DashboardDynamicGridArgs) As XFDynamicGridSaveDataResult Implements IWsasDynamicGridV800.SaveDynamicGridData
Try
If (brGlobals IsNot Nothing) AndAlso (workspace IsNot Nothing) AndAlso (args IsNot Nothing) Then
Dim getDataArgs As DashboardDynamicGridGetDataArgs = args.GetDataArgs
Dim saveDataArgs As DashboardDynamicGridSaveDataArgs = args.SaveDataArgs
If (getDataArgs Is Nothing) OrElse (saveDataArgs Is Nothing) Then
Return Nothing
End If
Dim editedDataRows As List(Of XFEditedDataRow) = saveDataArgs.EditedDataRows
If editedDataRows IsNot Nothing Then
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
XFDataTableHelper.SaveRows(dbConn, "dbo", customTable, args.SaveDataArgs.Columns, True, editedDataRows, True, True, True)
End Using
End If
Dim result As XFDynamicGridSaveDataResult = New XFDynamicGridSaveDataResult()
result.DataTable = GetDynamicGridData(si, brGlobals, workspace, args)?.DataTable
result.PageIndex = (getDataArgs.StartRowIndex / getDataArgs.PageSize)
result.IndexOfSelectedRowOnPage = 0
result.SaveDataTaskResult = New XFDynamicGridSaveDataTaskResult() With {
.IsOK = True,
.ShowMessageBox = False,
.Message = "Save Finished"
}
Return result
End If
Return Nothing
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
' ** utility method - Retrieve the contents of our custom table, or create and populate it with default values.
Private Function GetOrCreateCustomTable(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, ByVal args As DashboardDynamicGridArgs) As IDataFrame
Dim sqlCheck As String = "SELECT
CASE
WHEN EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = DB_NAME()
AND TABLE_NAME = @customTableName
) THEN 1
ELSE 0
END as TableExists"
Dim sqlCheckParams = New List(Of DbParamInfo) From {
New DbParamInfo("customTableName", customTable)
}
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
' check if table already exists
Dim dfCheck = BRApi.Database.GetDataFrame(dbConn, "tableCheck", sqlCheck, sqlCheckParams, False)
If Convert.ToBoolean(dfCheck.GetValue(Of Integer)(0, 0)) Then
' table exists, return contents
Return BRApi.Database.GetDataFrame(dbConn, "tableData", $"SELECT * FROM {customTable}", False)
Else
' table does not exist yet, create it
Dim df = BRApi.Database.CreateDataFrameWithColumns(customTable, New Dictionary(Of String, XFDataType) From {
{"SKU", XFDataType.Int32},
{"Item", XFDataType.Text},
{"Amount", XFDataType.Decimal},
{"Added On", XFDataType.DateTime},
{"Available", XFDataType.Boolean},
{"RefreshFreq", XFDataType.WorkflowTrackingFrequency}
})
Dim sqlCreate As String = BuildSqlCreate(df, "SKU")
Dim createResult = BRApi.Database.ExecuteSql(dbConn, sqlCreate, False)
' populate it in memory
df.AddRow(1, "Lightsaber (green)", 1000D, DateTime.Now, True, WorkflowTrackingFrequency.Monthly)
df.AddRow(2, "Darth Helmet", 10000D, DateTime.Now, False, WorkflowTrackingFrequency.Yearly)
df.AddRow(3, "Bounty Hunter JetPack", 500D, DateTime.Now, True, WorkflowTrackingFrequency.Quarterly)
df.AddRow(4, "X-Wing", 200000000D, DateTime.Now, True, WorkflowTrackingFrequency.AllTimePeriods)
df.AddRow(5, "Sleaveless Vest", 50D, DateTime.Now, False, WorkflowTrackingFrequency.HalfYearly)
' convert it in order to use convenience calls for saving
Dim dt = df.ToDataTable()
BRApi.Database.SaveCustomDataTable(si, "Application", customTable, dt, True, False)
' return contents
Return df
End If
End Using
End Function
' utility method - given a DataFrame, generate a CREATE statement
Private Function BuildSqlCreate(ByVal df As IDataFrame, ByVal Optional primaryColumn As String = Nothing) As String
Dim sb = New StringBuilder()
sb.AppendLine($"CREATE TABLE [{df.Name}] (")
Dim primaryKeyCol As String = Nothing
For Each col In df.Columns
' probably incomplete mapping, but covers the basics well
Dim sqlType As String = _
If(col.Type = GetType(Integer), "INT", _
If(col.Type = GetType(String), "NVARCHAR(MAX)", _
If(col.Type = GetType(Decimal), "DECIMAL(18,2)", _
If(col.Type = GetType(DateTime), "DATETIME", _
If(col.Type = GetType(Boolean), "BIT", _
"NVARCHAR(MAX)"))))) ' default fallback
sb.Append($" [{col.Name}] {sqlType}")
' If this is the primary key column and integer, set as IDENTITY and NOT NULL
If (primaryColumn IsNot Nothing) AndAlso col.Name.XFEqualsIgnoreCase(primaryColumn) Then
sb.Append(" IDENTITY(1,1) NOT NULL")
primaryKeyCol = col.Name
ElseIf Not col.Nullable Then
sb.Append(" NOT NULL")
End If
sb.Append(",")
sb.AppendLine()
Next
' remove last ",\n" -- cross-platform way
Dim trimLength as Integer = Environment.NewLine.Length + 1
sb.Remove(sb.Length - trimLength, trimLength )
If Not String.IsNullOrEmpty(primaryKeyCol) Then
sb.AppendLine($", CONSTRAINT [PK_{df.Name}] PRIMARY KEY ([{primaryKeyCol}])")
End If
sb.Append(");")
Return sb.ToString()
End Function
End Class
End Namespace
Create a Table View
The following example creates a TableView Service using a DataFrame
Example in C#
Copy
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;
using OneStreamWorkspacesApi;
using OneStreamWorkspacesApi.V800;
/**
Sample TableView implemented with DataFrame.
**/
namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
{
public class TableViewWithDataFrame : IWsasTableViewV800
{
public List<string> TableViewGetCustomSubstVarsInUse(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, Dictionary<string, string> custSubstVarsAlreadyResolved)
{
try
{
if ((brGlobals != null) && (workspace != null))
{
if (tableViewName.XFEqualsIgnoreCase("DataFrame"))
{
}
}
return null;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
public TableView GetTableView(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, Dictionary<string, string> customSubstVars, Dictionary<string, string> nameValuePairs)
{
try
{
if ((brGlobals != null) && (workspace != null))
{
if (tableViewName.XFEqualsIgnoreCase("DataFrame"))
{
using(DbConnInfo dbConn = BRApi.Database.CreateApplicationDbConnInfo(si)){
// retrieve your data
IDataFrame df = BRApi.Database.GetDataFrame(dbConn, "MyTableView", "SELECT * FROM Dim", false);
// optional: change headers
var newFrame = df.ConvertFrame(new DataFrameConversionOptions{
ColumnNameMappings = new Dictionary<string, string>{
{"DimId", "Dimension ID"}, // {"old name", "new name"}
{"DimTypeId", "Dimension Type ID"}
}
});
// build the tableview
TableView tv = newFrame.ToTableView();
// optional: format headers
tv.HeaderFormat.IsBold = true;
// optional: enable submission of edited data
tv.CanModifyData = true;
// return the tableview
return tv;
}
}
}
return null;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
public bool SaveTableView(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, TableView tableView)
{
try
{
if ((brGlobals != null) && (workspace != null) && (tableView != null))
{
if (tableViewName.XFEqualsIgnoreCase("DataFrame"))
{
// optional: add your saving strategies here
}
}
return false;
}
catch (Exception ex)
{
throw new XFException(si, ex);
}
}
}
}Example in VB:
Copy
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.CSharp
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.Abstractions
Imports OneStreamWorkspacesApi
Imports OneStreamWorkspacesApi.V800
'**
' Sample TableView implemented with DataFrame.
'**
Namespace Workspace.__WsNamespacePrefix.__WsAssemblyName
Public Class TableViewWithDataFrame
Implements IWsasTableViewV800
Public Function TableViewGetCustomSubstVarsInUse(
si As SessionInfo,
brGlobals As BRGlobals,
workspace As DashboardWorkspace,
tableViewName As String,
custSubstVarsAlreadyResolved As Dictionary(Of String, String)
) As List(Of String) _
Implements IWsasTableViewV800.TableViewGetCustomSubstVarsInUse
Try
If brGlobals IsNot Nothing AndAlso workspace IsNot Nothing Then
If tableViewName.XFEqualsIgnoreCase("DataFrame") Then
' No custom substitution variables used
End If
End If
Return Nothing
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
Public Function GetTableView(
si As SessionInfo,
brGlobals As BRGlobals,
workspace As DashboardWorkspace,
tableViewName As String,
customSubstVars As Dictionary(Of String, String),
nameValuePairs As Dictionary(Of String, String)
) As TableView _
Implements IWsasTableViewV800.GetTableView
Try
If brGlobals IsNot Nothing AndAlso workspace IsNot Nothing Then
If tableViewName.XFEqualsIgnoreCase("DataFrame") Then
Using dbConn As DbConnInfo =
BRApi.Database.CreateApplicationDbConnInfo(si)
' Retrieve data
Dim df As IDataFrame =
BRApi.Database.GetDataFrame(
dbConn,
"MyTableView",
"SELECT * FROM Dim",
False
)
' Optional: change headers
Dim newFrame =
df.ConvertFrame(
New DataFrameConversionOptions With {
.ColumnNameMappings =
New Dictionary(Of String, String) From {
{"DimId", "Dimension ID"},
{"DimTypeId", "Dimension Type ID"}
}
}
)
' Build the table view
Dim tv As TableView = newFrame.ToTableView()
' Optional: format headers
tv.HeaderFormat.IsBold = True
' Optional: enable submission of edited data
tv.CanModifyData = True
' Return the table view
Return tv
End Using
End If
End If
Return Nothing
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
Public Function SaveTableView(
si As SessionInfo,
brGlobals As BRGlobals,
workspace As DashboardWorkspace,
tableViewName As String,
tableView As TableView
) As Boolean _
Implements IWsasTableViewV800.SaveTableView
Try
If brGlobals IsNot Nothing AndAlso
workspace IsNot Nothing AndAlso
tableView IsNot Nothing Then
If tableViewName.XFEqualsIgnoreCase("DataFrame") Then
' Optional: add your saving strategies here
End If
End If
Return False
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Function
End Class
End Namespace
