May 8, 2013 at 12:40 am
Like a lot of organisations, we struggle with the fact that a lot of users manage important business data in excel spreadsheets. Wherever possible, I've been encouraging them to move primary data out of excel and into our corporate systems... leaving excel for just analysis. Once the data is being managed in a corporate system, I can then offer them automated reports... which generally makes everybody happy 🙂
However, there are plenty of examples that I come across where existing corporate systems simply don't have the capacity or capability of storing the data we've been capturing in excel. Nor is there any real cost-benefit in implementing a new system.
For example... we have a small team that handles customer complaints. They currently manage complaints data in an excel spreadsheet, and asked if my team could help automate their reporting. Most of our customer data is already stored in our ERP, but we don't have a CRM so there is nowhere to really store complaints information.
So what do we do?
* Continue to let them maintain their data in spreadsheets, but use SSIS to suck data into the warehouse on a regular basis
* Build some kind of simple bespoke application using Access (ugh) or web forms, with a SQL Server backend
* Use some kind of Sharepoint functionality (unfortunately, not currently implemented around here)
* ?????
May 8, 2013 at 8:27 am
Tyberious Funk (5/8/2013)
Like a lot of organisations, we struggle with the fact that a lot of users manage important business data in excel spreadsheets. Wherever possible, I've been encouraging them to move primary data out of excel and into our corporate systems... leaving excel for just analysis. Once the data is being managed in a corporate system, I can then offer them automated reports... which generally makes everybody happy 🙂However, there are plenty of examples that I come across where existing corporate systems simply don't have the capacity or capability of storing the data we've been capturing in excel. Nor is there any real cost-benefit in implementing a new system.
For example... we have a small team that handles customer complaints. They currently manage complaints data in an excel spreadsheet, and asked if my team could help automate their reporting. Most of our customer data is already stored in our ERP, but we don't have a CRM so there is nowhere to really store complaints information.
So what do we do?
* Continue to let them maintain their data in spreadsheets, but use SSIS to suck data into the warehouse on a regular basis
* Build some kind of simple bespoke application using Access (ugh) or web forms, with a SQL Server backend
* Use some kind of Sharepoint functionality (unfortunately, not currently implemented around here)
* ?????
I would outline the different solutions e.g. build/acquire a CRM, etc. including functionality to be provided, cost and an estimate for delivery. I would also include the option of doing nothing and quantify what the company is loosing for handling complains in that way; then, let upper management decide.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 9, 2013 at 7:04 am
There's nothing wrong with using MS Access (assuming you know it) to capture the data and then bring it into a warehouse.
May 9, 2013 at 5:22 pm
Tyberious Funk (5/8/2013)
* Build some kind of simple bespoke application using Access (ugh) or web forms, with a SQL Server backend
* Use some kind of Sharepoint functionality (unfortunately, not currently implemented around here)
* ?????
I would look into standing up a simple web form app and an SQL Server database on the backend.
Access is fine if it is backed up properly and if there is only one version of it. When we use to stand up Access databases we found that users would copy the entire mdb to their workstation and enter data in there, thus causing four of five bootlegged copies to live in parts unknown. We now keep control by using one codebase on an application server and a single database in SQL Server.
Miles...
Not all gray hairs are Dinosaurs!
May 9, 2013 at 6:25 pm
Miles, I used to do a similar process but using linked tables it avoided them keeping local data that needed to be merged later. Made my life so much easier. 🙂
Tyberius, I believe the options outlined above are really your best choice. You're not in a position to know all the business priorities, at least not usually. Set up a very basic ROI expectation for the process and losses, expected times for lack of automation etc, and then what it'll take to build a simple system that mimics what they have but will allow for better options in the future.
You'll be surprised to find out that sometimes the right answer, for expenses, is to simply hire a secretary or admin assistant to take care of the grind tasks for teams like that.
Now, some things you can do to make your life much easier however is to ask them to take their files and save them as CSVs for you. You don't want to try to hash out the Excel drivers, they're painful. From there, start stuffing them into a simplified warehouse and seeing if you can derive a simple model for them.
If the spreadsheet is simple enough that it's that comfortable, it shouldn't be too bad of a model to split up and then the forms from an access front end should practically write themselves. In the meanwhile just delta the spreadsheets they send you. That should get you a baseline for reports even if you don't switch them over to an app interface, in case the execs decide the project's a go.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 9, 2013 at 6:44 pm
Miles Neale (5/9/2013)
Tyberious Funk (5/8/2013)
* Build some kind of simple bespoke application using Access (ugh) or web forms, with a SQL Server backend
* Use some kind of Sharepoint functionality (unfortunately, not currently implemented around here)
* ?????
I would look into standing up a simple web form app and an SQL Server database on the backend.
Intuitively, this is my preference too. But do you have any suggestions/recommendations on the tools or technology?
May 13, 2013 at 9:35 am
C# .Net web pages or forms app whatever your architecture supports. The trick is in the data model since there could be a multiple of uses for this type of app in the future, build the data model in a relational manor so you can expand the uses later as you build on to the process.
FYI - The old adage plays here, that being - Once a user sees how a system can help them do the job better they want more.
M.
Not all gray hairs are Dinosaurs!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply