There are several factors to keep in mind when creating business rules in Planning Analytics. What are rules, you ask? Rules are dynamic calculations that enable analysts, department leads, and planners to transform their data into real-time insights.

Rules are the secret sauce when reporting in PA, but with great power, comes great responsibility. Adding the right rule qualifiers to your statements are a critical component to efficient data flow of the model. Knowing how and when to use them can greatly impact the performance of your PA model. Let’s dive in!

What are rule qualifiers?
Rule qualifiers determine whether the range of cells need to be calculated at the leaf (N:), consolidated (C:), or at all (N:&C:) levels of the cube. The basic rule syntax is shown below:

<A range of cells> = N: <Formula for N-cells> ;
<A range of cells> = C: <Formula for C-cells> ;
<A range of cells> = <Formula for N-cells & C-cells> ;

The Golden Rule: N-Level Qualifiers
PA’s natural consolidation engine is extremely performant, so N-level rules should be relied upon more when creating rules instead of C or no-qualifier rules. This is the golden rule of PA. The reason why 95% percent of the rules you write will have this N qualifier.

Let’s imagine we are reporting sales for a single entity (Figure 1) and we chose an N qualifier. This means all leaf level Products in the Products dimension will become calculated. Therefore, when data is calculated at the leaf level, PA will “Naturally” consolidate at C-levels of the cube.

Figure 2: N-Level Qualifier

 

 

 

 

 

 

 

 

 

The Exceptions: C or No-Qualifiers
Did you see what happens to my prices when they are allowed to natural consolidate? The are added up. Given that we want to display an average price at the consolidated level, this is where using a C qualifier makes sense.

In this case, I want to override PA’s ability to naturally consolidate for the price at the C-level. This will calculate the average price consolidated level only and paints a more understandable picture. Figure 4 shows how the calculation of Gross Margin % uses of no-qualifier and calculates at every intersection of the cube.

Figure 3: N-Level Qualifier

 

 

 

 

 

 

 

 

 

 

 

Figure 4: No-Qualifier

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Overall, rules are a very powerful tool in Planning Analytics and with the right qualifiers you can harness that power and increase your organization’s analytical ability. For more articles on building efficient and effective rules, stay tuned to the QueBIT knowledge base.