Forum Discussion

Bella_Yu's avatar
Bella_Yu
New Contributor III
21 hours ago
Solved

More Filters in FdxExecuteDataUnit

Hello, I'm building Dashboard Data Set business rule to retrieve data from cubes. Besides the member filters in FdxExecuteDataUnit parameters, I'd like to add some additional filters, like account ...
XFBR
  • sameburn's avatar
    sameburn
    18 hours ago

    Does your DataTable return any rows when you pass in an empty string as a filter e.g.

    dt = BRApi.Import.Data.FdxExecuteDataUnit(
        si,
        "Total_Magna",      ' Cube name
         "E#Parent1,E#Parent2",         
         ConsMember.Local.Name,
         ScenarioTypeId.Actual,
         "S#Actual",
         "T#2025M1",
         ViewMember.Periodic.Name,
        True,
        String.Empty,
        8,            
        False

    If so, check that the accounts you are trying to filter on actually exist in the Data returned.  I would recommend building up your logic 1 filter at a time e.g. check EBIT exists, then add EBIT to filter, then check SALESTOT exists, then add SALESTOT to filter and check filter working correctly, etc

    Also please observe you should not be passing in C# and V# into the BRApi (please see earlier comments on these dimensions and what the BRApi is expecting to be passed into it, this has been corrected in above snippet)

    Edit: It should be noted that the filter on non-data unit dimensions is for base members only.  Therefore I suspect EBIT and SALESTOT are parents, not base members of your Account dimension.  Which might explain why your filter is not behaving as you would expect

Bella_Yu's avatar
Bella_Yu
New Contributor III
19 hours ago

Thanks so much for your reply.

I read the posting and tried the manner below, it's not working. 

My goal is to create a data adapter used in OS Connector in Fabric. The command type of the data adapter is Method, it calls this new BR to retrieve data from cubes,

 

sameburn's avatar
sameburn
Icon for OneStream Employee rankOneStream Employee
18 hours ago

Does your DataTable return any rows when you pass in an empty string as a filter e.g.

dt = BRApi.Import.Data.FdxExecuteDataUnit(
        si,
        "Total_Magna",      ' Cube name
         "E#Parent1,E#Parent2",         
         ConsMember.Local.Name,
         ScenarioTypeId.Actual,
         "S#Actual",
         "T#2025M1",
         ViewMember.Periodic.Name,
        True,
        String.Empty,
        8,            
        False

If so, check that the accounts you are trying to filter on actually exist in the Data returned.  I would recommend building up your logic 1 filter at a time e.g. check EBIT exists, then add EBIT to filter, then check SALESTOT exists, then add SALESTOT to filter and check filter working correctly, etc

Also please observe you should not be passing in C# and V# into the BRApi (please see earlier comments on these dimensions and what the BRApi is expecting to be passed into it, this has been corrected in above snippet)

Edit: It should be noted that the filter on non-data unit dimensions is for base members only.  Therefore I suspect EBIT and SALESTOT are parents, not base members of your Account dimension.  Which might explain why your filter is not behaving as you would expect

  • sameburn's avatar
    sameburn
    Icon for OneStream Employee rankOneStream Employee
    17 hours ago

    You can achieve your use case with the FDX Extended solution I mentioned earlier on this post, this will Group (aggregate) this data for you for a given Dimension e.g. A#.   

    However since you want to aggregate 4 members in a single dimension; you would need 4 separate outputs e.g. 1 for EBIT, 1 for SALESTOT, 1 for ROIC_DIV_Y and 1 for ADJEBIT (assuming these are all parent members in your Account dimension).  Alternatively you can use a Data Management Export step which will achieve the same

    The FDX Data Unit methods return base members only for A#-U8# because this is how the data in the cube is stored.  The only aggregated data you can output using this method is for the Entity dimension. 

    Therefore filters can only be applied for Base Members

    Hope this helps demystify

    • Bella_Yu's avatar
      Bella_Yu
      New Contributor III
      16 hours ago

      Yes, when I put base account members in filter string, it works. But when I put parent account members like SALESTOT, it doesn't work. In my business case, I need filters on account + UD1 + UD2 + UD4 + UD7, parent members and also base members.

      FDX Extended solution may not work in my case because large amounts of data needs to be extracted with plenty of filters.

      I was using data management extract steps, now I want to build a data adapter used for OneStream connector in Fabric (GetCustomAdapterData method in OS Connector). That's why I try to develop the business rule, so the data adapter can call it.

      Do you have suggestions in this case?

      • sameburn's avatar
        sameburn
        Icon for OneStream Employee rankOneStream Employee
        16 hours ago

        Probably Data Management Export is your path of least resistance.  But the amount of overhead created by passing in Parent and Base members in a filter for the same dimension will definitely increase the time it takes to generate the output this way.

        If you remove the requirement to filter by Parents and filter by Base members only e.g. A#Parent.Base instead of A#Parent, then you have a lot more options to automate this and reduce the time it takes to generate the output

        Might be worth reaching out to the Remote Consulting Department via Support for some help with your integration requirement.  Please be advised this would be likely to be considered as a consulting (billable) activity

    • Bella_Yu's avatar
      Bella_Yu
      New Contributor III
      16 hours ago

      In short, I want to implement the same extract output as we have in data management extract steps. Here are the filters in DM extract steps

      • scenario (multiple scenarios)
      • account (50+ base and parent accounts, )
      • view (Periodic, YTD)
      • Time:  
        T#YearPrior1(2025M10).base, T#2025M10.AllPriorInYearInclusive, T#YearNext1(2025M10)Period(Q1) ,T#YearNext1(2025M10)Period(Q2),T#YearNext1(2025M10)Period(Q3),T#YearNext1(2025M10)Period(Q4), T#YearNext2(2025M10),T#YearNext3(2025M10),T#YearNext4(2025M10),T#YearNext5(2025M10),T#YearNext6(2025M10)
      • UD1 (U1#UD1_TOT.base, U1#NotDefined)
      • UD2 (U2#UD2_TOT.base, U2#NotDefined)
      • UD4 (U4#UD4_TOT.base, U4#NotDefined)
      • UD7 (parent UD7 member)