OneStream Fast Data Extracts APIs –

by Blog Admin
0 comment

In modern financial performance management, efficiency, accuracy, and speed in handling vast amounts of data are paramount. OneStream Software, a leader in corporate performance management (CPM) solutions, offers powerful APIs known as the OneStream Fast Data Extracts. This API significantly enhances the process of extracting and utilizing financial and operational data, empowering organizations to make informed decisions swiftly and effectively. 

Understanding the OneStream Fast Data Extract API

The OneStream Fast Data Extract API is a specialized interface that allows seamless integration between OneStream’s platform and other applications or systems. It provides a fast and efficient way to extract data from the OneStream application, making it accessible for various purposes such as reporting, analysis, or integration with other software systems.

This API is designed to optimize the data retrieval process, ensuring high performance and minimal processing time. It leverages modern technology to deliver a streamlined approach to accessing critical financial data housed within the OneStream platform.

Key Features and Capabilities

1. High-Speed Data Retrieval

The Fast Data Extract API is engineered to swiftly retrieve data from OneStream cubes (Including parent-level data and Dynamic calc members), minimizing latency and ensuring a rapid data extraction process. This is especially critical for organizations dealing with large datasets and requiring real-time or near-real-time access to financial information.

2. Custom Data Extraction

The API offers flexibility in defining the data extraction parameters. Users can tailor the extraction based on specific criteria, such as time frames, dimensions, members, or any other relevant filters, to retrieve only the data that is needed for their particular use case.

3. Scalability

The API is built to handle increasing volumes of data and growing organizational needs. It scales effectively, accommodating larger datasets and more complex extraction requirements as an organization expands or evolves.

FDX Query Types

BRApi.Import.Data.FdxExecuteCubeView  BRApi.Import.Data.FdxExecuteCubeViewTimePivot  BRApi.Import.Data.FdxExecuteDataUnit  BRApi.Import.Data.FdxExecuteDataUnitTimePivot  BRApi.Import.Data.FdxExecuteStageTargetTimePivot  BRApi.Import.Data.FdxExecuteWarehouseTimePivot

Use Cases

1. Integration With Other Systems

Integrating OneStream data with other systems, such as ERP systems or business intelligence platforms, becomes seamless using the Fast Data Extract API. It ensures that data is consistent and up-to-date across the organization.

2. Customized Analysis

Users can tailor data extractions to perform specific analyses, aiding in budgeting, forecasting, trend analysis, and other financial modeling tasks.

3. Data Refresh To Other Systems Can Be Initiated From Onestream Workflows

Data refresh can be initiated from workflows in OneStream, providing a user-friendly interface for business users to refresh the latest data whenever needed with a click of a button.

Special Use of FdxExecuteCubeViewTimePivot and FdxExecuteCubeView

The FDXExecuteCubeView function triggers a particular cube view in the OneStream application. This action refreshes the data in a temporary data table. Subsequently, this data can be imported into an external SQL table, which can then be utilized by various Business Intelligence platforms to create interactive dashboards.

The FdxExecuteCubeViewTimePivot function operates similarly to FdxExecuteCubeView, with the distinction of creating the time dimension into columns. example, each period is represented as an individual column in the table.

Example Financial Business Rule Utilizing FdxExecuteCubeViewTimePivot

Imports System  Imports System.Collections.Generic  Imports System.Data  Imports System.Data.Common  Imports System.Globalization  Imports System.IO  Imports System.Linq  Imports System.Windows.Forms  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 System.Threading.Tasks    Namespace OneStream.BusinessRule.Finance.AS_OSOT_Export_CV      Public Class MainClass          Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object              Try                                    Dim connectionString As String = "XXXXXX"                                    Select Case api.FunctionType                                                                      Case Is = FinanceFunctionType.CustomCalculate                            #Region                "Clear the OSOT_DATA_STAGING_CV table before loading"                            If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("OSOT_Export_CV_Clear") Then                                                          '                            Dim sql As String = "DROP Table IF EXISTS [Automation].[dbo].[OSOT_DATA_STAGING_CV]"                              Dim sql As String = "DELETE [Automation].[dbo].[OSOT_DATA_STAGING_CV]"                                                            Dim year As Integer = Now.Year                              BRApi.Dashboards.Parameters.SetLiteralParameterValue(si,False,"OSOTTime",year)                                                            'Execute Query On External DB                              Using dbConnExt As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, connectionString)                                                                    BRAPi.Database.ExecuteSql(dbConnExt, sql, True)                                                            End Using                                                        End If  #End Region                                                                              If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("OSOT_Export_CV") Then                                                            Dim scn As String = args.CustomCalculateArgs.NameValuePairs.Item("scn")                              Dim time As String = args.CustomCalculateArgs.NameValuePairs.Item("time")                                                            Dim scnfilter As String = "S#"&scn                              Dim timefilter As String = "T#"&time &".Base"                                                            Dim EntityAUS As String = "ENT_AUS"                              Dim EntityKOR_AKL As String = "ENT_AKL"                              Dim EntityKOR_MTJ As String = "ENT_MTJ"                              Dim EntityMAL As String = "ENT_ATM"                              Dim EntitySING As String = "ENT_NAS"                              Dim EntityTaiwan As String = "ENT_AGT"                              Dim EntityThai As String = "ENT_ATH"                                                              Dim i As Integer = api.Pov.ScenarioTypeId                                                            'Fast Data Extract using cubeview data                              Dim dt_OSOT_AUS As DataTable = BRApi.Import.Data.FdxExecuteCubeViewTimePivot(si,"Australia","MainEntities",EntityAUS,"MainScenarios",scnfilter,timefilter,Nothing,False,True,False,"",8,False)                    #Region       "Understanding the Table structure; Use this to define custom table structure"                            '                            Dim columns As String = ""  '                            Dim Colcount As Integer = dt_OSOT_KOR.Columns.Count                                '                            For Each col As DataColumn In dt_OSOT_KOR.Columns                                    '                                columns = columns + col.ColumnName + ", "                                    '                            Next  '                            BRApi.ErrorLog.LogMessage(si, columns & "-" & Colcount)  #End Region                                                              #Region     "Create Custom Table Query with year column names"    '                            Dim timecolums As String = BRApi.Dashboards.Parameters.GetLiteralParameterValue(si,False,"OSOTTime")                                '                            Dim createtbl As String = "CREATE TABLE [Automation].[dbo].[OSOT_DATA_STAGING_CV](  '                                                                                            [Cube] [varchar](500) NULL,  '                                                                                            [Entity] [varchar](500) NULL,  '                                                                                            [Parent] [varchar](500) NULL,  '                                                                                            [Cons] [varchar](500) NULL,  '                                                                                            [Scenario] [varchar](500) NULL,  '                                                                                            --[Time] [varchar](500) NULL,  '                                                                                            [View] [varchar](500) NULL,  '                                                                                            [Account] [varchar](500) NULL,  '                                                                                            [Flow] [varchar](500) NULL,  '                                                                                            [Origin] [varchar](500) NULL,  '                                                                                            [IC] [varchar](500) NULL,  '                                                                                            [UD1] [varchar](500) NULL,  '                                                                                            [UD2] [varchar](500) NULL,  '                                                                                            [UD3] [varchar](500) NULL,  '                                                                                            [UD4] [varchar](500) NULL,  '                                                                                            [UD5] [varchar](500) NULL,  '                                                                                            [UD6] [varchar](500) NULL,  '                                                                                            [UD7] [varchar](500) NULL,  '                                                                                            [UD8] [varchar](500) NULL,  '                                                                                            [" & timecolums & " Jan] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Feb] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Mar] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Apr] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " May] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Jun] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Jul] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Aug] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Sep] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Oct] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Nov] [decimal](28, 9) NULL,  '                                                                                            [" & timecolums & " Dec] [decimal](28, 9) NULL,    '                                                                                        ) ON [PRIMARY]"                                ''                            BRApi.ErrorLog.LogMessage(si, createtbl)                                '                            'Execute Query On External DB  '                            Using dbConnExt As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, connectionString)                                '                                    BRAPi.Database.ExecuteSql(dbConnExt, createtbl, True)                                '                            End Using      #End Region                                  #Region     "Save Data to Custom Table"                              'Execute Query On External DB; Save to custom table                              Using dbConnExt As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, connectionString)                                    If Not dt_OSOT_AUS Is Nothing Then                                                                        BRApi.Database.SaveCustomDataTable(si, connectionString, "Automation.dbo.OSOT_DATA_STAGING_CV", dt_OSOT_AUS, True)                                                                                                 'Update Alias names; Calling stored procedure                                      Dim SQL_Update_Aliases As String = "EXEC Automation.dbo.OSOT_AddAliases_CV"                                      BRAPi.Database.ExecuteSql(dbConnExt, SQL_Update_Aliases, True)                                                                        End If                                                                End Using                                #End Region                                            End If                                                              End Select                    Return Nothing              Catch ex As Exception                  Throw ErrorHandler.LogWrite(si, New XFException(si, ex))              End Try          End Function      End Class  End Namespace -


In the fast-paced world of financial management, having timely and accurate access to data is a game-changer. OneStream’s Fast Data Extract API addresses this need by providing a robust and efficient mechanism to retrieve data from the OneStream platform.

Opinions expressed by MaximusDevs contributors are their own.

You may also like

Leave a Comment