New to the Epicor/Compass universe so apologies if I’m posting this topic in the wrong forum, please redirect me if I am.
I’ve been trying to port some Epicor/EagleDW data into PowerBI for our company and running into an odd issue. I can successfully connect PowerBI to our EagleDW database and import various tables into the report. However, I need to link customer numbers with sales transactions and can’t find anything that relates to customer info at all in the Sales table. I can create a basic report in Compass that does this so I know these tables are linked somehow, but I can’t get behind the UI to tell how the customer and sales are being linked. Is there a common key between these tables and, if so, what is it?
I’ve looked in the Customer table, yes. I don’t currently find a table called “OrderHed” or “OrderHead”, however. I’m currently looking at the full Sales table and it doesn’t contain any columns for things like Cust ID, Customer Name or any other variant of those (screenshot is from MySQL Workbench):
Currently, I still can’t find a way to tie a customer name or account data to the transactions in the Sales table. The Customer and Sales tables must be linked somehow. I can create a report in Compass that includes sales totals for a given date with customer account name and ID number. How that linkage/join is done I have no idea. I should be able to import the relevant tables into Power BI and establish a relationship between them on Customer Name or ID. But the sales table doesn’t contain any kind of customer reference data.
Your post is indicating E9?
Is that really your version…do you have direct access to the BAQ tool in the Epicor Client (or the database)? If so, might start in your Epicor client with a simple query on the Customer and OrderHed tables.
Otherwise, maybe you can describe tools you are using to connect to which datasource(s)?
I’m pulling the tables directly from the EagleDW database into PowerBI using the MySQL connector. I can also view the entire DB using MySQL Workbench. Unfortunately, I have no idea what ERP version the company is using (I just inherited this situation recently) so I may have posted my question in the wrong forum.
The question pertains more to the contents of the Sales table than anything else. I can see every table in the DB with MySQL Workbench and do various queries on them, but what I don’t find is any reference to Customer Name or ID in the Sales table - nothing that relates a given sales transaction with a customer account. There’s a Customer table, but a search through the tables in the DB doesn’t turn up a table called OrderHed.
I don’t have the BAQ tool (not that I know of anyway). I do have the Eagle Browser and Compass tools. As I showed in my post yesterday, I can create a report in Compass that shows total sales by customer account name/number so there’s some kind of linkage between the Sales and Customer tables.
That sales table does look like a view or some other kind of aggregated report. Does anyone know the official name of the table containing sales transaction data?
My understanding is that the EAGLEDW database sits behind some kind of Epicor front end. It appears to be some kind of Retail ERP system according to the link below.
In any case, it seems increasingly clear this forum isn’t the one I should be asking this question in. I’ve just learned the company has some kind of technical account manager that may be able to help so I’ll pursue that. Appreciate everyone’s replies, sorry for any confusion.
