Hello guys. We are starting with the configuration of Epicor 10.
We have 2 environments: Client and Client-Pilot, also Education.
We are planning to make the Configuration on Pilot and I would like to export the information into Excel and just load that configuration using DMT.
I can load the information using DMT if I have it in a CVS file. But the main question is:
Can I export the information to the CVS file using DMT?
Usually, when you do the configuration, the Epicor implementation, do you do the configuration direct on the Client Environment? or do you do it on Pilot?
Client Environment = Production Environment.
I ask you this because if I do the configuration on Pilot, we have to specify a lot of information on the company, create the Production and Fiscal Calendars, create Warehouses and Bins, etc. and I just want to know if we have to do the same steps on the Client environment. If is right, then, I have to do it directly on Client instead of Pilot.
Additionally, this is a SAAS version, then I can not backup/restore the databases or, I have to request it to Epicor and I’ll try to avoid it as much as possible.
Not sure if SaaS makes a difference (I’m on prem), but you can definitely use DMT to export (and import from file) data via the DMT command line interface and Powershell. I think there is a GitHub for it (search) but just as an example, you would set up your BAQ in Epicor where you want DMT to execute and dump the results to file.
Example Powershell Script to extract data from a BAQ via DMT:
#Extract Data From From BAQ -> CSV File -> Load in with DMT
$DMTPath = "C:\Epicor\Clients\EpicorVAL\DMT.exe"
$User = "yourSvcAccountUserName"
$Pass = "yourSvcAccountPassword"
Write-Output "Extracting Data via BAQ $(get-date)"
#Extract Data from VAL
$Target = "C:\Temp\VAL_Exports\SalesRegion.csv"
$completeLog = $Target + ".CompleteLog.txt"
$BAQ = "YourBAQID"
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User $User -Pass $Pass -Export -BAQ $BAQ -Target $Target -NoUI -ConfigValue=Epicor10VAL"
Write-Output "Exporting Data $(get-date) " $Target
Ohh thank you. Here is some information about it:
Version:1.0 StartHTML:000000212 EndHTML:000005384 StartFragment:000004172 EndFragment:000005330 StartSelection:000004172 EndSelection:000005330 SourceURL:http://www.dotnetit.co.uk/page/dmt-support-portal DMT Support Portal | Dot Net IT
BAQ Exports
We are proud to announce a powerful new feature for DMT – the ability to export data from Epicor. DMT can now be used to export data from an Epicor Business Activity Query (BAQ), producing a file compatible with DMT imports. This has enormous scope for keeping Epicor environments in sync.
Initially, the new feature is only available from the Command Line. We will be extending this functionality in later releases, but for now, a DMT BAQ Export can be initiated using a Command Line similar to the following:-
Where :
-Export – instructs DMT to perform a BAQ Export rather than an import.
-BAQ=“CustomerExport” – informs DMT to use the BAQ called “CustomerExport”. If the results of this BAQ export are to be used to re-import data to another environment using DMT, please ensure that columns in the BAQ are compatible with the relevant imports (this can be checked in template builder)
-Target=“C:\CustExport.csv” – tells DMT where to create the DMT import file.
This is not a real query being executed, but a simplified version for general vision.
Executing it with any other tool may produce a different result.
*/
select
[Customer].[Company] as [Customer_Company],
[Customer].[CustID] as [Customer_CustID],
[Customer].[CustNum] as [Customer_CustNum],
[Customer].[Name] as [Customer_Name],
[Customer].[Address1] as [Customer_Address1],
[Customer].[Address2] as [Customer_Address2],
[Customer].[Address3] as [Customer_Address3],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Zip] as [Customer_Zip],
[Customer].[Country] as [Customer_Country]
from Erp.Customer as Customer
And from the Command (CMD) y executed:
start DMT.exe -User=username -Pass=password -Export -BAQ=“CustomerExport” -Target=“Customer.csv”
It generated the Customer.csv.
This would be complicated because we would need to create a lot of BAQ’s to export all the information, taking care of relationships or some rows would not be returned, also, selecting the columns that we need.
But don’t name the columns like you did. Instead make sure that they match the column names specified in DMT.
And there might be issues with the order of importing tables that have relationships. If a customer has a specific ShipTo as it’s default, you might get an error trying to create that user before the shipto exists (which cant be created until the customer exists). You’d have to
ADD the Customers - and not set their default ShipTo
ADD the ShipTo’s
UPDATE the Customers, setting their ShipTo
And by the way … DMT ignores the CustNum, and always uses the CustID. So make sure the BAQ’s for the tables that need to know the CustNum, also export the CustID
The more I think about all the inter-related tables, you’re probably better off biting the bullet, and having the SaaS manager do the backup and restore. You might want to request that they make backups at points after you’ve got certain things set right. Then if during testing you do something “wrong”, you can rollback to a known point without having to do all the DMT’ing again from scratch.
We are SaaS too. We started as Single Tenant but are now Public Cloud but in the beginning, we could do a lot of restores. This was fine for EMS but even then, I think I pushed the limit on requests. Also, it worked fine for the first company but for the second implementation we couldn’t do it the same as the first since we had live data already in the database. I would have EMS copy Live to Pilot and then run DMT PowerShell scripts to load data.Copy Live to Pilot and add more records, etc. We started with static files (parts, suppliers, customers, Resource Groups, etc, and then kept adding more until we got the process working from scratch the exact way we want. Don’t make a pet out of your system. Blow it away and start again and again. On launch day, just point to the Live instance and run your well-tested scripts.
If I were to do it again, I would do a more DevOps approach to automate the load. I would start with the Empty Live and copy it to Pilot. This is a very easy operation for SaaS. No backup to keep track of, you just always start with the current state of Live. I envision the copy Live to Pilot becoming an item we’ll be able to do from a portal one day but for now it’s an item on EpicCare. It works just starting out or if you are implementing a new company.
I would take the DMT files and check them into a repository like GitHub. I would then create a build script in in some tool of your choice (Azure DevOps, Jenkins, whatever) and run the same process as mentioned above. The advantage is you’ll have a nice history of your DMT files and you won’t accidentally run things out of order or forget a step. Also, you can actually take code updates along the way and test how they’ll work quite easily.
Even after launch, I would try to keep up with this same process. This has several advantages but from a public company point of view, it demonstrates control of your ERP process. I can make it so I don’t ever have Security Admin privileges! Code/Solutions are checked-in the repo, someone besides me can review and approve, and then have an automated process load it into Live. With a little work on comparing elements of XML files, we could even do nice diffs between current and new versions of the software we get from Epicor twice a year in the new cadence.
How do you run DMT in the cloud?
I just have installed Epicor Cloud and it created a folder with 3 subfolders, one for Production, One for Pilot and One For Education. I just downloaded the DMT and license and paste it on the folder I want to access. I double click the DMT and it ask me for the user and password.
Then, The data management tool is displayed with the options on the left side to select Management, Migration, … and Favorites.
And I can select any table to create the template. But it just allow me to create the templates or load information, but do not allow me to export it.
You would open an Azure account if you don’t already have one - you’ll get $200 for learning. You can then spin up a very small server in the Central Region (where Epicor SaaS is hosted in the US) and install the Epicor Clients and DMT. Finally, copy your files to the server (download from GitHub or SharePoint) and then run your DMT PSH scripts from there.
Another option for Microsoft 365 users will be the new Windows Virtual Desktop product that is coming out of preview very soon. We are looking at this as an option for our EMEA and Asian E10 users. The rich client is not cloud friendly and until a nice web version of Kinetic comes out, it will have to do.
I’ve been working on a copy of our company setup and opening trans based on the migration steps in the DMT…I’m up to 157 steps. Using the power-shell script from the dotnet GitHub site I modified to read from a csv and execute the SQL ether stored procedure or view to update. It gives me the ability to either generate the data into a csv only or generate and execute. Interestingly it allows you to add additional information like comments to the csv which are ignored by the power-shell script, which was a nice unintended consequence.
It’s been a great learning experience.
I believe the DMT Planner which is under development aims to aid in the implementation process of standing up a company.
I’m still working through things and picking through some of the gaps. The order of things in the DMT is not 100% perfect if you want to include additional columns. GL controls is a good example. Not to mention anything that relates to person contact ids (workforce, employee, buyer). In some instances I have had to run things a second time. As always it’s a refinement process.
Transfer orders is another area where I will be logging a case with as requested by Gary Parfrey after I contacted him.
Why have I done this. I needed to create a copy of our company in the Demo environment with no customizations at the insistence that users couldn’t understand the Demo data!. Now of course the online course documents have no relevance! But at least now users can go in and use their data against a totally out of the box Epicor environment.
… not to take away from all of the good learning you’ve done with DMT… couldn’t you have just copied your DB to a new Appserver, and then gone to Customisation Maintenance and deleted them all?
Agreed there was probably better things I could have been spending with my tiime. In this instance it was requested a new company to be created in the Demo environment. We also have CSG customisations and the last time I looked they don’t appear in the list of solutions to delete.
…As always there are many ways to skin a cat. This time, in hindsight, I might have done it from the inside out
