Ok I’ve made a lot of progress over the past two weeks. This is quite a complex project actually… but it’s about finished.
The final results look basically like this:
1. use SSIS (Sql Server Integration Services) to grab the csv files from designated directory and import them into a raw data table.
2. move any transactions from raw import data to the processing table… exclude any transactions that have already been imported.
3. using some data transformations in the form of user defined functions (UDF), make the data look like expense detail transactions
4. insert the expense detail transactions into the expense reports for the corresponding employees
5. allow employees to edit their expense transaction details in their pre-populated expense report
6. show the entirety of credit card expense transactions in a report… that shows the imported data, next to the matching expense detail records in Vision, allowing employees to accurately fill out details on their expenses, and make sure that everything has been accounted for.
7. for the payment to the credit card company, assign the cash disbursement to the different project, phase and tasks reflected in the expense detail transactions so that projects show the cash out for those expenses.
So I’ve completed everything except step 7, which I will complete over the next day or two.
Some really cool things this does:
– puts the CSV files in an archive folder and assigns a unique, logical name to them so you don’t have to worry about losing raw data history
– allows you to compare imported data from the CSV file to the actual data in expense reports by employee and expense report
– allows you to trace any imported transaction back to the actual CSV file it came from… it shows you the file name on the report
– reconciles the amounts imported vs. the amounts in the employee’s expense report so you can make sure you didn’t miss anything
– shows data that was imported into the processing section, but didn’t actually make it into an employee expense report
In a couple weeks I will provide more details on how it actually works with some screen shots of the results.
In the mean time, here is a snapshot of the entire process looked up wrapped up in an elegant diagram in SSIS. Of course, this doesn’t show any details or code… just the big picture logic of it. It’s interesting all the same.
Have an excellent day!
I’ll be at Deltek Insight in a couple weeks, and will be teaching a break out session:
PS-80 Putting Data in the Right Place
If you happen to be at Insight feel free to come say “hello” and give me some feedback on my blog!