I have a legacy workflow that I would like to optimize and automate (at least partially). The current workflow goes like this. I receive a MS Access .mdb file containing a single table. This table is then loaded into an an existing Access db that contained some macros ,which I will reference later, and renamed BTMUA. To the table BTMUA several fields are manually inserted into the middle of the table. Additionally two fields data types are changed from text to date to more accurately reflect the data they contain. Once these changes have been completed a series of queries, via two macros, are run to parse, trim, and concatenate data from the original fields into the new fields. Specifically Field A id parsed into fields A1 A2 using a left and right functions, likewise field B is parsed into field B1 and B2. Any whitespaces are removed from fields A1, A2, B1, and B2 fields are removed. Finally fields A1, A2, B1, B2, and C are concatenated in one of 7 different ways depending on which fields actually contain data. Here ends the data modification and my use of Access.

I then use FME Desktop load this data into an existing table in my MS Sql based SDE GDB (after first truncating the table).

I would like to redesign this workflow and remove Access from the equation although I cannot change the fact that the data will originate in a .mdb file. I would also like to automate the process as much as possible so it can run either with a few mouse clicks or on a fixed schedule.

Basically I am trying to figure out if it is best to use FME to load the data from the .mdb into my GDB and use either ArcCatalog and Model Builder or MS SQL Server management Studio an T-SQL to transform the data. Or is it more efficient to utilize FME Desktop to do the data trans formations.

2 Answers
2

If you are using FME to do the data load, I think you may as well use it to read the original table and do the transformation stage. Then it's all in one process. If you do the work in Workbench then there are transformers for parsing, trimming, and concatenating.

I won't say this is the best way, or compare it to any others, because I currently work for Safe Software (makers of FME) and I don't think that would be ethical on here. However, I do think it would be a definite improvement on the current process, and quite simple to set up too.

Would he be able to schedule this process, too? I personally think that it would be legitimate for you to compare and contrast with other methods because you've disclosed your connection to the company - and hey, he's already got the software, so it's not like you're going to trick him into buying it :)
–
Matt ParkerJan 28 '11 at 18:52

2

Yes, that could be scheduled. All FME processes can be kicked off by a command line argument, so any scheduling tool should be able to handle it. FME Server 2011 also has a scheduling tool built in, if you wanted to go that far.
–
Mark IrelandJan 28 '11 at 18:56

I'm by no means an expert at this kind of thing and I'm not at all familiar with FME Desktop, but if I were in your shoes, I'd bridge the two databases with a script that pulls the data over ODBC, completes all the modifications in-script, and then pushes the results to your SQL Server DB (again over ODBC).

I'd write that script in R, because it's what I know and the kinds of data cleaning you describe are trivial in it, but you could probably do that in just about anything with a decent ODBC library - Python might be more palatable for your organization. Scheduling that to run would then be trivial.