Support for automatic selection of appropriate measure based on dimensions specified

[MadhusudanN]

Intro :: I am working on one of the usecases with Adventureworks 2019 OLTP schema(https://akela.mendelu.cz/~jprich/vyuka/db2/AdventureWorks2008_db_diagram.pdf) as part of exploration of Cube semantic layer

Details of the Usecase :: AdventureWorks Schema has SalesOrderDetail table which provides details about line total price for every product that is being sold

It also contains SalesOrderHeader table which provides details about subtotal of an order which is shipped to a particular country.

1. The business user wants to know the total sales based on bill_to_country and ship_to_country dimensions
2. The business user wants to know the total sales based on bill_to_country , ship_to_country and product dimensions

Expectation For first case, sum of subtotal needs to be considered automatically by Cube semantic layer as measure from table (SalesOrderHeader ) when dimensions are bill_to_country and ship_to_country (The join path to reach to the country would be SalesOrderHeader (BillToAddressID) --> Address (AddressID) --> Address(StateProvinceID) --> StateProvince (StateProvinceID) --> StateProvince (CountryRegionCode) -> CountryRegion (CountryRegionCode))

For second case, sum of line total needs to be considered automatically by Cube semantic layer as measure from table (SalesOrderDetail ) when dimensions are bill_to_country and ship_to_country and product is used. (The join path to reach country is SalesOrderDetail(SalesOrderID) ->SalesOrderHeader (SalesOrderID) -> SalesOrderHeader (BillToAddressID) --> Address (AddressID) --> Address(StateProvinceID) --> StateProvince (StateProvinceID) --> StateProvince (CountryRegionCode) -> CountryRegion (CountryRegionCode)) and join path to reach product would be SalesOrderDetail(ProductID) --> Product.ProductID

Question Can you please let me know is there any way to achieve as mentioned in the expectations above where Cube Semantic layer will automatically figure out the measure based on the dimensions used.

[igorlukanin]

Hey @MadhusudanN 👋

I think that the best way to achieve that is to use views. You can define views that make sense for each use case and have full control over joins paths to specific dimensions. I hope it helps, please feel free to ping me back if more details are needed.

[MadhusudanN]

Hi @igorlukanin Thanks a lot for the reply. We can notice that there is support for dynamic modelling using Jinja templating as described in this link (https://cube.dev/docs/product/data-modeling/dynamic/jinja). Can you please let us know is there any way we can access the dimensions supplied in the Cube UI in the modelling layer (i.e, in cubes) so that we can use jinja templating to add conditional statements so that we can use the appropriate measures for the same.

[igorlukanin]

@MadhusudanN Let me clarify something with regards to dynamic data modeling. The dynamic model still doesn't have access to the query being run against it—and for a good reason. Making the data model independent of queries allows to apply many optimizations ahead of time, implement caching, use pre-aggregations, etc. That being said, dynamic data modeling allows to generate the data model based on external data.

The closest to what you're asking about is using the query_rewrite configuration option (https://cube.dev/docs/reference/configuration/config#query_rewrite) to rewrite the query (e.g., adjust used cubes or measures within them) based on the provided dimensions. However, in my opinion, using views would be a better fit for this use case.

Is there anything that makes you think views aren't a good fit?