05-13-2022 10:17 AM - last edited on 05-25-2023 06:59 AM by JackLacava
BRApi.Import.Data.FdxExecuteCubeView(
si,
cubeViewName,
entityDimName,
entityMemFilter,
scenarioDimName,
scenarioMemFilter,
timeMemFilter,
nvbParams,
includeCellTextCols,
useStandardFactTableFields,
filter,
parallelQueryCount,
logStatistics
)
05-27-2022 10:16 AM
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).
06-06-2022 10:21 AM
Thanks Chris understood
Best
Sai
10-16-2023 11:15 AM - edited 10-16-2023 11:56 AM
05-27-2022 10:20 AM
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.
06-06-2022 10:30 AM - edited 06-06-2022 10:32 AM
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
06-12-2023 10:37 AM
Hi @Sai_Maganti
Do you have an example code to extract data to the SQL table using FDX queries ?
Thank you
Sridhar
06-07-2022 07:20 AM
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 ---
06-07-2022 07:24 AM
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