Good Morning,
We are using AX 2012R3 CU9 with Management Reporter CU16.
The issue we have is the performance of the query reading security information from AX to MR. It is executing every 5 minutes and reading a lot of data (IO up to 1000 MB/sec), returning over 110000 records. Here is the beginning of the query:
"select T.USERKEY, T.NAME, T.ALIAS, T.DOMAIN, T.SECURITYID, MAX(T.GENERALLEDGERROLETYPE) GENERALLEDGERROLETYPE, T.COMPANYKEY, T.ISENABLED
from (
select UI.RECID USERKEY, UI.NAME, UI.NETWORKALIAS ALIAS, UI.NETWORKDOMAIN DOMAIN, SID SECURITYID,
CASE st.AOTNAME
WHEN 'SysSecSecurityMaintain' THEN 5
WHEN 'LedgerBalanceSheetDimMaintain' THEN 4
WHEN 'LedgerFinancialJournalReportBGenerate' THEN 3
WHEN 'LedgerBalanceSheetDimPrintGenerate' THEN 3
WHEN 'LedgerViewFinancialStatement' THEN 2..."
I read multiple posts about that query. Unfortunately the only solution was to reduce the frequency of running that update:
http://www.summitgroupsoftware.com/blog/management-reporter-data-mart-synchronization-frequency
The name of the task responsible for that in MR setup tables is 'AX 2012 Companies to Company'. Do you know if this task is only performing the user security query or some other information? I wonder if reducing the frequency to (for example) 1 hour would be a good idea or can it influence other areas that security.
Maybe someone has more information what exactly is executed under: 'AX 2012 Companies to Company'?
Or maybe do you have other solutions for this heavy query?
Best regards,
Maciej
