SUPPORT / SAMPLES & SAS NOTES
 

Support

Usage Note 37278: Calculating the maximum tuple size for rendering relational data in crosstabulation tables in SAS® Web Report Studio and SAS® Web Report Viewer

DetailsAboutRate It

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

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Web Report StudioMicrosoft Windows 2000 Advanced Server3.19.1 TS1M3 SP4
Microsoft Windows 2000 Datacenter Server3.19.1 TS1M3 SP4
Microsoft Windows 2000 Server3.19.1 TS1M3 SP4
Microsoft Windows 2000 Professional3.19.1 TS1M3 SP4
Microsoft Windows NT Workstation3.19.1 TS1M3 SP4
Microsoft Windows Server 2003 Datacenter Edition3.19.1 TS1M3 SP4
Microsoft Windows Server 2003 Enterprise Edition3.19.1 TS1M3 SP4
Microsoft Windows Server 2003 Standard Edition3.19.1 TS1M3 SP4
Microsoft Windows Server 20083.19.1 TS1M3 SP4
Microsoft Windows XP Professional3.19.1 TS1M3 SP4
Windows Vista3.19.1 TS1M3 SP4
64-bit Enabled AIX3.19.1 TS1M3 SP4
64-bit Enabled Solaris3.19.1 TS1M3 SP4
HP-UX IPF3.19.1 TS1M3 SP4
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.