Skip to main content

Noetix QueryServer (NQS)—virtual tables (VTables) and mappings

The building blocks of Noetix QueryServer (NQS)—virtual tables (VTables) and mappings—are explained in this chapter, along with a high-level overview of working with these objects.

Overview

One of the key benefits of NQS is that it provides a single point of access to multiple data sources, both for reporting and for maintenance. The flexible reporting model, based on the idea of VTables, makes this possible.

This section explains the NQS reporting model, how queries are handled by NQS, and how to manage enterprise data at the table level.

VTable Architecture

VTable is the core of reporting against NQS. You can use VTables to map information in columns from physical database tables to NQS and create an optimal configuration of enterprise information. Any tables in your databases that you want to report against through Noetix are defined in NQS by using VTables.

A VTable is similar to a view in Oracle or Microsoft SQL Server. A view is an out-facing set of columns that are filled by a SQL statement retrieving data from one or more tables. The view doesn’t store any information; it just provides the user with a specific view of the data. If you want to create a view, the basic syntax looks like the following:

CREATE VIEW (column1, column2, column3)

AS (SELECT a_column, another_column, yet_another_column

FROM table1, table2

WHERE table1.column = table2.another_column)

A VTable works in the same way. A VTable starts as a list of out-facing columns, just like the column list (column1, column2, column3) from the CREATE VIEW statement above. When a user queries the VTable, they see these out-facing columns. These columns can be given any name and don’t have to be the same as the columns from the underlying database.

Once you have defined your column set, you need to define a query to fill the columns. This is done with the VTable’s mapping. A mapping links the columns in the VTable to data in a physical database, just like the SQL statement portion of the CREATE VIEW statement above (select a_column, another_column, yet_another_column from table1, table2 where table1.column = table2.another_column) links actual data to the columns defined in the view.

When you run a query against a VTable, NQS in turn runs the query defined in the mapping against the physical database to return data to the user, just like querying a database view. Also just like a database view, a VTable doesn’t store any data. It just provides a way to view it.

The separation of the column list and mapping in NQS makes VTables much more powerful than database views. A database view can only have one SQL statement to fill the out-facing columns. However, a VTable can have multiple mappings for this purpose. When a query is run, NQS uses a complex algorithm that determines which mapping should be used based on criteria such as permissions, as well as the structure of the SQL statement. This provides administrators with a way to redirect a user’s query to different tables or even a different database. This can be used to direct users to local copies of their data to minimize network traffic, direct users to pre-aggregated versions of their data to minimize query time, and so on. Creating multiple mappings in this way is optional, and an advanced task. For more information about mappings, see Mappings.

Simplified Data Access

This flexible architecture of VTables and mappings gives the administrator the power to manage enterprise data access in a variety of ways to best suit the reporting needs of the company. Individuals in the company who need access to this data, however, do not need to be bothered by structural information (servers, connections, tables, and stored procedures) and technical details (network paths and protocols, server names, and cryptic formulas).

When end users access NQS using a query tool or Noetix WebQuery (NWQ), they see the VTables available to them, with names and columns the administrator has set up using business terminology, and none of the technical details. Users can write reports against these VTables without regard to where the data is stored or what mapping is being used to retrieve the data for the report.

Before You Begin

Determine reporting needs: It is a good idea to do some planning before you start creating VTables in NQS. While you have a lot of flexibility in that you can rename folders, subfolders, and VTables, or move these objects around, once your users start building reports based on these objects, any changes you make can break these reports.

Before creating VTables, decide what folders and subfolders will make sense to your users, as well as which database objects should be imported into NQS. Decide whether you will create VTables to act as views, which users can create their own queries against, or to correspond directly to reports, which will require more maintenance by the system administrator or report developer.

If a database object is too complex for your users (for example, too many columns) you should consider creating a Complex VTable that does not have all of the columns of the physical object or that joins multiple tables or views together. If your database is highly normalized, you will probably want to use the Complex Virtual Table Wizard and denormalize any IDs or lookups.

VTables and VViews

VTables and virtual views (VViews) are the reporting objects your users will base their queries on in NWQ or other query tools. VTables are based on tables or views that already exist in a database in your enterprise. VTables are closely related to database views in that they do not store any data but instead reference other tables or views using SQL. The advantage of VTables in NQS over database views that live in a particular database is that VTables can be configured to intelligently span multiple databases.

VViews give you more options to join and configure these VTables in NQS. A VView is a special kind of VTable that is based on other VTables you have already created in NQS. VViews enable you to build more and more Complex VTables on top of those VTables that you have already defined.

If a report needs to be created against multiple VTables, they can be joined into a VView using NQS Administrator. Creating VViews requires advanced knowledge of SQL and joining tables from your particular data sources.

In NWQ, users can be granted permission to create VTable joins in addition to those created in NQS. For more information about VTable joins in NWQ, see the NWQ User Help.

VViews can join VTables that point to different physical databases. To do this, each VTable first needs a heterogeneous mapping against the same heterogeneous mapping server. For more information about heterogeneous mappings, see Complex VTables.

After you set up these reporting objects in NQS Administrator and grant permissions to them, users can run them as queries from NWQ.

It is a good idea to do some planning before you get started creating VTables in NQS. You may want to list all the sources you need to import tables and views from, as well as how to effectively organize them into folders and subfolders. While you have a lot of flexibility in that you can rename folders, subfolders, and VTables, as well as move these objects around, once your users start building reports based on these objects, changes you make will break these reports.

Simple VTables

A simple VTable is a one-to-one reference to an existing database table or view definition. To access an existing database view or report in NQS, set it up as a simple VTable.

Create a simple VTable to:

Import a table already in a database in your enterprise.

Create a VTable from a custom view in your database.

Import a table or view so you can then create more complex objects from it.

Complex VTables

A complex VTable also imports table or view definitions from existing data sources, but can join multiple tables or views together and uses a complex SQL statement. Complex VTables allow you to join tables so they can be queried against in NWQ.

In NWQ, users can be granted permission to create VTable joins in addition to those created in NQS. For more information about VTable joins in NWQ, see the NWQ User Help.

Create a complex VTable to:

Import and join multiple tables and views for users to build reports against.

Build complex SQL statements against multiple tables.

Build a SQL statement to limit the number of columns included in a VTable.

VViews

A virtual view (VView) is a VTable that is based on other VTables you have already created in NQS. This is very similar to creating a view based on one or more existing views.

Create a VView to:

Join simple or complex VTables that you've already created or imported in NQS.

Build more complex VTables on top of those VTables that you have already defined.

Creating a Simple VTable

Before you begin:

Make sure you have set up a server in NQS corresponding to the data source or database from which you want to import the table or view.

Make sure the connection to this server has access to the table or view.

To create a VTable, use the Simple Virtual Table Wizard. The wizard walks you through each step of creating simple VTables from existing database tables or views. If you need more information about any of the steps involved, click the Help button.

To create a simple VTable

In NQS Administrator, click on the toolbar.

The Administrator Wizards dialog box appears. On the Basic Setup tab, double-click Simple Virtual Tables Wizard.

After the introductory pages, the wizard prompts you to select a server. Select the server from the list, and click Next. (If the server you want to import from is not in the list, click the Add new server button to set it up.)

Select the connection to use to connect to your database. (If an appropriate connection has not yet been set up, click Add new connection to create one.) Click Next.

On clicking Next, the Which tables to setup page appears.

Select the tables to import. When you reach this page, the wizard will connect to your database (using the default user and password of the connection chosen) and fetch the objects owned by this database user.

Before the Which tables to setup page appears, you might see the Loading dialog box depending on the size of the metadata in the selected database. You can click Abort or Cancel to stop loading the metadata if you have selected a wrong data source or if the metadata is taking a long time to load. Clicking Abort stops the loading process immediately and might leave some traces of incomplete data. Clicking Cancelstops the loading process at a logical point, and it might take longer. However, clicking Abort or Cancel does not close the wizard.

On the left-hand side of the page, you will see a schema tree and object types. On the right-hand side, you will see a list of the objects owned by the node that is currently selected in the tree. By default, this page displays the tables owned by the database user.

A simple VTable is created for each table or view you select in this page. You can quickly select all objects of the current type under a node by selecting its check box. You can also quickly select all or none of the items in the right-hand list by selecting the Select All or Select None buttons. Click Next when you are finished.

(Optional) The next page allows you to create descriptions for the new VTables. You may change these later as well. Click Next to continue.

Next, select the folder or subfolder in which to put this new VTable. You may create a new folder or subfolder or select existing ones. Click Next to continue.

For more information about folders and subfolders, see Organizing VTables.

The Processing Duplicates page asks you what NQS should do if a VTable of the same name already exists in the subfolder you selected. Select an option, then click Next to continue.

(Optional) Select a log file or accept the default. This page also prompts you for whether to use heterogeneous mappings. If there is a chance you might want to join this VTable to another VTable in the future, ensure that this check box is selected. Click Next to continue.

(Optional) The Initial Security page allows you to grant permission to this VTable. You can do this later or choose to do it now if you have already set up users and roles in NQS. Click Next when you are finished.

(Optional) Next, select any custom name options you want to use, and then click Next to continue.

(Optional) Select any category you want to add to this table, and then click Next to continue.

Categories are currently used within NQS only, but may have additional functionality for NWQ in an upcoming version.

The last page shows you an overview of what you are importing. Click Finish to import the tables or views.

Creating a Complex VTable

Before you begin:

Decide which objects you want to import and how you want to join or limit them.

Ensure you have set up a server in NQS corresponding to the data source or database from which you want to import the table or view.

Ensure that the connection to this server has access to the table or view.

The Complex Virtual Table Wizard helps you in creating a complex VTable from existing database tables or views. If you need more information about any of the steps involved, click Help.

To create a complex VTable

In NQS Administrator, click on the toolbar. The Administrator Wizards dialog box appears.

Click the Maintenance tab, and then double-click the Complex Virtual Table Wizard. The Complex Virtual Table Wizard appears.

Click Next in the wizard. Type the name of the folder and subfolder where you want to create the VTable. You may create a folder and subfolder or select existing ones. Click Next to continue. The Name the Virtual Table page appears.

For more information about folders and subfolders, see Organizing VTables.

Enter a name for the new VTable. You may customize the name of the mapping and add a description if you want. Click Next to continue. The Pick Server page appears.

Select a server from the list, and then click Next. (If the server that you want to import from is not in the list, click Add new server to set it up.) The Pick Connections page appears.

Select the connection to use to connect to your database. (If an appropriate connection has not yet been set up, click Add new connection to create one.) Click Next to continue. The Enter SQL source page appears.

Build the SQL code for the complex VTable. You may write the SQL code from scratch or click Build SQL to use the Query Builder tool.

The SQL code that you type or paste in the box must not exceed 65, 535 characters.

If using Query Builder, first select the tables, expanding and dragging each window until they are easy to work with. Specify the columns (by double-clicking them) and criteria (by typing in the appropriate SQL code). To join tables, drag a column from one table onto a column in another that can be joined (for instance, the same Z$ column in each view). Click Close and keep changes when finished with Query Builder.

Click Next to continue. The Virtual Table Structure page appears.

The Virtual Table Structure page displays the columns for your VTable that are returned by the SQL code you entered in the previous page. The wizard detects the column attributes, such as data type, length, and scale. You can add a description for these columns. Click Next to continue. The Relate Physical data to the Virtual Table page appears.

The Relate Physical data to the Virtual Table page lets you make the association between the columns that are being returned from the Record Source SQL (on the left) and the columns in the new VTable (on the right).

The wizard should have these matched up one-to-one between the two column sets.

To manually make an association, select a column entry under Record Source Column. Then, in the corresponding column entry under the VTable Expression column, type the VTable Expression or click to create a VTable Expression by using the Expression Editor. Click Next to continue. The Choose custom options page appears.

Make sure that you select the check box if you want to join this VTable to another VTable or virtual view (VView) from different servers in future. Click Next to continue. The Initial Security page appears.

(Optional) The Initial Security page allows you to grant permission to this VTable. You can do this later or choose to do it now. Click Next when you are finished.The Categories page appears.

For more information about permissions, see Granting Permission to a Role in “Managing Security.”

(Optional) Select any category that you want to add to this VTable, and then click Next to continue.

Categories are currently used within NQS only, but will have additional functionality for NWQ in an upcoming version.

The last page in the wizard shows you an overview of what you are importing. Click Finish to import the table or view.

Creating a VView

To create a virtual view (VView), use the Virtual View Wizard. The wizard walks you through each step of creating a VView from existing VTables. If you need more information about any of the steps involved, click the Help button.

To create a VView

In the NQS Administrator, click the on the toolbar.

The Administrator Wizards dialog box appears. On the Maintenance tab, double-click Virtual View Wizard.

After the introductory pages, the wizard prompts you to select the folder or subfolder in which to put this new VView. You may create a new folder or subfolder or select existing ones. Click Next to continue.

Enter a name for the new VView. You may customize the name of the mapping and add a description if you want. Click Next to continue.

Build the SQL code for the VView. You may write the SQL code from scratch or click the Build SQL button to use the Query Builder tool.

The SQL code that you type or paste in the box must not exceed 65, 535 characters.

If using Query Builder, first select the tables, expanding and dragging each window until they are easy to work with. Select the columns (by double-clicking them) and criteria (by typing in the appropriate SQL code). To join tables, drag a column from one table onto a column in another that can be joined (for instance, the same Z$ column in each view). Click Close and keep changes when finished with Query Builder.

Click Next to continue.

The Virtual View Structure page displays the columns that are returned by the SQL you entered in the previous page. The wizard detects the columns' data types, length, scale, and so on. If you want, you can add a description for these columns.

This page lets you make the association between the columns that are being returned from the Record Source SQL (on the left) and the columns to be created in the VTable (on the right).

The wizard should have these matched up one-to-one between the two column sets.

To manually make an association, select the Record Source column from the list in the left-hand cell. Then, in the right-hand cell, either type the VTable expression or open the Expression Editor. Click Next to continue.

(Optional) The Initial Security page allows you to grant permission to this VView. You can do this later or choose to do it now. Click Next when you are finished.

(Optional) Select any category (see below) you want to add to this VView, and then click Next to continue.

Categories are currently used within NQS only, but will have additional functionality for NWQ in an upcoming version.

The last page shows you an overview of the VView you are creating. Click Finish.

Modifying a VTable

The Table Change Wizard walks you through the steps necessary to change a VTable in NQS.

If there is no mapping defined for a VTable and you run the Table Change Wizard for the VTable, then an error message is displayed.

The wizard helps you do the following:

Add a new column to a VTable

Rename a column

Delete a column

When a complex VTable or virtual view (VView) is deleted, the obsolete change alerts remain in all the VTables that the deleted complex VTable or VView used as the data sources. If you see such change alerts and find the referenced complex VTable orVView deleted, you can either ignore the change alerts or manually delete them from the repository. The change alerts are stored in the IM_Notes table of the nqsdb database in Microsoft SQL Server.

To add a column to a VTable

Right-click on the VTable you want to modify, and on the shortcut menu, click Table Change Wizard.

The welcome page appears.

Select Add new columns, and click Next.

The Choose the Mapping page appears.

In the Choose the Mapping page:

If the VTable has a single mapping, then the mapping is selected by default.

If the VTable has multiple mappings, then select the mapping you want to modify.

Click Next. The Modify the column mappings page appears. The new columns that are available to be added from the source table in the database will be displayed.

For simple VTables, if a mapping maps all of the columns in the underlying database table or view, the wizard will detect this and display a message to the user stating that no additional columns can be added. However, if a simple VTable contains a mapping that does not map all of the columns in the underlying database table or view, the wizard will allow additional columns to be added to the mapping and the VTable, or just the mapping.

Select the check boxes next to the columns you want to add to the VTable. By default, the column name in the VTableColumn list is the same as the column name of the base table. You can change the column name in the VTable Column list. Click Next. The Completing the Table Change Wizard page appears. The page provides information about the columns that will be added to the VTable.

Click Finish. The selected columns will be added to the VTable.

To rename or delete an existing VTable column

Right-click on the VTable you want to modify, and on the shortcut menu, click Table Change Wizard.

The welcome page appears.

On the welcome page, select one of the following:

Rename columns: If you want to rename a column in the VTable.

Delete virtual table columns: If you want to delete a column in the VTable.

Click Next. The Modify the virtual table columns page appears.

In the Modify the virtual table columns page, do one of the following:

If you selected Rename columns in step 2, then the Name field will be editable. Edit the column names as required. After you edit a column name, the font color changes to blue and font style changes to italic.

If you selected Delete virtual table columns in step 2, then select the check box next to each column you want to delete.

Click Next. The Completing the Table Change Wizard page appears. The page provides information about the changes that will be made to the VTable.

Click Finish.

Important: If the object you are modifying or deleting is created by Noetix Generator for Noetix Platform—Oracle E-Business Suite Edition (Noetix Generator), the Change Alert dialog box appears. Changing these objects manually might result in loss of information during regeneration. Click OK to proceed with the change (renaming or deleting the column) or click Cancelto abort the change and close the Table Change wizard.

Granting Permissions to a VTable

A user must have certain permissions to query a VTable or virtual view (VView) from either NWQ or a third-party reporting tool.

To allow the users to run queries against a VTable, ensure the following permissions have been granted:

Grant the users permission to the VTable or View and Mappings: This can be done implicitly (through organizations or roles) or explicitly (directly granting permissions to specific users), using NQS Administrator. For more information about permissions, see Granting Permission to a Role in “Managing Security.” For more information about accessing mappings, see Mappings.

Grant the users permission to the necessary connections: When a query is run against a VTable, it uses one of the connections you created for that data source. The user will need permission to this connection as well, either implicitly (through organizations or roles) or explicitly (directly granting permissions to specific users), using NQS Administrator. For more information about permissions, see Granting Permission to a Role in “Managing Security.” For more information about connections, see Connections in “Servers and Connections.”

For information about making a VTable display in NWQ, see Exposing VTables in NWQ in “Web Queries and Folders.” For information about making a VTable display in a third-party tool, see Granting a Default Subfolder.

Although you can grant an object explicit permission to a server, Magnitude Software, Inc. recommends granting permission to a role, and then assigning users and/or organizations to that role.

Organizing VTables

The reporting objects you create or import in Noetix QueryServer (NQS) are organized into folders and subfolders under the VTables folder. When querying a VTable, the “path” of that object reflects the folder and subfolder hierarchy. For example, if you create a folder named NY, and within it, a subfolder named sales and then save a VTable named invoices within that subfolder, you could query the table from NQS Query or a query tool using the syntax SELECT * FROM ny.sales.invoices. In NWQ, this full path also displays in the properties of a query.

Since the names of folders and subfolders will be displayed to end users, choose a folder structure and names that not only make them manageable but also have meaning to users in your enterprise.

Folders

VTables and VViews are grouped into folders and subfolders. You decide what to name these folders to help your users find the information they are looking for, as well as to make it easier to grant permission to groups of like objects at once.

Reporting objects cannot be stored directly in a folder; they must be stored in a subfolder. However, you can grant permissions at the folder level if you choose. For more information about permissions, see Granting Permission to a Role in “Managing Security.”

Create a folder to:

Organize VTables so they are easy to manage.

Be able to grant permission to a set of VTables at once, instead of granting each individually.

Subfolders

A subfolder is a folder within a folder, which can be used to organize reports by subject, task, or other manageable units. More importantly, a subfolder can be set as a default subfolder for a user or role, which makes the contents of the subfolder show up in query tools and NWQ (under the VTables folder) for that user or members of that role.

Create a subfolder to

Organize queries into a detailed folder structure.

Give a user access to the VTables in a certain subfolder (by granting it as a default subfolder).

Creating a Folder

To create a folder

In NQS Administrator, right-click on VTables in the left-hand pane.

From the menu, select New Folder. The New Folder properties page displays to the right.

On the General tab, type the name of the new folder, as well as a description and maximum number of rows to return in a query, if required.

(Optional) On the Notes tab, you may add notes to this folder. (You must click Save before adding a note.)

On the Permissions tab, specify any required permissions to users or roles.

For more information about permissions, see Granting Permission to a Role in “Managing Security.”

Click Save when finished.

Folders can also be created when you are importing or building VTables. You can name the new folder and later specify any other required properties for the folder.

Creating a Subfolder

To create a subfolder

In the NQS Administrator, right-click on VTables in the left-hand pane.

From the menu, select New Subfolder. The New Subfolder properties page displays to the right.

On the General tab, type the name of the new subfolder, as well as a description and maximum number of rows to return, if required.

(Optional) On the Notes tab, you may add notes to this subfolder. (You must click Save before adding a note.)

On the Permissions tab, specify any required permissions to users or roles.

For more information about permissions, see Granting Permission to a Role in “Managing Security.”

Click Save when finished.

Subfolders can also be created when you are importing or building VTables and VViews. You can name the new subfolder and later specify any other properties for the subfolder.

To prevent a reduction in performance while opening the subfolder in NWQ, take care to not add too many reporting objects to a single subfolder.

Moving a VTable to Another Subfolder

You can move a VTable or virtual view (VView) to another subfolder by selecting it, then dragging and dropping into the required subfolder. You may also right-click on the VTable, choose Cut from the menu, right-click on the destination, and choose Paste.

Be aware that if users have built reports from these objects, the reports may break if you move the underlying VTables.

Granting a Default Subfolder

A subfolder is displayed in NWQ (in the VTables folder) or a third-party query tool when the logged-on user has been granted the subfolder as a “default subfolder,” either implicitly (through organizations or roles) or explicitly (directly granting permissions to specific users). A user will only be able to access an object that is within one of these default subfolders.

For information about making a reporting object display for a user in NWQ, see Web Queries in “Web Queries and Folders.”

To grant a default subfolder

In NQS Administrator, double-click the user, organization, or role to which you want to grant the default subfolder.

Click the Default Subfolders tab.

Click within the white text area to display the ellipses button ( ).

Click the ellipses button to open the Locate Subfolder window. A tree of current folders and subfolders appears.

Browse to and select the required subfolder, and then click OK.

The subfolder is listed in the text area. Select the ellipses button in the empty text area below to add more default subfolders, or click Save to finish.

The user should now have access to the subfolder and its contents in NWQ or a third-party query tool.

To be able to run the VTable or any reports against it, ensure the user has permission to query the underlying VTable. For more information about permissions, see Granting Permissions to a VTable.

If a user has permission to an item that is not in one of his or her default subfolders, the item will not display in the VTables folder of NWQ. If a user has been granted a default subfolder but not granted permission to the object, the object will display, but the user will not be able to open or run it.

Mappings

Every VTable in Noetix QueryServer (NQS) has a mapping, which is the SQL that maps the VTable to the physical tables in your database. Users only interact with VTables, but the data they request is returned by a mapping. This can be a 1:1 mapping against a particular table in the database, or can be highly customized for security or performance improvements. A VTable can have multiple mappings, each tuned for a specific purpose.

When you create a VTable using the NQS Administrator wizards, a 1:1 mapping is created by default. This means that any query against this VTable uses the same mapping, and the mapping retrieves every column included in the VTable. Most of the time, these 1:1 mappings are sufficient.

In VTable with a Default 1:1 mapping, the Customers VTable and its 1:1 mapping in NQS point to a data source called US Customers. The VTable shows the columns and the mapping shows the SQL sent to the data source.

VTable with a Default 1:1 mapping

You can create additional mappings as required to route certain users or queries to different physical tables, or to use different SQL. In VTable with Custom and Default 1:1 mappings, a custom mapping has been added that points to a different data source, CA Customers Only. Queries against the Customers VTable can now be routed to either of these data sources.

VTable with Custom and Default 1:1 mappings

The following sections describe how to set up these custom mappings in NQS. For information about the reasons why you may choose to create a custom mapping, see Examples of Custom Mappings.

Creating custom mappings requires an understanding of your data and how it is structured in the database. Most of the examples listed require additional (non-NQS) activity on the database. Creating and maintaining aggregations, partitions, and so on, is not an easy task and should be relegated to an ETL tool.

Mappings

A mapping describes where and how data should be found in the physical database when a user queries against a VTable. Multiple mappings can be created for a single VTable, each tuned for a specific purpose or based on a specific expression in the query.

When you create a mapping, you define the server; the connection (or schema) that owns the tables; the Record Source (which is the SQL used to query the database); a list of the columns the mapping can retrieve; and the mapping's cost, or order of priority. Optionally, a mapping can include a virtual dimension (VDimension), a virtual filter (VFilter), and/or a heterogeneous mapping, described below.

Create a mapping to:

Speed up lookups by routing queries against a single column (for instance, to populate a list of values (LoV)) to a targeted physical table.

Create column-level security by restricting access to sensitive columns of a VTable (such as Salary).

Optimize access by location, routing queries against regional servers or reporting servers when appropriate.

Route users to specific servers (for instance, routing users who need up-to-the-minute data to an OLTP server and other users to a reporting server).

Mappings use the vertical partitioning concept. For more information about vertical partitioning, refer to your database documentation.

VDimensions

Virtual dimensions (VDimensions) can be added to a mapping to specify an aggregation (such as sum, avg, min, or max) that must be present in the query in order for the mapping to be used.

Create a VDimension to:

Route queries based on an aggregation that you set up.

Point queries that summarize detailed data to summary tables instead, for better performance.

VDimensions use the aggregate partitioning concept. For more information about aggregate partitioning, refer to your database documentation.

VFilter

A virtual filter (VFilter) can be added to a mapping to specify a condition that must be present in the query in order for the mapping to be used. That is, the query must be filtered by particular criteria.

Create a VFilter to:

Cause a mapping to be chosen based on a portion of the WHERE clause in the query.

Point queries against a limited result set to a different table or server that can more efficiently retrieve them.

VFilters use the horizontal partitioning concept. For more information about horizontal partitioning, see your database documentation.

Heterogeneous Mappings

A heterogeneous mapping can be added to a mapping to allow the VTable to be used in cross-database reporting. Heterogeneous mappings bring together data from different servers, or even different database types (for instance, joining a table in an Oracle database to a table in a Microsoft SQL Server database). By creating a heterogeneous mapping, you are defining an alternative route, through the heterogeneous mapping server (HMS), that NQS can use to retrieve data. When multiple VTables are defined that use the same alternative route, these tables can be combined into a single query or SQL statement.

A heterogeneous mapping gets its data the same way, and from the same place, as the regular mapping it is based on, but it takes an alternative route through the HMS.

Create a heterogeneous mapping to:

Provide a mapping to allow a single report to get data from different databases or database types.

Allow VTables using data from different servers to be joined together.

Which Mapping Will NQS Use?

When a given user sends a query or SQL statement to NQS, the Noetix QueryServer service must pick the best mapping for the job from the list of mappings for that VTable. The mapping is then used to determine where to retrieve the data from. NQS chooses which mapping to use by evaluating the following three criteria:

Permissions: First, the user running a query must have permissions to the mapping and to the connections the mapping uses. If the user executing the query does not have these permissions, NQS will not allow the mapping to be used; it will evaluate the next mapping, until one is found that the user can access. If the user does not have permissions to any of the existing mappings, NQS will return an error message instead of the query results.

Completeness: A mapping doesn't need to include every column in the VTable; it can include a subset of the columns instead. Because each mapping can be different in this way, NQS evaluates the mapping to ensure it satisfies the requirements of the query. If the mapping does not include everything needed in a query (for example, a column is missing), NQS will not use the mapping. It will move on to the next mapping, evaluating it in the same way until an appropriate mapping is found. NQS can only use a mapping that can return at least the columns asked for in the SELECT clause of the query.

Cost: Every mapping has a cost value from 0 (the lowest) to 100 (the highest). If a user has permissions to multiple mappings that all meet the criteria of the query, then NQS chooses the lowest-cost mapping possible and use this mapping to send the query to the data source. By ranking each mapping associated with a particular VTable using its cost, you determine which mapping you would prefer NQS to use if this happens. The default cost, which is used in the 1:1 mapping when you create a VTable using the NQS administrator wizards, is 50.

When virtual filters (VFilters) and/or virtual dimensions (VDimensions) are used in a mapping, NQS will also determine whether the WHERE clause or expression specified in the mapping exists in the query.

If you need to troubleshoot a query or determine which mapping it used, the NQS Monitor's Query Log displays which mapping each query was routed to. For information about using the Query Log, see the NQS Help.

Viewing a VTable’s Mappings

NQS Administrator displays a list of mappings associated with each VTable.

To view the mappings for a VTable

In the left-hand pane of NQS Administrator, navigate to the VTable.

Click the VTable to see its property pages. The top right-hand pane displays the mappings associated with the VTable.

This list allows you to quickly see what mappings exist for a VTable and what cost they have each been given. In this example, the VTable has a 1:1 mapping, and in addition, has two mappings created for optimizing lists of values (LoVs) for specific columns. These custom mappings have a lower cost (25) than the 1:1 mapping (50).

Click a mapping to view its properties in the property sheets below. These properties include the mapping's Record Source, column list, whether any heterogeneous mappings, virtual dimensions (VDimensions), or a virtual filter (VFilter) exist for the mapping, and whether the mapping is cached.

Click the Heterogeneous tab to view the heterogeneous mappings that exist for the mapping. This list displays the heterogeneous mappings and what cost they have each been given.

Double-click a heterogeneous mapping (or select it and click the Edit button) to view its properties. These properties include permissions to the heterogeneous mapping and the server and connection associated with it.

Creating a Custom Mapping

You need to know connection information for the data source that the mapping will point to. If this data source has not been created as a server and connection in NQS, you can create them during this process.

Use the Custom Mapping Wizard to create a custom mapping. The wizard walks you through each step of creating your mapping. If you need more information about any of the steps involved, click the Help button on any wizard page.

To create a mapping that is similar to an existing mapping, you can instead create a copy of the existing mapping, then edit the copy as you want. To do this, in NQS Administrator, navigate to the VTable and mapping. Right-click the mapping you want to copy and select Copy. Right-click on an empty row and select Paste. You can now edit properties of the copy, such as the columns retrieved, server, and so on. You can copy and paste mappings between VTables as long as the columns in the mapping are compatible with the columns in the VTable.

To create a custom mapping

Run the Custom Mapping Wizard in any of the following ways:

In NQS Administrator, click on the toolbar. The Administrator Wizards dialog box appears. Click the Maintenance tab, then double-click the Custom Mapping Wizard.

In NQS Administrator, on the Tools menu, click Administrator Wizards (Begin Here!). The Administrator Wizards dialog box appears. Click the Maintenance tab, then double-click the Custom Mapping Wizard.

Click on the required VTable, and, on the shortcut menu, click New mapping using the Custom Mapping Wizard. If you run Custom Mapping Wizard from a VTable's shortcut menu, then the Assign a Virtual Table page (step 3) will not appear.

Click Next on the Welcome to the Custom Mapping Wizard page.

On the Assign a Virtual Table page, select the VTable or virtual view (VView) for which you want to create this mapping, then click Next.

On the Name the Mapping page, enter a name for the new mapping. This name must be unique among the mappings of the selected VTable. End users will generally not see this name, but it will display in the administrative tools. You can add a description for these columns if you want. Click Next when you are finished.

The Mapping Cost page is where you indicate how NQS should choose which mapping to use when more than one mapping satisfies the requirements of a query. NQS uses the mapping with the lowest cost percentage in this case. Using the slider on the left-hand side of the page, drag the arrow to the required percentage. Click Next when you are finished.

For more information about costs and routing queries with mappings, see Which Mapping Will NQS Use?

On the Pick Server page, select the server containing the table information you want to use. If an appropriate server doesn't exist, you can create one by clicking the Add new server button above the Servers list. Click Next to continue.

On the Pick Connections page, select the connection (schema) that owns or has access to the tables or views you want to use. You may select more than one connection.

If you add multiple connections, the first connection you check displays with a check mark over the icon. This indicates that it is the Reference connection, the one that Query Builder and the Expression Editor tools will use to access and validate against the underlying RDBMS when helping you build your Record Source (SQL) in the next page. To change which connection is used, right-click the required connection in the list and choose Set as Reference Connection.

If an appropriate connection doesn't exist, you can create one by clicking the Add new connection button above the Servers list.

Click Next to continue.

On the Enter SQL Source page, enter the SQL that will form this mapping. To do this, you can either write or paste your SQL directly into the entry box or click the Build SQL button above the box to use the Query Builder tool. For information about using Query Builder, click the Help button on the Query Builder page.

The SQL code that you type or paste in the box must not exceed 65, 535 characters.

Your mapping does not need to contain all columns in the VTable, but should not contain more columns than the VTable.

If your SQL is complex or does not correspond directly to a physical table or view, you may want to select the Maintain Record Source as a View check box. This will create a view in the database schema of the connection you selected, using the SQL you enter in the Record Source. You must select this option if your database does not support SELECT statements in the FROM clause of a query (which is not an issue if using NoetixViews or an Oracle, Microsoft SQL Server, or DB2 database).

When you are finished, click Next to continue.

The Relate Physical Data to the Virtual Table page displays the columns in the new mapping (in the Record Source Column column) and the columns in the VTable (in the VTable Expression column). The columns will be initially related based on their names, if NQS can match them. Ensure that the columns are related correctly, and relate any columns that NQS could not automatically relate. You can make changes directly on this page or by clicking the Back button and editing your SQL.

If you want this mapping to be used only when a specific expression or aggregation is used, you can modify the VTable expression for the appropriate column. For example, if the mapping points to a physical table that aggregates total sales, you would change the “Sales_Total” column’s VTable expression to SUM(“Sales_Total”). In step On the Advanced Needs Analysis page, answer the two questions below, then click Next to continue., you must remember to create a virtual dimension (VDimension) for any aggregations. For an example of creating a VDimension, see Routing a Query Based on an Aggregation.

Click Next when you are finished.

On the Advanced Needs Analysis page, answer the two questions below, then click Next to continue.

Does the physical record set contain the complete set of rows that the VTable will ever return?

Answer Yes if your mapping will point to a table containing all rows the VTable could return.

Answer No if your mapping will point to a table with a subset of the data that could be queried using the VTable, and you want to create a virtual filter (VFilter). (For example, your mapping points to a table that contains California orders only, whereas the VTable in NQS can return orders from any state.)

If you answer No, then after clicking Next, you are prompted with the Virtual Filter page. To create the VFilter, enter the condition that must be present in a query's WHERE clause in order for this mapping to be used. (For this example, you would enter “SHIP_TO_STATE”=‘CA’) Click Next when you are finished. For an example of creating a VFilter, see Optimizing Data Access by Location.

Are any of the physical record set columns aggregates of VTable columns (SUM, AVG, MIN, and so on.)?

Answer No if your mapping is not pointing to an aggregated result set.

Answer Yes if your mapping will point to a table where one or more columns are aggregates of the columns in the VTable, and you want to create a VDimension. (For example, your mapping points to a table where sales are summarized by state, whereas the VTable in NQS can return non-summarized sales.)

If you answer Yes, then after clicking Next, you are prompted with the Virtual Dimensions page. On this page, select which columns must be included in a query's GROUP BY clause for the mapping to be used. (For this example, you would check the box next to the “SHIP_TO_STATE” column.) Make sure you adjusted the VTable Expression in The Relate Physical Data to the Virtual Table page displays the columns in the new mapping (in the Record Source Column column) and the columns in the VTable (in the VTable Expression column). The columns will be initially related based on their names, if NQS can match them. Ensure that the columns are related correctly, and relate any columns that NQS could not automatically relate. You can make changes directly on this page or by clicking the Back button and editing your SQL. as well; you can click the Back button if you need to return to do this now.

Click Next when you are finished. For an example of creating a VDimension, see Routing a Query Based on an Aggregation.

The last page shows you an overview of the mapping you are about to create. Click Finish to create the mapping and exit the wizard.

After creating your mapping, verify or set the permissions that users have to the mapping. A user sending a query to NQS must have permissions to the mapping, as well as the connection it uses, before a mapping can be used. For more information about permissions, see Granting Permission to a Role in “Managing Security.”

Creating a Heterogeneous Mapping

A heterogeneous mapping can be added to an existing mapping to enable cross-database reporting (that is, to allow users to join VTables from different databases in a single query).

When initially creating a VTable using the administrator wizards, you have to select the I want to join this table with other tables from different servers check box (on the Log File page) and an additional mapping, called a heterogeneous mapping, will be created for the new VTables. To add cross-database support to an existing mapping, follow the steps below to use the New Heterogeneous Mapping Wizard.

To add a heterogeneous mapping to an existing mapping

In NQS Administrator, navigate to the VTable and mapping to which you want to add the heterogeneous mapping.

Right-click on the mapping and select New Heterogeneous Mapping Wizard.

Click Next on the Welcome to the New Heterogeneous Mapping Wizard page.

On the Name the Heterogeneous Map page, enter a name for the new mapping. This name must be unique among the mappings of the selected VTable. End users will generally not see this name, but it will display in the administrative tools. You can add a description for these columns if required. Click Next when you are finished.

The Heterogeneous Map Cost page is where you indicate how NQS should choose which mapping to use when more than one mapping satisfies the requirements of a query. NQS uses the mapping with the lowest cost percentage that fulfills the requirements of the query. Using the slider on the left-hand side of the page, drag the arrow to the required percentage:

If you assign a higher cost than the normal mapping, NQS will use only this new heterogeneous mapping when a user sends in a query joining this VTable to a VTable in a different database. One reason to set this mapping's cost higher is because a heterogeneous mapping may have slower performance than a regular mapping.

If you assign a lower cost than the normal mapping, NQS will always use this mapping over the normal mapping. One reason you may want to assign a lower cost to the heterogeneous mapping is if you use third-party reporting tools and you don't want to install numerous ODBC drivers on a client computer. If you set all of the heterogeneous mappings' costs to be lower than the normal mappings, a client computer will need only the NQS ODBC Driver installed on it.

For more information about mapping costs and how NQS routes queries to mappings, see Which Mapping Will NQS Use?

Click Next when you are finished.

On the Pick Server page, select the HMS you want to use. The default server, HMServer, can be found in the “N$System” server group. Cross-database VTables can only be joined when they point to the same HMS.

If an appropriate server doesn't exist, you can create one by clicking the Add new server button above the Servers list. Click Next to continue.

On the Pick connections page, select the connections you want to use. You may select more than one connection. If an appropriate connection does not exist, you can create one by clicking the Add new connection button above the Servers list.

If you add multiple connections, the first connection you select displays with a check mark over the icon. This indicates that it is the Reference connection, the one that Query Builder and the Expression Editor tools will use to access and validate against the underlying RDBMS when helping you build your Record Source (SQL) in the next page. To change which connection is used, right-click the required connection in the list, and choose Set reference connection.

Click Next to continue.

The last page shows you an overview of the mapping you are about to create. Click Finish to create the mapping and exit the wizard.

Granting Permissions to Heterogeneous Mappings

A user sending a query to NQS can only use a heterogeneous mapping if the user has permission to the heterogeneous mapping, as well as the connection it uses. While you can use the same procedure for granting permission to a mapping, you can also use the following procedure specifically for granting permission to a heterogeneous mapping.

To grant permissions to a heterogeneous mapping

In the left-hand pane of NQS Administrator, navigate to the VTable.

Click the VTable so its property pages are displayed. The top right-hand pane displays the mappings associated with the VTable.

Click the mapping to view its properties in the property sheets below.

Click the Heterogeneous tab.

Select the required heterogeneous mapping, then click the Edit button.

Click the Permissions tab. The tab displays the users and roles trees—users, organizations, and roles can all be granted permissions to this heterogeneous mapping.

Grant permissions to the required users, organizations, and roles. For more information about granting permissions, see Granting Permission to a Role in “Managing Security.”

Click Save.

Examples of Custom Mappings

The examples in this section illustrate problems that can be creatively solved using custom mappings.

Speeding Up Lookups (LoVs)

This example shows how to create a custom mapping to minimize the performance hit of users doing lookups when running queries.

Problem

The Sales department has come to you with a problem with the “Orders” VTable, which returns order information along with the salesperson's name. Each salesperson regularly runs a query against this VTable and filters the query using the “Salesperson” column, to retrieve only those orders where he or she is listed as the salesperson. When the salespeople run this query in NWQ, they can click the List of Values button, so that instead of having to type in their name, they can select it from a list of all salespeople.

Retrieving this list of values (LoV) usually takes a long time. The Sales department asks if you can help.

You look at the “Orders” VTable in NQS and see that it has only one mapping, the default 1:1 mapping. When a salesperson wants to filter by the “Salesperson” column, and then chooses to get an LoV, a SQL statement is sent to the back-end database asking for the distinct values found in the column. The SQL looks like this:

SELECT DISTINCT salesperson

FROM Orders

ORDER BY salesperson

Assuming the “Orders” table was imported by the Simple Virtual Table Wizard, NQS sends this SQL along to the back-end database and it chugs through all of the records, finding the distinct salespeople. This can be time consuming if the table involved has many rows.

Solution

The answer to this performance problem lies in creating a specialized mapping for this lookup column. The mapping will point to a different table in the database, one that is devoted to the column being filtered on.

Luckily, since you are working with a normalized database, you already have a table devoted to salespeople – called “Salespeople” – where each salesperson is represented in a single row, in the “Salesperson” column.

In NQS Administrator, you create a new mapping for the “Orders” VTable using the Custom mapping Wizard. You name the mapping “LOV_Salespeople.” You select the appropriate server and a connection that has access to the “Salespeople” table. You give this mapping a lower cost than the default 1:1 mapping and set the mapping's Record Source to:

SELECT salesperson

FROM Salespeople

ORDER BY salesperson

You don’t need a DISTINCT in the mapping’s SQL, since the data is already set up this way. However, you still only want the mapping to be used if a user is retrieving an LoV or otherwise looking for distinct values only – you wouldn’t want the mapping to be used if a user wrote a query against all rows in this column. To enforce this in the mapping, you must create a virtual dimension (VDimension). On the Advanced Needs Analysis page, you select Yes to the second question to create a VDimension. After this, on the VDimensions page, you select the check box labeled No GROUP BY can be used, this is a fully aggregated recordset, which will enforce the DISTINCT rule.

You verify the “Salesperson” column in the mapping is associated to the “salesperson” column in the VTable, then click Finish to create the mapping.

Result

Now, when a salesperson clicks the List of Values button and the initial SQL statement above is sent from the query tool, NQS chooses the new “LOV_Salespeople” mapping (because of the lower cost and the fact that the mapping returns the column asked for in the query) and the query is routed to the “Salespeople” table in the database. Performance greatly improves. But, if the user sends a query that includes more than the “salesperson” column in the SELECT clause, then NQS has no choice but to use the default mapping.

NQS can only use a mapping whose Record Source returns at least the columns asked for in the SELECT clause of the query.

Creating Column-Level Security

This example shows one way to create column-level security for a VTable, using mappings.

Problem

Your company uses Magnitude NoetixViews (NoetixViews) in conjunction with NQS. When Oracle E-Business Suite users run queries against NQS (through NWQ), their Oracle security is checked and they can only access the data they have been granted access to through Oracle responsibilities.

You have set up other users in NQS by importing their accounts from the directory. These users do not have security to Oracle E-Business Suite. However, one of these users, Jack Smith, needs to set up a mailing list for the finance department. You need to give him access to this information in the “AR_Customer_Addresses” VTable, but need to restrict the columns he can access from the VTable.

Solution

To give this user access to the VTable, you create a new mapping that includes only the columns you want the user to access and then set permissions so the user can only access this new mapping.

The first step is to create a new mapping with fewer columns than the VTable's 1:1 mapping. This mapping points to the same physical table as the 1:1 mapping, so no changes need to be made in the physical database.

Instead of creating this mapping from scratch, you make a copy of the existing mapping and delete the unnecessary columns from the copy. To do this, in NQS Administrator, you navigate to the AR_Customer_Addresses VTable and select it, displaying its mappings in the right-hand pane. You copy the existing 1:1 mapping, and paste it into a blank row. Using the Properties tabs, you first change the name of the new mapping to “Mailing List Columns Only.” You click the Columns tab and delete all the columns except the columns needed for the mailing list (Customer, Address1, Address2, City, State, and Postal_Code), then click Save.

Jack Smith needs access to a valid server and connection the mapping can use as well. In this case, he doesn’t yet have access to any of the existing servers or connections. You create a new default type connection on the server that uses a valid Oracle user name and password instead of Jack Smith’s user name and password. Then, you return to the Mailing List Columns Only mapping and add this new connection.

Next, you need to assign Jack Smith the proper permissions to access this new mapping. You create a new role called “Mailing List Access” that grants access to the new “Mailing List Columns Only” mapping, the new server, and the new connection, and revokes access to the 1:1 mapping. You navigate to Jack Smith under users and make him a member of this new role. (In the future, if another employee needs this same access, you can quickly set up the permissions by making that employee a member of the role as well.) You also check the Permissions tab for his user account and ensure he does not have access to the 1:1 mapping.

Finally, you log on to NWQ and create a quick query from the AR_Customer_Addresses view, including the mailing list columns above, and give a copy of the query to Jack.

Result

Jack Smith can now log on to NWQ and run a query that returns all the mailing list information he needs. When the query is run, NQS sees that Jack only has permissions to the “Mailing List Columns Only” mapping and always routes the query to that mapping. Because he does not have access to a mapping that would return other columns from the underlying VTable, he cannot add any of the other columns in the VTable to his query or the query will fail. He now has access to the columns he needs but not to any others.

Routing a Query Based on an Expression

This example shows how a mapping can be created and used when a specific expression is present in a query.

Problem

The sales manager wants to know how much money the company could be making if they increased prices by 150%. She has access to a VTable, Order Lines, that can return the current order details and prices. This table was imported with the Simple Virtual Table Wizard, so it uses a 1:1 mapping to the physical table, order_lines. The sales manager is planning to run a number of queries against this VTable, each time performing a calculation to multiply the selling_price column value by 1.5.

You know from performance statistics that this VTable is very heavily used within the sales department, and you worry that performance for both the sales manager and the rest of the department will be poor when all these calculations are being done.

Solution

You create a new table in the physical database that pre-calculates the 150% increase, then point queries that include this calculation to this new table to improve performance.

First you create another table in the physical database that is batch loaded with the data from order_lines, and each selling_price column value is multiplied by 1.5. This new table is called order_lines_price_increase.

You use the Custom Mapping Wizard to add a new mapping to the order_lines VTable. The Record Source is SELECT * FROM order_lines_price_increase. The information on the columns page is changed to look like the following:

Notice that the right side of the page has an expression, (“selling_price”*1.5). This mapping is also given a lower cost than the normal 1:1 mapping.

Result

Now, if the sales manager sends a query, such as SELECT selling_price * 1.5 as increase FROM order_lines, she is directed to this new database table using the new mapping. This mapping is chosen because the expression sent in the SQL statement matches the VTable Expression in the mapping.

The advantage of this is that the data has been pre-calculated and the performance of the query should be better than before, when the calculation was done as the query was run.

This method can also be used to route queries with commonly used expressions such as FirstName + ' ' + LastName, or queries with very complex calculations that could be pre-calculated for performance improvements. This feature is particularly powerful when combined with an aggregation. For an example, see Routing a Query Based on an Aggregation.

Optimizing Data Access by Location

This example shows how queries can be routed to different mappings based on a portion of the WHERE clause.

Problem

Your company has all its data in a server in New York. The company also has a regional office in California. You notice that the users in California are complaining of poor performance when they do queries against customer data, especially the Customers VTable.

After some quick analysis, you realize that most of the queries done by California users deal with California customers, and their slow response is due to the time it takes to send large amounts of data across the country.

Solution

You decide that a local source for this data would solve the California users’ performance problems.

You create a separate reporting server and place it in the regional office. The reporting server is set up to take a periodic snapshot of the California customer data.

In NQS, you create a new mapping for the Customers VTable using the Custom Mapping Wizard. This mapping is identical to the 1:1 mapping, but points to the new reporting server and has a lower cost than the 1:1 mapping. You only want this mapping to be used when a user's query is fetching only California data, so in the wizard, you also create a virtual filter (VFilter). A VFilter causes a mapping to be chosen based on a portion of the WHERE clause found in the incoming SQL.

Choose No to the question in the wizard, Does the Physical Recordset contain the complete set of rows that the VTable will ever return. You are then prompted to create a VFilter.

You enter the condition that must be present in the WHERE clause in order for a query to be routed to this mapping:STATE=CA,’ then save the new mapping.

Result

Now, if a user sends a SQL statement like SELECT * FROM Customers WHERE STATE = CA,’NQS uses this new mapping and routes the query to the reporting server in California. NQS chooses this new mapping because it is able to give the user the information they are looking for and because it was given a lower cost than the default 1:1 mapping.

See VTable with Custom and Default 1:1 mappings for a diagram of the mappings that now exist for this VTable.

NQS immediately starts routing these queries to this local server, without any query modification needed from users.

If you needed to solve the same problem without creating a VFilter based on the WHERE clause, you could instead use permissions, granting only the California users access to the new mapping against the California data and giving the mapping a lower cost.

Routing a Query Based on an Aggregation

This example shows how to use a mapping to overcome the performance hit of aggregations, by pointing queries with aggregations to pre-aggregated data.

Problem

The DBA, using the NQS Monitor, has discovered that many users are interested in knowing the total sales that happen by state. These users are writing queries against the order_lines VTable, which was imported using the Simple Virtual Table Wizard from a physical database table also named order_lines. In the queries, users are summarizing the order details (using a SUM on the Line_Total column) in accordance with Ship_To_State. All these users doing aggregations are hurting the performance of the database.

Solution

You decide to use a mapping with a virtual dimension (VDimension) to point these queries to pre-aggregated data. A VDimension is used to route a query based on an aggregation that you have set up.

On the back end, you create a table that sums all of the sales by state. This table is called order_lines_by_state and has only the columns ship_to_state and total.

Using the Custom Mapping Wizard, you create a new mapping with a lower cost than the 1:1 mapping. This new mapping’s Record Source is: SELECT ship_to_state, total FROM order_lines_by_state. The columns page looks like:

Note the second line and the association between the total column in the Record Source and the VTable Expression. If the table had been pre-aggregated using an operator other than SUM, there would be a different VTable Expression.

On the Advanced Needs Analysis page, you select Yes to the second question to create a VDimension. After this, you see a page similar to below, where you select the grouped columns. You select the columns that must display in the group by clause of any incoming SQL:

Result

Now, if a user sends a query such as SELECT ship_to_state, SUM(line_total) FROM order_lines GROUP BY ship_to_state, NQS routes that query to this new mapping and the aggregated table. Note that in this case, because this mapping only includes two VTable columns, any SQL statement that has more columns in it is routed to the 1:1 mapping.

Was this article helpful?

We're sorry to hear that.