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

[MadhusudanN]

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.