Create an ODBC Data Source for DB

[DougA]

For some of our reporting it would be considerably easier to use either MS Access or MS Excel. Can I set up an ODBC data source to it? If so, what would the settings need to be?

[Gerry]

Hi Doug,

At this time we have no way of providing you direct access to the underlying database for this purpose.  Being a SaaS service we have to continuously optimise queries and database performance, something we could not do with such an arrangement. 

Gerry

[NeilWJ]

Hi Doug,

Adding to that if you want to get your data in CSV format you can use the Data view in the admin tool under Home -> System -> Database Direct. From here you can query your data and export it to CSV which you could then use in Excel and do what you like with it.

Cheers

[DougA]

I'm exporting the data at the moment through the reporting tool and then processing the raw data in Excel pivot tables. Because we're a small department I'd really like to automate at much as possible especially repetitive tasks such as monthly reports. 

The existing report writer doesn't deliver what the customer is looking for. e.g. a list for each request type of categories with count of requests ordered by the count in descending order.

[NeilWJ]

Hi Doug,

You need a report to show a list of counts by request category grouped by request type? That should be do-able.

If you clarify what columns you are selecting on an i am sure we can see if its possible. The reporting tool does support grouped count lists. If you can provide us with example report you generate that will help greatly.

If we need to modify the reporting tool to make a report we can within reason i.e. we are not trying to mimic crystal reports here.

Cheers

[DougA]

I appreciate everyone's input into this. However, I would use Crystal Reports if only I could get a connection to the db! I know there's an ODBC connector for MySQL, I've used it before to provide a live link to data. A few minutes searching confirmed that there's one from MariaDB as well.

Is there a risk that someone could write a poorly optimised sql query? Possibly, but couldn't I do that through any of the SQL tools you already provide?

We're a small team and we need our management tools to be as admin light as possible. The reason for asking is to reduce the amount of time I'm spending on creating reports. Go to the reports and download the extract. save it, import it into whatever tool I'm using, invariably I then have to tweak the data before I can generate a report. Now extrapolate that by the number of reports required and that's a chunk of my time gone. For us (and I suspect many others) to have end-user ready reporting automatically created would be a god-send.

Please could I make a formal request for ODBC connectivity to be made available?

Many thanks

Doug

 

[Gerry]

Hi Doug,

Yes someone could possible load the database through the tools we already provide, however we have restricted the amount of direct DB access via SQL you can perform, we have application-level pre-parsing of SQL to prevent harmful statements and we have application-level load supervision and controls to watch for dumb queries and can kill them before any harm is done to the performance of the system/instance

I cannot give you a positive response to your request because as I say there is currently no technical way for us to provide this to you in a safe an appropriate way.  We would not provide customers direct access to the underlying database raw as it would be too risky and would mean delivering performance to our performance standards could easily be compromised.  

One day in the future we may offer a data replication service where a separate database is run in our DC and your instance data would be replicated to it, this server we could provide you direct access via the MySQL wire protocol to without any issues, this would mean you could use an ODBC driver.  However, I must stress that while we have this under consideration we have no technical solution to provide this, we still need to consider security and other issues. 

Gerry

[DougA]

Thanks Gerry,

Not what I wanted to hear but that's life. I think I'm going to have to schedule a monthly extract of all of the data. Then I can use a proper reporting tool to split it up for each recipient and email them their section. 

I have to generate the performance statistics, extract the incidents, service requests and change management for each watch (4 per fire station), and a station summary to the station commander for 36 stations. It's simply too much to be handled manually.