Transferring Data From OneStream Cube to SQL Table
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.
Join the DZone community and get the full member experience.Join For Free
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 Namespace
By 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.
Opinions expressed by DZone contributors are their own.