Ways to model cubes and views for data in 3NF form which involves complex joins to get accurate results

[MadhusudanN]

Problem 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 I am trying to create models in cube using cube core docker installation. I wanted some help to know what is the best way to model the cubes and views so that the purpose is served.

1. Measure total sales achieved based on bill_to_country and ship_to_country dimensions More details ::

• Sales Order Detail table has column called line total, which stores sales details for every product sold.
• bill_to_country and ship_to_country are role playing dimensions which the below reference path (SalesOrderDetail -> SalesOrderHeader -> Address -> StateProvince -> CountryRegion)
• SalesOrderHeader table has two foreign key references (BillToAddressID , ShipToAddressID) which points to AddressID column of Address table
• Address table further has reference to StateProvince table
• StateProvince table refers to CountryRegion table which has the column called 'Name' of the city which is the dimension of interest.

The below models are created. (Cubes and Views are added here.) cubes.zip views.zip

The output query generated is as below ::

SELECT
  TOP 10000 "shipping_address".Name "linetotal__shipping_address_shiptocountry",
  "billing_address".Name "linetotal__billing_address_billtocountry",
  sum("sales_order_detail".LineTotal) "sales_order_detail__sumlinetotal"
FROM
  Sales.SalesOrderDetail AS "sales_order_detail"
  LEFT JOIN Sales.SalesOrderHeader AS "sales_order_header" ON "sales_order_detail".SalesOrderID = "sales_order_header".SalesOrderID
  LEFT JOIN (
    SELECT
      address.*,
      state_province.StateProvinceCode,
      state_province.CountryRegionCode,
      state_province.IsOnlyStateProvinceFlag,
      state_province.Name as provinceName,
      state_province.TerritoryID,
      country_region.Name
    FROM
      Person.Address AS address
      LEFT JOIN Person.StateProvince state_province ON address.StateProvinceID = state_province.StateProvinceID
      LEFT JOIN Person.CountryRegion AS country_region ON state_province.CountryRegionCode = country_region.CountryRegionCode
  ) AS "shipping_address" ON "sales_order_header".ShipToAddressID = "shipping_address".AddressID
  LEFT JOIN (
    SELECT
      address.*,
      state_province.StateProvinceCode,
      state_province.CountryRegionCode,
      state_province.IsOnlyStateProvinceFlag,
      state_province.Name as provinceName,
      state_province.TerritoryID,
      country_region.Name
    FROM
      Person.Address AS address
      LEFT JOIN Person.StateProvince state_province ON address.StateProvinceID = state_province.StateProvinceID
      LEFT JOIN Person.CountryRegion AS country_region ON state_province.CountryRegionCode = country_region.CountryRegionCode
  ) AS "billing_address" ON "sales_order_header".BillToAddressID = "billing_address".AddressID
GROUP BY
  "shipping_address".Name,
  "billing_address".Name
ORDER BY
  3 DESC

Please note that the below sql

SELECT address.*,state_province.StateProvinceCode,state_province.CountryRegionCode,state_province.IsOnlyStateProvinceFlag,state_province.Name as provinceName,state_province.TerritoryID,country_region.Name
FROM Person.Address AS address
LEFT JOIN Person.StateProvince state_province ON address.StateProvinceID = state_province.StateProvinceID
LEFT JOIN Person.CountryRegion AS country_region ON state_province.CountryRegionCode = country_region.CountryRegionCode

is used in the billing_address.yml and shipping_address.yml so as to enforce the required joins

Can you please let us know what is the best way to model in Cubes to achieve the above expected result.

Env :: SQL Server Data is in 3 NF form For these complex joins, please let us know what is the recommended modelling mechanism. Is there any other alternative way for these cases