Exporting Data automatically to outside of OneStream

Hi Mike

Sorry for the late answer. The filter works like in an sql query's where statement: "Account = 'Test' or Flow='None'"

Cheers

The data management sequence is needed in order to use the task scheduler to run the export each day/week/month.  The DM sequence calls a DM step which runs a business rule.  the business rule contains the logic to pull the data out of the cubes/BIBlend and replicate the data out of onestream's database into our internal database server.

Relationship table.

We use a data adapter to query the Relationship table so that we can see exactly what the relationships look like. Then we reference the that data adapter to get the SQL statement into the EBR.

This is the query that pulls the relationships ("ADU_Dim_Metadata":

WITH Rec
AS (SELECT Member.MemberID, Name, Description, 0 as ParentID, 1 as Level, DimTypeID, CAST(0 as DECIMAL) as Agg 
      FROM Member
      WHERE Member.MemberID = (select MemberID from Member where Name = '|!ADU_Param_Select_Top_Member!|' )
       
UNION ALL

SELECT m.MemberID, m.Name, m.Description, this.ParentID, parent.Level + 1, m.DimTypeID, CAST (UDAggWeight AS DECIMAL) as Agg 
    FROM Rec parent
    INNER JOIN Relationship this ON this.ParentID = parent.MemberID
    INNER JOIN Member m ON this.ChildID = m.MemberID
    )

SELECT distinct 
'' as RowID,
'|!ADU_Param_Select_Dim_Name!|' as Dim_Name, 
'|!ADU_Param_Select_Dim_Type!|' as Dim_Type, 
Rec.MemberID, Name as Member_Name, Description as Member_Description, ParentID, 
(select Name from Member where MemberID=ParentID) as Parent_Name, 
'0' as Level, 
CASE
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =0 THEN 'Group'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =1 THEN 'Revenu'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =2 THEN 'Expense'
  WHEN Rec.DimTypeID = 5AND mp.DecimalValue =3 THEN 'Asset'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =4 THEN 'Liability'
  WHEN Rec.DimTypeID = 5AND mp.DecimalValue =5 THEN 'Flow'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =6 THEN 'Balance'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =7 THEN 'BalanceReccuring'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =8 THEN 'NonFinancial'
  WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =9 THEN 'DynamicCalc'
  WHEN Rec.DimTypeID <> 5 THEN 'N/A'
  ELSE 'Other'
END as Acct_Type, 
Agg,
CURRENT_TIMESTAMP as Update_Time_Stamp

FROM REC 

LEFT JOIN (SELECT * FROM MemberProperty WHERE PropertyID = 100) mp on Rec.MemberID = mp.MemberID

--order by Level;

This is the EBR that gets the SQL query from the data adapter, replaces the parameters, and executes the query to push the data out to an external database:

#Region "Metadata Transfer"

		Private Function MetaData_Transfer (ByVal si As SessionInfo, dimName As String, dimType As String, topMember As String, StageTableName As String) As Boolean 
'			system.Threading.Thread.Sleep(10000)
			Dim AdapterName As String = "ADU_Dim_Metadata"
			'Get the sql from the data adapter
			Dim AdapterContents As String = BRApi.Database.LookupRowFieldValue(si, "App", "DashboardAdapter", "Name='" & AdapterName & "'", "SQLQuery", "")
						
			'send the Adapter Contents to the log
			brapi.ErrorLog.LogMessage(si,"AdapterContents before changes: " & AdapterContents.ToString)
			'Change Top Member Filter
			AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Top_Member!|", topMember, True)
			'Change Dim Name Filter
			AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Dim_Name!|", dimName, True)
			'Change DIm Type Filter
			AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Dim_Type!|", dimType, True)
			'Send new Adapter Contents string to the log
			brapi.ErrorLog.LogMessage(si,"AdapterContents: " & AdapterContents.ToString)
			
			'Get metadata into a table
			Dim objDbConnInfoApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
			Dim dt As DataTable = BRApi.Database.ExecuteSql(objDbConnInfoApp, AdapterContents, False)		
		
			BRApi.Database.SaveCustomDataTable(si, externalConnector, StageTableName, dt, True)

			Return True
			
		End Function
#End Region