DO NOT go the DMT route. Why would you want to load that into the transactional tables, plus then you have to load all the customers, vendors, parts, etc. User Defined Tables is the way and only way to go. And not for nothing are the users really going to look at data that far back.
3 Likes
Agreed. Power BI is a great tool for joining/unioning data from multiple sources, including your legacy ERP database. When I made a report to help with forecasting, the powers-that-be made it a requirement to get at least a few years’ worth of legacy data, and were very happy to be able to see almost 20 years’ worth of sales history for any given part number…
(Our Epicor launch was 2020 Q4)
2 Likes
Grant -
I’m the cowboy in the group here.
I have brought in Sales Orders and Purchase orders that are closed into the database by using DMT.
For sales orders, you are only bringing in the same dataset that you bring in when the went live with the open orders. The difference is, at the end of the import, you close the orders.
The benefit is you then can go into the customer tracker to see prior orders, you can use history to create orders (company setting how far back to do go).
I am not recommending Shipping or invoicing details, just OrderHead, OrderDetails, and OrderRel - all closed after the import.
In the 15 years of doing consulting and many go lives, I have always brought in the history to what the customer wanted.
4 Likes
I recommend DMT for the “open” transactions.
If you require history for other transactions those could be loaded into UDT’s.
Mark, I am struggling to make this argument clear to our leadership. We are a little behind the state-of-the-art here. Still using mapped drives to file shares for collaboration when I joined. I have us leveraging Teams, SharePoint, and Collaborative tools like MS Lists over passing a spreadsheet around email. Yet, still struggling a bit to get them modernized away from wanting paginated reports automated to their email and onto BI dashboards. When I show them BI and the dynamic nature of going from high-level visuals down to granular row/column data and the variations inbetween, they get excited. But going through this change has got everyone scared stiff. To the point where they are back to trying to customize Kinetic to do exactly what our legacy system does today. Bringing 20 years of data (including the transactional history - build and service records) from an INFOR on-prem to a Kinetic Cloud, just is asking for trouble. Do you mind if I snip your post into a presentation?
2 Likes
If you want some extra ammunition:
I recently signed on to a company that had moved to Epicor, bringing in all of the transactional and contact data going back to 1994. In addition, they’ve never purged old Epicor data since 2013. It takes users about 20 minutes to enter a sales order. BAQs and other queries frequently time out. We can’t use most BI tools because the SQL Queries time out. The database and log file combined are nearly 900 GB, so we need 2 TB of storage just to keep one backup on hand.
3 months and I have most of the issues cut down to where they are at least manageable, but it will take me years to untangle this mess.
4 Likes
The 20 minutes per sales order thing isn’t because of old data. It’s because tons of customization was added to make everything like up with their previous ERP processes. @mlazorchak
3 Likes
I have another take on this. I put 1M history records in UD tables and showed ownership they could see them in 2012. No one every looked and we went on our way. They still exist in UD100 twelve years later. It is just a big lump of coal, but it does not cause any performance issues.
The urge to do as we always did is hard, but they moved from Infor to Epicor for some reason when they are both good, so in my opinion that is the bigger fire to fight. I have 400+ dashboards to see what we used to see that hopefully in he next upgrade I can halve.
4 Likes
I agree with the general sentiment here, if possible don’t do this. Its a huge amount of information that mostly won’t be used. The main use case will be reporting and as already discussed there’s better ways of doing this.
When we went live we used DMT for the main ‘static’ data such as customers and contacts, and then entered any live/in flight transactions such as orders and quotes manually on the go-live weekend - doubled up as last minute user training! (We’re a small company, appreciate not everyone could get away with this).
Historical data we kept one licence of the previous system for a year, and then exported important info and put it into a SQL database, and we query manually if needed.
However, if you need to do this for some business reason, then I’d suggest DMT as a start, it worked well for us when importing the necessary data. We created a spreadsheet using the DMT templates to give us the necessary columns, and laid it out as below:
Row 1 - Epicor field name, so the column heading when importing into DMT (note - the DMT column header is nearly always matches the field name in the database, but not always, of course. Hence use DMT templates).
Row 2 - Format and validation of the field
Row 3 - Epicor field help so we knew what it actually was…
Row 4 - Field name of previous system.
A section of our customer one as an example:
Then, using ODBC (the only option in the previous system) and more VLOOKUPS & IF formulas than I’d care to do again, we connected the spreadsheet to our old system and populated the columns with live data. This meant we could refresh the spreadsheet constantly to check for errors and that it was easy to prepare for the import.
Finally at go live, we saved the spreadsheets as a csv’s, deleted rows 2,3 & 4, and that left us with our import templates.
I’m sure there’s better ways, but it worked ok for us. If you need to do this, my main concern would be that 30 days doesn’t sound that long to get this sorted, depending on your company size and number of transactions - if you’re not familiar with it, it can take a little time to get your head around the imports required per Epicor entity.
I hadn’t heard about the historical data DMT licence mentioned by @bderuvo but sounds worth checking out.
3 Likes
@mlazorchak , if management does not budge, I would look into creating another company in your Cloud and loading the data there. It won’t be available in the active company, but at least you could just switch companies to see the old data.
4 Likes
Not at all. If it helps, tell them that I was one of the consultants that implemented the ASK MANMAN system there at Magline when the Berlin Wall came down. (Most of them are probably gone, maybe Stephanie is still in accounting? 
) So, even someone older than they are agrees with you. 
In the end, they shouldn’t care where the data is stored. Organizations should give us requirements and we should deliver on those requests. With a few exceptions in my experience, most management level team members don’t know the best way to do things and shouldn’t dictate the method. For example, there’s a price to importing historical data into backup and recovery time. If storing backups online, there’s an added storage/egress cost as well. If they want to see historical data, which is a reasonable request, you can give them a grid that shows the historical information.
We don’t tell them where to buy aluminum, so why should they tell us how to do our job?
4 Likes
Since your leadership is a little behind anyway, tell them all history for shipments, orders, methods of manufacturing will be archived and accessible for quick access. Then dump the records as reports to pdf files and be done with it. Your wasting time on conversion stuff you will never use and will waste money to support and maintain when you should be concentrating on the new system features. Their reaction and requests are very common, your job is to take them into the future kicking and screaming. Your reward will be 9 months from now when they say gee this is much better than the old way.
2 Likes
Another vote to storing the old date in a separate database. With modern BI tools, it’s not hard to combine data sets. Also, will anyone really use PartTran History or GL Journal records from more than 3 years ago? We purge that in our live database… The end transactions are still there, like invoices, shipments, orders, jobs, PO’s. Make life easier and see how the data is being used before backing EVERYTHING up. You likely need a smaller subset.
And for those on Epicor 2023.2+, use the Activity Tracking app. It’s a great way to see what BAQ’s (used in Dashboards), Report Styles, and Menu Items are being used. And pitch (archive) the ones that aren’t to lower your future upgrade issues footprint.
2 Likes
You all are offering fantastic angles and insights. I like the idea of being able to deliver some of this data using the OPEN LOAD method. As long as I can get them away from the build and costing tie-ins, I’m amenable but at that point, BI can only be a level-up. If we are going to integrate any third party market data or industry trends, take advantage of AI data analytics, we should position ourselves for an aggregate data set to pull it all together. The more “composable” ERP gets, the more sources to pull from. I’ve had this same conversation more than 50x in my career (mostly spent as a consultant) and everyone wants this data migrated, but the case for a clean new system while keeping access to legacy data has always been a fairly easy argument to make. In fact, I have a character flaw that has been popping up… when I find myself having a nearly identical discussion to the many I’ve had before, I anticipate the argument. I hear the first couple of statements and the moment I recognize the objection, I begin to express the clarifying point. That comes off rude and as if I’m interrupting without letting my colleagues finish their point. I have to work on that. But it’s funny how often we have the same discussions surrounding pragmatism during transformation. How we recognize the anxiety surrounding change. Yet, when you onboarded, you were ready to adopt whatever system we had in place and, in a relatively short amount of time, you got comfortable with things. Warts and all! Folks here are really intelligent, I don’t mean to make it sound like they’re less-than. If I can put the tool in their hands, they often thrive and readily adapt the value to their day-to-day.
5 Likes
I love it! I hope I survive that long. Ha! It was much easier as a consultant with a major firm behind the @ on my email. GE Capital IT Solutions added some gravitas to my arguments. Now, as an independent, I find resorting to an anecdotal account of some wild rip and replace gone wrong. I’ve got a few of those stories that I love to tell, but most of my really good one’s come from shared experiences on forums like this. I’ve heard some doozies! I used those same words when I told them I wanted them to start to leverage TEAMS instead of email for the more syncronous/collaborative/deep work priorities. They said “Around here, if it didn’t come in email, it didn’t happen,” and I replied, “Well, I need to drag you all kicking and screaming down the IT Roadmap to a brighter day where you aren’t spending energy trying to figure out if you should or should not click the link, if the person sending you the email is legit, and if you should or should not respond to the 25th solicitation message this month from yet another sales optimization consultant,” 
1 Like
I have been implementing for 15+ years and I still believe in bringing in closed orders for a year or two back into the database.
When the user opens up the customer activity window - they can see what the customer purchased for the last couple years.
No hit on performance as the order are all closed and ignored by MRP etc…
Same with Purchase orders. Who were the prior suppliers in the last couple years of materials. All at your finger tips in the purchasing advisor dashboard.
History in the actual tables is good - two years, yes, ten years no.
3 Likes
We are using phocas so we merged the old and new databases with the synch on the old system turned off, a bit of a pain to set up, but the business can see everything including rolling 12 months.
Really it could have been any BI tool.
3 Likes