Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strategies for dealing with Excel Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:05 PM
Points: 7, Visits: 33
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)
* ?????



Post #1450421
Posted Wednesday, May 8, 2013 8:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1450606
Posted Thursday, May 9, 2013 7:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 815, Visits: 2,032
There's nothing wrong with using MS Access (assuming you know it) to capture the data and then bring it into a warehouse.


Post #1451098
Posted Thursday, May 9, 2013 5:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 2,341, Visits: 1,401
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!
Post #1451383
Posted Thursday, May 9, 2013 6:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 5,383, Visits: 7,454
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1451386
Posted Thursday, May 9, 2013 6:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:05 PM
Points: 7, Visits: 33
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?

Post #1451390
Posted Monday, May 13, 2013 9:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 2,341, Visits: 1,401
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!
Post #1452175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse