How to let customers use their own reporting tools on a SaaS application

reporting

We have a SaaS application that generates a lot of operational data that our customers want to report on. We have a built-in business intelligence tool, but some of our customers want to use their existing tools, like Tableau or Cognos.

Some of them have asked us to let them connect to their SQL database directly over a VPN tunnel. That works, but it will be difficult to manage if we have more than a few.

Is there any standard way to let outside reporting tools access data without opening a direct network connection to the database? By "standard" I mean something that we can implement that will be usable by a lot of common BI and reporting tools.

Best Answer

Do not permit ad hoc querying against the operational database, no matter if it is internally generated or external. The potential to lock data or saturate resources is too large. Client B is not going to forgive the SLA breach caused by client A submitting a malformed query then going for lunch.

Separating each customer's data from the others' is important, too. Even if the data isn't "sensitive" it is not professional and can lead to awkward conversations when A realises their outcomes are so much worse than B's. The question doesn't make clear if the source is multi-tenant.

One solution is to replicate / mirror / copy data from the operational DB to a second, separate, isolated DB. Use whatever tool your product supports. This may be an opportunity to do some transformation if required and supported by the tool. The lag between primary and secondary is to be negotiated with the customer in the light of license, set-up and support costs. Grant read-only access on this secondary to the client. Ideally the primary and secondary would be on separate boxes with a firewall between since the latter will be externally accessible. Be kind to yourself on the SLA you agree for the secondary.

If the client can accept a reporting lag on the order of hours you could simply ftp the data to them on a schedule.

Is there a better option than VPNs? I'm not a networking expert, but I'd say that unless you have a specific problem with using a VPN, go with it.

Related Solutions

Sql-server – Looking for advice on how to integrate data from 100+ client DB’s into a centralized reporting database

Do we need to consider replicating each database individually and then perhaps use some sort of data virtualization technique to combine data from the various replicated sources?

Yes. You can host multiple subscriber databases on a single instance, and then query across them with views, or load them into a consolidated database.

Sql-server – Aggregating Multiple COTS Databases into a Data Warehouse or similar for Reporting

It appears that you just want a copy of all your databases centrally located, which could then be used to run the same report for different regions, just by changing the datasource?

You could acheive this with a single Server and a single instance.

While I wouldn't call this a data warehouse (per se), but it might be the simplest solution to solve your requirements.

With this approach you would end up with 42 databases on your instance and you could name them anything thing you like, but I would suggest including the source location in the name of the database

- for example
   [Source Server].SourceDB    -->      [Dest Server].DestDB
  [Sydney.xyz.com].Database1   -->  [Central.xyz.com].Sydney_Database1
  [Sydney.xyz.com].Database2   -->  [Central.xyz.com].Sydney_Database2
[Brisbane.xyz.com].Database1   -->  [Central.xyz.com].Brisbane_Database1
[Brisbane.xyz.com].Database2   -->  [Central.xyz.com].Brisbane_Database2
etc...

You could achieve this via

Replication ,or
Log Shipping

Both of these solutions are relatively simple (assuming you have network access between all your nodes), and replication you only need to replicate speific objects (tables, procs etc) that you use in your reports (whereas log shipping will be the whole database), but replication can be problematic to troubleshoot.