Why do BI queries execute multiple times against the raw tables instead of aggregate tables?

 

The following reasons can cause the BI query to execute against the raw tables instead of aggregate tables:

1.  "Aggregate usage" setting has been disabled. Check Summary Zip --> engine--> properties.json  
For example,

  "userProperties" : {
    "admin" : {
      "use_aggregates" : {
        "value" : false,
        "duration" : 1800000,
        "timestamp" : "2020-10-13T02:29:01.450651Z"
      }
    }
  }
The AtScale UI shows the same property in the User's PROFILE.

2. Current Aggregate is being widened or narrowed. Logs indicate:

com.atscale.engine.aggregation.prediction.AggregationPredictorActorr        
- Widening aggregate with m_productcost
....
com.atscale.engine.aggregation.prediction.AggregationPredictorActor        
- Requesting new aggregate based on

3. Compression score is low.

com.atscale.engine.aggregation.prediction.AggregationPredictorActor - Not building preagg: 
Compression score too low - 1.0. Minimum needed is 3.0


4. Compression score is zero as aggRowEstimate turns out higher than fact table data since the compression score is generated by the multiplication of cardinalities of all the dimensions involved.  Logs indicate:

com.atscale.engine.aggregation.prediction.AggregationPredictorActor - Attribute stats: [Map(
KeyCardinalityDescriptor(FlatKey(088b38e6-8b00-48b4-a04e-594121369069),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 5543, 
KeyCardinalityDescriptor(FlatKey(06b57e03-2ae6-4fb3-afee-0ea6efd7d0cb),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 4810, 
KeyCardinalityDescriptor(FlatKey(bb348219-0846-414e-8a2a-71166941b264),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 2, 
KeyCardinalityDescriptor(FlatKey(5b184e22-d10f-42e6-8edd-1ee45b00b096),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 637, 
KeyCardinalityDescriptor(FlatKey(4d92b88b-7096-487b-ad3b-30d19011c527),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 6, 
KeyCardinalityDescriptor(FlatKey(f9cc10c2-3621-4379-b01c-c0e8f37047f2),Cube(98b255e7-6b47-4bef-4763-7f725ee73e05: "Hubbleimpressions")) -> 1)]

com.atscale.engine.aggregation.prediction.AggregationPredictorActor - aggRowEstimate 203803028520 dataSetRowEstimate 981505409 compressionScore 0
com.atscale.engine.aggregation.prediction.AggregationPredictorActor - Not building preagg: Compression score too low - 0.0. Minimum needed is 3.0

Note: aggRowEstimate 203803028520 is based on 5543 * 4810 * 2 * 637 * 6 * 1
5. An UDA exists on a dimension involved at a higher level of the hierarchy. 

6. Sometimes, the second/third run of the query is executed too quickly before the aggregate creation is finished.

Was this article helpful?

0 out of 0 found this helpful