FdxExecuteCubeView Function

Sai_Maganti
Contributor II
  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
)

 

8 REPLIES 8

ChrisLoran
Valued Contributor

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). 

Thanks Chris understood

Best

Sai

Stan
New Contributor II

  

ChrisLoran
Valued Contributor

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.

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

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

Thank you

Sridhar 

ChrisLoran
Valued Contributor

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
Valued Contributor

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