Extract Data From Micros and Aloha POS Systems 2

[stuckintexas]

Hello, I'm trying to learn about, get support or work with a solution provider to routinely extract transaction data (like an order ticket with date/time, group or party count if exists and ticket total) from Micros and Aloha POS. Frankly, I have no clue having not worked with POS systems before. My assumptions are there is a SQL database somewhere which would require some sort of authentication and from there (at least I'm hoping) a sql query to get the required data.

I'm hoping someone has done this kind of thing before and can shed light on how to do this, requirements/dependencies and potential issues and work on this.

Thanks in advance

 

[coorsman]

In aloha each dated subdirectory contains gnd*.dbf files that contain the daily sales, by item, time, check, payment, promo, void and many other detailed information. These files are created at end of day and can be read with any dbf viewer and some information are version dependent.

As far as SQL the newest version of Aloha 7.0 is SQL based.

Real time data cannot be retreived in the manor.


Cheers,
Coorsman

 

[RestaurantGenius]

They're going to totally different databases. You probably have two "easy" ways to do it if you want to avoid a year of coding.
1. Set the software to export a .csv every night that has the info that you need.
2. Sign up with a service like ctuit (

http://www.ctuit.com)

or avero (slingshot.averoinc.com) that already has the mapping to the database buildt so that you don't have to rebuild the wheel.

3. Try and contact the software companies and get a data map and then write the queries. I've seen people do it before... many times.

I hope that helps a little.

 

[stuckintexas]

Coorsman,RestaurantGenius:
Thanks for the replies guys, in both cases the first issue is 'realtime' data. From my understanding...
Coormans - I would be working off DBF files generated at the end of each day (thanks to your input, I'm parsing and posting those right now)?
RestaurantGenius - I would be working with a report generated each day/night? With respect to mitigating writing queries/working with databases, I feel confident that wouldn't be too much of an issue and I currently have schemas assuming the variance isn't going to be too much between versions.

Incremental Queries
In both cases I need a solution which would allow me to query hourly or other incremental period, probably not as much an issue with Micros as Aloha? Any input/methods, resources, solution providers on this?

Test/Development Bed
What's the best and most cost-effective way to set up a development machine so i don't have to go to a site or set up remote access?

Again, thanks in advance for your help - if you know anyone who has or is interested in providing a tried and tested solution I would really appreciate that too.

 

[RestaurantGenius]

Hey StuckinTX,
You can also try

http://www.getlinked.com.

I've never used them before but I've been refered to them a few times.

A lab machine is going to be tough because of the way they lock the software down. Both types you a hardlock key to prevent people from stealing the software so you would have to get permission from the dealer and have them let you borrow a key to run the software.

The best solution providers are avero and ctuit but they're selling a product and it sounds like you're making a new custom one so that part my be tough. I'm curious is this for sales reporting? You have more programming knowledge than I do, so you could probably get some headway with a database map and a DBA password that the dealer can provide for Micros. For Aloha it's all flat dbf files, so you can probably set up an ODBC connection.

Hope that helps.

brad

 

[pmegan]

Micros will depend on the type of system you have. Newer 9700's use SQL Server, older ones and the 8700 use a proprietary Micros database, and the 3700 uses Sybase. Not sure about the E7.

 

[stuckintexas]

RestaurantGenius:
Thanks for the links, I looked at avero and ctuit... some very sophisticated stuff there, didn't read too deeply but I if I understand correctly you were thinking I could one of their solutions to effectively get data off and out and work with it from their? The cost overhead might be high in that case.

What Am I doing? Frankly, don't know! Well, more accurately looking into transaction analysis. It all starts with getting data in hand regardless, what you do with it, where you send or what you blend it with secondary and not well defined right now but somewhat the easy part. It's this animal called POS I don't understand... closest I've been till now is ordering a Cappuccino from a Starbucks...

I think we can hack through it with a test bed and some been their experts such as yourselves with great insight and 'don't push this button' comments.

Let me know if any other great ideas come to mind... cheers RG!

 

[stuckintexas]

pmegan:
So glad you joined this... it was one of your posts I came across while browsing which made me think this was the right place to ask. Any insight/input or this project?

Cheers.

 

[RestaurantGenius]

My advise for what it's worth is to use CTUIT. I think they charge $89 bucks per site per month and their support is very awesome. We had 20 venues so we may have gotten a small discount on the price. They have written some crazy reports for me before - basicly anything that the business leaders (my bosses) could ask for. If you do contact them tell them Brad Karl sent you.

If you want to do it yourself it will be tough but it can be done. Another company that may be able to help is My Fondant. They do custom programming and they've worked with both systems. They're a little smaller so they be willing to work with you more on price if the scope of the work is small.

Hope this helps... I'm not affiliated with any of these companies. I do POS support but I have stayed away from making a new reporting product because there are already so many good ones out there and the support for the POS software itself seems to be what's lacking.

 

[stuckintexas]

RestaurantGenius:
Excellent input. Given the sited approx costs, I agree it is well worth looking at what CTUIT can provide for modelling and workflow (bridge this part of the code).

As a development project, always need to be working with source so will contact fondant to see what they can offer and hopefully further input will trickle in from this excellent professional forum.

Much appreciated sir.
Cheers.

 

[stuckintexas]

RestaurantGenius:
Thanks, that would be very helpful I'm sure - let's see how things shape up in the next few days with discovery and inputs. Key is this is a very small project looking for a small piece of code/solution right now. I's like to get to a point where we can have a more intelligent conversation together.

Looking forward and will keep you posted. Fondant looks very interesting indeed btw - Thanks Again.

 

[pmegan]

I'm a bit confused about what you're trying to do and how Avero or CTUIT will figure into the plan.

It sounds to me like you're looking to develop a transaction analysis system, which is pretty much what they are already. (BTW, there's a new kid on the block, Mirus, that's got all the competitors shaking in their boots). Any of these will create custom reports on your data after you've signed up with them, but you won't get raw data dumps to manipulate on your own out of them, especially not on a near real time basis. Also, by getting your customer's data flowing out to an analysis service you've pretty much cut yourself out of the loop outside of maybe a consultant fee.

 

[pmegan]

You may want to see if you can strike up a friendship with your local Micros dealer and get a copy of the software too. It's functional enough in demo mode for most development needs.

 

[stuckintexas]

pmegan:
Great input - thanks.

Avero or CTUIT
I think RG was looking forward beyond the request anticipating the end solution and I think you're exactly right with respect raw data streams... just shifting the parsing process to a friendly environment (my browser or machine).

Demo Copy
This is a good idea and goes to requirements and dependencies... what OS, is there a northwinds/sandbox dataset, will key/Dongle be required? Is such a thing possible for Aloha and what's required there?

As a programmer PMegan, have you done this kind of thing before/know your way around? How about real time data/incremental queries before end of day dumps? How deep is the quicksand?

Appreciate your input and expertise.
Thanks.

 

[pmegan]

I've been working on Micros systems for about 14 year and the 3700 almost exclusively for the last 10 so I can help with that side of things, but I'm not familiar enough with the other POS systems to offer anything.

Micros 3700 v4.0 - 4.9 will run on XP or 2003 Server.
3700 v5.0+ will run on Win7 Pro 64 bit or 2008 Server.
9700 will run on 2003 Server. I haven't loaded it since they switched to SQL Server so I'm not sure if a redistributable SQL load installs with it or if you have to provide it yourself.

Running in demo mode won't require a key or dongle. You'll only need one if you want to get a license, which is pretty pricey if you're not sure this is going to be a permanent thing. As far as I know there's no northwind-like database. There was an option back in the day to install a demo db, but the 3700 has branched out in so many directions that it probably wasn't possible anymore. Again, the dealership may be able to help you out, especially if you offer a decent price on custom work.

I have a bunch of incremental extractions running. You'll need to make a custom table to hold an identifier for each extraction you write and the last transaction sequence that was exported. Your query will store the last posted and last system transactions in a couple of variables, export anything in that range and then update the last posted transaction in your table. The best idea is to work your way through the code in the posting sprocs. Totals are posted incrementally throughout the day, so these procedures do something similar, just updating the table that hold daily totals instead of dumping data.

At the risk of sounding like Morpheus, the quicksand is as deep as you want to make it. The database is crazy, with over 1500 tables total and 255 that hold details use to post totals. Granted, you'll probably never have a system that uses all the tables, but there's definitely a learning curve involved.