cube-js / cube-js/cube

what are the best practices to model cubes and views when we have multiple measures

Open
#7,596 0 comments 0 reactions 0 assignees View on GitHub
question
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Usecase**
I am conducting Cube POC with Adventure works OLTP schema (https://akela.mendelu.cz/~jprich/vyuka/db2/AdventureWorks2008_db_diagram.pdf))
Sales Quota is assigned to a territory which is part of a country. The user needs to know the overall sales and sales quota at the country level.
Measure :: Sum of line_item_total , Sales Quota at the territory
Dimension :: Country

Sales_Quota_Territory cube model would look like below ::

cubes:
- name: sales_quota_territory
sql_table: Sales.SalesQuotaTerritory
data_source: default

joins:
- name: sales_territory
sql: "{CUBE}.TerritoryID = {sales_territory}.TerritoryID"
relationship: many_to_one

- name: sales_quota_territory_country
sql: "{CUBE}.TerritoryID = {sales_quota_territory_country}.TerritoryID"
relationship: many_to_one

dimensions:

- name: salesquota_dimension
sql: SalesQuota
type: number

- name: id
sql: SalesQuotaId
type: number
primary_key: true

measures:
- name: count
type: count

- name: salesquota_measure
type: sum
sql: SalesQuota

I have created a view to specify the join path to be used to calculate the measure as follows ::

views:
- name: linetotal

cubes:
- join_path: sales_order_detail
includes:
- sumlinetotal

- join_path: sales_quota_territory
includes:
- salesquota_measure

**- join_path: sales_order_detail.sales_order_header.billing_address_old.state_province.sales_quota_territory.sales_territory.country_region
prefix: true
includes: "*"**

I notice that we need to explicity specify the path to be used which connects both the measures (The one highlighted above) for the cube to compute the results properly. But the two measures (total sales and sales_quota are independant of each other)
Can someone please let me know is there any other way to model the view so that Cube will automatically figure out the join path it has to use to calculate measures independantly.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.