Forum Discussion

Bella_Yu's avatar
Bella_Yu
New Contributor III
7 hours ago

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
Icon for OneStream Employee rankOneStream Employee
6 hours ago

Hi Bella_Yu​ 

There are already a few posts on this on OneCommunity e.g. Account filter in FdxExecuteDataUnit | OneStream Community

In short, your entire filter must be valid; else the BRApi returns the full Dataset since the filter is not valid.  You can check this by logging a row count of the DataTable returned for each filter pass as you develop

To include multiple members you can use the equivalent of a SQL IN Clause e.g. "Account IN ('EBIT', 'CAPEX')", etc

Hope this helps

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

FYI - there is also a solution available on Solution Exchange (CommunitySolutions) called Fast Data Extract Extended, that does exactly this filtering for you of the non Data Unit Dimensions e.g. A#-U8# and uses the FDX Data Unit BRApi's.

 

  • Bella_Yu's avatar
    Bella_Yu
    New Contributor III
    5 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
      4 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
        3 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