Dashboard not responding

Adrian_Mepham · October 3, 2019, 1:03pm

Hi everyone,

I have a dashboard which is used daily mainly for KPIs and it works well until on the Order Entry tab, the date filters are set to more than a couple of weeks. Then it stops responding. I am hoping one of you guys could have a look and advise me as to how I can get it working. I know that there is a lot of data to process but I feel it’s something I have done (or not done) that is making it crash.
I have attached the offending BAQ along with a couple of screens if anyone can take a look.
Thanks
Adrian.

Returning only three days works perfectly

)

ckrusen · October 5, 2019, 4:25pm

How long does it take to refresh when the date is just short of the time when it freezes? You could be reaching a timeout limit.

Add another filter on the date, with <= and set that to a little after the other date. This is just a check if it’s just too much data.

So your original date filter works back to say 9/1/19, but craps out when you set it to 8/15/19. The data from 9/1 to today is okay, but from 8/15 to today is too much. Set the newly added filter to 9/15/19 to see if 8/15 to 9/15 works. If it doesn’t it may be bad data in an order dated between 8/15 and 9/1

Edit

Whoops! Just saw that you do have an upper limit filter already there.

Does choosing any 3 day range always work?

In the BAQ designer, if you filter it to a period that doesn’t work in the dashboard, how long does it take to execute (shown on the analyze tab)?

gpayne · October 5, 2019, 6:11pm

If it is something you can upload, I will load it and take a look. Or just screenshot the generated query, so we can see the details. I had a situation like this where the query that was being built was odd and I had to change the order of the tables I had joined to get the query to change.

Adrian_Mepham · October 7, 2019, 6:32am

Hi @gpayne, thanks for your help. Here is the query;

 select distinct
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[Customer].[Name] as [Customer_Name],
	[OrderHed].[EntryPerson] as [OrderHed_EntryPerson],
	[OrderHed].[OrderRecDate_c] as [OrderHed_OrderRecDate_c],
	[SubQuery3].[Calculated_NonStandard] as [Calculated_NonStandard],
	[SubQuery4].[Calculated_SpecialProduct] as [Calculated_SpecialProduct],
	[SubQuery5].[Calculated_DrgCompleteDate] as [Calculated_DrgCompleteDate],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	[OrderHed].[TotalLines] as [OrderHed_TotalLines],
	[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
	[SubQuery2].[Calculated_ReworkLines] as [Calculated_ReworkLines],
	[SubQuery7].[Calculated_StorageLine] as [Calculated_StorageLine],
	[JobProd].[OrderNum] as [JobProd_OrderNum],
	((case when JobProd.OrderNum  <> '' then 1  else 0 end)) as [Calculated_JobsCreated],
	[OrderHed].[ProjectID_c] as [OrderHed_ProjectID_c],
	[Project].[Description] as [Project_Description],
	[ShipDtl].[PackNum] as [ShipDtl_PackNum],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	[OrderHed].[OrderAmt] as [OrderHed_OrderAmt],
	[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
	((case when PartShipAmount.InvoiceValue_InvoiceAmt  > 0 then (PartShipAmount.InvoiceValue_InvoiceAmt - OrderHed.DocTotalTax) else OrderHed.DocTotalCharges end)) as [Calculated_orderValue],
	[PartShipAmount].[PartShip_InvoiceNum] as [PartShip_InvoiceNum],
	[PartShipAmount].[InvoiceValue_InvoiceAmt] as [InvoiceValue_InvoiceAmt],
	[PartShipAmount].[PartShip_PackNum] as [PartShip_PackNum],
	(DateDiff(dd, OrderHed.OrderRecDate_c, ShipHead.ShipDate)) as [Calculated_PoToShipDays],
	(DateDiff(wk, OrderHed.OrderRecDate_c, ShipHead.ShipDate) * 2) as [Calculated_PoToShipWks],
	(DateDiff(dd, OrderHed.OrderDate, ShipHead.ShipDate)) as [Calculated_EntToShipDays],
	(DateDiff(wk, OrderHed.OrderDate, ShipHead.ShipDate) * 2) as [Calculated_EntToShipWeeks],
	(( PoToShipDays - PoToShipWks)) as [Calculated_POtoShip],
	(( EntToShipDays - EntToShipWeeks)) as [Calculated_EntryToShip],
	(Case When DatePart(weekday, ShipHead.ShipDate) = 7 Then 1 else 0 end) as [Calculated_EndOnSat],
	(Case When DatePart(weekday, OrderHed.OrderRecDate_c) = 1 Then 1 else 0 end) as [Calculated_StartOnSun],
	(POtoShip - EntryToShip) as [Calculated_LoadDays],
	[PartShipAmount].[Calculated_amountPartShip] as [Calculated_amountPartShip]
from Erp.OrderHed as OrderHed
left outer join Erp.Customer as Customer on 
	OrderHed.BTCustNum = Customer.CustNum

left outer join Erp.JobProd as JobProd on 
	OrderHed.OrderNum = JobProd.OrderNum

left outer join Erp.Project as Project on 
	OrderHed.ProjectID_c = Project.ProjectID

left outer join Erp.ShipDtl as ShipDtl on 
	OrderHed.OrderNum = ShipDtl.OrderNum

left outer join Erp.ShipHead as ShipHead on 
	ShipDtl.PackNum = ShipHead.PackNum

left outer join  (select 
	[NonStandard].[OrderNum] as [NonStandard_OrderNum],
	(sum(
   case 
     when NonStandard.CheckBox03 =  1  then 1
     else 0 
   end
 		)) as [Calculated_NonStandard]
from Erp.OrderDtl as NonStandard
group by [NonStandard].[OrderNum])  as SubQuery3 on 
	OrderHed.OrderNum = SubQuery3.NonStandard_OrderNum

left outer join  (select 
	[Special].[OrderNum] as [Special_OrderNum],
	(sum(
   case 
     when Special.Checkbox02 =  1  then 1
     else 0 
   end
 )) as [Calculated_SpecialProduct]
from Erp.OrderDtl as Special
group by [Special].[OrderNum])  as SubQuery4 on 
	OrderHed.OrderNum = SubQuery4.Special_OrderNum

left outer join  (select distinct
	[CAD].[Company] as [CAD_Company],
	[CAD].[OrderNum] as [CAD_OrderNum],
	(max( QuoteDtl.MdCompDate_c )) as [Calculated_DrgCompleteDate]
from Erp.OrderDtl as CAD
inner join Erp.QuoteDtl as QuoteDtl on 
	CAD.Company = QuoteDtl.Company
And
	CAD.QuoteNum = QuoteDtl.QuoteNum
And
	CAD.QuoteLine = QuoteDtl.QuoteLine

group by [CAD].[Company],
	[CAD].[OrderNum])  as SubQuery5 on 
	OrderHed.OrderNum = SubQuery5.CAD_OrderNum

left outer join  (select 
	[PartShip].[Company] as [PartShip_Company],
	[PartShip].[InvoiceNum] as [PartShip_InvoiceNum],
	[PartShip].[PackNum] as [PartShip_PackNum],
	[OrderAmount].[OrderAmt] as [OrderAmount_OrderAmt],
	[InvoiceValue].[InvoiceAmt] as [InvoiceValue_InvoiceAmt],
	((case when InvoiceValue.InvoiceAmt   <>  null then InvoiceValue.InvoiceAmt else null end)) as [Calculated_amountPartShip]
from Erp.InvcDtl as PartShip
inner join Erp.InvcHead as InvoiceValue on 
	PartShip.Company = InvoiceValue.Company
And
	PartShip.InvoiceNum = InvoiceValue.InvoiceNum

inner join Erp.OrderHed as OrderAmount on 
	InvoiceValue.Company = OrderAmount.Company
And
	InvoiceValue.OrderNum = OrderAmount.OrderNum)  as PartShipAmount on 
	PartShipAmount.PartShip_PackNum = ShipHead.PackNum

left outer join  (select distinct
	[OrderDtl4].[OrderNum] as [OrderDtl4_OrderNum],
	[OrderDtl4].[LineDesc] as [OrderDtl4_LineDesc],
	(case
 	when
 			sum(
 			  case 
 			    when OrderDtl4.LineDesc  =  'Weekely Pallet Charge'  then 1
 			    else 0 
 			  end
 					)
 					> 0 then 1
 					else 0
 				end) as [Calculated_StorageLine]
from Erp.OrderDtl as OrderDtl4
group by [OrderDtl4].[OrderNum],
	[OrderDtl4].[LineDesc])  as SubQuery7 on 
	OrderHed.OrderNum = SubQuery7.OrderDtl4_OrderNum

left outer join  (select distinct
	[OrderHed-Rework].[OrderNum] as [OrderHed-Rework_OrderNum],
	(case
 	when
 			sum(
 			  case 
 			    when OrderDtl.Rework =  1  then 1
 			    else 0 
 			  end
 					)
 					> 0 then 1
 					else 0
 				end) as [Calculated_ReworkLines]
from Erp.OrderHed as OrderHed-Rework
left outer join Erp.OrderDtl as OrderDtl on 
	OrderHed-Rework.Company = OrderDtl.Company
And
	OrderHed-Rework.OrderNum = OrderDtl.OrderNum

group by [OrderHed-Rework].[OrderNum])  as SubQuery2 on 
	OrderHed.OrderNum = SubQuery2.OrderHed-Rework_OrderNum

 where (OrderHed.VoidOrder = false  and OrderHed.OrderDate >= DATEADD (day, -2, GETDATE()))

Here is the BAQ itself;
Order-Creation 06-10-2019.baq (106.8 KB)

Adrian_Mepham · October 7, 2019, 7:00am

Hi @ckrusen, your suggestion regarding the bad data got me thinking. I selected some random dates for my filters and started to narrow down where my problem was. At the moment I have found that if I set the date filters to show just the results for 18-9-2019 then the dashboard processes for around 20 seconds and then returns nothing. So I’m thinking there is something not quite right in my data for that day. I do not get any error message though. Is there a way to find out what has gone wrong, or where the problem is?
Adrian

ckrusen · October 7, 2019, 12:44pm

Temporarily add that date as table criteria in your BAQ, and see if that runs.

Then duplicate the BAQ, and start removing sub-queries (one at a time) to see if the issue is with the sub-query. Note: You don’t have to delete the sub-q from the BAQ. Just remove it from the top level query.

If you find a sub-q that is causing problems with that date:

Note the values of the records that the main query would pass to that sub-q
Make that sub-q the Top Level query (and change the top level to an inner sub-q).
Add criteria to the sub-q, to make it run for just the data that the original top level query would have had relationships.
Then you can run the sub-q by itself.

That’s the direction I would go.

ckrusen · October 7, 2019, 1:11pm

Also, I can import that BAQ. But my not having those 4 or 5 UD fields, keeps me from running it.

ckrusen · October 7, 2019, 1:23pm

One more thing … I don’t know if it matters, but I see that table relationships on your top level query, that have only one relationship, have the AndOr field set.

Did you used to have table relationship #1 as OrderHed.Company = JobProd.Company but then deleted it?

If so, theAndOr should be blank

But you really should have the Company relationships in there too.

Adrian_Mepham · October 7, 2019, 1:25pm

Thanks Calvin, I guessed those UD fields would be a problem. I am going to work through your suggestion and try and track down the problem. When you say remove the sub-queries, do you mean just delete them from the canvas (diagram)?

ckrusen · October 7, 2019, 1:28pm

Yes. But only only on Subquery1 (your top level). That way you don’t have to rebuild them when you want to add it back in.

I might just be lucky, But I’ve never had an issue with a BAQ having an orphaned sub-query. And by orphaned, I mean a designed sub-q, that is not used by any other sub-q. It’s still in the BAQ, but doesn’t come into play for the data revival.

Adrian_Mepham · October 7, 2019, 1:30pm

Yes, I actually did have the Company in there, but I read somewhere that someone had found that removing it actually helped to speed up their dashboard. I have since read others who like you say it should be there, so I will put it back (I actually removed it from all the joins)

gpayne · October 7, 2019, 3:57pm

Removing Company is a specific case that @timshuwy pointed out.
A couple of things I noticed. You are joining on your UD fields. Did you add indexes for them?
In you case statements on your UD fields, I would add null checks to them. I think something like below.

Is Weekely Pallet Charge a typo?

case 
     when Special.Checkbox02 is null then 0
	 when Special.Checkbox02 =  1  then 1
     else 0 
   end

Adrian_Mepham · October 8, 2019, 6:29am

Hi Greg, I have not added indexes for the joins on the UD fields. How do I do that? I will add the null checks to the case statements. ‘Weekly Pallet Charge’ is not a typo. I needed to select those lines which had used that option from the configurator. I thought matching the line description would work, well it does but perhaps not the most efficient method?

gpayne · October 8, 2019, 2:42pm

I use the sql execution plan which analyzes the query in ssms and will advise on missing indexes and will mock up the command for adding the index. You will need to read up on it and do in a test environment first. Also, not every index that is recommended actually will help.

In the code you posted Weekly was Weekely.

Topic		Replies	Views
Dashboard taking longer / doesn't run compared to BAQ itself Epicor ERP 10 baq	12	1784	October 15, 2018
BAQ TEST Performance Epicor ERP 10 baq	12	2226	March 24, 2022
Dashboard Help Epicor ERP 10 e10 , baq	5	643	January 16, 2019
BAQ Works in Designer - Not in Dashboard Kinetic ERP e10 , dashboard	21	2253	December 7, 2022
BAQ Fast in Designer Slow in Dashboard Epicor ERP 10 baq	16	2469	February 1, 2023

Dashboard not responding

Edit

Related Topics