A Multi-driven Approach to Improve Data Analytics for Multi-value Dimensions - New Contributions in Information Systems and Technologies - page 116

• Reasoning to invoke the SUM operator: SUM (0) + X = X

• Changing the way the SUM operator is invoked: SUM(InputParameter) , where

the input parameter corresponding to:
0)+ dbo. DistinctSum ("FACT"."DLOT_ID","FACT"."PrecoLote"

• Outcome of the computed expression:

SUM (0)+ bo. DistinctSum ("FACT"."DLOT_ID","FACT"."PrecoLote")

As presented in Table 3, the outcomes provided by the Pentaho tool to this tech-
nological-driven approach are presented with the right measures for BasePrice ,
independently of the combination of the dimensional attributes used for “Grand To-
tal”. The Grand Total is

€ 13.000,00 as a careless data analyse
could indicate. The performance results are also very promising when compared to
the results obtained for any of the scenarios presented in Table 3.

€ 8.000,00 and not

€ €

Table 2. “Grand Total” consistency considering three dimensions and two metrics. The two
metrics (SUM Contracting Ent. and Base Price) are determined for each dimensional concept,
i.e., for each fact row defined by the combination of Procedure-Lot-Contracting Entity.

Dimensions Facts

SUM
Contracting Ent.

Base
Price

Procedure Lot Contracting Entity

Lot A-1 Contracti ng Ent. A 1 € 1.000,00

Procedure A

Lot A-2 Contracti ng Ent. A 1 € 2.000,00

Contracti ng Ent. A 1 € 5.000,00

Procedure B Lot B

Contracti ng Ent. B 1 € 5.000,00

G rand Total: 2 € 8.000,00

The proposed solution does not use allocations (weights) when in Procedure B the
base price is repeated for each Contracting Entity. Similarly, the base price of Proce-
dure B ( € 5.000, 00) is accounted for entity A and entity B, without causing inconsis-

tent values to the Grand Total.
Pentaho makes use of internal cache to optimize calculations. A tricky feature can
occur when a drill-down into the most granular metric or when the contracting entity
is dynamically removed without refreshing main memory. One way to overcome this
problem when using operation such as Distinct-Count is to deactivate the inter-
nal cache. This can be accomplished by changing the configuration parameter ' mon-
drian.rolap.star.disableCaching=true' but with impact in performance. The lessons
learnt from the SUM operator, enabled us to apply a similar approach to the operator
DISTINCT-COUNT . Based on the following assumption Count (Distinct 0)
* X = X , we forced Pentaho to accept the following expression:

€

COUNT ( distinct 0) * dbo . DistinctSum ("Factos"."DLOT_ID","
Factos"."PrecoLote" )