By day, I work for a fair-sized office products distributor. The company, like a lot of independent OP dealers, is on ECI’s DDMS platform. I’ve got my issues with the software, but it’s what we’ve got, so I do what I can to make it as painless as possible.
By coincidence, around the same time I was learning SQLite a couple years ago, DDMS introduced perhaps their greatest improvement ever: a MSSQL Express server running alongside DDMS, syncing the data from DDMS’ dbf tables into a SQL database.
I went to the ECI National Conference at the end of 2011. At that point, I was aware the SQL Server existed, but I hadn’t had time to even look at it. Chris Androlia’s presentation was something of a revelation.
If you know DDMS, you know creating new reports is a bit of a drag. Report Writer is a terrible, terrible way to produce reports. My feeling is that it goes back to those early days of DDMS, and it wasn’t until the second decade of the 21st century that DDMS got around to doing something about it. Along with the SQL Server, DDMS brought along the Preview Reports functionality. While it may not appear so, Preview Reports is using the SQL db to query the data. Designing reports works, but it’s less than ideal.
At the Nationals, Chris himself pointed out that if you know SQL, you wouldn’t like Preview Reports. If you can directly write SQL queries, there are better ways to work with the data. First on the list is Microsoft’s SQL Server Management Studio. The linked version is the one you want. While you can use the SSMS from SQL Server 2005, the version linked, version 10, for SQL Server 2008 R2, is by far superior. If for no other reason, it has code completion, which will save a ton of tedious typing if you write SQL as I do.
Before you start that up, though, we need to be sure your SQL Server is running properly on your DDMS server. To do that, go to your DDMS server, either on the console or via PCAnywhere or RDP. In the system tray, you’ll see a red DDMS icon, something like so:
Double-click it to open the EBS SQL Control Panel:
At the top, if you don’t see “Schedule engine is actively running until manually stopped”, then you’ve got some work to do. In the above image, you can see I have a lot of imports scheduled. What you’ll want to do is establish import schedules for your tables based on your business and what you want to use SQL reporting for. Establishing a good update schedule is a bit beyond the scope of what I’ll cover here, but there are a few factors to keep in mind: First, don’t run Full imports during business hours. It’s just a bad idea. Second, keep in mind that even running delta updates (that sync only changes) can bog a system down if the table is large enough. My Customer Price Plan table has something just over 700k records, and the delta I run at 3pm causes the system to hiccup a bit while it runs. On the other hand, some tables you’ll want to run deltas on quite often. I have certain times of day I run delta updates on Shipping Manifest every 5 minutes. However, this is such a comparatively small table that it makes little difference to performance (note that it is at the top in my scheduler window – this delta took 1.36 seconds, but it was running on a Sunday.)
So, figure out what you want to report on (or set up schedules for everything you use and adjust later as needed, as I do) and establish update schedules. Keep your full imports to overnights and weekends (notice my Full Customer Price Plan update took better than 11 minutes) and remember that you can establish multiple schedules for most all the tables if you need to. There is a somewhat dated but useful reference on this topic here: http://www.ddms.com/Resources/Support/faq/utilities/EBSSQLimport.pdf.
You’ll need to first import all your data, and you may need to clean up your dbf files first – SQL can be very picky about imports – it doesn’t like duplicates and it doesn’t like bad data types (special characters trying to import into int fields, for example.) You may need to work with your DDMS support people, and you might spend a lot of time doing ZF6s to get rid of bad records, but it shouldn’t take long. Have a look at this, as well: http://www.ddms.com/Resources/Support/faq/utilities/ebsvalidErrors.pdf.
Right, you’ve got SSMS installed, you’ve got your Scheduler Engine running and updates scheduled, and now you’re ready to connect. The connection window looks like so:
The server type is ‘Database Engine’. The server name is simply the network name of your DDMS server, followed by ‘\ECI2’. You’re using SQL Server Authentication, and safely or not, the credentials are the same for everyone. Username is ‘NetClient’ and the password is ‘db_user’. Save the password or not at your own discretion.
Once you’re connected, expand the Databases/Ensite/Tables tree and you’ll see something like this:
Expand a table, then expand Columns:
Congratulations. You’re now ready to get down to pulling reports more quickly than DDMS has ever been able to do on its own.
If you don’t know SQL, the best way to start is learning select *. select is how you retrieve data from SQL tables, generally speaking. Running SSMS against DDMS’ tables, there isn’t much else to do. I’m partial to Customer, myself. I might be a bit biased, though, as I do customer maintenance all day long. Now hit ‘New Query’ and type select * from Customer.
In the results pane at the bottom of the SSMS window, you’ll be greeted by something like this:
Sorry for the blurring, but there are some secrets that aren’t mine to give away. As you’ll see on the left, there are many, many columns held in Customer. Using the asterisk wildcard is a great way to see what data resides within the tables, as the column names aren’t always self-explanatory. In most cases, useful reports will only return certain portions of a particular table. (To be honest, single-table reports often aren’t that useful, but that’s a matter for later.)
Now you’ve got the basics. To get something a little more useful, you need to get a little more specific about what data you want returned. A very basic report might be Account Number, Department, Company Name, and Address. To do this, use
select Customer.Account, Customer.Department, Customer.Name, Customer.Address1, Customer.Address2, Customer.City, Customer.State, Customer.Zip
from Customer
Which results in something like this:
You can add or remove columns at will – notice that the results come back in the same order you specified the columns in the query. If you ran this and realized you need the department description as well, it might make sense to rearrange the query like so:
select Customer.Account, Customer.Name, Customer.Department, Customer.DepartmentDescription, Customer.Address1, Customer.Address2, Customer.City, Customer.State, Customer.Zip
from Customer
Notice I’ve put Department and Department Description next to each other. I find it easier to read this way, but your experience might be different, of course.
Also, something I’ll note fairly early on here – notice I always write these as Table.Column. In this query, I don’t need to specify table, since I’m only working with one. Later on, though, when we get into joining tables together, you will need to do that, and so I stay in the habit of always doing it.
Now that you have some results to see, you might want to put them into a spreadsheet program so you can work with this data. I won’t get into the various ways of doing that beyond the one I use most frequently. The results pane of SSMS is like Excel in that if you left-click the top-left corner of the table, it will select all cells. Then right-click on a selected cell for some options:
Copy with Headers is what I use most often, and simply paste results into a new workbook in Excel. Keep in mind, though, that if you have data with leading zeros, you will want to format the Excel columns as Text before pasting. If your results only contain numeric data for a column, Excel will interpret it as a Numbers column and drop leading zeros.
In this brief introduction to SQL for DDMS, I’ve shown you how to start your EBS SQL Scheduler Engine, connect to the DDMS database and get basic query results using Microsoft’s SQL Server Management Studio. In later articles, I’ll expand on this, diving deeper into more complex queries and other ways of working with the data. Feel free to email me at flux (at) bendustries (dot) co if you need assistance with any of this.
Recent Comments