Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

XDS filtering in D365

Posted on by 500

Hi Experts,

I made an XDS policy which is used to filter the custom dimensions in D365,

The query used for the policy uses a table which joins to a Custom tempdb table. An XDS method was created on the temp table with refresh frequency set as PerInvocation.

Issue: The policy works as expected in D365 DEV boxes and not in TEST instance? does any one had similar issue ? or have any suggestions pls on the possible cause?

The setup in DEV and test is same

The code or objects are same in dev and test

the only difference i found was on the query creation, in dev the query uses tempdb and in TEST it does not. below is the example

Query in DEV:

SELECT T1.RECID FROM DIMENSIONFINANCIALTAG T1 
WHERE (T1.PARTITION=5637144576) 
      AND EXISTS (SELECT 'x' FROM tempdb."DBO".t20085_B9185767C2C7463E8B51EF747CF631E4 T2 WHERE          ((T2.PARTITION=5637144576) AND (((T1.RECID=T2.DIMFINANCIALTAGRECID) AND                 (T1.FINANCIALTAGCATEGORY=T2.FINANCIALTAGCATEGORY)) 
      AND (T1.VALUE=T2.VALUE)))) ORDER BY T1.FINANCIALTAGCATEGORY,T1.VALUE

Query in TEST:

SELECT T1.RECID FROM DIMENSIONFINANCIALTAG T1

WHERE (T1.PARTITION=5637144576)

AND EXISTS (SELECT 'x' FROM "DBO".t18113_C08B372B81D74728A61218434319219B T2 WHERE ((T2.PARTITION=5637144576) AND (((T1.RECID=T2.DIMFINANCIALTAGRECID) 
AND (T1.FINANCIALTAGCATEGORY=T2.FINANCIALTAGCATEGORY))

AND (T1.VALUE=T2.VALUE)))) ORDER BY T1.FINANCIALTAGCATEGORY,T1.VALUE

Please note that when i set the refreshfrequency to Persession, it worked earlier in both DEV and TEST. but the business requirement was to have it done for perinvocation. 

*This post is locked for comments

  • Verified answer
    dileep varma Profile Picture
    dileep varma 500 on at
    RE: XDS filtering in D365

    Hi Andre,

    Thanks for your kind support.

    I was able to figure out the issue and the reason why my policy is not working in TEST.

    The difference between both environments was, the default system language in DEV was EN-US and in TEST is NL-BE.

    The Dimension lookup is using different tables/views when translations exist for the Dimensions.  I have created XDS policy queries initially on the table "DimensionFinancialtag" and the XDS policy was triggered when the system language was EN-us. When i switched the language to NL-BE, the policy is no more triggered, for this i have to create XDS policies query using the view DimAttrTransFinancialtag.

    With these changes, XDS policies are getting applied as intended.

    Thanks.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,134 Super User 2024 Season 2 on at
    RE: XDS filtering in D365

    Hi Varma,

    I haven't seen this issue before. I assume you are not testing it with the system administrator role. This role is bypassing XDS as well. Can you check if there are possible more XDS policies for the same table? Or did you rename and move the policy to another model? Deployment is adding packages; not removing them.

    Did you check if the contents of the temporary table is correct? What exactly is not working? Do you get all records on the form or none?

  • dileep varma Profile Picture
    dileep varma 500 on at
    RE: XDS filtering in D365

    Hi Andre,

    Thanks for the message.

    Yes the test instance was updated with an deployable package. I have done an full build, Full DB synchronize and also perfomed IISReset on DEV. The Xds policy is working fine for some reason in dev instance.

    In Test instance, filtering is not working anymore even though the refresh frequency was set to persession or perinvocation. :(

    The custom temp table created is filled in with the details of the custom dimensions from the DimensionFinancialTag Table.

    Then the table is used in the query by joiining to the dimensionFinancialtag Table.

    Below is the example code in the table method (XDS) which is inserting the records.

    CustomDimensionFinancialTagFilter  dimFilter;

    DimensionFinancialTag           dimensionFinancialTag;

    Unchecked(Uncheck::XDS)

    {

               insert_recordset dimFilter(FinancialTagCategory, Value, DimFinancialTagRecId)

                   select FinancialTagCategory, Value, RecId from dimensionFinancialTag

                       where (dimensionFinancialTag.FinancialTagCategory !=

                                DimensionAttribute::findByName(#CustomDimension1,

                                false).financialTagCategory()

                                && dimensionFinancialTag.FinancialTagCategory !=

                               DimensionAttribute::findByName(#CustomDimension2,

                               false).financialTagCategory());

    }

    return RefreshFrequency::PerSession;

    i could not find any way to identify the problem in TEST. Please let me know for any suggestions.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,134 Super User 2024 Season 2 on at
    RE: XDS filtering in D365

    Hi Varma,

    I assume you used a deployable package to get the change from persession to perinvocation on the TEST instance? When you did change the XDS policy on the DEV box, did you build, but also performed a DBsync and possibly an additional IISreset? If not, do this and see if it is still working on the DEV instance. XDS policies ar applied correctly during a DBsync.

    What happens if you revert it back to persession on the test instance? What exactly is the contents of the temporary table?

    The difference between the DEV box and the TEST is that TEST is using Azure SQL services where the DEV is using on premise SQL server. Never noticed the query difference using tempdb, but it can be different on Azure SQL.

  • dekaasboer Profile Picture
    dekaasboer 1,530 on at
    RE: XDS filtering in D365

    nope, didn't read correctly

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans