Pivot View/Version 9.0

Pivot views allow you to create Grids and Small multiples from relational data. You define the data source and query using normal XLCubed designer, and can then use the standard XLCubed functionality against the view.

They will run a query against the relational source, so performance is dependent on the server that backs the queries.

Creating

If you add a grid and select a relational connection as the data source (SQL for example), you will be prompted to create the query that will be used for the Pivot View.

XLCubed will automatically generate hierarchies and measures based on the query results.

Configuring

In the connections screen, select the Pivot View connection and press Edit to bring up the configuration screen.

Hierarchies

By default a Dimension and Hierarchy is created for each field in the query.

You can rename hierarchies, group related hierarchies under a common dimension, create display folders or remove unwanted hierarchies to simplify the view.

Measures

By default XLCubed will create

• Sum, Average, Minimum and Maximum measures for each numeric column
• Distinct and Non-null Counts for every field
• Row Count

You can rename and remove measures here, as well as setting up default format strings.

Properties

The Database and Cube captions can be changed if required, and the Query edited, e.g. to add extra fields required for analysis.

Advanced

• Data store
  • Extract - The query is run once and the results stored. This gives good performance after the initial download, but will use more memory. Good for small datasets (< 100,000 rows).
  • Query Server - A query is run for every grid, which will contain an appropriate "group by" clause based on what is selected. Good for larger datasets as less data is transfered, and the work of aggregating the results is pushed down to the database server.

• Rewrite Query - in "Query Server" mode only.
  • XLCubed will rewrite some queries to reduce the load on the server. For example if the view contains a Sales table linked to Products, XLCubed may remove the join to Sales when requesting a list of all products available.
  • Can increase performance of the designer screens, but Products will not be filtered only to those with Sales.

Limitations

When Data store is set to "Query Server" the following are not supported.

• Distinct counts for the "All" member
  • You can get the equivalent number using a query that does not include the "All" hierarchy, but cannot have "All" and specific members combined in a grid.
• Advanced Member selections are not available, but Axis operations are still supported
• "All" member aggregates use "Visual totals", they are the total for the members retrieved, not all members in the database.