Got to get those numbers
December 18, 2006 12:09 PM Subscribe
I run operations for a fairly large retail chain in Europe with shops all over the continent and beyond. I need to capture some basic data from these shops that either doesn't exist in our (incredibly complex and fragile system infrastructure,) or lives in systems that don't talk to each other. As is often the case, there's
My company (largely a wholesaler) has decided to centralise management of our retail shops for all of Europe and further afield. Previously, each country went its own way, resulting in a dog's breakfast of incompatible systems out of which my team and I are trying to bring some sense. We're engaged in a project to install a common ePOS system through the entire portfolio and are using a third-party solution to manage our replenishment.
I need to capture things that won't live in the ePOS (or other common systems for a l-o-n-g time,) such as staff hours used, traffic counts and the like. The best my over-worked (and frankly not very responsive) corporate IT department can come up with is an Excel spreadsheet on the WAN. Fine-ish if only a couple of people needed to fill in data, notso-hotso with a couple of hundred...
So - what are my options? My Google-fu is evidently limited as the best I could find was AskGet which is probably too heavy and expensive for my simple needs.
What I'm looking for is a way for my store Managers to key in couple of figures into a simple form at the end of each day and then for analysis team to be able to get at these numbers, by store, in a basic format, ideally a .csv or plain text file so they can work their spreadsheet magic. Does such a service exist on the web? I realise it would be far simpler to have a simple page on the corporate intranet to do this capture would be simpler and probably cheaper, but that ain't gonna happen anytime soon.
If AskGet is my only real option, does anyone have any experience with them?
Thanks, HM!
My company (largely a wholesaler) has decided to centralise management of our retail shops for all of Europe and further afield. Previously, each country went its own way, resulting in a dog's breakfast of incompatible systems out of which my team and I are trying to bring some sense. We're engaged in a project to install a common ePOS system through the entire portfolio and are using a third-party solution to manage our replenishment.
I need to capture things that won't live in the ePOS (or other common systems for a l-o-n-g time,) such as staff hours used, traffic counts and the like. The best my over-worked (and frankly not very responsive) corporate IT department can come up with is an Excel spreadsheet on the WAN. Fine-ish if only a couple of people needed to fill in data, notso-hotso with a couple of hundred...
So - what are my options? My Google-fu is evidently limited as the best I could find was AskGet which is probably too heavy and expensive for my simple needs.
What I'm looking for is a way for my store Managers to key in couple of figures into a simple form at the end of each day and then for analysis team to be able to get at these numbers, by store, in a basic format, ideally a .csv or plain text file so they can work their spreadsheet magic. Does such a service exist on the web? I realise it would be far simpler to have a simple page on the corporate intranet to do this capture would be simpler and probably cheaper, but that ain't gonna happen anytime soon.
If AskGet is my only real option, does anyone have any experience with them?
Thanks, HM!
I don't know of any existing web services, but this wouldn't be very hard to create with PHP or another web development language, so maybe you can check with the IT (development?) department to see if they can create something?
You said that wouldn't happen anytime soon, but it's really the easiest, fastest and best solution.
posted by jayden at 1:05 PM on December 18, 2006
You said that wouldn't happen anytime soon, but it's really the easiest, fastest and best solution.
posted by jayden at 1:05 PM on December 18, 2006
Or better yet, scrape together a couple hundred bucks, figure out what programming language is supported on the corporate intranet, and find a software contractor to knock together the script for you. That's probably the fastest solution.
posted by SpecialK at 1:14 PM on December 18, 2006
posted by SpecialK at 1:14 PM on December 18, 2006
On your scale, it sounds like you need Enterprise Resource Planning (ERP) software.
ERP vendors include SAP and Siebel (now Oracle), among others.
You might consider these over jury-rigged solutions proposed above. Mainly because it is easier to hire IT staff who are trained to manage these systems, but also because, as your business expands, your custom software probably won't.
posted by Blazecock Pileon at 1:23 PM on December 18, 2006
ERP vendors include SAP and Siebel (now Oracle), among others.
You might consider these over jury-rigged solutions proposed above. Mainly because it is easier to hire IT staff who are trained to manage these systems, but also because, as your business expands, your custom software probably won't.
posted by Blazecock Pileon at 1:23 PM on December 18, 2006
I always cringe when I see people sacrificing the best solution for the right-now solution. Sounds like your company needs to invest in a serious system revamp. You can single-solution your POS with your other data capture needs - there's a lot of companies out there doing this kind of thing, but if your company isn't willing to consider the best long-term option, sounds like you're stuck. If they are willing to consider something better, my email is in profile - I'm in the Retail practice of my company, which happens to be a global biz consulting firm, and can probably get you/your company in touch with some Euro partners who can discuss options with you.
If IT can't help you anymore than you've mentioned, maybe multiple Google spreadsheets? Slightly better alternative to the Excel one - perhaps break them down to the one-per-country/region/whatever? Then have some poor peon to reap and process the inputs.
posted by allkindsoftime at 1:26 PM on December 18, 2006
If IT can't help you anymore than you've mentioned, maybe multiple Google spreadsheets? Slightly better alternative to the Excel one - perhaps break them down to the one-per-country/region/whatever? Then have some poor peon to reap and process the inputs.
posted by allkindsoftime at 1:26 PM on December 18, 2006
What I'm looking for is a way for my store Managers to key in couple of figures into a simple form at the end of each day and then for analysis team to be able to get at these numbers, by store, in a basic format, ideally a .csv or plain text file so they can work their spreadsheet magic."
You also need some ability for managers to correct mistakes, submit late numbers when they forgot, or the power was out, etc. As you said, a page on the intranet that writes directly to a corporate database is the correct solution, but it sounds like that is politically unfeasible at the moment.
I would establish a standard excel spreadsheet format for managers to enter this data into. Each manager updates their own copy and sends it to HQ, whether through email or a shared drive location. You could also slap a quick visual basic front end to automate this process to minimize the impact on the managers.
On the back end, a scheduled application (vb is good for working with excel) to import the spreadsheets into a relational database (this database should be designed with long-term use in mind, eventually coupled with the intranet page when the klugey excel solution goes away). You'll want some process oriented reporting, tracking of delinquent spreadsheets, errors, etc.
posted by Manjusri at 2:46 PM on December 18, 2006
You also need some ability for managers to correct mistakes, submit late numbers when they forgot, or the power was out, etc. As you said, a page on the intranet that writes directly to a corporate database is the correct solution, but it sounds like that is politically unfeasible at the moment.
I would establish a standard excel spreadsheet format for managers to enter this data into. Each manager updates their own copy and sends it to HQ, whether through email or a shared drive location. You could also slap a quick visual basic front end to automate this process to minimize the impact on the managers.
On the back end, a scheduled application (vb is good for working with excel) to import the spreadsheets into a relational database (this database should be designed with long-term use in mind, eventually coupled with the intranet page when the klugey excel solution goes away). You'll want some process oriented reporting, tracking of delinquent spreadsheets, errors, etc.
posted by Manjusri at 2:46 PM on December 18, 2006
I'm with allkindsoftime in my mortification. I love Excel for all kinds of things myself but it is -not- a database and shouldn't be used for that. Yes you can program off of it, but good lord that's a messy proposition. An Intranet page would be (a) safer for the data (b) simpler to administer (c) less prone to getting fuxed in transit, etc. etc. If the data is valuable, its worth treating that way.
I mean, how is this for a scenario:
Malware finds its way onto remote site X's system which dropes an Excel payload. Excel payload infects data which is then uploaded to corporate. The WAN happily distributes the virus along the supply chain. Thanks IT!
What's horrific to me about this is that the application to be written sounds so simple but tied up in so much BS beaurocracy.
posted by Ogre Lawless at 4:45 PM on December 18, 2006
I mean, how is this for a scenario:
Malware finds its way onto remote site X's system which dropes an Excel payload. Excel payload infects data which is then uploaded to corporate. The WAN happily distributes the virus along the supply chain. Thanks IT!
What's horrific to me about this is that the application to be written sounds so simple but tied up in so much BS beaurocracy.
posted by Ogre Lawless at 4:45 PM on December 18, 2006
It would be pretty easy for a competent web developer to write a simple Perl (or other) web script to handle this. Since this wouldn't be so critical that a few minutes of server downtime per year would lose you millions of dollars, you could probably get a simple custom application hosted on a virtual server, if you were being cheap (and why not, just to start).
You could have a custom-designed form, with a back end that would store each day/store result however you like (you can get basic database hosting with most web hosting packages, or store in the file system). If a store wanted to change/update their entries, they'd just overwrite that entry. Then, your analysts could display results for a particular day in whatever format you want - a separate Perl (or whatever) script could read the relevant files and send them to the web browser in CSV or whatever format you want.
If you do store the data in a real database, you get additional flexibility if you learn how to do actual SQL queries later. You can also import this data into a later more generalized application if you want to.
The points above about this not growing well over time are good ones, but if you just want something simple, why not go this direct route? It's not perfect, but it will get you your data, and you can always put the data into a better system later.
posted by amtho at 5:14 PM on December 18, 2006
You could have a custom-designed form, with a back end that would store each day/store result however you like (you can get basic database hosting with most web hosting packages, or store in the file system). If a store wanted to change/update their entries, they'd just overwrite that entry. Then, your analysts could display results for a particular day in whatever format you want - a separate Perl (or whatever) script could read the relevant files and send them to the web browser in CSV or whatever format you want.
If you do store the data in a real database, you get additional flexibility if you learn how to do actual SQL queries later. You can also import this data into a later more generalized application if you want to.
The points above about this not growing well over time are good ones, but if you just want something simple, why not go this direct route? It's not perfect, but it will get you your data, and you can always put the data into a better system later.
posted by amtho at 5:14 PM on December 18, 2006
For a running cost about 10 cents a month per store, and (big and here) your store managers have ready access to the general Internet, you could easily setup a shared Web host account, with a MySQL database, and a simple login screen to an SSL secured directory for store managers and your data analysts. Behind that, on the Web server, would live a simple PHP operated Web entry form for the store managers, and a simple query entry form for your data analysts. Data entered in the entry form would be stored in a simple table or a few linked tables. Your data analysts would login to their query forms, and specify things like dates and store numbers as filter criteria for stored queries they would routinely run, and get flat file dumps of their data, sent as CSV formatted text file attachments to emails they'd receive nearly instantly after running their queries.
All told. you'd be doing what AskGet is offering, but for yourself, and you'd have total control of the data and of the means by which you'd collect, store and retrieve the data for analysis. Start to finish, for an experienced PHP/MySQL administrator, this is a project measured in hours, not days. So, if you outsource it, perhaps using MeFi Jobs, your cost for setting it up might be a few hundred euros, total, or perhaps a couple euro per store. Be sure to include a daily script for making an online backup of the database, preferably 5 or 6 days deep, in a rotating ring, in case something happens to the active database file on any given day.
You probably wouldn't even to need to tell corporate IT about it. If you find your Web developer by close of business tomorrow, you should be looking at a working system by Thursday afternoon.
posted by paulsc at 7:00 PM on December 18, 2006
All told. you'd be doing what AskGet is offering, but for yourself, and you'd have total control of the data and of the means by which you'd collect, store and retrieve the data for analysis. Start to finish, for an experienced PHP/MySQL administrator, this is a project measured in hours, not days. So, if you outsource it, perhaps using MeFi Jobs, your cost for setting it up might be a few hundred euros, total, or perhaps a couple euro per store. Be sure to include a daily script for making an online backup of the database, preferably 5 or 6 days deep, in a rotating ring, in case something happens to the active database file on any given day.
You probably wouldn't even to need to tell corporate IT about it. If you find your Web developer by close of business tomorrow, you should be looking at a working system by Thursday afternoon.
posted by paulsc at 7:00 PM on December 18, 2006
Personally, I cringe when consultants, etc. advocate super-complex solutions over "get it done now" solutions. First of all, you're not going to know what's right until you've got version 1 in production for a little while. You're not even going to know what the hard parts are. Second, even ignoring the corporate difficulties with starting a project like "implement an ERP system", projects like that have an insanely high failure rate, and usually cost years/millions even to get to failure (personally, I worked at large company that spent ~$10m and two years on a JD Edwards rollout that was ultimately abandoned. It would be nice not to rely on anecdotal evidence, though. Fortunately, you don't have to). Basically, my theory is stuff actually gets done at large companies when employees work around the firm's inertia, rather than add to it (working around this inertia and plowing through politics are where consulting firms are most useful). I feel my skin turning a little pepsi blue posting this, but the company I work for's software is designed to deal with problems not entirely disimilar to this, but I'd have to know a bit more about yours to say for sure if we make sense. Email's in profile if you're interested. Beyond that, I'd say go with WuFoo/DabbleDB or the craigslisty web consultant.
In fulfillment of internet law, I must post that no discussion of "right now" vs. "right way" is complete without the obligatory Worse is Better link.
posted by jeb at 8:34 PM on December 18, 2006
In fulfillment of internet law, I must post that no discussion of "right now" vs. "right way" is complete without the obligatory Worse is Better link.
posted by jeb at 8:34 PM on December 18, 2006
Response by poster: Thanks all for the thoughtful responses. I tend to agree with both camps - the quick, "get it done now" solution usually isn't scaleable, the long-term solution is often more robust but less flexible.
I should have mentioned, we already use an ERP, the one with a three letter name, but only as yet the wholesale modules. I should also have mentioned that the solution I'm looking for would be a short-term solution, for a couple of years - effectively the 1.0 version that jeb mentioned.
Will look again at hiring in a web-developer for a more internal solution.
posted by Expat at 8:56 PM on December 18, 2006
I should have mentioned, we already use an ERP, the one with a three letter name, but only as yet the wholesale modules. I should also have mentioned that the solution I'm looking for would be a short-term solution, for a couple of years - effectively the 1.0 version that jeb mentioned.
Will look again at hiring in a web-developer for a more internal solution.
posted by Expat at 8:56 PM on December 18, 2006
If you're going for a web developer, I'd suggest someone programming in a super-fast development language (like Ruby or ColdFusion). Scalability is really only necessary when you're talking about thousands of transactions an hour. You're talking maybe a thousand a day, right? Ruby or ColdFusion linked to MySQL or MS SQL can easily, *easily* handle that much traffic even if written *poorly*.
What you want is something that comes out fast, is very simple in its interface, and can be changed later on. Get someone who's really good at databases to set up how the data is organized, as that's harder to fix later on.
A project like this, assuming you don't drag it out too long via office politics, should cost a couple grand or so tops to have developed.
posted by Deathalicious at 10:25 PM on December 18, 2006
What you want is something that comes out fast, is very simple in its interface, and can be changed later on. Get someone who's really good at databases to set up how the data is organized, as that's harder to fix later on.
A project like this, assuming you don't drag it out too long via office politics, should cost a couple grand or so tops to have developed.
posted by Deathalicious at 10:25 PM on December 18, 2006
On your scale, it sounds like you need Enterprise Resource Planning (ERP) software....You might consider these over jury-rigged solutions proposed above..... as your business expands, your custom software probably won't.
I respectfully disagree. It's only anecdotal, but I know of a number of instances where Enterprise-level solutions have been inferior to custom written software made by smaller companies. One example: a certain famous institution in DC which paid 6 figures for an enterprise-level CMS system hardly uses the system at all due to usability issues. One department does have a functioning CMS site, however -- based on software they purchased for a few grand from a small-town web development firm.
This fascinating article outlines why and how shadow systems -- homegrown systems built in direct opposition to "enterprise systems" -- come about, and what they tell us about enterprise systems in general.
I'm not necessarily badmouthing enterprise solutions (okay, so maybe I am) but the nice thing about custom solutions is just that -- that they're custom. They match exactly what you need.
Also, with rapid development becoming easier and machines becoming faster, this old kind of development is becoming increasingly irrelevent. If you're the stock exchange, yes, you need enterprise level systems. But if you're an average company -- even if you have thousands of employees -- you don't.
posted by Deathalicious at 10:39 PM on December 18, 2006
I respectfully disagree. It's only anecdotal, but I know of a number of instances where Enterprise-level solutions have been inferior to custom written software made by smaller companies. One example: a certain famous institution in DC which paid 6 figures for an enterprise-level CMS system hardly uses the system at all due to usability issues. One department does have a functioning CMS site, however -- based on software they purchased for a few grand from a small-town web development firm.
This fascinating article outlines why and how shadow systems -- homegrown systems built in direct opposition to "enterprise systems" -- come about, and what they tell us about enterprise systems in general.
I'm not necessarily badmouthing enterprise solutions (okay, so maybe I am) but the nice thing about custom solutions is just that -- that they're custom. They match exactly what you need.
Also, with rapid development becoming easier and machines becoming faster, this old kind of development is becoming increasingly irrelevent. If you're the stock exchange, yes, you need enterprise level systems. But if you're an average company -- even if you have thousands of employees -- you don't.
posted by Deathalicious at 10:39 PM on December 18, 2006
This thread is closed to new comments.
posted by Expat at 12:24 PM on December 18, 2006