Query Design Considerations

When you create a query to use with a pivot grid, keep in mind that:

• Any prompt values for the query will be used for the Pivot Grid filter.

• A number of PSQueries may be run to render data in the grid and the chart, so you should consider these two points when constructing a PSQuery for a Pivot Grid model:

  • PSQuery should be conducive to manipulation.

  • PSQuery performance should be efficient enough to render quickly in the grid and in the chart.

For example, you want to plot organization data attributes, such as Employee Information, Department Information, Location Information, Country Information, and so on, in a pivot grid. The base database table for this information contains the codes EMPLID, DEPT_ID, LOCATION_ID, and so on, but the descriptions for all these attributes are in different tables. When a PSQuery is created, all these tables are joined. Additionally, the related language tables, security records, effective date, and so on are relevant. As a result, the PSQuery is complex and its performance may not be efficient. This kind of PSQuery is not suitable for creating a Pivot Grid model. In this case, when table indexes are not defined properly, you have two options:

• Define a fact table that contains all the data and use it to create a Pivot Grid model.

• Define a SQL view that joins all these tables and provides a simple view of the data.

  You can then use this view as a source for the PSQuery. However, creating a SQL View on top of multiple tables using joins might degrade performance.

For both options, you should consider effective date criteria, related language tables, and security records.

Note:

If the query used in the Pivot Grid model is changed after the Pivot Grid model is created, you need to modify and save the Pivot Grid model to ensure all changes are properly displayed in the Pivot Grid Viewer and Pivot Grid pagelets.