OneStream Fast Data Extracts APIs
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.FdxExecuteWarehouseTimePivotUse 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 -Conclusion
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.
