Overview

The structure of your data plus report design can impact the maximum tuple size, and, as a result, the memory usage and performance of a query. The following example illustrates how data structure and report design affect memory usage, and then offers a suggestion for how to create a more efficient query. This example is just a predictive calculation to determine just how large the number of tuples might get when rendering relational data in crosstabulation tables. Fewer tuples might be returned depending on the distribution and correlation of the categories selected on the same axis. In addition, as a safeguard, you can set a value for MAX_TUPLE_SIZE, which prevents the query from returning more than the specified number of tuples.

 Maximum Tuple Set Size = Axis 1 Set Size + Axis 2 Set Size 

  Axis Set Size (simplified for non-ragged categories) =
     (Number of Measures on axis or 1 if none) *
      (Number of unique values of Category A * 
       Number of unique values of Category B * ...etc.)

         

However, if a ragged hierarchy is created by multiple categories on an axis, then the relationship between A and B might have fewer combinations. In this context, "ragged" means that not all values for one or more categories on the same axis exist for all other values of the other categories, and, so, the relationship will have fewer combinations.

  Axis Set Size (more accurately for ragged categories) =
     (Number of Measures on axis or 1 if none) *
      (Number of unique combinations between Category A and B and ...etc. on axis.)

         

Here are other factors that affect tuple size:

• If a measure is defined to use a specific aggregation, you must also consider the size of the set that is requested by the measure. The number of unique values for the level or the category might be the maximum set size for your particular query. Examples of this include DISTINCT aggregations on relational data.
• Totaling also affects the number of tuples. Turning on totals is similar to adding one more unique value to each category (and possibly more for each combination of categories in the hierarchy if subtotals are requested).

Examples of calculating an estimated maximum tuples returned

Suppose an information map has the following data items:

• Category A (45 unique values)
• Category B (60 unique values)
• Category C (20 unique values)
• Category D (10 unique values)
• Category E (15 unique values)
• Measures (10)

If you design a report with the five categories placed on rows and the measures placed on columns, the maximum tuple size can be calculated with this formula:

  Row Set Size = 1 x (A x B x C x D x E)


  Column Set Size = M 


  Maximum Tuple Size = Column Set Size + Row Set Size

  Row Set Size = (1) * (45 * 60 * 20 * 10 * 15) =   8,100,000 tuples


  Column Set Size = (10)= 10


  Maximum Tuple Size = 8,100,000 + 10 = 8,100,010 tuples

By moving the two smallest categories to the column axis, the set size and memory usage can be significantly decreased.

  Row Set Size = 1 x (A x B x C)


  Column Set Size = M x (D x E) 


  Maximum Tuple Size = Column Set Size + Row Set Size

  Row Set Size = (1) * (45 * 60 * 20) =   54,000 tuples


  Column Set Size = (10)* (10 * 15) = 1,500 tuples


  Maximum Tuple Size = 54,000 + 1,500 = 55,500 tuples

If D and E are ragged and there are only 80 unique combinations between them, then the calculation is different:

  Row Set Size = (1) * (45 * 60 * 20) =   54,000 tuples


  Number of unique combinations of D/E = 80


  Column Set Size = (10)* (80) = 800 tuples


  Maximum Tuple Size = 54,000 + 800 = 54,800 tuples

For information about calculating result sets for reports based on SAS OLAP cubes, see SAS Note 19765.

Operating System and Release Information