Forum Discussion

Sai_Maganti's avatar
Sai_Maganti
Contributor II
3 years ago

FdxExecuteCubeView Function

  1. What is the purpose of timeMemFilter, entityMemFilter and scenarioMemFilter parameters for the below function as these will be defined in the cube view?
  2. Is there a way to extract aggregated members as a table for the Account and all the UD1 to UD8 dims. Cube views have a limitation of 4 dims on the rows. This is required for an auto feed using OS REST api calls for an external system. We can export base data, dim structures using api calls and perform the aggregations in the external system but wanted to explore the option of aggregated data exports.

 

BRApi.Import.Data.FdxExecuteCubeView(
si, 
cubeViewName, 
entityDimName, 
entityMemFilter, 
scenarioDimName, 
scenarioMemFilter, 
timeMemFilter, 
nvbParams, 
includeCellTextCols, 
useStandardFactTableFields, 
filter, 
parallelQueryCount, 
logStatistics
)

 

Admin
  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    Hello,
    The additional arguments in the FDX query [ timeMemFilter, entityMemFilter, scenarioMemFilter] are so that you can run the same cube view multiple times with all the distinct combinations of Time/Entity/Scenarios that you specify. This would be useful if you have a cube view that looks at a Single Time Period, Single Entity, Single Scenario , and you wanted to 'materialise' it multiple times for various combinations of time/entity/scenarios.  The results from each cube-view iteration would get appended in the table.  Otherwise you would have to create a potentially monster-sized cube view just for the purposes of data extraction.   The list of distinct combinations of Time/Entity/Scenario in this list you supply, are iterated in parallel (up to the maximum threads you specify). 

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    As for aggregating, it would be very inefficient to have a cube view that shows all the base-level details for every Account/Flow/IC/UD1-8 and then aggregate it in the FDX / Business rule into a more summarized version. You could do this by using a Dictionary object (by AccountId, Amount ) and then simply aggregating the Data Rows returned from the FDX query into a Dictionary object. Then you would have a simple list of totals by just Account.  Or you could use a ValueTuple object if you wanted to combine multiple columns into one key.    But I may be misunderstanding the question here since I wouldn't recommend aggregating the results of an FDX query.

    • Sai_Maganti's avatar
      Sai_Maganti
      Contributor II
      3 years ago

      Chris

      I don't want to use a cube view for base level data or aggregate the results of an FDX query. My question was, is there a possibility to extract 'aggregated' data (Account and UD1 to UD8). The only way I found out was to use a cube view to define members at a higher level and then use an FDX query to extract. However, I can't use all the dimensions in a cube view right?

      Anyways we have now set up to extract base data and dimension hierarchies via API calls and feed it to our Power BI models.

      Thanks again for your inputs.

      Best

      Sai

      • Sridhar_M's avatar
        Sridhar_M
        Contributor
        2 years ago

        Hi Sai_Maganti 
        Do you have an example code to extract data to the SQL table using FDX queries ?

        Thank you

        Sridhar 

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    You could use the FDX for a DataUnit (FdxExecuteDataUnit) instead of a CubeView, however that would always return base-level intersections.  You would have to aggregate from the DataTable using a SELECT SUM / GROUP BY type query to perform the aggregation to certain top-level members.

    If you wanted to do it without doing SQL-style queries, then you would have to get your base-level data first into a DataBuffer. Then you create a 2nd databuffer, and use the DataBuffer SetCell() method with the Aggregate option = True.

    Below is a snippet that extracts base-level data out of a DataUnit in the GolfStream application, and aggregates it up into the parent account A#[61000],  without doing SQL.

    HOWEVER , it works on a single data unit at a time, since it uses the GetDataBufferDataCells() .
    The FDX method would retrieve all the data using member NAMES instead of member IDs, and with Member Names you cannot perform native DataBuffer aggregation using SetCell, so you would have to aggregate using a Dictionary object with lots of strings, which would be very inefficient.

    Disclaimer--- Code snippet below is for explanatory purposes only and not a supported solution ---


  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago
    Public Class MainClass

	Private dctLastMemberName As New Dictionary(Of DimTypeId, (Integer, String))

	Private Function GetMemberScript(ByVal si As SessionInfo, ByVal cpk As DataBufferCellpk) As String
		Dim sb As New Text.StringBuilder(1000)
		For Each dt As DimType In DimType.GetRightHandDimTypes()
			Dim strMemberName = String.Empty
			If dctLastMemberName.ContainsKey(dt.id) Then
				If dctLastMemberName(dt.id).Item1 = cpk.Item(dt.id) Then
					strMemberName = dctLastMemberName(dt.id).Item2
				Else
					Dim memberId As Integer = cpk.Item(dt.id)
					strMemberName = BRApi.Finance.Members.GetMemberName(si, dt.Id, memberId)
					Dim vt As ValueTuple(Of Integer, String) =(memberId, strMemberName)
					dctLastMemberName(dt.id) = vt
				End If
			Else
				Dim memberId As Integer = cpk.Item(dt.id)
				strMemberName = BRApi.Finance.Members.GetMemberName(si, dt.Id, memberId)
				Dim vt As ValueTuple(Of Integer, String) =(memberId, strMemberName)
				dctLastMemberName.Add(dt.id, vt)
			End If

			sb.Append(strmemberName & ",")
		Next

		Return sb.ToString
	End Function

	Private Sub ExtractSampleData(ByVal si As SessionInfo)
		Dim dupk As New DataUnitPk()
		dupk.CubeId = BRApi.Finance.Cubes.GetCubeInfo(si, "GolfStream").Cube.CubeId
		dupk.EntityId = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Entity, "Houston")
		dupk.ParentId = DimConstants.Unknown
		dupk.ConsId = ConsMemberId.Local
		dupk.ScenarioId = BRApi.Finance.Members.GetMemberId(si, DimTypeId.Scenario, "Actual")
		dupk.TimeId = TimeDimHelper.GetIdFromName("2018M2")
		Dim id_TopAcctMember = BRApi.Finance.Members.GetMemberId(si, dimTypeId.Account, "61000")
		Dim iTopCellPk As New DataBufferCellPk(DimConstants.All)
		iTopCellPk.AccountId = id_TopAcctMember
		Dim lstCells As List(Of DataCell) = brapi.Finance.Data.GetDataBufferDataCells(si, dupk, ViewMemberId.YTD, iTopCellPk, False, True)
		Dim resultBuf As New DataBuffer()
		For Each cellToAgg In lstCells
			cellToAgg.DataCellPk.AccountId = id_TopAcctMember
			resultBuf.SetCell(si, cellToAgg, True)
		Next

		Dim strFolder As String = brapi.FileSystem.GetFileShareFolder(si, FileShareFolderTypes.ApplicationOutgoing, Nothing)
		Dim strFilename As String = "testExtract.csv"
		Dim strFullPath As String = path.Combine(strFolder, strFilename)
		Using fs As New FileStream(strFullPath, Filemode.Create, FileAccess.Write)
			Using sw As New StreamWriter(fs, Text.Encoding.Default)
				For Each aggCell In resultBuf.DataBufferCells.Values
					sw.WriteLine(Me.GetMemberScript(si, aggCell.DataBufferCellPk) & "," & aggCell.CellAmount.XFToStringForFormula)
				Next
			End Using
		End Using
	End Sub

	Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
		Try
			Select Case args.FunctionType
				Case Is = ExtenderFunctionType.Unknown
					Me.ExtractSampleData(si)
			End Select

			Return Nothing
		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try
	End Function
End Class