Forum Discussion

Bella_Yu's avatar
Bella_Yu
New Contributor III
6 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 dimension filters, UD1,/UD2/UD4 dimension filters. And filters could include plenty of values. How can I make it work? I try to defined it in below manner, but the result is not filtered as expected.

 

Dim additionalFilter As String = "Account = 'EBIT' OR ACCOUNT = 'CAPEX'" 

'Execute FDX query

dt = BRApi.Import.Data.FdxExecuteDataUnit(
        si,
        "Total_Magna",      ' Cube name
         "E#Parent1,E#Parent2",         
         "C#USD",
         ScenarioTypeId.Actual,
         "S#Actual",
         "T#2025M1",
         "V#Periodic",
        True,
        additionalFilter,
        100000,            
        False
  )

 

 

XFBR

9 Replies

  • sameburn's avatar
    sameburn
    Icon for OneStream Employee rankOneStream Employee
    5 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
      5 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
        3 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

    Also please take care with the ParallelQueryCount variable.  You have set yours to be 100,000!

    This cannot exceed 128.  I would recommend setting this at a safe level of something like 8

    Similarly, you do not need to pass in the Dimension Token for Consolidation (C#USD becomes "USD" or "Local") and View (V#Periodic becomes "Periodic") Dimensions.  We only need to pass the Dimension Token into dimensions that stipulate we can pass in a filter e.g. Entity (E#), Scenario (S#), Time (T#), etc (see example snippet below)

    DataTable dt = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics);

    There are also some Tech Talks available on Navigator to better explain the Fast Data Extract (FDX) methods and a chapter in the OneStream Planning book

    Hope this helps.