How to enable the XXL Quality Rules using the Table Size Folder
XXL table Quality Rules are performance related Quality Rules that help detect incorrect or poorly performing SQL queries running on XXL tables.
XXL tables can be defined as extremely large tables, containing a large amount of data. The threshold that determines when a table is considered to contain a large amount of data can be configured by the user using the Assessment Models editor (Contextual Parameters tab)- the default is 100,000.
In order to enable these XXL tables Quality Rules it is necessary to provide the analyzer with table row size information. This can be done using the Table Size Folder option in the relevant Analysis Unit editor. For example:
This folder must be populated with XML files that define your table row size information. These XML files must use the extension .SQLTABLESIZE. A syntactically correct example file is displayed:
Note that an alternative syntax is available for those using the SQL Analyzer extension. Please see the relevant documentation for more information.
Determining server and database/schema names to use in the .SQLTABLESIZE files
The server, database/schema and table nodes in the .SQLTABLESIZE file must match the information stored in the CAST Analysis Service following an initial analysis. You can determine this information in several ways:
Find the server name
- Using CAST Enlighten
- Right click the Instance either in the Object Browser or in the Graphical View:
- Select Properties to update the Property window. The "host" name highlighted below can be used to define the "Server" node in the .SQLTABLESIZE file:
- Using CAST System Views
You can query the CAST System Views in the Analysis Service to identify the "server" name. Run the following query (this is customised for CSS servers, but can be adapted to Microsoft SQL Server and Sybase ASE):
select DESCRIPTIONfrom <analysis_service>.CSV_OBJECT_DESCRIPTIONSwhere DESC_TYPE = 'host name'
Find the Database and Table name
Databases/Schemas and Tables can be easily identified in CAST Enlighten using the Object Browser or the Graphical View:
