7 ms·

Sort of off topic, but every cool BI tool I can find (prompted by recently seeing metabase and AWS quicksight) seems to work with one database at a time. When

Sort of off topic, but every cool BI tool I can find (prompted by recently seeing metabase and AWS quicksight) seems to work with one database at a time. When working with microservices, your data is spread across some N databases, rendering most BI tools, from what I can see, completely useless for reporting on more than one service at a time. Are there any solutions for this? Or is the only one right now just dumping all your service DBs into a single DB for analytics? edit: thanks friends. ETL it is. don't know why I thought it would make sense to have a tool that reports across multiple databases since the performance would be horrific...although maybe there's space for a hosted BI tool that does ETL automagically. just a thought

smithian 11y ago
I've done some minor stuff in MS PowerBI and PowerQuery/PowerPivot that I think meets your need. It's pretty nifty. https://powerbi.microsoft.com/ https://powerbi.microsoft.com/ http://www.powerpivotpro.com/what-is-power-pivot/ http://www.powerpivotpro.com/what-is-power-pivot/
jrpt 11y ago
Some BI tools can connect to multiple databases, however the right solution is to put all your data into a single data warehouse and run the analysis off of that. Query performance is one reason, and here's a blog post I've written with some more: http://dataducketl.com/blog/every-tech-company-needs-a-data-warehouse http://dataducketl.com/blog/every-tech-company-needs-a-data-...
buro9 11y ago
The solution is to use an ETL (Extract, Transform, and Load) tool to copy data to a warehouse. You then point the BI tool at the warehouse. There are lots of tools for this already, and even more custom solutions and other things. CSV files tend to rule here... scheduled exports that are picked up by the ETL tools. The transformations that take place are usually normalisation of data from systems of differing schema or different types of data (perhaps changing strings into corresponding numbers, i.e. Risk Level = Orange could be normalised as 50% in the data warehouse). Businesses have taken this approach for a long long time as the databases that drive applications, services, and now micro-services have always been tuned for the performance of the production use-case and not for ad-hoc reporting.
mipmap04 11y ago
I've worked in this space a lot and in my experience, the only true solution is some sort of layer below your reporting tool to aggregate the data. That being said, it's not always a database that physically stores the data. It simply needs to be a semantic layer that models the way in which the data will be queried. Additionally, your OLTP systems are not optimized for reporting and you'll quickly choke yourself out trying to perform complex analysis on data structures attempting to solve a problem for which they weren't designed.
vog 11y ago
You will have N databases no matter what. Almost every company has some legacy systems around, or simply uses different tools for different jobs. However, you still want to copy & combine your data into a single database. The relevant Fowler patterns are: http://martinfowler.com/bliki/DataLake.html http://martinfowler.com/bliki/DataLake.html http://martinfowler.com/bliki/ReportingDatabase.html http://martinfowler.com/bliki/ReportingDatabase.html More precicely, it is a common pattern to split the analtics into three parts: 1) Collecting the data (using lots of adapters) into a "data lake", 2) filter and preprocess that data lake into a uniform data structure whose structure is determined by the analysis goal rather than the operational system, 3) analyze that uniform data with statistical and other tools. It usually makes no sense to combine those phases into a single overall tool. First, these are very different tasks, where different specialized tools will evolve anyway. Second, you want to keep the intermediate results anyway - for caching as well as to have an audit trail and reproducibility of the results. For example, you don't want the performance of operational system be affected by for how many analysis tools it is used at a point in point. Also, you don't to work on a always-modifying dataset while fine-tunung your analysis methods.
- jtmarmon 11y ago
  Thanks for your input. The point of a BI tool is to allow flexible analysis of your data. What types of transformations are generally required for making this work without actually _doing_ the analysis during the warehousing step?
  - vog 11y ago
    The data lake contains datasets in their original structure, raw data, with all warts and stuff, defined by the operational systems. The data warehouse contains datasets in a unified (and usually simplified and reduced) form, defined by the needs of analysis tools. If your analysis tool accesses the data lake directly, it will almost certainly contain "parsers" for various operational data formats. Also, it will perform those transformations over and over again every time. And multiple analysis scripts may contain multiple versions of those parsers. The idea is to separate these "parsers" out of the analysis step and to "cache" the cleaned-up intermediate result. That "cache of clean data" is usually called "data warehouse", and can create good indexes on that data, multiple runs of your analysis tools have very fast access.
deleted 11y ago
[deleted]
hglaser 11y ago
(Full disclosure, co-founder of Periscope Data here.) Periscope Data supports cross-DB joins. We cache the data in our own clusters so queries run really fast, and also so you can do things like join across databases and upload CSVs. More info: http://www.periscopedata.com http://www.periscopedata.com. I'm harry at periscopedata.com
- jtmarmon 11y ago
  woah, right as a I wrote that edit I get this comment ;) very cool. checking it out
demian 11y ago
Tool-level data integration is supported by tools like Spotfire, but for analyzing high volume of data you need to mantain a data warehouse using ETL code to integrate the data from all the diferent sources. For better performance you may also need to implement an OLAP tool.
salsakran 11y ago
So in practical terms, you'll eventually want to bring everything back in and create a analytics DB or data warehouse. Depending on what your schemas look like in each microservice, you might just be able to add each DB into Metabase and hit them up. We don't do cross DB joins in the query builder. You can still use something like PG foreign data wrappers to create an uber-db with all the tables from each microservice. Regardless of whether you use MB or another tool, there will be some legwork. Part of the tradeoffs involved in the great monolith vs microservices decision.
exelius 11y ago
This is why data warehousing exists. That way you can use many different BI tools against a consistent set of data and pre-calculate a lot of commonly-used summary data. ETL also isn't something you can just do automagically. It requires an understanding of the data and your goals, because you essentially have to build both your data model and your reporting requirements into your ETL process. You could probably do it automagically for some simple cases, but for most real-world scenarios it's just going to be easier to write a Python/Perl script to run your ETL for you. BI / reporting requires a lot of plumbing to work correctly. You have to set up read-only clones of your DBs for reporting (because you don't want to be running large queries against production servers) and generally an ETL process that dumps everything into a data warehouse. From there, you can push subsets of that data out to various BI tools that provide the interface.
- jrpt 11y ago
  If anyone's looking for a straightforward ETL framework, check out DataDuck http://dataducketl.com/ http://dataducketl.com/ The `dataduck quickstart` command is as close to automagic as you can get, and then you can customize your ETL after that.
  - demian 11y ago
    Thank you, I'm currently looking for alternatives to "enterprise" ETL tools. Most of leading tools are HORRIBLE as programming enviroments and also are incredibly expensive.
    - glogla 11y ago
      Yes. For example, I am convinced that Informatica Powercenter was made by the Devil to bring suffering into the world.
      - demian 11y ago
        PowerCenter is a piece of shit.
ajw0100 11y ago
This is something we saw at Chartio very early on. It's why we built the concept of layers directly into our product to support cross-DB networked joins: https://support.chartio.com/quick-start/#layers https://support.chartio.com/quick-start/#layers Feel free to reach out to me (aj at chartio.com) for more info!
jaltekruse 11y ago
Disclosure, I am a Drill committer. If you are looking for an open source technology to meet this need, Apache Drill is a distributed SQL engine that can talk to multiple datasources and make them available in a single namespace. Joins across sources are supported and can actually perform well when you use enough nodes for your data volume. Because Drill exposes ODBC and JDBC interfaces, it can very easily be used with BI tools to expose all kinds of data to analysts. The architecture is fully pluggable so anyone can write a connector to a new datastore and just include it on the classpath when starting the cluster. In the current 1.2 release Drill can connect to any database that exposes JDBC, like MySQL, Postgres, Oracle, as well as a number of non-relational datastores like HDFS, MongoDB and HBase. https://drill.apache.org/ https://drill.apache.org/
- salsakran 11y ago
  Dumb question, but what dialect of SQL does the JDBC interface use?
  - jaltekruse 11y ago
    Drill uses standards compliant SQL for flat data. To work with all of the non-relational stores Drill supports complex types like maps and lists. The syntax for working with this complex data is the same javascript, i.e. a.b.c[3], so this is a compliant extension of SQL, but is non-standard.
njx 11y ago
lot of bi tools provide connectivity via odbc and jdbc so works with multiple databases at the same time. check http://www.infocaptor.com http://www.infocaptor.com for e.g, serves both odbc and jdbc, on prem and on cloud
bra-ket 11y ago
take a look at Apache Calcite: https://calcite.incubator.apache.org/ https://calcite.incubator.apache.org/
sixdimensional 11y ago
I agree, the "one database at a time" approach is the way vendors ignore the harder problem of connecting data across platforms. I used to work for a company that built the "automagic ETL" kind of solution. You could write a single straight SQL statement across virtual "tables" (if you had non-tabular data, say NoSQL, you could query it through a table-like projection). It could literally join data across heterogeneous systems by shuffling data between them or to a dedicated "analytical processing platform" for join processing. Or you could do things like, create a table on one system, as the result of a select from a join of tables on three other systems. At the time, this was way ahead of what anyone else was doing. However, it is a hard problem to solve, the company is/was small and funding was a problem because it took a long time to find ways to invent the tech. Also, it was an enterprise solution and closed source - when it really probably needed to be open source to be able to support the diversity of data sources. These days, between Apache Drill, Spark, Ignite, etc., and any number of other commercial solutions, we're starting to see the solutions to solve the problem you're talking about. I bet this Metabase UI on top of Apache Spark, and your databases, would be a killer. That's a common pattern (BI tool on top of Spark), see Apache Zeppelin for how it uses Spark, for example. That said, as long as your data isn't truly ridiculously huge - if it can be centralized, centralization still works just fine.
bigger_cheese 11y ago
We use SAS Enterprise Guide for this at my work. Handles source data spread across multiple databases.
cgio 11y ago
The non-cool BI tools work alright with multiple sources and integrating them into a common OLAP model that takes care of automating the relevant backend queries. This is a valid approach if you do not want to move all your data into a DW before reporting on consolidated views, but the there is no magic bullet in terms of which model is optimal. I am talking about Oracle BI, SAP BO, Cognos BI.

smithian 11y ago

I've done some minor stuff in MS PowerBI and PowerQuery/PowerPivot that I think meets your need. It's pretty nifty. https://powerbi.microsoft.com/ https://powerbi.microsoft.com/ http://www.powerpivotpro.com/what-is-power-pivot/ http://www.powerpivotpro.com/what-is-power-pivot/

jrpt 11y ago

Some BI tools can connect to multiple databases, however the right solution is to put all your data into a single data warehouse and run the analysis off of that. Query performance is one reason, and here's a blog post I've written with some more: http://dataducketl.com/blog/every-tech-company-needs-a-data-warehouse http://dataducketl.com/blog/every-tech-company-needs-a-data-...

buro9 11y ago

The solution is to use an ETL (Extract, Transform, and Load) tool to copy data to a warehouse. You then point the BI tool at the warehouse. There are lots of tools for this already, and even more custom solutions and other things. CSV files tend to rule here... scheduled exports that are picked up by the ETL tools. The transformations that take place are usually normalisation of data from systems of differing schema or different types of data (perhaps changing strings into corresponding numbers, i.e. Risk Level = Orange could be normalised as 50% in the data warehouse). Businesses have taken this approach for a long long time as the databases that drive applications, services, and now micro-services have always been tuned for the performance of the production use-case and not for ad-hoc reporting.

mipmap04 11y ago

I've worked in this space a lot and in my experience, the only true solution is some sort of layer below your reporting tool to aggregate the data. That being said, it's not always a database that physically stores the data. It simply needs to be a semantic layer that models the way in which the data will be queried. Additionally, your OLTP systems are not optimized for reporting and you'll quickly choke yourself out trying to perform complex analysis on data structures attempting to solve a problem for which they weren't designed.

vog 11y ago

You will have N databases no matter what. Almost every company has some legacy systems around, or simply uses different tools for different jobs. However, you still want to copy & combine your data into a single database. The relevant Fowler patterns are: http://martinfowler.com/bliki/DataLake.html http://martinfowler.com/bliki/DataLake.html http://martinfowler.com/bliki/ReportingDatabase.html http://martinfowler.com/bliki/ReportingDatabase.html More precicely, it is a common pattern to split the analtics into three parts: 1) Collecting the data (using lots of adapters) into a "data lake", 2) filter and preprocess that data lake into a uniform data structure whose structure is determined by the analysis goal rather than the operational system, 3) analyze that uniform data with statistical and other tools. It usually makes no sense to combine those phases into a single overall tool. First, these are very different tasks, where different specialized tools will evolve anyway. Second, you want to keep the intermediate results anyway - for caching as well as to have an audit trail and reproducibility of the results. For example, you don't want the performance of operational system be affected by for how many analysis tools it is used at a point in point. Also, you don't to work on a always-modifying dataset while fine-tunung your analysis methods.

jtmarmon 11y ago
Thanks for your input. The point of a BI tool is to allow flexible analysis of your data. What types of transformations are generally required for making this work without actually _doing_ the analysis during the warehousing step?
- vog 11y ago
  The data lake contains datasets in their original structure, raw data, with all warts and stuff, defined by the operational systems. The data warehouse contains datasets in a unified (and usually simplified and reduced) form, defined by the needs of analysis tools. If your analysis tool accesses the data lake directly, it will almost certainly contain "parsers" for various operational data formats. Also, it will perform those transformations over and over again every time. And multiple analysis scripts may contain multiple versions of those parsers. The idea is to separate these "parsers" out of the analysis step and to "cache" the cleaned-up intermediate result. That "cache of clean data" is usually called "data warehouse", and can create good indexes on that data, multiple runs of your analysis tools have very fast access.

deleted 11y ago

[deleted]

hglaser 11y ago

(Full disclosure, co-founder of Periscope Data here.) Periscope Data supports cross-DB joins. We cache the data in our own clusters so queries run really fast, and also so you can do things like join across databases and upload CSVs. More info: http://www.periscopedata.com http://www.periscopedata.com. I'm harry at periscopedata.com

jtmarmon 11y ago
woah, right as a I wrote that edit I get this comment ;) very cool. checking it out

demian 11y ago

Tool-level data integration is supported by tools like Spotfire, but for analyzing high volume of data you need to mantain a data warehouse using ETL code to integrate the data from all the diferent sources. For better performance you may also need to implement an OLAP tool.

salsakran 11y ago

So in practical terms, you'll eventually want to bring everything back in and create a analytics DB or data warehouse. Depending on what your schemas look like in each microservice, you might just be able to add each DB into Metabase and hit them up. We don't do cross DB joins in the query builder. You can still use something like PG foreign data wrappers to create an uber-db with all the tables from each microservice. Regardless of whether you use MB or another tool, there will be some legwork. Part of the tradeoffs involved in the great monolith vs microservices decision.

exelius 11y ago

This is why data warehousing exists. That way you can use many different BI tools against a consistent set of data and pre-calculate a lot of commonly-used summary data. ETL also isn't something you can just do automagically. It requires an understanding of the data and your goals, because you essentially have to build both your data model and your reporting requirements into your ETL process. You could probably do it automagically for some simple cases, but for most real-world scenarios it's just going to be easier to write a Python/Perl script to run your ETL for you. BI / reporting requires a lot of plumbing to work correctly. You have to set up read-only clones of your DBs for reporting (because you don't want to be running large queries against production servers) and generally an ETL process that dumps everything into a data warehouse. From there, you can push subsets of that data out to various BI tools that provide the interface.

jrpt 11y ago
If anyone's looking for a straightforward ETL framework, check out DataDuck http://dataducketl.com/ http://dataducketl.com/ The `dataduck quickstart` command is as close to automagic as you can get, and then you can customize your ETL after that.
- demian 11y ago
  Thank you, I'm currently looking for alternatives to "enterprise" ETL tools. Most of leading tools are HORRIBLE as programming enviroments and also are incredibly expensive.
  - glogla 11y ago
    Yes. For example, I am convinced that Informatica Powercenter was made by the Devil to bring suffering into the world.
    - demian 11y ago
      PowerCenter is a piece of shit.

ajw0100 11y ago

This is something we saw at Chartio very early on. It's why we built the concept of layers directly into our product to support cross-DB networked joins: https://support.chartio.com/quick-start/#layers https://support.chartio.com/quick-start/#layers Feel free to reach out to me (aj at chartio.com) for more info!

jaltekruse 11y ago

Disclosure, I am a Drill committer. If you are looking for an open source technology to meet this need, Apache Drill is a distributed SQL engine that can talk to multiple datasources and make them available in a single namespace. Joins across sources are supported and can actually perform well when you use enough nodes for your data volume. Because Drill exposes ODBC and JDBC interfaces, it can very easily be used with BI tools to expose all kinds of data to analysts. The architecture is fully pluggable so anyone can write a connector to a new datastore and just include it on the classpath when starting the cluster. In the current 1.2 release Drill can connect to any database that exposes JDBC, like MySQL, Postgres, Oracle, as well as a number of non-relational datastores like HDFS, MongoDB and HBase. https://drill.apache.org/ https://drill.apache.org/

salsakran 11y ago
Dumb question, but what dialect of SQL does the JDBC interface use?
- jaltekruse 11y ago
  Drill uses standards compliant SQL for flat data. To work with all of the non-relational stores Drill supports complex types like maps and lists. The syntax for working with this complex data is the same javascript, i.e. a.b.c[3], so this is a compliant extension of SQL, but is non-standard.

njx 11y ago

lot of bi tools provide connectivity via odbc and jdbc so works with multiple databases at the same time. check http://www.infocaptor.com http://www.infocaptor.com for e.g, serves both odbc and jdbc, on prem and on cloud

bra-ket 11y ago

take a look at Apache Calcite: https://calcite.incubator.apache.org/ https://calcite.incubator.apache.org/

sixdimensional 11y ago

I agree, the "one database at a time" approach is the way vendors ignore the harder problem of connecting data across platforms. I used to work for a company that built the "automagic ETL" kind of solution. You could write a single straight SQL statement across virtual "tables" (if you had non-tabular data, say NoSQL, you could query it through a table-like projection). It could literally join data across heterogeneous systems by shuffling data between them or to a dedicated "analytical processing platform" for join processing. Or you could do things like, create a table on one system, as the result of a select from a join of tables on three other systems. At the time, this was way ahead of what anyone else was doing. However, it is a hard problem to solve, the company is/was small and funding was a problem because it took a long time to find ways to invent the tech. Also, it was an enterprise solution and closed source - when it really probably needed to be open source to be able to support the diversity of data sources. These days, between Apache Drill, Spark, Ignite, etc., and any number of other commercial solutions, we're starting to see the solutions to solve the problem you're talking about. I bet this Metabase UI on top of Apache Spark, and your databases, would be a killer. That's a common pattern (BI tool on top of Spark), see Apache Zeppelin for how it uses Spark, for example. That said, as long as your data isn't truly ridiculously huge - if it can be centralized, centralization still works just fine.

bigger_cheese 11y ago

We use SAS Enterprise Guide for this at my work. Handles source data spread across multiple databases.

cgio 11y ago

The non-cool BI tools work alright with multiple sources and integrating them into a common OLAP model that takes care of automating the relevant backend queries. This is a valid approach if you do not want to move all your data into a DW before reporting on consolidated views, but the there is no magic bullet in terms of which model is optimal. I am talking about Oracle BI, SAP BO, Cognos BI.