KB485145: Provide a new VLDB property "Attribute Relationship Model" for Analysts and Architect to build Data Import applications with ability to specify attribute relationship model

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

The new VLDB property is ONLY accessible via workstation for Data Import Intelligent Cube (aka Super Cube).

Name: Attribute Relationship Model
Options:
- "Attributes are related through imported tables" (Default)
- "Attributes are related through 1:M Relationships only"
 

Content

The VLDB property Attribute Relationship Model is available starting Strategy 2021 Update 3. It can be set for Data Import Intelligent Cube (aka Super Cube) in BOTH Live Connect and In-Memory modes.

• "Attributes are related through imported tables" This option is Default option, keep backward compatibility
  
• Attributes are implicitly related to each other through fact tables
• Automatically involves other fact tables to enable indirect filtering or aggregations.
• Joins on Unrelated attributes
• "Attributes are related through 1:M Relationships only" This option only takes effect with Data Engine Version 2021 or above
  
• Attributes are explicitly related with each other by set 1:M relationships. 
• Automatically rejects unrelated filtering or aggregation.
• Joins only happen on related attributes.

Scenarios that are affected

• Fact Extension as shown in below example
  
• Login Strategy Web, go to Add External Data -> Databases -> Select Tables, pick the following tables

• Click Finish and choose Import as an In-memory Dataset, save as MTDI01

• Create dossier D01 based on MTDI01, add into Visualization: Month Id, Region Id, Tot Cost

• View Query Details, notice that it joins table STATE_REGION_MNTH_SLS to extend to Month Id level

************   Individual Step Start   ************
Number of Rows Returned: 288
Time Spent: 0.050 sec(s)
Query Execution Start Time: 9/2/2021 2:45:41 PM
Query Execution End Time: 9/2/2021 2:45:41 PM
Tables Accessed:
Table15    [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]:    Region Id, Subcat Id, Cust State Id,     Gross Dollar Sales, Tot Dollar Sales, Tot Unit Sales, Row Count - STATE_SUBCATEG_REGION_SLS, Tot Cost,     FACT_TABLE    
Table18    [F_STATE_REGION_MNTH_SLS8F97F2464B954BE08DD6A388ACE35635]:    Region Id, Month Id,     Row Count - STATE_REGION_MNTH_SLS,     FACT_TABLE    
Alternative CSI: 
select    [Region Id]@[REGION_ID],
    sum([[F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1].Tot Cost])@{[Region Id]} as [Tot Cost]
with Table Join Tree:     [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]
Save As TempTable10000    
select    distinct    [Month Id]@[MONTH_ID],
    [Region Id]@[REGION_ID],
    sum([Table10000.Tot Cost])@{[Region Id],[Month Id]} as [Tot Cost]
with Table Join Tree:     TempTable10000
     Join (Set of distinct Tuple([Region Id]@[REGION_ID], [Month Id]@[MONTH_ID]) where Tuple([Region Id]@[REGION_ID], [Month Id]@[MONTH_ID]) in [F_STATE_REGION_MNTH_SLS8F97F2464B954BE08DD6A388ACE35635]) with output level Tuple([Region Id]@[REGION_ID], [Month Id]@[MONTH_ID])
************   Individual Step End     ************

• Modify the VLDB property Attribute Relationship Model to "Attributes are related through 1:M Relationships only" on MTDI01 via Workstation

• Execute dossier D01

• View Query Details, notice that it cross join L_MONTH_ID to extend to Month Id level

************   Individual Step Start   ************
Number of Rows Returned: 384
Time Spent: 0.062 sec(s)
Query Execution Start Time: 9/2/2021 9:55:03 AM
Query Execution End Time: 9/2/2021 9:55:03 AM
Tables Accessed:
Table6    [L_MONTH_ID]:    Month Id,         LOOKUP_TABLE    
Table15    [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]:    Region Id, Subcat Id, Cust State Id,     Gross Dollar Sales, Tot Dollar Sales, Tot Unit Sales, Row Count - STATE_SUBCATEG_REGION_SLS, Tot Cost,     FACT_TABLE    
select    [Region Id]@[REGION_ID],
    [Month Id]@[MONTH_ID],
    sum([[F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1].Tot Cost])@{[Region Id]} as [Tot Cost]
from    MTDI01: CITY_SUBCATEG_SLS STATE_SUBCATEG_REGION_SLS (2 tables)
with Table Join Tree:     [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]
     CrossJoin [L_MONTH_ID] with output level Tuple([Cust State Id]@[CUST_STATE_ID], [Subcat Id]@[SUBCAT_ID], [Region Id]@[REGION_ID], [Month Id]@[MONTH_ID])
************   Individual Step End     ************
Same report would have failed if it was built in Project Schema:

• Unrelated Filter as shown in below
  
• Modify the VLDB property Attribute Relationship Model to "Attributes are related through imported tables" on MTDI01 via Workstation
• Create dossier D02 based on MTDI01
• Add into Visualization: Region Id, Tot Cost, add into Filter: Month Id in {200801}

• View Query Details, notice that it joins to table STATE_REGION_MNTH_SLS to apply unrelated filter on Month Id

************   Individual Step Start   ************
Number of Rows Returned: 2
Time Spent: 0.033 sec(s)
Query Execution Start Time: 9/2/2021 3:54:36 PM
Query Execution End Time: 9/2/2021 3:54:36 PM
Tables Accessed:
Table15    [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]:    Region Id, Subcat Id, Cust State Id,     Gross Dollar Sales, Tot Dollar Sales, Tot Unit Sales, Row Count - STATE_SUBCATEG_REGION_SLS, Tot Cost,     FACT_TABLE    
Table18    [F_STATE_REGION_MNTH_SLS8F97F2464B954BE08DD6A388ACE35635]:    Region Id, Month Id,     Row Count - STATE_REGION_MNTH_SLS,     FACT_TABLE    
select    [Region Id]@[REGION_ID],
    sum([[F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1].Tot Cost])@{[Region Id]} as [Tot Cost]
from    MTDI01: CITY_SUBCATEG_SLS STATE_SUBCATEG_REGION_SLS (2 tables)
with Table Join Tree:     [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]
     Join (Set of distinct Tuple([Region Id]@[REGION_ID]) where Tuple([Region Id]@[REGION_ID]) in [F_STATE_REGION_MNTH_SLS8F97F2464B954BE08DD6A388ACE35635]<[Month Id]@[MONTH_ID] in (200801)>) with output level Tuple([Cust State Id]@[CUST_STATE_ID], [Subcat Id]@[SUBCAT_ID], [Region Id]@[REGION_ID])
************   Individual Step End     ************

• Modify the VLDB property Attribute Relationship Model to "Attributes are related through 1:M Relationships only" on MTDI01 via Workstation
• Execute dossier D02

• View Query Details, notice that unrelated filter on Month Id is ignored

************   Individual Step Start   ************
Number of Rows Returned: 8
Time Spent: 0.004 sec(s)
Query Execution Start Time: 9/2/2021 4:48:43 PM
Query Execution End Time: 9/2/2021 4:48:43 PM
Tables Accessed:
Table15    [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]:    Region Id, Subcat Id, Cust State Id,     Gross Dollar Sales, Tot Dollar Sales, Tot Unit Sales, Row Count - STATE_SUBCATEG_REGION_SLS, Tot Cost,     FACT_TABLE    
select    [Region Id]@[REGION_ID],
    sum([[F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1].Tot Cost])@{[Region Id]} as [Tot Cost]
from    MTDI01: CITY_SUBCATEG_SLS STATE_SUBCATEG_REGION_SLS (2 tables)
with Table Join Tree:     [F_STATE_SUBCATEG_REGION_SLS3E86222588124E8AB50A8111692EADC1]
************   Individual Step End     ************
Same report would have failed if it was built in Project Schema: