AX2270

Using file diagnostics for troubleshooting and optimization

Axiom Software provides a set of file diagnostics that can be used to check your files for potential issues. We recommend using these diagnostics as part of the initial file creation process to help ensure files are set up correctly and optimized, and also on an ongoing basis to make sure no subsequent changes have introduced potential issues.

NOTE: These tests are provided to help you identify issues with your files. Keep in mind the tests are not all-inclusive of every possible design consideration, and in many cases the results are simply a guideline. Files should always be manually tested in their expected environments and for their planned use cases.

Creating a diagnostics task pane or ribbon tab

File diagnostics are run by using the Run QA Diagnostics command in a custom task pane or a custom ribbon tab. You can create your own task pane or ribbon tab, or you can use the templates provided by Axiom Software. The template locations are as follows:

• Task pane: \Axiom\Axiom System\Document Templates\Sample Task Panes\QA Diagnostics.axl
• Ribbon tab: \Axiom\Axiom System\Document Templates\Sample Ribbon Tabs\QA Diagnostics Ribbon.axl

To use a template, an administrator must copy the file to the Task Panes Library or the Ribbon Tabs Library. In order to use the ribbon tab, it must be assigned as a startup file in security. The task pane can be opened directly from the library as needed, or assigned as a startup file.

The template is set up so that you can choose which tests you want to run:

• Composite Tests: You can run all diagnostic tests (with or without the save-to-database test).
• Diagnostics Categories: You can run all tests in a particular diagnostic category, such as Axiom query tests or Control Sheet tests.
• Individual Tests: You can choose individual tests to run.
• Utilities: You can run utilities to fix certain issues that are found by the tests.

If you find your own combination of tests that you like to run as a group, then you can modify the template to add your preferred tests (or create your own task pane or ribbon tab). Some tests are always run whenever any diagnostics are run. This includes the log analysis and the performance analysis.

The following screenshot shows an example of the QA Diagnostics task pane template. The ribbon tab template has the same contents.

If new tests are added in the future, they will run within the "Run all" tests and within whatever category they are assigned to, but they will not show up in the individual test section. You can manually add the new tests to your existing task pane or ribbon tab, or you can obtain a copy of the updated template from Axiom Software.

• To manually add new tests: Create a new item in your task pane or ribbon tab. In the Shortcut Target for the new item, point to the Run QA Diagnostics command in the Command Library. Then in the Shortcut Parameters, select the test that you want to associate with the item in the task pane or ribbon tab.

• To obtain an updated template: The task pane and ribbon tab templates are automatically updated when your system is upgraded. You can copy the updated files from the Document Templates locations listed at the start of this section.

Running diagnostic tests and utilities

Once you have created a QA Diagnostics task pane or ribbon tab, you can run diagnostic tests and utilities on a per file basis. To run diagnostics on a particular Axiom file, open the file and then use the desired item on the task pane or ribbon tab. After running the selected tests, the results display in an HTML page that opens in your browser.

A result summary displays at the top of the page, followed by detailed results for each test. If the results contain any errors or warnings, check the detailed results and address the issues as appropriate. The result format is continually evolving, but appears similar to the following example:

Testing files in bulk

You can use the Run Bulk QA Diagnostics utility to run tests on all files in a particular folder. This utility is available in the Utilities section of the standard task pane or ribbon tab template, or you can add it to your own task pane or ribbon tab.

When you use this utility, you are first prompted to select a folder. The diagnostics tests will be run on all eligible spreadsheet files in that folder and its subfolders. All tests will be run except for tests that require saving the file or performing a save-to-database.

When the process is complete, a summary page opens in the browser. This page indicates the errors and warnings in each processed file. You can click on a file name to be taken to the full results for that file.

Diagnostic tests

The following diagnostic tests are available:

Test	Description
Analyze Axiom UDFs	Analyzes all Axiom formulas in the file with the following goals: Reports the total number of Axiom formulas found. A large number of formulas may impact performance. Reports any warning conditions for certain formulas.
Analyze Excel Formulas	Analyzes all Excel formulas in the file with the following goals: Reports the total number of Excel formulas found. A large number of formulas may impact performance. Reports any warning conditions for certain formulas—for example, warning about settings that may not be compatible with the Windows Client.
Cell Formula Error Count	Reports the total number of cell errors in the file, and lists errors found. This test checks for both Excel formula errors and Axiom formula errors (#ERR).
Check AQ Names	Checks to see whether all active Axiom queries have defined names (rather than the default "Axiom Query #1", etc.). This test is also included in the Refresh All Active AQs test.
Check Data Validation	Checks for specific uses of data validation that may be incompatible with the Windows Client.
Check Embedded Images	Checks any embedded images for potential issues, such as use of file types that are incompatible with the Windows Client, or very large file sizes that may cause performance issues.
Check File Size	Reports the size of the file. Larger files may have worse performance, and may indicate an overall design issue.
Check for External Links	Checks the workbook for links to external files, and issues a warning if found. External links should be avoided whenever possible to help prevent the possibility of broken links.
Check Formula Lengths	Checks the length of all formulas in the file and issues a warning or an error if a formula exceeds a certain threshold of character length. Lengthy formulas may indicate an overly complicated design that could potentially be simplified.
Check for Old Control Sheet	Checks the Control Sheet of the workbook to see if it is out of date and should be upgraded to the latest.
Check for Save Errors	Saves the file, including a save-to-database if applicable, and reports any save errors. NOTE: Use caution before enabling this test. Do not include this test if you do not want to save the file and its data.
Check for VBA	Checks whether VBA is present in the file. Files should not contain VBA unless it is absolutely necessary to support a known custom solution.
Check Named Ranges	Checks all named ranges in the file to be sure they refer to valid worksheet ranges. Invalid named ranges can cause errors when performing certain Axiom operations, and should be corrected or removed as appropriate.
Circular Reference Check	Checks for circular references in the file and warns if found. Although in some cases circular references may be valid and intentional, in other cases they are likely to be the result of a design error and should be corrected. Circular references can cause issues with GetData calculations.
Conditional Format Count	Reports the total number of conditional formats used in the file. A large number of conditional formats may impact performance, and certain configurations may be incompatible with the Windows Client. For more information, see Spreadsheet design considerations for Axiom files.
Find Invalid Sheet Controls	Checks for invalid sheet names on the Control Sheet, as well as the presence of old Control Sheets (left over after upgrading).
Find Invalid Sheet Filters	Checks for invalid sheet filters on the Control Sheet.
Find Unmatched AQ Data	Checks for unmatched data for all Axiom queries in the file. Data is unmatched if it is brought back by the database query but it is not inserted or updated within any data ranges. You may want to check for unmatched data for performance reasons, or because you intended for all data to be matched. This test is also included in the Refresh All Active AQs test.
GetData Diagnostics	Analyzes all GetData formulas in the file and returns information on the server load required to resolve them, as well as any errors.
Hidden Formula Scan	Checks for hidden formulas, meaning formulas in cells with the Hidden protection option enabled. Hidden formulas should be avoided as they may cause issues with Axiom Software processes, such as action codes and views.
Max Column Number	Reports the total number of columns in the widest used range in the file. If the used range is larger than expected, you should reduce it to the actual used area to improve performance.
Max Row Number	Reports the total number of rows in the tallest used range in the file. If the used range is larger than expected, you should reduce it to the actual used area to improve performance.
Merged Cell Scan	Checks for use of merged cells and issues a warning if found. Merged cells may cause issues with processes that copy and paste, such as action codes and snapshot.
Refresh Active AQs	Refreshes all active Axiom queries in the file and reports any errors found. Inactive queries are not run.
Save2DB Tag Check	Checks for potential issues with save-to-database tags, such as when tags are present but the save-to-database process is not enabled in the sheet. Also checks for hard-coded table names in file groups (should use variables).
Scan for Missing AQ Tags	Checks to see whether all active Axiom queries have corresponding data range tags in the workbook. This test is also included in the Refresh All Active AQs test.
Sheet Name Validation	Checks sheet names in the file and reports any potentially problematic names. Remember to update the Control Sheet if you change the name of a sheet.
Total Worksheets	Reports the total number of worksheets in the file. An extremely large number of worksheets may indicate an overall design issue.
Validate AQ Refresh Settings	Checks for common design issues with the Axiom query refresh settings.

Diagnostic utilities

The following utilities are available to help resolve issues found by the file diagnostics. You can run these utilities on a single file by opening that file and then executing the utility, or you can run these utilities on all files in a designated folder. In order to run a utility on a file, you must have Sheet Assistant permission to that file.

The folder-level functionality is only available to administrators, and only when the currently active file is not a managed spreadsheet file (otherwise, the utility will be performed on that managed spreadsheet file). When you execute the utility, you will be prompted to select a folder. For an example of how to execute a utility on a folder, see Updating a Control Sheet.

Utility	Description
Clear Invalid Named Ranges	Removes any invalid named ranges from the file.
Remove Invalid Sheet Controls	Removes any invalid sheet controls from the Control Sheet. An invalid sheet control is a sheet name that is listed on the Control Sheet but does not correspond to an actual sheet in the file. The utility removes the entire column from the Control Sheet. NOTE: If the sheet control is invalid because you have renamed a sheet, then you should rename the entry on the Control Sheet rather than running this utility.
Remove Old Control Sheets	Removes any old Control Sheets in the file. These are archived Control Sheets that result from performing a Control Sheet upgrade. The sheet names of these Control Sheets are prefixed with "Old_".
Update Control Sheets	Updates the Control Sheet in the file to the latest version. This is the same process that is performed by the Update the Control Sheet command in the Sheet Assistant.

The following additional utilities are available to administrators:

Utility	Description
Run Bulk QA Diagnostics	Run QA Diagnostics tests on all files in a selected folder and its subfolders. All tests will be run except for tests that require saving the file or performing a save-to-database.
Validate Security Filters	Validates filters set in security, including plan file filters, table type filters, and table filters. The utility uses the same validation routine as the Security Management dialog. Filters defined on users, roles, and subsystems are validated. It may be useful to run this utility after changing security filters using Open Security in Spreadsheet or Save Type 4. For performance reasons, only a limited number of filters are validated when using these features. NOTE: This utility does not run against the current file, it runs against the database.