Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Epic: Connectors for additional BI tools #3906

Closed
7 tasks done
rpaik opened this issue Jan 10, 2022 · 33 comments · Fixed by #4435 or #4421
Closed
7 tasks done

Epic: Connectors for additional BI tools #3906

rpaik opened this issue Jan 10, 2022 · 33 comments · Fixed by #4435 or #4421
Labels
Epic Major changes and features from the core team. Roadmap: 2022 Q1 Cube roadmap for Q1 of 2022 Roadmap: 2022 Q2 Cube roadmap for Q2 of 2022

Comments

@rpaik
Copy link
Contributor

rpaik commented Jan 10, 2022

Continue to provide SQL connectors for leading BI tools.

Work items include:

  • Add a connector for Metabase
  • Add a connector for Tableau
  • Add a connector for PowerBI
  • Add a connector for Excel
  • Add a connector for Google Datastudio
  • Add a connector for Google Sheets
  • Explore/prioritize opportunities for other BI tools
@rpaik rpaik added Epic Major changes and features from the core team. Roadmap: 2022 Q1 Cube roadmap for Q1 of 2022 labels Jan 10, 2022
@MarkGStacey
Copy link

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

@ifokeev
Copy link

ifokeev commented Jan 24, 2022

+1 powerbi and tableau

@johanjvrens
Copy link

johanjvrens commented Feb 25, 2022

+1 Metabase

@abdel-87
Copy link

abdel-87 commented Mar 13, 2022

+1 metabase
+1 google datastudio
+1 google sheets

@astorrs
Copy link

astorrs commented Mar 25, 2022

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
Copy link

johanjvrens commented May 10, 2022

+1 Grafana

@abdel-87
Copy link

+1 Holistics

@igorlukanin
Copy link
Member

Beekeeper—thanks, @sspaeti!

@LXiang-JLL
Copy link

LXiang-JLL commented Jun 22, 2022

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
Copy link

chaos87 commented Jul 11, 2022

+1 Google Datastudio

@elhe26
Copy link

elhe26 commented Jul 13, 2022

+1 Google Sheets

@musharma888
Copy link

please add a connector for thoughtspot

@jose-bernard-receeve
Copy link

please consider qlikview 🙏🏿

@oskarabc
Copy link

oskarabc commented Aug 5, 2022

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
Copy link

+1 on datastudio

@archiewood
Copy link

+1 for Evidence

@jinyangzhen
Copy link

+1 Tableau
+1 googlesheet

@johanjvrens
Copy link

+1 DBeaver Universal Database Tool

@paco-valdez
Copy link
Contributor

@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
Copy link
Contributor

@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
Copy link

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
Copy link

VitaliyMF commented Oct 3, 2022

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
Copy link

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
Copy link

phohomi commented Oct 27, 2022

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:
#3752

@beubeu13220
Copy link

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
Copy link

tiyure commented Feb 3, 2023

Microstrategy!

@abdel-87
Copy link

abdel-87 commented Jul 23, 2023

+1 cumul.io

@YuriyGavrilov
Copy link

YuriyGavrilov commented Oct 3, 2023

Continue to provide SQL connectors for leading BI tools.

Work items include:

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

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

@paco-valdez
Copy link
Contributor

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
Copy link

VitaliyMF commented Oct 4, 2023

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
Copy link
Contributor

Domo please

@igorlukanin
Copy link
Member

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: #8660

@YuriyGavrilov
Copy link

@igorlukanin 🙏

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Epic Major changes and features from the core team. Roadmap: 2022 Q1 Cube roadmap for Q1 of 2022 Roadmap: 2022 Q2 Cube roadmap for Q2 of 2022
Projects
None yet