We have been having a continuing problem with Management Reporter where it blocks users printing checks and posting. It works fine for about 3 days and then on the fourth or fifth day it starts blocking other transactions.
Cold this be a timeout on the inserts into the datamart? Has anyone seen this behavior. Are there certain SQL counters we can check?
*This post is locked for comments
This problem seems to have been resolved after we updated the Statistics on SY40100
Do you know what the new count query looks like? Do have a copy of the query that it uses?
I want to say there have been changes to the count functionality throughout the versions. Are you running MR2012 CU8 (2.1.8001.00)? If not, you may want to upgrade to see if that helps.
I see a problem in that we have 10 years on file. Even though 9 are closed, they are still part of the count query that is part of GL Transaction to fact Processing. If that query was modified to include only open years, it would run in about 10 seconds against our 100000 records that are currently trying to be brought over, instead it keeps timing out after 60 minutes and the size of the query keeps growing. Immediately after rebuilding the datamart the query does complete, but as the trickle lags the posted records, the query runs slower and slower until it times out.
We currently get 130 records from this part of the query, and that oes not even make sense because we are comparing against GL20000 which has only open years. If this uery is needed it needs to be changed to an indexed temp table or table variable to avaoid our performance issue. If a historical year selection was added, that also seems to work.
(select 0 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID = 0
union
select 1 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID != 0) SY401
GL Transaction to Fact Processing 11.94 CE302C63-7E51-493C-BD88-F2B341FA4FC4 Enabled 1 Minutes
CompanyProvider to Organization Error 0.00 A18F16EB-4783-4978-A412-8446E349B2F7 Enabled 5 Minutes
CurrencyPairProvider to Exchange Rate Type Complete 0.00 C3284233-AC9F-4F64-9241-0332A680F9B3 Enabled 5 Minutes
CompanyProvider to Company Complete 0.00 51976CC9-64B2-4511-B90A-9B97DF1362B6 Enabled 5 Minutes
DimensionDefinitionProvider to Dimension Complete 0.00 72A5A15C-09DC-4040-81D2-63537A9CBE0C Enabled 5 Minutes
ExchangeRateProvider to Exchange Rate Complete 0.00 83BE36EB-59BB-4FCF-97D9-5A5B95E55DEF Enabled 5 Minutes
DimensionValueProvider to Dimension Value Complete 0.00 37DF17BA-80AB-45DA-A0A3-20FB50E4BA42 Enabled 5 Minutes
AccountProvider to Account Complete 0.00 5222ADE9-7C06-4DFB-9A36-821E5818E0CD Enabled 5 Minutes
FiscalYearProvider to Fiscal Year Complete 0.00 62ACDB23-90AF-41C9-9F70-9E8E5CF78A9B Enabled 1 Minutes
ScenarioProvider to Scenario Complete 0.00 306AF93D-6FFC-4645-B103-76084790C970 Enabled 5 Minutes
What do you see when you run the following against the ManagementReporter database? This script will list out all of the MR data load tasks and their status:
select t.Name,
CASE ts.StateType
WHEN 3 THEN 'Processing'
WHEN 5 THEN 'Complete'
WHEN 7 THEN 'Error'
END AS StateType,
ts.Progress,tr.Id AS TrigerID,
CASE tr.IsEnabled
WHEN 1 THEN 'Enabled'
ELSE 'Disabled'
END AS NameStatus,
tr.Interval,
CASE tr.UnitOfMeasure
WHEN 2 THEN 'Minutes'
ELSE 'Seconds'
END AS IntervalTiming
from scheduling.task t join scheduling.taskstate ts
on t.id = ts.taskid join scheduling.[Trigger] tr on tr.Id = t.TriggerId
where isenabled <> 0 AND T.TypeId = '55D3F71A-2618-4EAE-9AA6-D48767B974D8'
The query that never completes
Select count(distinct a.GLTransactionID) from ( SELECT seg1.ACTNUMBR_1, seg1def.DEX_ROW_ID as ACTNUMBRID_1, seg2.ACTNUMBR_2, seg2def.DEX_ROW_ID as ACTNUMBRID_2, seg3.ACTNUMBR_3, seg3def.DEX_ROW_ID as ACTNUMBRID_3, seg1.ACTDESCR, GL102.ACCATDSC as Category, GL102.DEX_ROW_ID as CategoryID,
GL105.ACTNUMST, GL105.ACTINDX, GL0.PERIODID, GL0.TRXDATE, GL0.SOURCDOC, GL0.REFRENCE,
seg1.ACCATNUM, GL0.TRXSORCE, GL0.XCHGRATE, GL0.ORTRXSRC, GL0.SERIES, GL0.VOIDED,
GL0.Adjustment_Transaction, GL401.Ledger_Name, GL0.JRNENTRY, GL0.CURRNIDX, MC4.FUNCRIDX, GL0.DEX_ROW_ID as GLTransactionID, GL0.DSCRIPTN,
GL0.ORDOCNUM, GL0.ORMSTRID, GL0.ORMSTRNM, GL0.ORTRXTYP, GL0.DEBITAMT as DebitAmount, GL0.CRDTAMNT as CreditAmount,
GL0.ORDBTAMT as OrigDebitAmount, GL0.ORCRDAMT as OrigCreditAmount,GL0.SEQNUMBR, (rtrim(cast(GL0.DEX_ROW_ID as nvarchar(30)))) as keyString, SY401.BBF, OPENYEAR as YEAR1, 'GL20000' KeyTable FROM GL20000 GL0
join GL00105 GL105 on GL0.ACTINDX = GL105.ACTINDX
join GL40001 GL401 on GL401.Ledger_ID = GL0.Ledger_ID
cross join MC40000 MC4 JOIN GL00100 seg1 on seg1.ACTINDX = GL105.ACTINDX
left join GL40200 seg1def on seg1def.SGMNTID = seg1.ACTNUMBR_1 and seg1def.SGMTNUMB = 1 JOIN GL00100 seg2 on seg2.ACTINDX = GL105.ACTINDX
left join GL40200 seg2def on seg2def.SGMNTID = seg2.ACTNUMBR_2 and seg2def.SGMTNUMB = 2 JOIN GL00100 seg3 on seg3.ACTINDX = GL105.ACTINDX
left join GL40200 seg3def on seg3def.SGMNTID = seg3.ACTNUMBR_3 and seg3def.SGMTNUMB = 3 left join GL00102 GL102 on GL102.ACCATNUM = seg1.ACCATNUM join
(select 0 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID = 0
union
select 1 BBF, YEAR1, PERIODID, PERIODDT, PERDENDT from SY40100 where FORIGIN = 1 and PERIODID != 0) SY401
on GL0.OPENYEAR = SY401.YEAR1
and ((SY401.BBF = 0 and GL0.TRXDATE = SY401.PERIODDT and GL0.SOURCDOC in ('BBF', 'P/L')) OR (SY401.BBF = 1 and GL0.TRXDATE <= SY401.PERDENDT and GL0.TRXDATE >= SY401.PERIODDT)) INNER JOIN CHANGETABLE(CHANGES GL20000, 7149664) GL0_CT ON GL0.DEX_ROW_ID = GL0_CT.DEX_ROW_ID AND GL0_CT.SYS_CHANGE_VERSION <= 7224285 ) a
--?>
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (