Forum Discussion

particle's avatar
particle
New Contributor II
3 years ago

FdxExecuteCubeView parameters

If I build a cubeview that contains the desired POV and functions on its own, using FdxExecuteCubeView seems to work great. However, there is no documentation available on actually using the FDX functions and I can find no functional examples of how to pass the various filters/members from the FdxExecuteCubeView function to the cubeview it's actually calling.

For example, I can enter T#2022M10 as part of the POV for the cube view and then FdxExecuteCubeView will create the table. However, if I pass the timeMemFilter a value of T#2022M10, I get no data. It seems obvious that I need to put something in the cube view POV to tell it to use this value but there's no indication of what that might be.

Am I missing something obvious here? Is there some missing documentation or fundamental functionality that I'm unfamiliar with?

Data Integration
  • JackLacava's avatar
    JackLacava
    3 years ago

    You're not missing anything obvious, it's the documentation that's missing...

    From some internal resources, i see a comment like this over all the "filter" parameters of FdxExecuteCubeView:

     

    'when the extracted cube view references an entity parameter called |!FDXEntity!|, this field needs to be set to the name of the entity dimension the extracted entities exist in
'if the cube view does not use an entity parameter called |!FDXEntity!| then this field can be left blank "" or as String.Empty

Dim entityDimName As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references an entity parameter called |!FDXEntity!|, this field needs to be set to an entity member filter that controls the extracted entities based on the member filter
'if the cube view does not use an entity parameter called |!FDXEntity!| then this field can be left blank "" or as String.Empty

Dim entityMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references an scenario parameter called |!FDXScenario!|, this field needs to be set to the name of the scenario dimension the extracted scenarios exist in
'if the cube view does not use a scenario parameter called |!FDXScenario!| then this field can be left blank "" or as String.Empty

Dim scenarioDimName As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references a scenario parameter called |!FDXScenario!|, this field needs to be set to an scenario member filter that controls the extracted scenarios based on the member filter
'if the cube view does not use a scenario parameter called |!FDXScenario!| then this field can be left blank "" or as String.Empty

Dim scenarioMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references a time parameter called |!FDXTime!|, this field needs to be set to a time member filter that controls the extracted time periods based on the member filter
'if the cube view does not use a time parameter called |!FDXTime!| then this field can be left blank "" or as String.Empty

Dim timeMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

     

    So I assume those fields do nothing unless those Parameters are present on the cube view.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    You're not missing anything obvious, it's the documentation that's missing...

    From some internal resources, i see a comment like this over all the "filter" parameters of FdxExecuteCubeView:

     

    'when the extracted cube view references an entity parameter called |!FDXEntity!|, this field needs to be set to the name of the entity dimension the extracted entities exist in
'if the cube view does not use an entity parameter called |!FDXEntity!| then this field can be left blank "" or as String.Empty

Dim entityDimName As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references an entity parameter called |!FDXEntity!|, this field needs to be set to an entity member filter that controls the extracted entities based on the member filter
'if the cube view does not use an entity parameter called |!FDXEntity!| then this field can be left blank "" or as String.Empty

Dim entityMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references an scenario parameter called |!FDXScenario!|, this field needs to be set to the name of the scenario dimension the extracted scenarios exist in
'if the cube view does not use a scenario parameter called |!FDXScenario!| then this field can be left blank "" or as String.Empty

Dim scenarioDimName As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references a scenario parameter called |!FDXScenario!|, this field needs to be set to an scenario member filter that controls the extracted scenarios based on the member filter
'if the cube view does not use a scenario parameter called |!FDXScenario!| then this field can be left blank "" or as String.Empty

Dim scenarioMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

'when the extracted cube view references a time parameter called |!FDXTime!|, this field needs to be set to a time member filter that controls the extracted time periods based on the member filter
'if the cube view does not use a time parameter called |!FDXTime!| then this field can be left blank "" or as String.Empty

Dim timeMemFilter As String = args.NameValuePairs.XFGetValue("", String.Empty)

     

    So I assume those fields do nothing unless those Parameters are present on the cube view.

  • particle's avatar
    particle
    New Contributor II
    3 years ago

    Peculiarly, FDXTime does seem to work but FDXEntity doesn't. In this case, I don't need entity to be passed but it does make me wonder how the usage between them actually differs.

  • particle's avatar
    particle
    New Contributor II
    3 years ago

    I notice that FDXEntity is referenced twice in the comments that you shared. I suspect the second instance is a typo and FDXEntity is the entityDimName while there's another parameter for passing the entityMemFilter.

    • JackLacava's avatar
      JackLacava
      Honored Contributor
      3 years ago

      No, it's the same for FDXScenario. I expect it's because that, if you use the parameter, the cube view gets both dimension and member filter from the parameter itself. I suspect your troubles might come from not having "E#" in the filter...?

      • particle's avatar
        particle
        New Contributor II
        3 years ago

        Hm, mixed results. If I use |!FDXEntity!| in a row, it fails, If I use it in the POV, it succeeds. I don't know why that would be (beyond "it's a data unit thing") but I think I read that the FDX function will intelligently run a separate CV query for every member in the filter and then append all the results into a single dataset. I'll have to do a row count on the output of each method to see if they're really the same.

        I do see why it's referenced twice in the documentation though. The cube view can take an entity filter without needing to specify the entity dimension but the FDX function cannot. So, for FDXEntity to produce a value, both entityDimName and entityMemFilter have to specified. It was two requirements but only produces the member filter as output.

  • Sergey's avatar
    Sergey
    Contributor III
    2 years ago

    Hi ! I'm sorry to dig into this topic, but I get the same error : I have the entity dimension set as a parameter in my cube view POV : |!DashBoardEntity_Exec!|. It's an input parameter with only "Houston" in it.

    This Cube View with this parameter will not work. However the same cube view but the POV set as 'Houston' (without the parameter then) will work.

    What is the configuration needed with these |!FdxEntity!| ?

    • particle's avatar
      particle
      New Contributor II
      2 years ago

      This parameter only works if you're calling the cube view using the FdxExececuteCubeView function from a business rule. It's not used if you're looking to just execute a cube view for normal reporting situations.

      FDX functions are for "Fast Data eXtracts" so they're useful if you're looking to do exactly that but they're difficult to leverage because there's no documentation. This thread is the most information I've ever seen on one and it's still missing a lot to properly leverage it, not to mention all of the others.

  • Tom's avatar
    Tom
    New Contributor III
    2 years ago

    I"m having trouble with this function FdxExecuteCubeView, anyway you could post your code ?  a working example would greatly help.  Thanks

    • Gidon_Albert's avatar
      Gidon_Albert
      Contributor II
      2 years ago

      Here's an example for you Tom.

      The code below will return a data table with data from the following cube view:

      The data table will have the following columns:  Cube, Entity, Parent, Cons, Scenario, Time, View, Account, Flow, Origin, IC, UD1, UD2, UD3, UD4, UD5, UD6, UD7,UD8, VCY, VDelta_PY, VDelta_Pct_PY_100

      The last three columns (starting with "V") are the amounts (i.e. columns) from the cube view.

      The data table will initially have 18 rows, one for each data cell in the cube view.

      The filter in the code is limiting the results to only rows that have "Op_Rev" in the Account column and "None" in UD8 column. This will limit the number of rows returned to a single row with a value of 499,272,231 (the top left cell in the cube view).

       

       

      'Get Name Value Pairs
Dim cvName As String = args.NameValuePairs.XFGetValue("Cube View", String.Empty)
Dim acName As String = args.NameValuePairs.XFGetValue("Account", "")
Dim u8Name As String = args.NameValuePairs.XFGetValue("UD8", "None")
Dim value As Decimal = 0
				
'While this is not used, it shows how to get the Entity Dim Name from the Entity member name
'Dim enName As String = args.NameValuePairs.XFGetValue("Entity", String.Empty)
'Dim enID As Integer = BRApi.Finance.Members.GetMemberId(si, dimtypeid.Entity, enName)		
'Dim enDimID As Integer = BRApi.Finance.Members.GetDimIdFromMemberId(SI, dimtypeid.Entity, enID)	
'Dim enDimName As String = BRApi.Finance.Dim.GetDims(si, dimtypeid.Entity).Find(Function(x) x.DimPk.DimId = enDimID).Name
				
Dim nvbParams As New NameValueFormatBuilder 'this can be empty
Dim filter As New System.Text.StringBuilder
filter.Append("Account=" & "'" & acName & "'")
filter.Append(" AND ")
filter.Append("UD8=" & "'" & u8Name & "'")
				
Dim cvDT As DataTable = BRApi.Import.Data.FdxExecuteCubeView(si, cvName, "", "", "", "", "", nvbParams, False, True, filter.ToString, 8, False)

If cvDT.Rows.Count > 0
	value = cvDT.Rows(0).Item("vCY").ToString
End If

       

       

      The last three lines in the code simply select the value from the "VCY" column. This value can then be returned to a parameter or another function that calls the BR.

      Hope this helps.