DataFrame Methods

A 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:

DataFrame NameSpace
Basic DataFrame Functions
Quickstart DataFrame Examples

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.Abstractions;

Basic 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.

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")

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.

Convert DataFrame to Legacy Types

Use toDataTable(), toDataTable(), toDataTable(), and toDataTable() to convert data to a DataTable, XFDataTable, DataSet 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 retieve data from a DataFrame.

Example in C#

Copy

using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;

// 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

Imports OneStream.Data.DataFrame
Imports OneStream.Data.DataFrame.Abstractions

' 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 myXfDataTable = myFrame.ToXFDataTable(options)

Example in VB:

Copy

Dim myFrame = BRApi.Database.GetDataFrame(...)
Dim options As New DataFrameConversionOptions()
options.MaxRows = 100
options.StartRow = 10
Dim myXfDataTable = myFrame.ToXFDataTable(options)

Logs

Use ErrorLog.LogMessage() to write DataFrame content to the log.

Example in C#:

Copy

using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;

// Convert the first 10 rows to string for logging
BRApi.ErrorLog.LogMessage(si, "DataFrame Test Output", myFrame.Peek(10));

Example in VB:

Copy

Imports OneStream.Data.DataFrame;
Imports OneStream.Data.DataFrame.Abstractions;

' 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

using OneStream.Data.DataFrame;
using OneStream.Data.DataFrame.Abstractions;

// 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

Imports OneStream.Data.DataFrame;
Imports OneStream.Data.DataFrame.Abstractions;

// 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 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 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);
       // build the tableview
       TableView tv = new TableView();
       // set up headers 
       foreach(var col in df.Columns){
        // set basic info
        var tvCol = new TableViewColumn();
        tvCol.Name = col.Name;
        tvCol.Value = col.Name;
        tvCol.IsHeader = true;
        // set the datatype
        tvCol.DataType = XFDataTypeHelper.GetXFDataTypeFromDotNetDataType(si, col.Type);
        // add to table
        tv.Columns.Add(tvCol);
       }
// Loop through rows to populate table
// Note how we keep track of the row index: 
//   values belong to *columns*, so to find them we ask a column to "go down X cells".
//  Alternatively, we could keep track of the column position too, 
//   and use df.GetValue(colIndex, rowIndex), but it's more work.
       foreach(var item in df.Rows.Select((row, index) => new {row, index})){
        var tvr = new TableViewRow();
        foreach(var col in df.Columns){
   // TableViews want strings everywhere, so we use GetValueAsString. 
   // In other situation, we could use GetValueAsObject and cast it to the right type.
         var tvrc = tv.CreateColumn(col.Name, col.GetValueAsString(item.index), false, true);
         tvr.Items.Add(col.Name, tvrc);
        }
        tv.Rows.Add(tvr);
       }
       return tv; 
      }
                    }
                }

                return null;
            }
            catch (Exception ex)
            {
                throw new XFException(si, ex);
            }
        }
  
        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 bool SaveTableView(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, TableView tableView)
        {
            try
            {
                if ((brGlobals != null) && (workspace != null) && (tableView != null))
                {
                    if (tableViewName.XFEqualsIgnoreCase("MyTableView"))
                    {
                    }
                }

                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.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 TableViewWithDataFrame
    Implements IWsasTableViewV800


    Public Function GetTableView(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, ByVal tableViewName As String, ByVal customSubstVars As Dictionary(Of String, String), _
  ByVal 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 your data
                        Dim df As IDataFrame = BRApi.Database.GetDataFrame(dbConn, _
             "MyTableView", "SELECT * FROM Dim", False)
      ' build the tableview
                        Dim tv As TableView = New TableView()
      ' set up headers 
                        For Each col In df.Columns
       ' set basic info
                            Dim tvCol = New TableViewColumn()
                            tvCol.Name = col.Name
                            tvCol.Value = col.Name
                            tvCol.IsHeader = True
       ' set datatype
                            tvCol.DataType = XFDataTypeHelper.GetXFDataTypeFromDotNetDataType(si, col.Type)
       ' add to table
                            tv.Columns.Add(tvCol)
                        Next
  ' Loop through rows To populate table
  ' Note how we keep track Of the row index: 
  '   values belong To *columns*, so t' find them we ask a column to "go down X cells".
  '  Alternatively, we could keep track of the column position too, 
  '   and use df.GetValue(colIndex, rowIndex), but it's more work.
                        For Each item In df.Rows.[Select](Function(row, index) New With {row, index
                        })
                            Dim tvr = New TableViewRow()

                            For Each col In df.Columns
  '  TableViews want strings everywhere, so we use GetValueAsString. 
  '  In other situations, we could use GetValueAsObject and cast it to the right type.
                                Dim tvrc = tv.CreateColumn(col.Name, col.GetValueAsString(item.index), False, True)
                                tvr.Items.Add(col.Name, tvrc)
                            Next

                            tv.Rows.Add(tvr)
                        Next

                        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 TableViewGetCustomSubstVarsInUse(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, ByVal tableViewName As String, ByVal 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
    ' implement this as you need
                End If
            End If

            Return Nothing
        Catch ex As Exception
            Throw New XFException(si, ex)
        End Try
    End Function

    Public Function SaveTableView(ByVal si As SessionInfo, ByVal brGlobals As BRGlobals, ByVal workspace As DashboardWorkspace, ByVal tableViewName As String, _
  ByVal 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
    ' implement this as you need
                End If
            End If

            Return False
        Catch ex As Exception
            Throw New XFException(si, ex)
        End Try
    End Function
End Class
End Namespace

DataFrame Methods

DataFrame NameSpace

Basic DataFrame Functions

GetDataFrame

CreateDataFramewithColumns

CreateEmptyDataFrame

Quickstart DataFrame Examples

Convert DataFrame to Legacy Types

Get Metadata from DataFrame

Get DataFrame Column by Position

Assign Metadata to Columns

Add and Retrieve Data

DataFrame Conversion

Logs

Create DataFrame from Data Adapter

Create a Dashboard DataSet Service

Create a Dynamic Grid

Create a Table View

Intellectual Property Disclaimer

Business Rule and Code Disclaimer