Multilevel cPlans

A multilevel cPlan allows you to join two or more data sets into a merged Dive file. A multilevel merge does not merely concatenate records. Dives are done into each of the sources and the results are then combined. It is a best practice to limit the number of cBases in a multilevel cPlan.

The following scenario defines a multilevel cPlan with two input cBases. The cBases have different columns but with a common Customer column. The cPlan is used as input to a Spectre dive file, and the results of the dive are shown.

cplan {
  multilevel {
    cbase-input"first.cbase"
    cbase-input"second.cbase"
  }
}

Assume the two cBases in the cPlan, have the following columns:

first.cbase has columns "Customer," "Product," and "Sold," like:

Customer Product Sold
Cust1 Prod1 10
Cust1 Prod2 20
Cust2 Prod1 30

second.cbase has columns "Customer" and "Loss," like:

Customer Loss
Cust1 10
Cust2 20
Cust3 30

When you create a Dive file on the multilevel cPlan, as in the following example, several things happen in the background:

  • The cPlan first splits into two windows or into as many windows as there are inputs.
  • Each window is run against a single input. Dimensions not present in that input are dropped.
  • The two (or more) windows are then joined into a single window for the result. 
dive {
  cplan "cust-prod.cplan"
  window {
    dimension "Customer"
    dimension "Product"
    column "Sold"
    column "Loss"
  }
}

Running the above Dive file (Run > Run) gives the following results.

Results of running the Dive file:

Customer Product Sold Loss
Cust1 Prod1 10 10
Cust1 Prod2 20 10
Cust2 Prod1 30 20
Cust3     30

NOTES:

  • A cPlan filter only filters the inputs for which all the referenced columns exist. In the previous example, a filter `value("Product")="Prod1"` would only filter first.cbase because Product does not exist in second.cbase.
  • If a value is missing on one side or the other when the two sides are joined, a null value is produced for columns where there is no data.
  • If a non-dimension column is found in both inputs, Spectre uses only the column from the first one.

 

Consider a multilevel cPlan of two cBases, one with the summary Units, the other with the summary Budget Units.

The actual vs budget.cplan:

cplan {
  multilevel{
     cbase-input "demo_drs.cbase 
     cbase-input "demo_drs_budget.cbase" 
  }
}

A simple Dive shows how the parent() totals operate on the window's working set:

dive {
  cplan "actual vs budget.cplan"
  window {
	dimension "Sales Region"            
	column "Budget Units"            
	column "Budget Units parent" `parent(sum(value("Budget Units")))`
	column "Units"                
	column "Units parent" `parent(sum(value("Units")))`
	aux-table "Totals"
  }                
}

The end result of the dive:

Multilevel Dive

 

See also: About MultiLevel MultiModel DivePlans