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
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.
nope, didn't read correctly
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.
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.
insert_recordset dimFilter(FinancialTagCategory, Value, DimFinancialTagRecId)
select FinancialTagCategory, Value, RecId from dimensionFinancialTag
where (dimensionFinancialTag.FinancialTagCategory !=
&& dimensionFinancialTag.FinancialTagCategory !=
i could not find any way to identify the problem in TEST. Please let me know for any suggestions.
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?
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.
Business Applications communities