For example, one of our EDD charts is the number of open DMR’s in the system. We would like to create a way to record that value at a set point daily and save it to a UD table to be used in another dashboard. We have a few KPI’s we’d like to track this way. Can this be done and what would the best method be?
Create a PowerShell script linked to a Windows Task Schedule that uses the DMT command line tools to export the data from a BAQ and the import it into a UD table - you then schedule this to run as often as you want the data updated
I have used this method to maintain a history of qty/value on hand every day so historical values can be interrogated
3 Likes
Hi Sue,
Thank you for your suggestion. I will have to learn how to do some of this, but your direction is clear and I appreciate you taking the time to reply. Have a great day!
Have you looked at using the Executive Query functionality? We create BAQ’s that produce the results we are looking for on a daily basis to trend. (Late Jobs, Inventory Value, etc) then we setup them up in a EQ and run on a daily process set it writes that value to the EQ. Then use the EQ table to pull into your trend charts.
2 Likes
Hi Nicholas,
We’ll look into using Executive Queries also. Thank you for your suggestion.
I’ve done this a bunch of ways over the years though mostly through a true data warehouse that does extracts and data restructuring for historical analysis like epm or eda. If you want to go big scale with snapshots over long history that’s the recommended way.
Since edd is not a data warehouse and only a dashboarding tool we’ve shied away from adding a snapshot/data extract capability to it, but the suggestions here are good ways to do it yourself then hook edd up as the visualization/dashboard surface.
Executive query does do this though it’s limited in how many dimensions you can use. Sue’s method is a bit more involved but a lot more powerful in that you can build out as many dimensions or whatever snapshotting time rules you want. Add a date of capture time to that and use it as your date dimension in edd and you can get a pretty nice historical analysis working.
the other way you can do this with edd btw is edd advanced will hook up to sql server analysis services so some folks will roll their own data warehouse that way, then use edd advanced to connect to it. That’s getting into the custom data warehouse creation space though which does require some time & skills.
Bting back corvu
Brian,
Thank you for your suggestions! I’ve started down the executive query path for now. Most of the data has few dimensions and I think I would have to build separate queries for each one. For example tracking the number of open DMR’s, Job Candidates for completion, Late PO Releases, Late Sales Order Releases all recorded at the time of the run date. The run date will be scheduled within the system agent. I think it should be pretty basic, and I certainly appreciate everyone’s input!