SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strategies for dealing with Excel


Strategies for dealing with Excel

Author
Message
Tyberious Funk
Tyberious Funk
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 76
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 Smile

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)
* ?????
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5353 Visits: 4639
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 Smile

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.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2728 Visits: 3498
There's nothing wrong with using MS Access (assuming you know it) to capture the data and then bring it into a warehouse.



Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3030 Visits: 1694
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!
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8589 Visits: 7660
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
Tyberious Funk
Tyberious Funk
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 76
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?
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3030 Visits: 1694
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search