Hmm... I need to find out myself. I don't know what is the answer. I'll do some research and get back to you if I discover an anything. You should email the people at iPage as they probably can answer it..
One thing to remember whenever reporting directly off of your transactional database is that your reporting queries will actually slow down theresponse time the customers in the shop have, especially as your reporting structures become more and more de-normalized..
I always recommend backing your data up, and restoring it to a different database, and using that as an operational data store, to support all of your intermediate and long term analysis...
As long as you are just pulling the info into excel, no problem dont make it where you are 'linking' to the live data, where you can wipe out some if your data...
That depends on how you are 'pulling the data into excel', if you are putting together very complex joins, building temporary temp and work table, and using processor intensive SQL queries before you pull it into excel, you will still see a DB performance degradation..
For small shops, this probalby isn't a big deal. For larger shops that do alot of web log reporting, this can be huge...
Thanks for all this..
I like the idea of using a backup as an ODS. For a proper analysis of data I'm sure that is the way to go..
For the quick and dirty reports I want to pull back into Excel I think I'll continue with the MySQL > MSQuery > Excel > PivotTable route - all this reporting would be done at quiet times anyway...
Can I just check something here.
Does this mean pulling back tables into MSAccess with no joins and then doing the more intensive queries with all the joins on the local data source? I guess in that case the MSAccess DB becomes the ODS?.
Have I got this right?..
I've done both ways, it really depends on the amount of data needed,.
Eg if it's a big table where I can easily select the records needed, I'd use attached tables unless the performance is really bad..
If you are going to do regular reporting and have lot's of data it might be better to import in the mdb.
Keeping your store running smoothly is the most important consideration..
Being able to use attached tables is hand as your report will always be using the latest data. If your reports are more time/periodically, you might be bettter of with exporting the data whenever you are starting a new period...
Just a quick note, I've been a Data Warehouse Programmer for a fortune 500 company for about 5 years now..
I'd be very interested in contributing to any development to further the reporting capibilities of OSC. Let me know if there's anything I can help with...
I'm probably doing this in the wrong order having posted a couple of threads about ODBC in the last few days.
But does anyone have any watchouts or cautions against using an ODBC connection to the dB from something like Excel to provide flexible reporting capability to osC. The functionality it gives - with a bit of Excel and VBA knowledge - is fantastic but I wonder if there's something I'm missing........I can't see any issues..
Gurus, please weigh in...
To extract data I don't see any real issue using ODBC.
I'd only be concerned with the number of joins you might need and a possibility to create queries on top of other queries (views)..
Should investigate if your mysql version supports database views as it will make querying much easier if you can reuse/standardize on a flattened table..
In the past I've used msaccess to create that intermediate layer simulating database views, and then used excel linking back to access queries to create for instance cross tab queries very easily..
I have used excel built-in queries in the past, but it generally preferred to work this out in access..
If you are looking to create reports with for instance tabular data, you could also do this in msword but it's also more tricky to change the query definition, though for creating layout and emailing reports it works very well...