Last post Mar 22, 2011 12:03 AM by abhisheks
Mar 20, 2011 12:26 AM|Goalie35|LINK
A friend of mine approached me about re-architecting his company's entire system. They're a somewhat small company but are starting to grow pretty quickly and their current system is outdated and takes too much manual labor to interact with. I don't normally
deal with architecture of this sort but figured it would be a fun challenge and I'm looking for advice on the best way to handle this. I would greatly appreciate any help or tips you can provide. This is a rather long post, but I wanted to be as descriptive
as possible, so thanks in advance for bearing with me
Just to give you some background...his company deals with reading and measuring energy usage for all apartments within an apartment complex. His client's are the apartment landlords (not the apartment renters). His company employs field technicians to
review and measure energy usage using these custom developed tools. Once the technician has read all energy meters in the apartment complex, he downloads the data he gathered up in his little tool, to the company's server. This data is downloaded in the
form of an ms access mdb file. Each mdb file contains about 10 tables of data and is about 600kb in size. One mdb file is created for each apartment. So if an apartment complex has 50 apartments, the technician downloads 50 mdb files to the server.
Once the data is downloaded to the server, the company has a staff of about 6 data entry employees to go into this old, outdated, custom developed application they own. The purpose of this application is to allow these data entry employees to review energy
data for each apartment, and to export an apartment's data into a report (in the form of an ms word document). This ms word report is created for the purpose of providing it back to the apartment landlord. The data entry employees take these reports and
manually upload them to the company website, one by one, using the website's administration backend and link them to the correct client user account. The landlords can then log into their account and review these reports. The website is currently storing
this data in a sql server 2005 database on a dedicated server hosted by a 3rd party company.
I've provided these details in this images here:
Ok, so that's what his system currently does. He would now like to rearchitect much of it. Here's what he's looking to change:
So here are my questions that I have:
Any tips or advice you can give me as far as setting this whole process up? I want to create a process that runs as clean and smoothly as possible.
Thanks so much!
Mar 22, 2011 12:03 AM|abhisheks|LINK
Couple of observation
It looks to me you are trying to create a workflow application where technician submit data and employees review and approve data. Either way creating a datamart that stores all the data that technician submit won't be a bad idea at all.
Now to the mechanism of transforming data
1) SSIS and SSRS tools: If you have those tools at disposal, your problem can be resolved pretty easily. You can write packages that imports data from technician to a datamart. You can write web app where employee review the data. Once data is reviewed,
you can use ssrs tool to create dynamic reports, which can be exported to pdf. Exporting to pdf is supported by SSRS, so it won't matter whether it's a internal user or a external user. Since you can export pdf from reporting tools, you don't have to use any
disk space either.
2) Thoughts on your approach now:
Let me know your thoughts on these.