Epic: Connectors for additional BI tools

[rpaik]

Continue to provide SQL connectors for leading BI tools.

Work items include:

• [x] Add a connector for Metabase
• [x] Add a connector for Tableau
• [x] Add a connector for PowerBI
• [x] Add a connector for Excel
• [x] Add a connector for Google Datastudio
• [x] Add a connector for Google Sheets
• [x] Explore/prioritize opportunities for other BI tools

[MarkGStacey]

I'd heavily push for PowerBI, and then I'd also really like to see a cube Pivot table driver for Excel

[ifokeev]

+1 powerbi and tableau

[johanjvrens]

+1 Metabase

[abdel-87]

+1 metabase +1 google datastudio +1 google sheets

[astorrs]

I'd heavily push for PowerBI, and then I'd also really like to see a cube Pivot table driver for Excel

An Excel pivot table driver would truly be next level. Almost nothing in the modern data stack is directly accessible from Excel (while being easy to use like a cube), yet Excel is the most common analytics tool in the world. If we want a headless BI tool that everything flows through this is a must.

[johanjvrens]

+1 Grafana

[abdel-87]

+1 Holistics

[igorlukanin]

Beekeeper—thanks, @sspaeti!

[LXiang-JLL]

In addition to Tableau, PowerBI connection, I'll add Cube connection to Databricks SQL Analytics and Snowflake SQL. This will give data engineers one SQL notebook to query both derived metrics in the Cube and data in Lakehouse.

[chaos87]

+1 Google Datastudio

[elhe26]

+1 Google Sheets

[musharma888]

please add a connector for thoughtspot

[jose-bernard-receeve]

please consider qlikview 🙏🏿

[oskarabc]

Nice @rpaik Big big vote up for Google Sheets! Very happy to help test and think through workflow. Over at CodeSandbox we're frequently building Dashboards in Apache's superset and the operations team needs a little more flex sometimes, so we're now copying data, instead of using either connected sheets or stream it in some other form.

[kaizendae]

+1 on datastudio

[archiewood]

+1 for Evidence

[jinyangzhen]

+1 Tableau +1 googlesheet

[johanjvrens]

+1 DBeaver Universal Database Tool

[paco-valdez]

@musharma888 For Thoughtspot you can select Redshift as the origin. We are currently working on adding official support to TS but most queries already work out-of-the-box.

[paco-valdez]

@johanjvrens For DBeaver select postgres as the type of DB and you will be able to connect to it. The same applies to other DB IDEs like Jet Brains Datagrip, VS Code and others.

[VitaliyMF]

I've tried to connect via Postgre protocol to the docker container 'cubejs/cube' (the latest image) from SeekTable (cubejs was configured for real PostgreSQL DB) but got this strange error on any query (even for just "select 1"):

XX000: Internal: Error during planning: No field named 'typ_and_elem_type.typtype'. Valid fields are 'typ.oid', 'typ.typnamespace', 'typ.typname', 'typ.typrelid', 'typ.typnotnull', 'typ.relkind', 'typ.typtype', 'typ.elemtypoid', 'elemtyp.oid', 'elemtyp.typname', 'elemtyp.typtype', 'elemtyp.typrelid', 'elemtyp.typreceive', 'elemcls.oid', 'elemcls.relkind', 'elemproc.oid', 'elemproc.proname'.

SeekTable uses official Npgsql (.NET) driver.

[VitaliyMF]

I've found the reason: Npgsql driver requires a special option in the connection string Server Compatibility Mode=NoTypeLoading (btw, it makes sense to mention about that for .NET users), when specified SeekTable was able to use Cube.js as a data source without problems; date-part dimenions (year/month/day) and report parameters (SQL filters) work just fine, so SeekTable can be considered as a pretty compatible with Cube.js and it is already mentioned as a supported data source for SeekTable.

@rpaik @paveltiunov is it possible to add SeekTable to this list https://cube.dev/docs/config/downstream ? Let me know if I can help with that somehow.

[fabricebaranski]

I've tried to connect cube.js with Chartbrew (https://chartbrew.com/) using SQL API, but I obtain the following error :

Unable to parse: ParserError("Expected equals sign or TO, found: ZONE")
QUERY: SET client_min_messages TO warning;SET TIME ZONE INTERVAL '+00:00' HOUR TO MINUTE;

It seems that Charbrew uses Sequelize to connect to a PostgreSQL DB.

[phohomi]

Hi,

Is there work being done on a XMLA connector or something similar? Such an API would allow the BI tools to preserve the information as it is defined in the schema of the cube: the user would see dimensions instead of only columns.

In the issue below there is a reference to an XMLA project, but that is all that I can find: https://github.com/cube-js/cube.js/issues/3752

[beubeu13220]

Hi, Have you found a solution for google sheets ? I think, we can use some paid gsheets add-on like coefficient.io But, we would like to find a free solution :) Thanks to all

[tiyure]

Microstrategy!

[abdel-87]

+1 cumul.io

[YuriyGavrilov]

Continue to provide SQL connectors for leading BI tools.

Work items include:

• [x] Add a connector for Metabase
• [x] Add a connector for Tableau
• [x] Add a connector for PowerBI
• [x] Add a connector for Excel
• [x] Add a connector for Google Datastudio
• [x] Add a connector for Google Sheets
• [x] Explore/prioritize opportunities for other BI tools

+1 to add https://www.querybook.org +1 to add https://intl.finebi.com

[paco-valdez]

Since creating this epic, we have improved the support of the PostgreSQL dialect for the SQL API. I recommend testing any BI tool that supports PostgreSQL connections. It's even possible to connect to Excel using the official Postgres odbc connector.

Link for windows: https://www.postgresql.org/ftp/odbc/versions/msi/

The version I tested that works is “psqlodbc_13_02_0000-x64-1.zip”

[VitaliyMF]

we have improved the support of the PostgreSQL dialect for the SQL API

@pacofvf in this regard, maybe you know about plans to support 'GROUP BY CUBE' syntax for SQL queries? I mentioned above that SeekTable is able to connect to Cube.js via PostgreSql driver (with a special connection string option) and it works good for common aggregates (COUNT, SUM, AVG, MIN, MAX) and subtotals are also ok because for these aggregate functions it is possible to calculate subtotals on BI tools side. However, for custom-SQL metrics - like 'count unique' via COUNT(distinct user_id) - subtotals can be technically calculated only on a DB side, and for that cases SeekTable can generate queries like

SELECT col1, col2, COUNT(distinct user_id) as count_unique_user_id FROM orders GROUP BY CUBE (col1, col2)

to load subtotals/grand total and show them in a pivot table report accordingly. Almost all popular DBs support GROUP BY CUBE (the only exception so far is MySql, actually). I guess other BI tools that use 'direct query' DB connections also are capable to show subtotals for this kind of measures only they can be returned in SQL query results.

[jlloyd-widen]

Domo please

[igorlukanin]

As @paco-valdez mentioned, with every release Cube becomes more and more capable and now, with the query pushdown, supports dozens of BI tools through the SQL API. Many tools mentioned in this thread are already supported.

Everyone who is interested in the support for a specific tool is encouraged to create a separate issue for that.

I've also created a new meta-issue where I will be gathering links to such individual tickets: https://github.com/cube-js/cube/issues/8660

[YuriyGavrilov]

@igorlukanin 🙏