Transferring Data From OneStream Cube to SQL Table
In today's data-driven business environment, efficiently transferring data from one system to another is crucial for informed decision-making. This article will guide you through the process of loading data from a OneStream cube to a SQL table, enabling seamless integration between these two powerful platforms. By following these steps, you can ensure accurate and timely data transfer for enhanced reporting and analysis.
Step 1: Understand the OneStream Cube Structure. Before beginning the data loading process, it is important to have a clear understanding of the structure and organization of the OneStream cube. Familiarize yourself with the dimensions, hierarchies, and measures within the cube, as well as the specific data you want to extract and load into the SQL table.
Step 2: Identify the Target SQL Table. Determine the SQL table where you want to load the data from the OneStream cube. Ensure that the table structure matches the dimensions and measures of the cube data. If necessary, create the SQL table with the appropriate columns to accommodate the data.
Step 3: Extract Data from OneStream Cube. Use the data extraction capabilities of OneStream to export the required data from the cube. This can typically be achieved through the use of OneStream's built-in export functionality by leveraging its FDX Query capabilities. Configure the extraction to retrieve the necessary dimensions, hierarchies, and measures, ensuring the data is compatible with the SQL table structure.
Step 4: Establish a Connection to the SQL Database. To load data into the SQL table, establish a connection to the target SQL database using appropriate credentials and connection parameters. This can be accomplished using Connection String if SQL Table is in an external database.
Step 5: Load Data into the SQL Table. With the connection established, execute the SQL commands using OneStream's built-in BRApi functions.
Step 6: Validate the Data Transfer. After loading the data, perform a validation step to ensure the successful transfer of data from the OneStream cube to the SQL table. Compare a sample of the loaded data against the source cube data to verify the accuracy and completeness of the transfer. Address any discrepancies or errors that may arise during this validation process.
Step 7: Automate the Data Loading Process. To streamline and automate the data loading from the OneStream cube to the SQL table using OneStream Data Management steps and Sequences, consider implementing a scheduled or triggered process using OneStream Task Scheduler. Automating the process reduces manual effort, ensures regular updates, and improves overall efficiency.
Here is the example code written in Finance Type Business Rule:
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
Namespace OneStream.BusinessRule.Finance.AS_PLP_Assumptions_Export
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
Select Case api.FunctionType
Case Is = FinanceFunctionType.CustomCalculate
If args.CustomCalculateArgs.FunctionName.XFEqualsIgnoreCase("Assumptions") Then
Dim scn As String = args.CustomCalculateArgs.NameValuePairs.Item("scn")
Dim time As String = args.CustomCalculateArgs.NameValuePairs.Item("time")
Dim time2 As String = args.CustomCalculateArgs.NameValuePairs.Item("time2")
Dim scnfilter As String = "S#"&scn
Dim timefilter1 As String = "T#"&time &".Base"
Dim timefilter2 As String = "T#"&time2 &".Base"
Dim workdaystimefilter As String = timefilter1 & "," & timefilter2
Dim assumtimey1 As String = "T#"&time &"M12"
Dim assumtimey2 As String = "T#"&time2 &"M12"
'BRAPi.ErrorLog.LogMessage(si, scnfilter & workdaystimefilter & assumtimey1 & assumtimey2)
Dim i As Integer = api.Pov.ScenarioTypeId
Dim dt_workdays As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si,"Financial","E#ENT_TEST","Local",i,scnfilter,workdaystimefilter,"Periodic",True,"Amount <> 0 and Account IN('ACC_WORKDAYS')",8,False)
Dim dt_Assumptions_y1 As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si,"Financial","E#ENT_TEST2.Base,E#ENT_None","Local",i,scnfilter,assumtimey1,"Periodic",True,"Amount <> 0 and Account like '%_Y1'",8,False)
Dim dt_Assumptions_y2 As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si,"Financial","E#ENT_TEST2.Base,E#ENT_None","Local",i,scnfilter,assumtimey2,"Periodic",True,"Amount <> 0 and Account like '%_Y2'",8,False)
Dim connectionString As String = "OneStream SQL DB"
Dim sql As String = "Delete From OST_Workday.dbo.OST_PLP_ASSUMPTIONS"
'Execute Query On External DB
Using dbConnExt As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, connectionString)
If Not dt_workdays Is Nothing Then
BRAPi.Database.ExecuteSql(dbConnExt, sql, True)
BRApi.Database.SaveCustomDataTable(si, connectionString, "OST_Workday.dbo.OST_PLP_ASSUMPTIONS", dt_workdays, True)
BRApi.Database.SaveCustomDataTable(si, connectionString, "OST_Workday.dbo.OST_PLP_ASSUMPTIONS", dt_Assumptions_y1, True)
BRApi.Database.SaveCustomDataTable(si, connectionString, "OST_Workday.dbo.OST_PLP_ASSUMPTIONS", dt_Assumptions_y2, True)
End If
'Start Updating Entity Descriptions
Dim Assumptions_SQL As String = "Select distinct Entity From OST_WORKDAY.dbo.OST_PLP_ASSUMPTIONS"
Dim Assumptions As DataTable = BRAPi.Database.ExecuteSql(dbConnExt, Assumptions_SQL, True)
Dim Entity As String
Dim ent_mbr As Member
Dim EntDimType As Integer = 0
Dim Ent_Desc As String = Nothing
If Not Assumptions Is Nothing Then
For Each dr As DataRow In Assumptions.Rows
Entity = dr("Entity")
ent_mbr = api.Members.GetMember(EntDimType, Entity)
Ent_Desc = "'" & Entity & " - " & ent_mbr.Description & "'"
Dim UpdateSQL As String = "Update OST_WORKDAY.dbo.OST_PLP_ASSUMPTIONS SET Entity = " & Ent_Desc &" Where Entity = '" & Entity & "'"
BRAPi.Database.ExecuteSql(dbConnExt, UpdateSQL, True)
Next
End If
'End Updating Entity Descriptions
End Using
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 NamespaceBy following these steps, you can effectively transfer data from a OneStream cube to a SQL table, facilitating seamless integration and enabling better reporting and analysis. Understanding the cube structure, identifying the target SQL table, extracting and transforming the data, establishing a connection to the SQL database, loading the data, validating the transfer, and optionally automating the process are key components of this data-loading workflow. Implementing these practices will empower your organization to make data-driven decisions with accurate and up-to-date information.
