Query from a BI tool
You can query Cube using a BI or visualization tool through the Cube SQL API. To provide a good end-user experience in your BI tool, we recommend mapping the BI's data model to Cube's semantic layer. This can be done automatically with Semantic Layer Sync or manually.
Semantic Layer Sync programmatically connects a BI tool to Cube and creates or updates BI-specific entities that correspond to entities within the data model in Cube, such as cubes, views, measures, and dimensions.
Semantic Layer Sync will synchronize all public cubes and views with connected
BI tools. We recommend making your cubes private and only exposing views. Both
cubes and views are public by default. To make cubes private, set the
public parameter to
- name: orders
Let’s create our first Semantic Layer Sync with Apache Superset (opens in a new tab)!
You can create a new sync by navigating to the Semantic Layer Sync tab on the BI Integrations page and clicking + Create Sync. Follow the steps in the wizard to create a sync.
Under the hood, Semantic Layer Sync is configured using the
option in the
cube.js configuration file.
Cube uses the Superset API, which requires a
authentication. You can use your own username and password or create a new
service account. You can copy a
URL from any page of your Superset workspace.
cube.py configuration file for Superset:
from cube import config
def semantic_layer_sync(ctx: dict) -> list:
'name': 'Superset Sync',
Replace the fields for user, password, and URL with your Superset credentials, then click on Save All. You can now go to the BI Integrations page and trigger the synchronization of your newly created semantic layer.
After running the sync, navigate to your Superset instance. You should see the
orders_view dataset that was created in Superset. Cube automatically maps all
metrics and dimensions in Superset to measures and dimensions in the Cube data
Alternatively, you can connect to Cube and create all the mappings manually. To do this, navigate to your Apache Superset instance and connect to Cube Cloud as if it were a Postgres database.
You can find the credentials to connect to Cube on the BI Integrations page under the SQL API Connection tab.
After connecting, create a new dataset in Superset and select "orders_view" as a table. Now you can map Superset metrics and columns to Cube's measures and dimensions.
As you can see, we use the
MEASURE function in the "SQL expression" field.
This function informs Cube that we are querying the measure and that it should
be evaluated based on Cube's data model. You can now query Cube from Superset,
as shown in the image below.
In the next section, we will learn how to use Cube's REST API to query our view from a React app.