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).

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.

Product Family | Product | System | Product Release | SAS Release | ||

Reported | Fixed* | Reported | Fixed* | |||

SAS System | SAS Web Report Studio | Microsoft Windows 2000 Advanced Server | 3.1 | 9.1 TS1M3 SP4 | ||

Microsoft Windows 2000 Datacenter Server | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows 2000 Server | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows 2000 Professional | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows NT Workstation | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows Server 2003 Datacenter Edition | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows Server 2003 Enterprise Edition | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows Server 2003 Standard Edition | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows Server 2008 | 3.1 | 9.1 TS1M3 SP4 | ||||

Microsoft Windows XP Professional | 3.1 | 9.1 TS1M3 SP4 | ||||

Windows Vista | 3.1 | 9.1 TS1M3 SP4 | ||||

64-bit Enabled AIX | 3.1 | 9.1 TS1M3 SP4 | ||||

64-bit Enabled Solaris | 3.1 | 9.1 TS1M3 SP4 | ||||

HP-UX IPF | 3.1 | 9.1 TS1M3 SP4 |

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 example in this note illustrates how data structure and report design affect memory usage.

Type: | Usage Note |

Priority: |

Date Modified: | 2009-10-19 11:50:20 |

Date Created: | 2009-09-28 10:35:52 |