what are the best practices to model cubes and views when we have multiple measures
- 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
Assessment
This issue has not been assessed yet.