External BAQ Analyze error

Epicor ERP 9
1

Hi there,

I’ve got an external BAQ that I want to use in a dashboard that will show part numbers, on hand quantities, and the transaction dates. I’ve created it so it groups the parts together is a way that if they start with the same 4 characters, they’ll show the overall quantity. For example:

Part number Onhand Quantity
ABC123 1
ABC134 2
ABC153 6
ABC178 1
DEF245 8
DEF260 2

PartNumber Onhand Quantity
ABC1 10
DEF2 10

However, I’m getting the following error:

Table: , Level:, Type: , RowID: , Text: Business Query Execution error. Please contact your System Administrator.
Table: , Level:, Type: , RowID: , Text: [Microsoft][SQL Server Native Client 10.0]Query timeout expired

I have a feeling (due to the fact I’ve ran it in the SQL management studio which took some time) that it’s too much data for it to handle? Would I be right in thinking this?

2

Sounds like you got the answer right here. May want to consider adding some indexes and such in your external DB.

3

I’ve re-ran the query in SQL Management Studio, and it took roughly 6 and a half minutes to complete; I’m assuming that I’ll have to edit something for this to run in the BAQ/Dashboard?

4

6 1/2 minutes is a LONG time even if you could change the time out setting (you can) that’s a long long time for your users to wait around. I think you should try to workout how to speed up the query. Add indexes oni the external DB? or some how narrow the scope of the query.
There are settings in the BAQ to extend the timeout

5

I’ll add indexes onto the external query or I’ll modify the query so that it returns the data quicker, thanks

6

Wow, that does seem excessively long. I agree with Jose that you should first investigate how to make your SQL run more efficiently, and only get what’s needed.

That said, we had some number crunching calcs for an inventory model that caused timeout issues in Epicor that we could not get around. We determined that serving up the data daily was good enough. Therefore, in the middle of the night, post MRP, some jobs are scheduled to run on the sql server that execute queries and put the data into our own tables; from there we make a view served up in Epicor using our populated tables.

Nancy

7

I’ve found out that it’s the PartTran.TranDate that’s causing it to have such a long wait time

8

I thought you said this was an external table?

9

This is an external query and I’m using the SQL Management Studio to create, I’m using the normal Epicor tables and fields but what I’m trying to achieve is to group the parts by the first 4 characters and show the overall On hand Quantity of them.

10

I’m still struggling with this as it’s still not working.

11

Can you post the query you are trying to run.

12

The SQL code for this that I’m using is:

Create View PartFamily As
Select Left(A.PartNum, 4) As PartNumLeft, Sum (A.OnHandQty) As PartNumQty, A.PartNum
From PartBin A
Group By Left(A.PartNum, 4), A.PartNum

And the BAQ looks like:

for each PartFamily no-lock  where  PartFamily.OnHandQty >= 0  ,  each PartTran no-lock  where (PartFamily.PartNum = PartTran.PartNum ).
13

Can you create it as a view or as a query to check it?
Not using the “create view” part or your statement, and just doing a new view on SQL studio pilot db, it works for me. I did need to add the “ERP.” to the PartBin table.

What exactly is the error statement you are getting?

image
image.png1395×795 64.5 KB

Nancy

14

I’ve created it as a View, but before hand, I set it as a query to ensure it worked before I set it as a view. It’s fully functional now in the BAQ.

However, now when I attach it to a dashboard and when I run it, it doesn’t bring back any data, only ‘No Results’ as a column head