Skip to main content

Configuration tasks for Noetix Platform

This chapter explains the maintenance and configuration tasks for Noetix Platform.

Overview

Use the procedures in this chapter to configure your Noetix Platform and to perform periodic maintenance tasks.

Unless otherwise specified, the tasks in this chapter are performed using NQS Administrator.

Scaling Noetix Platform for Better Performance

The Noetix Platform software has been designed to be modular, giving you the ability to scale your solution as the number of users and/or data sources increases. You can start with a small deployment for a limited number of users and data sources and later scale the solution to fit your needs. The components that will interest you most when deciding how to scale Noetix Platform are the Noetix QueryServer service, your heterogeneous mapping configuration, and your caching setup.

Magnitude Software Inc. recommends that you routinely check for the latest updates and patches for Noetix Platform and install them to get the best performance. The updates and patches address a wide range of topics from obvious bugs to performance issues.

ContactMagnitude Support for additional assistance with the tasks described in this section.

Also, periodic maintenance of the operating system and third-party software, such as Microsoft SQL Server, Microsoft SQL Server Reporting Services, Internet Information Services (IIS), Microsoft .NET Framework, and Microsoft Data Access Components (MDAC), improves the performance of Noetix Platform. For information about the maintenance tasks for the operating system and third-party software, see the respective product documentation.

Scaling NQS

The techniques outlined in this section will help you to scale Noetix QueryServer (NQS) within your enterprise. In some cases, you might get the best performance by using multiple NQS computers.

For information about installing NQS components, see the Noetix Platform Installation Guide.

Memory Usage

The performance of Noetix Platform is dependent on the memory used by the Microsoft Windows Server, Microsoft SQL Server, and Noetix QueryServer service.

The memory used by Windows Server, SQL Server, and Noetix QueryServer service can be monitored through the Windows Server operating system's Performance tool (click Start > Control Panel > Administrative Tools > Performance). For information about working with the Performance tool, see your Windows Server documentation.

Windows Server

To determine whether the Windows Server has sufficient memory for all its tasks (including NQS), you must monitor the server for excessive virtual memory paging in the Performance tool.

You can do that through the Pages/sec counter associated with the Memory object and the Avg. Disk sec/Transfer counter associated with the LogicalDisk object, where the LogicalDisk instance is the disk that contains the Pagefile.sys file.

Multiply the values of these two counters. If the product of these values is greater than 10 percent (0.1) of the total memory on a recurrent basis, then the system needs more RAM.

SQL Server

You can monitor and record the amount of memory that SQL Server uses through the Total Server Memory (KB) counter associated with the SQLServer:Memory Manager object in the Performance tool.

If the amount of memory used by SQL Server exceeds 50 percent of the server’s physical memory, then you should consider either increasing the amount of RAM on your server (recommended) or moving the NQS repository to a different server.

Noetix QueryServer Service

To deliver the highest performance, the Noetix QueryServer service keeps a majority of its metadata in a memory cache. During normal query activity, the Noetix QueryServer service has little interaction with the repository database. The Noetix QueryServer service operates using the information cached in the memory. Thus, as the amount of metadata increases, the amount of memory that the Noetix QueryServer service needs increases.

Another key indicator on the server is the amount of memory being used by the Noetix QueryServer service. You can also track the memory used by the Noetix QueryServer service through the Working Set counter of the Comconnection instance associated with the Process object in the Performance tool.

If you have SQL Server on the same server as NQS, then the combined memory used by both SQL Server and the Noetix QueryServer service should not exceed 75 percent of your server's physical memory. If it does, you must increase the amount of RAM on your server.

Important: If the memory usage of Noetix Platform is more than 1.7 GB, then the Noetix QueryServer service stops responding. For better performance in scenarios that require large-scale memory usage, Noetix Platform is compiled with the /LARGEADDRESSAWARE linker option set to YES. Therefore, the /3GB and /userva switches in the Boot.ini file of the server can be modified for allocation of more memory to the Noetix QueryServer service. For information about the linker option and switches, see the Windows Server documentation.

The Server’s Processor

Besides memory, the other important server resource is the CPU. The CPU on your server is adequate as long as the %Processor Timecounter associated with the Processor object stays consistently below 80 percent and the Processor Queue Length counter associated with the System object stays at or below 2. If these thresholds are exceeded on a regular basis, you should upgrade the server to one with a faster CPU, or one with multiple CPUs.

The Server’s NIC

Most scalability issues can be resolved by increasing either the memory or processing power. But, with a very high amount of query traffic you may run into a situation where the techniques mentioned earlier no longer produce results. In most cases, the bottleneck to further scalability will be due to the fact that most servers have a single network interface card (NIC). Every request for data must be routed through the NQS computer, and a large number of requests may be bottlenecked trying to enter or leave the server through your NIC. There are two solutions to this problem:

Verify that you are using a NIC with the maximum bandwidth available on your network. If you have an old 8-bit NIC in your server, you should upgrade to the fastest NIC possible for your network. If your company is running a 100-MB network, you must verify that your server has a 100-MB NIC and that it is truly running close to this speed. If most of the NQS activity is coming from Noetix WebQuery (NWQ) and you have installed the Web server on a separate computer, then you should consider creating a fast backbone connecting these two servers using the Fiber Distributed Data Interface (FDDI) technology or fiber optics.

Add additional NICs or a specialized NIC that has multiple ports, and set up adapter teaming. Adapter teaming is set up to maximize performance in and out of a server. This lets the server assign a single IP address to multiple cards. Traffic is then distributed among these NICs, providing increased bandwidth and performance. This configuration is analogous to the gains realized when multiple Integrated Drive Electronics (IDE) or Small Computer System Interface (SCSI) controllers are used to maximize the I/O performance of hard drives.

For information about installing the NQS components, see the chapter “Installing Noetix Platform” in the Noetix Platform Installation Guide.

Scaling Heterogeneous Mappings

NQS uses the heterogeneous join functionality in Microsoft SQL Server to provide heterogeneous mappings. Heterogeneous joins are required when the data needs to be pulled from more than one database and joined together to produce a single report. If you have defined heterogeneous mappings for your virtual tables (VTables), and a query spans databases, then NQS will use this functionality of SQL Server to combine the data into a single result set.

The job of processing a heterogeneous query is processor-intensive and consumes a lot of memory. These processor and memory requirements increase as the amount and complexity of the data increases. You should monitor the amount of resources used by the repository instance of SQL Server if you are performing heterogeneous queries or if these queries return considerable data.

Within NQS, your heterogeneous mappings will point to a heterogeneous mapping server (HMS). This is an instance of SQL Server. By default, your NQS repository server is also setup as an HMS (with the name HMServer), and this server is located in the N$System server group. This is the default HMS, and all heterogeneous mappings built by the administration wizards use it.

If the processing of your heterogeneous queries seems to be slowing down your system, you should consider moving this functionality from your NQS computer to a different server.

Adding a New HMS

To add a new HMS, use the Servers and Connections Wizard in NQS Administrator (on the Tools menu, click Administrator Wizards > Setup Servers and Connections) to define this new server. For more information about creating a server using the Servers and Connections Wizard, see Creating a Server in “Servers and Connections.”

After you have created the server, select it in the right pane of the NQS Administrator window, and click the General tab in the lower-right pane. Next, select the Can be used for Heterogeneous Mappings check box.

Changing the Default HMS

There are different wizards within NQS Administrator that help you set up your virtual VTables and mappings. Most of them have the ability to automatically set up heterogeneous mappings for you. To change the default HMS in NQS Administrator:

On the Tools menu, click Options. The Options dialog box is displayed.

The Default Heterogeneous Server box displays the server to which the heterogeneous mapping points to. Click next to the Default Heterogeneous Server box, browse to your newly added server (in the previous section), and select it.

Click OK or Apply to save your changes.

Now, whenever one of the wizards adds a heterogeneous mapping, it will point to this HMS.

Load Balancing

You can define additional HMSs without changing any mapping definitions. To do this:

In NQS Administrator, navigate to and select the required mapping in a virtual table (VTable).

In the lower pane, click the Heterogeneous tab in the Map Properties dialog box.

Select the heterogeneous mapping that you want to change, and click Edit.

Click next to the Server box, browse to the required HMS, and select it.

Click Save.

Scaling the Noetix Cache Maintenance Server

The Noetix Cache Maintenance Server (CMS) or report server is another memory and processor-intensive component. If you have installed CMS on the same computer as your Noetix QueryServer service and you are caching a lot of mappings, then you must watch its resource usage. If this usage starts to interfere with NQS performance, you must increase the RAM of your server or move CMS to a new computer (recommended). For more information about caching and CMS, see Caching.

If you have moved CMS to a new computer, you can also watch its resource usage to determine when you should add a second CMS.

Removing CMS from the NQS Computer

Before you start the process of removing CMS from your NQS computer, you must prevent cache refresh jobs from starting. The easiest way to do this is to stop the Agent process. To do this, right-click the Agent node in the left pane of the NQS Administrator window, and click Stop Agent in the shortcut menu. Depending on how long it takes you to set up the new CMS computer, you should make sure you know which of your mappings (if any) would have been refreshed while the CMS was being switched. After the new CMS computer is functional, you can manually refresh these mappings.

To remove CMS from your NQS computer, you must uninstall Noetix Platform.

Installing CMS on a New Computer

CMS can be installed on multiple computers that are running on Microsoft Windows in your network. This lets you scale your caching capabilities by installing CMS on additional servers. After this is done, NQS will immediately start using the new server.

During the installation process, CMS is installed as a Windows service and connects to the specified Noetix QueryServer service. For more information about the CMS installation, see the chapter “Installing Noetix Platform” in the Noetix Platform Installation Guide.

You should install CMS on multiple servers as your needs increase. After you have more than one CMS up and running, NQS will automatically balance the load between them. For information about how NQS handles load balancing among different CMSs, see Architecture.

Log Files

This section provides information about the log files created by Noetix Logging Service, Noetix Add-in for Microsoft Excel (add-in), and the Noetix QueryServer service. It also provides information about how the log files must be removed.

Creating Log Files

The creation of log files and the ways to identify the log files are discussed in the following sections.

Noetix Logging Service

Noetix Logging Service creates two types of log files as part of running the Noetix Platform installation wizard or Noetix Platform Prerequisite Checker (Prerequisite Checker). These files store valuable information about the Noetix Platform environment in your organization. For more information about the log files, see About Log File in “About Noetix Platform.”

Types of Log Files

There are two types of log files created by Noetix Logging Service. For information about the types of log files, see Types of Log Files in “About Noetix Platform.”

Location of Log Files

For information about the location of log files, see Location of Log Files in “About Noetix Platform.”

Naming Convention of Log Files

The log file name indicates the name of the computer on which the application (installation wizard or Prerequisite Checker) was run, the name of the application, and the date and time when the application was run.

Depending on the type of the log file, the file name extension is either .log or .xml.

The log file name comprises three parts, which are as follows:

Part 1: The computer on which the application was run. In this case, the computer name is TESTSRV.

Part 2: The application that created the log file. In this case, it has been created using the Noetix Platform 5.5 SP1 installation wizard.

Part 3: The date and time (session) when the application was run. In this case, the application was run on February 15, 2007, at 18:32:54.

In addition to these three parts, the .log file name is suffixed with “Details.”

Noetix Add-in for Microsoft Excel

Errors that occur while performing a task in the add-in are saved in a file for future reference. This file is created when an error occurs for the first time in the add-in. Information about the subsequent errors are saved in the same file.

The log file is a .txt file. By default, the name is Error Log.txt. The name should be changed only if you want to archive the file. The error messages are saved only to a file that is named Error Log.txt. When the file name is changed, another .txt file with the name Error Log.txtis created to save the subsequent errors.

Information in Log File

The log file provides the following information:

The date when the error occurred. The date is specified in the mm/dd/yyyy format.

The time when the error occurred. The time is specified in the hh:mm:ss AM or PM format.

The error message as displayed in the application.

Information about how to track the error that occurred.

Location of Log File

The log file is created on the computer on which the Noetix Add-in for Microsoft Excel (add-in) is installed.

It is created in <hard disk drive>:\Documents and Settings\<user name>\Application Data\Noetix\Noetix Add-In for Microsoft Excel <version of Microsoft Excel>.

If the Noetix folder does not exist at this location, then a folder named Noetix is created when the first error occurs.

Noetix QueryServer Service

The Noetix QueryServer service creates log files to trace the information relating to the tasks performed in NQS.

Types of Information Logged by Noetix QueryServer Service

The Noetix QueryServer service logs the following types of information:

Fatal: Includes errors that make the application unstable or unusable.

Error: Includes errors that occur in the application. These are expected errors, such as a user attempting to run a virtual table (VTable) without having permissions on it.

Warning: Includes conditions that do not cause errors or prevent functionality but that point to potential problems.

Debug: Includes information that is used for debugging purposes. This may include information about parameters, functions, or other programmatic levels of detail.

Information: Includes information at the most detailed level about every task performed in Noetix Platform.

Important: By default, the log file logs only the fatal errors. To log any other type of information, contact Magnitude Support.

Naming Convention of Log File

The name for the log file is ComConnection<process ID>.log, where process ID is a numeric value that is assigned by the operating system when the Noetix QueryServer service is started.

Each time the Noetix QueryServer service is restarted, a new process ID is assigned, and therefore, a separate log file is created.

Location of Log File

The log file is created in <hard disk drive>:/Program Files/Noetix/Log of the NQS computer.

This folder is created when the log file is created for the first time. Otherwise, this folder is not available.

Managing Log Files

This section provides information about what happens to the log files when the respective program is uninstalled and how to manage and remove the log files.

Noetix Logging Service

Uninstalling Noetix Platform does not delete the log files from your computer. If you want to remove the log files from your computer, you must manually delete or archive them. Deleting the log files from your computer helps you track them more efficiently, especially when there are too many log files on your computer.

Noetix Add-in for Microsoft Excel

Uninstalling Noetix Add-in for Microsoft Excel (add-in) does not delete the log file. If you want to remove it, you must delete it manually.

If you want to have separate log files for each installation of the add-in, you must archive the log file of each installation by changing the file name from Error Log.txt to an intuitive name. Magnitude Software, Inc. recommends that you archive the file, otherwise all the messages will be displayed in one file making the maintenance and tracking of error messages difficult.

Noetix QueryServer Service

Uninstalling Noetix Platform deletes the log files. If you want to retain the log files pertaining to one instance of Noetix Platform, then you must save them in a different location before uninstalling Noetix Platform.

Noetix Agent Tools

The Noetix agent tools allow you to schedule recurring tasks and automatic alerts. You can set up agent jobs to periodically scan your metadata for issues or to receive alerts about specific conditions in your data (for example, sales exceeds 5,000 orders).

To send notifications about the success or failure of agent jobs and alerts for the agent jobs to operators by email and pager, you must configure the agent tools as follows:

Set up an email profile in Microsoft SQL Server

Create operators in NQS Administrator

Create agent jobs with notifications or create data alerts and validation alerts for agent jobs in NQS Administrator

Setting Up an email Profile

Noetix Platform uses Microsoft SQL Server’s SQL Server Agent Mail feature to send email notifications about the success or failure of agent jobs and email alerts for agent jobs. SQL Server Agent Mail uses Database Mail to send email notifications and alerts. Therefore, you must configure Database Mail, and then configure SQL Server Agent Mail to use Database Mail. Then, you must configure the Noetix agent tools with the mail profile created while configuring Database Mail. For information about configuring Database Mail and Noetix agent tools, see the information provided below. For information about configuring SQL Server Agent Mail to use Database Mail, see the Microsoft documentation.

The first task in configuring the agent tools is to set up a Database Mail profile with administrative privileges. You can set up a Database Mail profile in SQL Server in one of the following ways:

By using the Database Mail Configuration Wizard: You can use the Database Mail Configuration Wizard to accomplish the first six steps in the procedure. For more information about using the Database Mail Configuration Wizard, see the SQL Server documentation.

By using Transact-SQL: You can configure a Database Mail profile by running Transact-SQL statements in the query window of SQL Server Management Studio. Transact-SQL statements to perform the first six steps listed in the following procedure are tabulated after the procedure. For more information about using Transact-SQL, see the SQL Server documentation.

To set up a Database Mail profile for agent jobs

Enable the Database Mail feature on the SQL Server computer.

Create a Database Mail account.

Create a Database Mail profile.

Add the Database Mail account that you created in step 2 to the Database Mail profile you created in step 3.

Grant the Database Mail profile access to the msdb public database role and make the profile the default Database Mail profile.

Send a test email from SQL Server.

Change the mail properties for SQL Server Agent to use Database Mail. Specify the Database Mail profile that was created in step 3.

Run the Agent Configuration Utility in NQS Administrator, and provide the default Database Mail profile as your new Database Mail profile.

Transact-SQL statements for the first six steps of the above procedure are as follows:

Step No.

Transact-SQL statements

1

begin

exec sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

exec sp_configure 'Database Mail XPs', 1

RECONFIGURE WITH OVERRIDE

exec sp_configure 'Agent XPs',1

RECONFIGURE WITH OVERRIDE

2

EXECUTE

msdb.dbo.sysmail_add_account_sp

@account_name = 'MyMailAccount',

@description = 'Mail account for Database Mail',

@email_address = 'xxxxx@xxxxxx.com',

@display_name = 'xxxxxxxx',

@username = 'xxxxxxxx',

@password = 'xxxxxxxx',

@mailserver_name = 'xxxx.xxxxxxx.xxx' ;

3

msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MyMailProfile',

@description = 'Profile used for Database Mail' ;

4

EXECUTE

msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MyMailProfile',

@account_name = 'MyMailAccount',

@sequence_number = 1 ;

5

EXECUTE

msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MyMailProfile',

@principal_name = 'public',

@is_default = 1 ;

6

declare @body1 varchar(100)

set @body1 = 'Server:'+@@servername+ ' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail

@recipients='xxxxx@xxxxxx.com',

@subject = 'My Mail Test',

@body = @body1,

@body_format = 'HTML' ;

Table 1.1 Transact-SQL statements to configure a Database Mail profile.

Provide appropriate values for account name, description, email address, profile name, user name, password, mail server name, and recipients.

Creating Operators

The next step is to create operators, the users who want to receive notifications about the success or failure of agent jobs and agent job alerts through email messages. After setting up NQS, operators are added using NQS Administrator. Ensure that your NQS is running before starting this task.

Additional operators can be created at any time.

To create an operator

On the left-hand side of the main window (the Navigator area) of NQS Administrator, expand the Agent node listing to show its submenus.

Right-click Operators and choose New Operator. The New Operator properties sheet appears in the main window. For more information about any of the tabs, click the tab and press F1.

Type the operator's unique full name along with a destination for the notifications and alerts. The destination for the notifications and alerts can be an email address, pager address, or workstation on the network. Click to select the email or pager address from your system’s address book.

When using a pager as a destination, you may specify the on-duty days and hours during which a message can be sent.

Click Save.

Creating Agent Jobs

The final step is to create the agent jobs that you want to run. Jobs are created using NQS Administrator. If you want to send notifications about the success or failure of agent jobs and the agent job alerts through email messages to the operators, the operators must first be created using the steps mentioned in Creating Operators.

The Agent Job Wizard helps you create a validation agent job or a data alert job. A validation agent job alerts if any of the mappings are no longer valid, and a data alert job informs when a certain condition occurs in the data.

Agent jobs can also be created manually using the New Job property sheet.

To create a job using the Agent Job Wizard

On the left-hand side of the main window (the Navigator area) of NQS Administrator, expand the Agent node listing to show its submenus.

Right-click on Jobs, and choose:

New Auto-Job > New Validation Agent Job to create a validation agent job

New Auto-Job > New Data Alert Job to create a data alert.

To create a job manually

On the left-hand side of the main window (the Navigator area) of NQS Administrator, expand the Agent node listing to show its submenus.

Right-click on Jobs, and choose New Job.

For more information about the tabs in the Job properties sheet, press F1 in the selected tab.

Creating a Data Alert

A data alert alerts you when certain conditions are met in your data. You define an SQL statement using the VTables you have defined in NQS that returns one row and one column of data.

Then, you define what conditions need to be met for an alert to be sent. For example, you can write an SQL statement that returns the current number of orders for today. You can then define a condition that alerts you when that number exceeds 5,000.

To create a data alert using the SQL page

On the left-hand side of theNQS Administrator window, click Agent. The submenus are displayed.

Right-click on Jobs, and in the New Auto-Job menu, click New Data Alert Job. The Create a New Agent Job wizard appears.

Click Next. The Instructions page for creating an agent job appears.

Click Next. The Query Builderwizard appears. You have to use the standard Query Builder to build an SQL statement that uses VTables in your NQS. The Query Builder only lets you add tables from your local NQS repository.

Remember that the SQL you build can only return one column and one row of data.

Click . The Add Table page appears. Under the From box, select a server and a database you want to use. All the tables for the selected database are displayed in the Tables/Views box.

Select a table, and then click Add. Click the Close button to close the Add Table page. The table and its columns are displayed in the Table area.

Double-click the column for which you want to set the criteria. The column details are displayed in the Column area.

Type the filter conditions for the column in the Criteria field. These criteria relate to the WHERE clause in a query and restrict the data returned by the query. If the criteria (WHERE clause) contains an "OR" operator, use the additional "Or ..." fields.

Click Close and Keep Changes to save the column criteria and quit the Query Builder wizard. The Data Alert Condition page appears.

To add a condition in the Data Alert Condition page, select an operator from the list, then type an appropriate value. Some operators (like “equals”) require one value, some operators (like “between”) require two values, and some (like “exists”) do not require any value. For example, select “equals” from the drop-down list box and type 5000 in the box. A job alert will be sent to the user when the quantity reaches 5,000. Click Next.

You can combine up to three different conditions with AND or OR. To add more conditions, select the check box of the next row.

In the Edit Job Properties page, create or edit the schedule properties and select the operators to be notified from the Notify via email list. Click Next. The job alert is created.

Click Finish to exit the Create a New Agent Job wizard.

To send a job alert manually (other than at the scheduled time), right-click the job that you want to send in the NQS Administrator window’s detail view area, and then click Start Job. Click View History to view the status of the job.

Creating a Validation Job

The Validation Agent is a separate application that runs on the computer on which you installed your NQS repository.

If you have installed your NQS repository on the same computer as NQS then the Validation Agent would already be installed for you. If your NQS repository is on a separate computer than your NQS then you will need to install the agent tools on the repository computer.

When the agent is opened, it scans the metadata to detect any issues. This includes most errors that might prevent a particular virtual table (VTable) or mapping from being used. Any errors or warnings that are discovered are written to a table called N$System.Base.IM_JobLog, and can be viewed in NQS Administrator under the \Agent\Logs item in the Navigator. Note that you will have to refresh the Navigator (using the F5 key) to view any new logs if you run a validation agent job from NQS Administrator.

You can also specify the objects that should be checked by the agent. You can pick a particular folder, subfolder or VTable to validate by clicking . All VTables and mappings under this chosen object will be validated.

To create a validation agent job

On the left-hand side of the NQS Administratorwindow, click Agent. The submenus are displayed.

Right-click on Jobs, and in the New Auto-Job menu, click New Validation Agent Job. The Create a New Agent Job wizard appears.

Click Next. The Instructions page for creating a validation job appears.

Click Next. The Validation Agent Options page appears.

Type the name and password to connect to and validate your VTables and mappings.

Select one of the following options to indicate the object that the validation agent must validate:

All of them: Validates all the objects in the server.

Starting at: Validates only the objects that you select to validate. To select specific objects for validation, click . The Locate Folder, Subfolder or VTable dialog box appears. You can select the object you want to validate, and then click OK. The selected object and its children will be validated. Click Next.

In the Edit Job Properties page, create or edit the schedule properties, and select the operators to be notified from the Notify via email list. Click Next. The Validation Agent Job alert is created.

Click Finish to exit the Create a New Agent Job wizard.

To send a job alert manually (other than at the scheduled time), right-click the job that you want to run in the NQS Administrator window's detail view area, and click Start Job. Click View History to view the status of the job.

Caching

When implementing any decision support system, all administrators are eventually faced with the problem of performance. Relational databases store information in a very efficient manner, especially if the database is highly normalized. But they do not always retrieve this information efficiently for reporting and other operations.

If queries take longer than a few seconds to return their first row of data, most users get restless. If queries take longer than a few minutes, users complain.

The caching feature in NQS has been designed to handle this issue. Administrators can configure a mapping to be cached, as well as define a schedule for its caching. After this is done, the Noetix QueryServer service does the work of maintaining the cache tables and then redirecting user requests to the cached data.

Architecture

NQS is able to cache data with the help of Noetix CMS. This component does the real work of caching the data from the source to the defined cache connection, that is, the target.

CMS can be installed on multiple computers in your network as long as they meet the minimum system requirements. This can include the computer on which you are running NQS. CMS is installed as a Windows service (CacheMaintenanceServer) on a computer and connects to NQS. If CMS must connect to a database other than Microsoft SQL Server (source or target), you must also install and configure the vendor's database connectivity software on the CMS computer.

When NQS determines that a cache should be refreshed from either a scheduled or manual launch, it queries all CMSs that have been connected and determines which one is the least active. It then passes the cache request to the chosen CMS, which loads the data from the source system and inserts the data into the cache table. More specifically, CMS runs the mapping's Record Source against the source database and then inserts the results of this query into the cache table on the target system.

After the cache has been refreshed, CMS reports the results back to NQS. If the cache refresh is completed successfully, NQS will automatically reroute all subsequent user requests to the cache table.

Recommendation

You might consider installing CMS on a dedicated cache computer to reduce network traffic.

About Cache Connection

CMS has the ability to cache a mapping's data to either a Microsoft SQL Server database or an Oracle database. This means that you could cache from a DB2 database to an Oracle database, or from Oracle to SQL Server, and so on.

If possible, Magnitude Software, Inc. recommends that you set up a cache-enabled mapping connection back to the same database instance as the source data. This will let you join cached and non-cached VTables with greater ease without having to use heterogeneous mappings. You will experience better performance with this configuration. For more information about heterogeneous mappings, see Heterogeneous Mappings in “VTables and Mappings.”

Caching VViews

To cache a virtual view (VView), the connection type must be the default connection. The default Loopback Server is configured with a pass through connection. So to cache a VView, you need to create a new Loopback Server and a connection configured with the default connection. Then use that new server and connection with the VView mapping you want to cache.

Things to Consider

You must first define the connection to the cache destination either manually or with the Servers and Connections Wizard.

A connection can only be used as a cache connection if it has been set to the default type (not prompt, pass through, or lookup).

Consider the amount of information you may be moving over the network. You might want to have the CMS computer as well as the source and target database computers on a fast network backbone.

If you are caching to a computer other than the source, remember that if you join this virtual table (VTable) to any other VTable on a different connection (cached or non-cached), heterogeneous mappings will be used.

If the source data and the CMS are from different database vendors, some vendor-specific data types may not work properly (that is, Unicode data types).

About Caching Modes

You can configure a cache-enabled mapping in either of the two modes: one-copy or two-copy.

One-Copy Mode

In one-copy mode, CMS creates and maintains only one cache table on the cache database. This is ideal if you want to minimize the space required for this mapping's cache. The disadvantage is that, while the cache is being refreshed, this mapping will not be accessible to users, that is, it will be offline.

Advantage

Disadvatage

Minimizes space used

Data is offline during refresh

Table 1.2 Advantage and Disadvantage of One-Copy Mode

Two-Copy Mode

In two-copy mode, CMS creates and maintains two different tables on the cache database. The first time the mapping's cache is refreshed, CMS creates a table and loads the data into it. The next time that the mapping's cache is refreshed, CMS creates a second table and loads the current data into it. During refresh, any user requests is routed to the first table. When the refresh is finished, CMS instructs NQS to cut over to the second table and any new requests from users is redirected to the second table. This occurs as each new refresh is completed.

In NQS Administrator, these two tables are referred to as the Active and the Inactive Tables.

Advantage

Disadvantage

Data is always available

Requires enough space for two copies of the cached data

Table 1.3 Advantage and Disadvantage of Two-Copy Mode

About Cache Indexes

Indexes help relational databases return your queries faster. This is also true for cache tables, so NQS lets you define indexes for your cache tables.

You should define indexes that include the fields that will most likely be filtered on by your end users. Any index that you define is created and maintained by CMS. If you make changes to the indexes you have defined for a cache-enabled mapping, the following changes will take effect the next time the cache is refreshed:

You can create multiple indexes for a cache-enabled mapping.

An index can contain one or more columns.

Some databases can be affected by the order of the columns in the index. Please check with your database vendor's documentation for more information.

When caching to a Microsoft SQL Server database, CMS creates cache table indexes that are non-unique and non-clustered.

When caching to Oracle, CMS creates cache table indexes that use the default storage parameters for the user defined in the chosen cache connection.

About Routing Queries to Cached Mappings

When choosing a cache connection, you should take into consideration the other VTables and mappings that may be used in conjunction with (or joined to) this cached mapping.

For example, let us say that you have four VTables (each with one mapping) that use the same connection. You enable caching on one of the mappings and choose a different cache connection for it. Now, if a user sends a query that joins one or more of the non-cached mappings to the cached-mapping, NQS will be forced to route the query to a common heterogeneous mapping (if one exists). The down side to this is that using a heterogeneous mapping usually results in poorer performance.

Another thing to remember is that you can have multiple mappings on a VTable where some are cached and some are not. By adjusting the cost or access to these mappings, you can route certain queries or users to live or cached data.

Caching a Mapping

You cannot cache a mapping if it has more than one connection. For more information about connection, see Connections in “Servers and Connections.”

Creating a Cache for a Mapping

To create a cache for a virtual table (VTable) mapping

In NQS Administrator, navigate to and select the required mapping in a VTable for which you want to create a cache.

In the lower pane, click the Cache tab in the Map Properties dialog box.

Select the Enable check box. Enter the following information, and then click Save:

Connection to cache to: Select the connection you want to cache to.

Mode: Click one of the following caching modes:

If you need to minimize the space required to cache the VTable, then click One Copy. The cached table will not be available when refreshing.

If space is not an issue and you want to ensure that the cached VTable is available when refreshing the cache, click Two Copy. If a cache job fails, the old cache will still be accessible to the end users.

Refresh Timeout: Type the hours and minutes for the time-out.

Indexes: Click in the Index area, select the required columns from the Build Index dialog box, and then click OK.

Refresh Properties: Click this to create the cache. The Refresh Mapping Job dialog box is displayed.

To create the cache immediately, click Start Refresh in the General tab.

When the cache is completed, you can click the Logs tab to view any error messages that might have occurred during the caching process.

Scheduling a Cache for a Mapping

To create a cache schedule job for a virtual table (VTable) mapping

Create a cache for the VTable by performing steps In NQS Administrator, navigate to and select the required mapping in a VTable for which you want to create a cache. through Select the Enable check box. Enter the following information, and then click Save: in “Creating a Cache for a Mapping.”

When you reach the Refresh Mapping Job dialog box, click the Schedules tab.

Click Add in the Schedules tab to add a caching schedule. The Schedule - New Properties dialog box is displayed.

In the Schedule - New Properties dialog box:

Enter the name for the caching schedule in the Name box.

Select the Enabled check box. To run a scheduled cache job, it must be enabled and the Agent must be started.

If you want to create a scheduled cache job but not run it, save the information in the Schedule - New Propertiesdialog box without selecting the Enabled check box.

Select a schedule for the caching job by clicking the required option button in the Schedule Type area.

Click Save.

Using Your Cached Mappings

Before users can use a cached mapping, it must be refreshed at least once. If it is not refreshed, an alert is displayed stating that the cached data is not available. Hence, for the first time you must manually refresh the caching. For information about refreshing a caching, see Managing Cached Mappings.

After you enable caching on a mapping, NQS attempts to route users to the cache. If the cache is not refreshed even once, an alert is displayed when they try to use this mapping.

Magnitude recommends that you schedule your cache to be refreshed periodically. The frequency of your cache refresh will depend on the data that you are caching and your user needs. You will want to schedule your cache to be refreshed during off-peak hours so that the refresh process does not interfere with other work.

The General tab of the Refresh Mapping Job dialog box displays information about the latest refresh of a cache. Use this information to help you plan out the refresh schedule for the cache. For example, if it is taking 2 hours to refresh a cache, make sure that you schedule the cache refresh to run at least 2 hours before the first user accesses the updated data.

Managing Cached Mappings

To view all the cached mappings in NQS Administrator, click Cache Status on the Tools menu. The Cache Status dialog box displays all the cache-enabled mappings in NQS and their current status.

In the Cache Status dialog box:

To refresh the status, click Update Display.

To manually start the process of refreshing a mapping’s cache, then select the mapping in the Cache enabled maps list and click Refresh Now.

To view a mapping’s property page, double-click the mapping in the Cache enabled maps list or select the mapping and click Properties.

Caching Limitation

You may face the following limitations while caching data between different databases:

If your Oracle database (source) contains data prior to January 1, 1753, then you will not be able to cache this data to Microsoft SQL Server. This is because SQL Server datetime data type cannot accept date values below January 1, 1753. If you attempt to cache data prior to January 1, 1753, an alert will be displayed and the caching will fail.

As a workaround, you can cache the data to another Oracle database.

Cached virtual table (VTable) mappings of Unicode data must use connections that employ the native Oracle ODBC driver. Unicode data should not be cached from an Oracle database to a SQL Server database because Unicode characters are not converted correctly.

You get a data conversion error when you cache smallmoney, tinyint, and uniqueidentifier data types of SQL Server to Oracle database. These data types of SQL Server are changed to the FLOAT data type of Oracle Database when cached from SQL Server to Oracle Database. Though the data remains intact and usable, it cannot be queried through NQS.

As a workaround, change the data type of the VTable column containing the real data type to FLOAT.

When you cache data from a database of one type to a database of another type (for example, from Oracle Database to SQL Server), the sort orders for data types may differ between the noncached record set and the cached record set. This problem occurs because the character set for the first database type may differ from that for the second database type.

Backing Up and Restoring Repositories

Magnitude recommends backing up your Noetix Platform environment regularly. If a problem occurs, this will allow you to restore your system to the state it was in at the time of the backup. Performing a backup is also recommended before upgrading to a new version of the Noetix Platform.

Backing Up Repositories

The Backup Repository Wizard in NQS Administrator lets you back up your NQS and NWQ configuration and data. This tool is based on Microsoft SQL Server’s robust backup tool and lets you create a backup file of your NQS/NWQ repository, NQS Monitor repository, and/or SQL Server Reporting Services database.

You can also use this wizard to back up repositories used by Noetix Generator for Noetix Platform—Oracle E-Business Suite (Noetix Generator), if available.

You can choose to create your backup file immediately or schedule the backup for a later date.

This process does not back up the NQS/NWQ file system, including configuration files. Backing up your Noetix servers on a regular basis is also recommended.

For information about the procedure of backing up the repositories, see “Backing Up NQS” in the NQS Help.

Restoring Repositories from Backup Files

The NQS Restore Repository Wizard lets you restore the NQS and NQS Monitor repositories and Microsoft SQL Server Reporting Services database from a previous backup.

You can also use this wizard to restore repositories used by Noetix Generator for Noetix Platform—Oracle E-Business Suite (Noetix Generator), if available.

Before you begin, you must complete the following tasks:

Verify that NQS is installed and the Noetix QueryServer service is running. If you need to reinstall NQS, use the setup file that was used during installation.

Locate the backup file on your file system. If the backup file exists on a different computer or tape device, copy the file to this computer (the computer on which NQS and/or client software are installed).

Verify that your version of Microsoft SQL Server has not changed since the backup was created.

While restoring the repository, the Noetix QueryServer service will be stopped temporarily. You may want to notify the users that NQS or NWQ will not be available during the restore process.

Ensure that the SQL Server Reporting Services and Noetix WebQuery Gateway services are stopped. These services can be stopped using the Services snap-in.

Ensure that you have exited NQS Administrator and all the applications that use SQL Server.

Obtain the user name and password of an NQS user who has been assigned the N$BackupOperators and N$AgentOperators roles.

Restoring Repositories Without Uninstalling Noetix Platform

This section provides the procedure for restoring the repositories without uninstalling Noetix Platform. For information about the procedure of restoring the repositories without uninstalling Noetix Platform, see “Restoring Noetix Repositories from Backup Files” in the NQS Help.

Restoring Repositories on a Fresh Installation of Noetix Platform

This section provides the procedure for restoring the repositories on a computer on which you have installed Noetix Platform, taken a backup of the repository, uninstalled Noetix Platform, and then installed it. When the repositories are restored in such a scenario, the NWQSystem user's password is not restored in the Noetix.Gateway.Service.exe.config file. Therefore, there will be problem in logging on to NWQ. For information about the procedure of restoring the repositories on a fresh installation of Noetix Platform, see “Restoring Noetix Repositories from Backup Files” in the NQS Help.

Support for Unicode

Unicode characters are supported in Noetix Platform. Unicode characters allow you to generate reports in multiple languages.

Prerequisites

To ensure complete support for Unicode characters in Noetix Platform, you must take care of the following:

The native Oracle ODBC driver must be used instead of Microsoft ODBC for Oracle driver to return Unicode data because the latter is not completely Unicode-compliant.

Oracle Database must be configured for Unicode data during the installation.

The character set of the Oracle Home's must be set to “UTF8” using the NLS_LANG subkey. You can find the NLS_LANG subkey in the Windows registry at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\.

Cached virtual table (VTable) mappings of Unicode data must use connections that employ the native Oracle ODBC driver. Unicode data should not be cached from an Oracle database to a SQL Server database because Unicode characters are not converted correctly.

The computer running Microsoft SQL Server Reporting Services must be configured with all the relevant language code pages and fonts to properly render files for subscription delivery. Otherwise, some file formats, such as CSV and PDF will return incorrect characters instead of Unicode characters.

The computers running NWQ, Microsoft Excel or any other data client must have the proper language code pages and fonts installed to display Unicode characters correctly.

Setting the Locale of Noetix QueryServer Service

On the computer on which you have installed the Noetix QueryServer service, you can modify certain registry keys to specify a different locale for the Noetix QueryServer service so that it supports string comparison of Unicode characters.

After you have installed the Noetix QueryServer service on a computer, the following registry keys are created in HKEY_LOCAL_MACHINE\SOFTWARE\Noetix\QueryServer\
CurrentVersion\Locale:

LC_ALL: Sets all locale settings.

LC_COLLATE: Affects string comparisons and collation.

LC_CTYPE: Affects upper/lower case conversions, string length functions, and multi-byte to wide character conversions.

LC_MONETARY: Affects monetary value formatting.

LC_NUMERIC: Affects numeric value formatting.

LC_TIME: Affects date/time formatting.

If the LC_ALL key is assigned a particular locale then all other keys will be ignored. By default, the value of this key is null.

By default, the locale is set to English for other keys if the respective key value does not exist. If you want a different locale, you should change the values of these keys after installation or upgrade and restart the Noetix QueryServer service.

Noetix Delivery Services Configuration Files

The Noetix Delivery Services rely on the configuration files for successful subscription delivery. Each service has its own configuration file with the elements that control their working.

Important: Customizations to the elements in the Noetix DeliveryManager.exe.config and Noetix DeliveryServer.exe.config files will be effective only after the corresponding services are restarted.

Noetix DeliveryManager.exe.config

This file is located in <hard disk drive>:\Program Files\Noetix\NWQ\Delivery. The elements specified in this file regulate the working of Noetix DeliveryManager. You can customize the following elements in the file. However, to customize any other elements, you must contact Magnitude Support. You must use a text editor to modify this file.

SystemPassword: Displays the password of the NWQSystem user.

SMTP: Displays the address of the Simple Mail Transfer Protocol (SMTP) server for outgoing messages. This is the SMTP server that was specified during the installation of Noetix Platform.

ReplyToUser: Sets a user name for the sender when delivering a subscription through email. If this value is not set, Noetix DeliveryManager will use the default value, Subscriptions. The value set in ReplyToUser element will be used only if the Reply To box on the Delivery Preferences page in NWQ is left blank when creating a subscription to be delivered through email.

ReplyToDomain: Sets the domain name (for example, xyz.com) when delivering a subscription through email. If this value is not set, Noetix DeliveryManager will use the value set for SMTP element if available or the default value. The default domain name is set as an empty string. The domain name set in ReplyToDomain element will be used only if the Reply To box on the Delivery Preferences page in NWQ is left blank when creating a subscription to be delivered through email.

Customization to the values of the SystemPassword and SMTPelements will be lost if Noetix Platform is reinstalled.

Noetix DeliveryServer.exe.config

This file is located in <hard disk drive>:\Program Files\Noetix\NWQ\Delivery. The elements specified in this file regulate the working of Noetix DeliveryServer. You can customize the following elements in the file. However, to customize any other elements, you must contact Magnitude Support. You must use a text editor to modify this file.

Customizations to the RegistrationIntervalMilliseconds and MaxRegistrationAttempts elements will not be effective. They are no longer supported by Noetix Platform.

SleepIntervalMilliseconds: Sets the interval time, in milliseconds, for which each task processor thread waits when the central queue in Noetix DeliveryManager is empty. If this value is not set, Noetix DeliveryServer will use its own default value, 100.

SystemPassword: Displays the password of the NWQSystem user.

VerboseLogging: Enables you to view more descriptive and complete error messages in the event log for all the process levels. If this value is not set, Noetix DeliveryServer will use its own default value, true.

MaxProcessorThreads: Sets the maximum number of task processor threads that can be run and managed in a thread queue. The default value is 5.

WebRequestTimeoutMilliseconds: Sets the time, in milliseconds, for which the website waits for a response from Microsoft SQL Server Reporting Services. The default time-out is 15,000.

Customization to the values of the SystemPassword and WebRequestTimeoutMilliseconds elements will be lost if Noetix Platform is reinstalled.

Time-Outs in NWQ

When running complex or long queries, web service applications, such as NWQ, may face problems because the waiting time for retrieval of data may cause time-outs resulting in termination of the process at various places in the technology stack. To solve this problem, NWQ uses a process called data polling.

For more information about how a query is processed in NWQ, see How a Query is Processed in “About Noetix Platform.”

Data Polling in NWQ

Data polling in NWQ includes two different processes. In the first process, the Noetix Gateway (gateway) connects to NQS to retrieve the query header information from the schema. In the second process, the gateway connects to the database using the routing information from NQS to retrieve the rows. The gateway will wait for a specified time to return the first set of rows. If it does not return any row within that time, then the query header information from the schema is returned to NWQ, thus preventing a query time-out. Then, NWQ repeatedly polls for data until all the data is returned. Thus, the double-threaded process helps to keep the session active until all the rows are returned.

Settings for Time-Outs

The time-outs set in NWQ and in other external applications are as follows:

External Settings

Connection Timeout: Sets the maximum time (in seconds) after which the server disconnects an inactive user. This ensures that all connections are closed if the Hypertext Transfer Protocol fails to close a connection. The default time is set to 900 seconds.

To change the Connection Timeout value, in the Internet Information Services window, navigate to Local Computer > Web Sites > Default Web Site, and then click Properties in the short-cut menu. In the Default Web Site Properties property sheet, type the value in the Connection Timeout box.

httpRuntime/executionTimeout: Sets the maximum time (in seconds) that a query is allowed to run before it is automatically closed by Microsoft ASP.NET. The default time is set to 110 seconds.

To change the executionTimeout value, navigate to <hard disk drive>\WINDOWS\Microsoft.NET\Framework\<version number>\CONFIG\machine.config, and then type the value in the executionTimeout subelement under the httpRuntime element.

Session timeout: Sets the maximum time to automatically end an inactive session (if the user has not used or refreshed a page of the application). The default time is set to 20 minutes.

To change the Session timeout value, in the Internet Information Services window, navigate to Local Computer > Web Sites > Default Web Site, and then click Properties in the short-cut menu. In the Default Web Site Properties property sheet, click the Configuration button on the Home Directory tab. The Application Configuration dialog box is displayed. In the Application Configuration dialog box, type the value in minutes in the Session timeout box.

processModel/responseDeadlockInterval Timeout: Sets the time interval (in the standard process model format (HR:MM:SS)) after which the process is restarted if there are other requests in the queue or there was no response during the interval. The default time is set to three minutes.

To change the responseDeadlockInterval value, navigate to <hard disk drive>\WINDOWS\Microsoft.NET\Framework\<version number>\CONFIG\machine.config, and then type the value in the responseDeadlockInterval subelement under the processModel element.

NWQ Settings

The time-outs for NWQ are set in the Noetix.Gateway.Service.exe.config file.

The following are the time-outs set for running a query in NWQ:

Data polling time-out: Sets the time for which NWQ waits for schema information (columns, data types, etc.) to be returned before cancelling a query. This time-out is determined by a combination of values defined in the file. The time-out will be the lowest among the MaxDataWaitTimeout, ODBCTimeout, and recieveTimeout (a WCF setting) values. These time-out values should be modified only with the assistance of Magnitude Support.

Command time-out: Sets the maximum time (in seconds) for which the gateway waits for the query to run. This value is defined in the ODBCTimeout subelement under the Execution element in the file. This time-out comes into effect only if any given operation against the ODBC driver takes longer than the set time, usually during the initial parsing and running of a query. The default value is 300.

Repository time-out: Sets the maximum time (in seconds) that NWQ can take to interact with its own SQL server repositories. This value is defined in the SQLTimeout subelement under the Repository element in the file. The default value is 100.

The following are the time-outs set for running a query in Microsoft Excel:

Excel link time-out: Sets the maximum time (in seconds) for which the gateway waits to retrieve all the data in one call in the IQY file format. This setting works in conjunction with ODBCTimeout. The gateway will use the lower value between the ODBCTimeout and ExcelLinkTimeout values. The default value is Infinite. This value is set in the ExcelLinkTimeout subelement under the Execution element in the file.

Command time-out: This setting is the same as the Command time-out setting set for running a query in NWQ.

NWQ Configuration Files

You may need to modify one of the configuration files for NWQ, This usually occurs when troubleshooting withMagnitude Support, but is also necessary for creating drill rules to allow users to drill between two queries in NWQ. This section explains how to work with these configuration files.

The gateway relies on configuration files, such as Noetix.Gateway.Service.exe.config, web.config, and nwqopt.xml for many of the parameters it uses when running queries. These parameters include time-out intervals and drill rules.

Noetix.Gateway.Service.exe.config: This file contains the custom gateway settings, such as system accounts, elements for querying in Microsoft Excel, folder and computer names, and configuring drill rules. It affects NWQ and Noetix Add-in for Microsoft Excel (add-in). Some of its elements should not be customized without the assistance of Magnitude Support. This file is available in <hard disk drive>\Program Files\Noetix\NWQ\NoetixGateway\Server\bin. For information about the elements available in this file that can be customized by the user, see Noetix.Gateway.Service.exe.config.

web.config: This file is used by the gateway.

nwqopt.xml: This file contains the settings that determine the working of NWQ. This file is available in <hard disk drive>\Program Files\Noetix\NWQ\nwq. For information about the elements in this file, see nwqopt.xml.

Noetix.Gateway.Service.exe.config

This file is found in <hard disk drive>\Program Files\Noetix\NWQ\NoetixGateway\Server\bin and contains the custom gateway settings. You must use a text editor to modify this file.

Gateway Settings

 

NWQClientVirtualDir: Displays the virtual folder name where NWQ is installed. This folder is different from the virtual folders for the gateway or NWQ Administrator. The default value is /nwq.

NWQClientDir: Displays the physical folder name where NWQ (client) is installed.

InstallPath: Displays the NWQ Gateway installation path.

GatewayUrl: Displays the NWQ Gateway URL that is used by the NWQ to determine the gateway’s URL. The default value is http://localhost/noetixgateway. If your organization policy requires the use of Secure Sockets Layer (SSL) for encryption of user credentials and data, then the URL must be https://localhost/noetixgateway. If you use a port number other than 80 (when you do not use SSL) and 443 (when you use SSL), then you must manually specify the port number after the server name with a colon. For example, if you use 3456 as the port number, then the URL must be changed to http://localhost:3456/noetixgateway.

Query Timeout:Sets the maximum time after which an IQY file is deleted and replaced by a new one while running a query in Excel. The default value is 300.

ExcelQueryDir:Displays the physical path to the folder where the .iqy files must be stored in the NWQ server.

ExcelQueryUrl: Displays the virtual directory for the .iqy files. The default value is /noetixgateway/queries/.

ExcelQueryPromptResponsibility:Determines whether the Oracle E-Business Suite responsibility prompt is displayed for the users when running a query in Excel. The valid values are Yes, No, and Inherit. The default value is Inherit, which indicates that the user will be prompted for a responsibility only if the user is an Oracle Applications user.

ExcelQueryVer:Determines the earliest version of Microsoft Excel that users may want to run queries in. This decides whether NWQ can use the new Excel features to pass data to Excel. The default value is xp.

SystemPassword: Displays the password for the NWQSystem user, which is used by NWQ to manage the NWQ profiles.

Server:Displays the name of the SQL Server computer.

Database:Displays the name of the NQS repository. The default value is nqsdb.

User: Displays the name of theNQS repository administrator. The default value is nqsadmin.

NQSHub: Displays the name of the NQS computer.

SQLTimeout: Sets the time (in seconds) that NWQ takes to interact with its own SQL Server repositories. The default value is 100.

SQLCommandTimeout: Sets the maximum time (in seconds) that Noetix repository (nqsdb) takes to return the data. The default value is 60.

MonitorMode: Sets the initial mode of NWQ Monitor. The valid values are Stop and Start. The default value is Stop.

Execution ODBCTimeout: Sets the maximum time (in seconds) for which the gateway waits for the query to run. Set a high value if you have to run queries that require more system resources. The default value is 300.

ExcelLinkTimeout: Sets the maximum time (in seconds) for which the gateway waits to retrieve all the data in one call in the IQY file format. This setting works in conjunction with ODBCTimeout. The gateway will use the lower value between the ODBCTimeout and ExcelLinkTimeout values. The default value is Infinite.

MaxDataWaitTimeout: Sets the maximum time (in seconds) for which the gateway waits for retrieving schema information and data. The gateway will use the lowest value among the MaxDataWaitTimeout, ODBCTimeout, and receiveTimeout (a WCF setting) values. The default value is 20.

FetchBufferMultiplier: Sets the maximum fetch buffer size of the result set based on the requested fetch size. For example, if the requested fetch size set in NWQ is 200 and the fetch buffer multiplier is 4, then the maximum fetch buffer size will be 800. The default value is 4.

FetchInactivityTimeout: Sets the maximum time (in seconds) the gateway waits between fetch calls before the query is cancelled. Pausing a query (by clicking the Pause button on the query results page of NWQ) for longer than the time set for the FetchInactivityTimeout element will cause the query to be cancelled. When you run queries in Excel, the gateway will attempt to fetch all the records in a single batch. In this case, the value of the FetchInactivityTimeout element should be set large enough so that all the records are retrieved in a single batch. Otherwise, the query might time out. The default value is 600.

DisallowConflictResolution:Determines whether the restricted features for a user in NWQ is hidden from view or made visible, but unavailable. The default value is 1. This element can also be set in NWQ Administrator.

Drill Rule

 

add RuleName: The name of the drill rule. This should be a descriptive name that will help you identify the purpose of the rule in the future. You may want to use a naming convention that helps you distinguish between the custom drill rules and the default drill rules, for example, by adding your company name as part of the rule name. This name will be displayed to the end users in NWQ when they do a drill across.

DrillFrom > Columns > add Column: The columns specified in this field will be considered when searching for matches for the drill-from column.

DrillFrom > Scopes > add Scope: This drill rule will be applied if the drill-from query exists in the specified folder or in one of its subfolders.

DrillTo > Columns > add MatchOn: The columns specified in this field will be considered when searching for matches for the drill-from column.

DrillTo > Columns > add FilterOn: The column specified in this field will be used to filter the results of the drill-to query.

DrillTo > Scopes > add Scope: All the folders and subfolders from within the specified folder will be searched for the correlated queries.

nwqopt.xml

This file is found in <hard disk drive>\Program Files\Noetix\NWQ\nwq. You must use a text editor to modify this file. You can edit the following elements in this file:

gwpost: Name of the base virtual directory and the web service provider for the gateway. This element must ideally never be changed.

iqypost: Enables you to set up an NWQ Router which can pass through the firewalls. This element can be left blank if the firewalls are configured.

QueryAuthor: Obsolete.

DisplayCookieWarnings: Obsolete.

MultipleValueDelimiter: Sets the character to be used as a delimiter when multiple values for a filter or a parameter are typed. The default delimiter used is the vertical bar (|).

QueryPerPage: Sets the number of queries/reports to be displayed per page. The value set here is used by the Browse page in the Query Migration/Rebase wizard.

LoadRowSize: Sets the maximum number of rows that the gateway can return per request. When you are running large queries, increasing the number of rows improves the query performance, but increases the server memory usage.

QueryHistoryMax: Sets the maximum number of queries that are cached for an NWQ session. The value must be in the range of 1 to 20. A user can see the query history for an NWQ session by clicking My Recent Queries in NWQ.

QueryCacheDuration: Sets the time (in minutes) for which query results are cached within a session. Within the set time, if a user runs a query more than once in a session, NWQ will fetch the stored results from the cache to improve the query performance. After the set time elapses, NWQ will go back to the server to fetch fresh results whenever a user runs the same query again. Set the query cache duration at “0” if you do not want to enable caching. Set the query cache duration at “-1” if you do not want query results to expire within a session. The user can still fetch real-time results for a query by clicking re-execute from the navigator.

The queries are cached in the web bowser’s process of the user’s computer. The query result cache is cleared when the user closes the web browser after an NWQ session.

AuthenticationSecurityLevel: Sets the level at which the user and domain credentials are secured. User credentials are used to log on to NWQ. Domain credentials are used to deliver subscriptions in NWQ. You use the domain credentials when you click the Deliver as this user option on the Options page of NWQ.

The values are Low, Medium, and High, and they are case-sensitive.

Low: It is the default value. This value does not result in encryption of the user and domain credentials by NWQ.

Medium: This value does not result in encryption of the user credentials by NWQ in the first call to the gateway. But, in subsequent calls, the user credentials will be encrypted. However, this value does not result in the encryption of the domain credentials.

High: This value results in encryption of the user and domain credentials by NWQ with the use of SSL. Thus, for the High value to be used, SSL must be configured in IIS where the gateway resides.

If SSL is activated and users log on using the https://<computer name>/nwq/login.htm route, both data and credentials (user and domain) are encrypted. Thus, the authentication security level can be set to Low so that NWQ does not encrypt the user credentials again.

Because SSL secures both data and credentials (user and domain), it may cause performance issues. Alternatively, to improve performance, the High setting can be used and users required to log on through the http://<computer name>/nwq/login.htm route. In this case, only the credentials will be encrypted.

DefaultDateTimeFormat: Specifies the default formatting for all columns of datetime data type that do not already have any formatting specified. The formatting specified by Noetix Generator for Noetix Platform—Oracle E-Business Suite Edition (Noetix Generator) for the generated reports and the ones specified in NWQ will take precedence over the value specified for this element. By default, the value is blank so that the formatting specified in NWQ for the columns of datetime data type is retained on an upgrade.

Data Drills

Data drill is an advanced feature and requires an understanding of the data structure and building reports. Data drills allow you to select a column in your query result and view a detailed level of information from a summary level. In NWQ, we can do the following three types of data drills:

Drill down: Lets you drill to a more detailed level of a query by adding other columns to the query results. Drilling down allows you to include additional columns from the query's base table, and also filter on a specific value of a column.

Drill across: Lets you drill to a correlated query, restricting the results by a specific value. By default, correlated queries refer to any query in the Personal, Public, or VTables folders that have a column with the same name (or if you have renamed the column, with the same column alias) as the column that was selected for drill across. Drilling across is effective when other queries return additional details about the value you selected. You can only drill across to queries that you have permission to view and run.

Drill through: Lets you drill to another query or a website to view related information using a column hyperlink or a URL hyperlink respectively. Drilling through is also effective when other queries return additional details about the value you have selected.

For more information about data drills, see “Data Drills” in the section “Working with Query Results” in the NWQ User Help.

Drill Rules

Drill rules are specified in the Noetix.Gateway.Service.exe.config file. You must use a text editor to modify the file. There is no limit to the number of drill rules that you can create. In the file, there are four example drill rules that are created. There is one example of a static rule, two examples of dynamic drill rules, and one example of a drill rule that will use the A$ columns available in Noetix views. You must customize these drill rules to improve performance.

After an upgrade of Noetix Platform from a version prior to 5.7, all the drill rules will be migrated from the previous version. However, the XML comments, if any, will not be migrated. If you need the XML comments, you have to manually provide them in the Noetix.Gateway.Service.exe.config file. After an upgrade of Noetix Platform from version 5.7 or later, there will be no change to the drill rules and XML comments.

Drill rules apply only to drill across. For the drill across feature to be usable, your administrator or a Magnitude consultant must set up drill rules to correlate queries. The drill down and drill through features do not require any configuration and are available upon installation of Noetix Platform.

Drill rules decide which queries will be displayed when a user does a drill across (that is, which queries will be considered correlated). You can create drill rules to limit the scope of NWQ’s search for correlated queries and to define synonyms, that is, columns with different names but that can be considered equivalent, such as Customer and Account. When drilling from one query to another, NWQ runs the drilled-to query and limits the results to where the column equals the value you selected.

Magnitude recommends that you customize or create the drill rules with the help of Magnitude Support.

For each drill rule you create, you will define the following:

Where the user is starting from:

The folders you can drill from using the rule: This is the drill-from scope. The drill rule for which you have set this scope will be applied if the drill-from query exists in the specified folder or in one of its subfolders. You can use the static name of a specific folder (such as My Web Queries.Personal), or you can use a variable, which will be replaced with the name of the current folder.

The columns in a query that you can drill from using the rule: This is the drill-from column. This drill rule will be applied if the user drills from a column defined in this field. You can use the column name or column alias that will be the start of the drill (such as Customer), or you can use a variable, which will be replaced with the name of the column on which you are performing the drill across.

Where the user can drill across to:

The folders that should be searched for matches: This is the drill-to scope. All the folders and subfolders from within the specified folder will be searched for the correlated queries. You can use the static names of specific folders (such as My Web Queries.Public), or you can use a variable, which will be replaced with the name of the folder the user is drilling from.

The drill-to scope should be set at the lowest level possible to improve performance. This will minimize the number of folders and subfolders that will be searched.

The column in the drill-to query that should be considered a match: This is the match-on column. The columns specified in this field will be considered when searching for matches for the drill-from column. Queries that contain this column (and exist in an appropriate folder) will be displayed as correlated queries. You can use the column name or column alias to match (such as Account), or you can use a variable, which will be replaced with the name of the drill-from column. Other variables can also be used to either add or subtract a specific string (such as A$) from the name of the column before matching.

The column in the drill-to query that should be filtered on: This is the filter column. The column that you specify will be used to filter the results of the drill-to query. Generally, you will want to filter the query on the same column that was used for matching. (For example, if you are drilling from the Customer column in one query to the Account column in another query, the query should be filtered by Account.) However, you can specify a different column to filter on if required. You can use the column name or column alias to filter on (such as Account), or you can use a variable, which will be replaced with the name of the column that was drilled from. Other variables can also be used to either add or subtract a specific string (such as A$) from the name of the column it filters on.

Supported Variables in Drill Rules

Important: You cannot mix variables and literals. For example, you cannot use Sales.[FOLDER].

The following table shows the context in which the various variables can be used while creating drill rules. In the table, “Y” indicates that the variable can be used for that element. Each variable is discussed in more detail after the table.

Variable

Drill-From Scope

Drill-To Scope

Drill-From Column

Drill-To Column, Match-On

Drill-To Column, Filter

[FOLDER]

Y

Y

 

 

 

[PARENT]

 

Y

 

 

 

[COLUMN]

 

 

Y

Y

Y

string
[COLUMN]

 

 

 

Y

Y

{+:string}

 

 

 

Y

Y

{-:string}

 

 

 

Y

Y

Table 1.4 Supported Characters for Drill Rules

[FOLDER]: NWQ will replace this variable with the name of the current folder. When used in the drill-from scope, this means that the drilled-from query can be in any folder in NWQ. When used in the drill-to scope, this means that NWQ will search within the same folder as the one used for the drill-from scope.

[PARENT]: NWQ will replace this variable with the name of the parent folder of the current folder. It can be used in conjunction with [FOLDER]. For example, [PARENT].[PARENT].[FOLDER] will cause NWQ to search in the folder two levels above the current folder, plus all subfolders within these folders.

[COLUMN]: NWQ will replace this variable with the name of the drill-from column that the user selects. Only one column can be entered. Along with this variable, you can add the following:

string[COLUMN]: This operator will always add the text specified for the string to the start of the column name. It is used in conjunction with [COLUMN].

{+:string}: This operator will add the text specified for the string (if it is not present already) to the column name before searching. It is used before or after [COLUMN] (that is, as either {+:string}[COLUMN] or [COLUMN]{+:string}). For example, {+:A$}[COLUMN] will add A$ to the column name before searching for correlated queries. If [COLUMN] is Vendor, then the operator will add A$ to make the column A$Vendor. Queries that have the A$Vendor column will be treated as matches. If the column name already has a string added at the beginning or end (depending on where you put the operator), it will not be added.

{-:string}: This operator will remove the text specified for the string (if it is present) from the column name before searching. It is used before or after [COLUMN] (that is, as either {-:string}[COLUMN] or [COLUMN]{-:string}). For example, {-:A$}[COLUMN] will remove A$ from the column name before searching for correlated queries. If [COLUMN] is A$Vendor, then the operator will remove A$, making the column name Vendor. Queries that have the A$Vendor column will be treated as matches. Nothing will happen if the column does not have a string.

Important: You cannot mix more than one variable listed above. For example, you cannot enter {+:A$}[COLUMN]{-:_History}.

Examples

The following examples discuss the process of defining criteria for a drill rule, creating the rule, and explaining the result.

Drill Between Two Columns With Different Names

This example shows how to create a drill rule that allows you to drill from one column to a column with a different name. This drill rule is static and does not use any variables.

Goal

Create a rule that allows users to drill from the Vendor column to the Supplier column, which are synonyms in this company. The Vendor column must be in a query located in a user’s Personal folder. The queries that they will likely want to drill to are located under the Suppliers and AP subfolders in the Public folder. For performance, we will only search within those subfolders for queries to drill to.

Creating the Rule

When creating the drill rule, you would enter the following values:

Rule Name: “Find Suppliers from Vendors”

Drill From Column: “Vendor”

Drill From Scope: “My Web Queries.Personal”

Match On Column: “Supplier”

Filter Column: “Supplier”

Drill To Scope: “My Web Queries.Public.AP” and “My Web Queries.Public.Suppliers”

Result

The drill rule above will be used when retrieving correlated queries if the user chooses to drill across from a column named Vendor in a query that exists in the My Web Queries.Personal folder (or any of its subfolders). When the rule is applied, it will tell NWQ to search in the My Web Queries.Public.Suppliers and My Web Queries.Public.AP subfolders (and their subfolders) for reports that have a column named Supplier. Any matching queries will show up in the Correlated Queries list when a user drills across. After the user runs one of the correlated queries, it will run the query with an added filter that states (Supplier = ‘[the value the user chose to drill from]’).

Drill Using Complex Variables

This example shows how to create a drill rule that uses complex variables in the Columns fields. The example uses a default rule that is created upon installation.

Goal

Create a rule that allows users to drill from any column and match to Noetix indexed columns, which are prefixed with A$. For instance, a user should be able to drill from the Customer column to an A$Customer column in another query. The query should always be filtered on the column without the A$ prefix, however. The drill rule only needs to apply within the Public folder.

Creating the Rule

When creating the drill rule, you would enter the following values:

Rule Name: “Noetix Indexed Rule”

Drill From Column: “[COLUMN]”

Drill From Scope: “My Web Queries.Public”

Match On: “{+:A$}[COLUMN]”

Filter On: “{-:A$}[COLUMN]”

Drill To Scope: “My Web Queries.Public”

Result

The drill rule above will be used when retrieving correlated queries any time a user drills from a query in the Public folder (or any of its subfolders). When the rule is applied, it will tell NWQ to search within the Public folder and subfolders for any query containing a column of the same name as the drill-from column, but with an A$ at the beginning. If the drill-from column already has an A$ at the beginning, NWQ will ignore this rule. Any matching queries will show up in the Correlated Queries list when a user drills across. When the user runs one of the correlated queries, it will run the query with an added filter that states ([the column the user chose to drill from] = ‘[the value the user chose to drill from]’).

Creating Drill Rules

To create a drill rule

Open the Noetix.Gateway.Service.exe.config file.

Do one of the following:

Change the values for the elements provided in the examples in the file.

Copy the elements that are provided in the file in the examples, paste it in the same file, and then customize the values for the elements.

For more information about the elements and how to set the drill rules, see Noetix.Gateway.Service.exe.config and Drill Rules respectively.

Save the file, and then close it.

After creating or editing a drill rule, Magnitude recommends that you periodically back up these files on another server. If a problem occurs, you can restore all the files from a backup.

Important: After creating or editing a drill rule, you must restart the Noetix WebQuery Gateway service for the drill rule to be effective.

Deleting Drill Rules

To delete the drill rules

Open the Noetix.Gateway.Service.exe.config file.

Select the drill rules you want to delete, and delete them manually.

Save the file, and then close it.

Restoring Connections Between NQS and NQS Repository

If the connection between NQS and the NQS repository is lost because the NQS repository is shut down, NQS will restore the connection after the NQS repository is restarted. It might take some time before the connection is restored. Therefore, you must try until the connection is restored.

If the connection is lost between a client application and the NQS repository or physical database, then to restore the lost connection, you must log on to the client application again after the NQS repository or the physical database is restarted.

For this feature to work, ODBC connection pooling must be enabled for the Microsoft ODBC driver for SQL Server in ODBC Data Source Administrator. The time for which the ODBC Driver Manager waits between attempts to restore a lost connection depends on the retry wait time set for the Microsoft ODBC driver for SQL Server. For example, if the connection retry wait time is set to 120 seconds, then the ODBC Driver Manager makes an attempt to reconnect to the NQS repository every 120 seconds until the connection is established successfully.

You can modify the settings for the connection pooling for the Microsoft ODBC driver for SQL Server using ODBC Data Source Administrator. To open ODBC Data Source Administrator, click Start > Control Panel > Administrative Tools > Data Source (ODBC). For more information about modifying the settings for the connection pooling, refer to the documentation of ODBC Data Source Administrator.

Was this article helpful?

We're sorry to hear that.