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.

Connections

After adding a Pivot View to a connection, you will find a second connection has been created for you.

Here connection 1 is the original relation connection, in this case to SQL Server.

Connection 2 is the Pivot View definition, which contains the query, fields and measures that are defined.

You can reuse this connection like any other if you need to create several Grids based on it.

If you want to create a Grid on a different aspect of the data you can select the original relational connection when creating a Grid and define a new query.

For example you could connect to your database and have two Pivot Views based on the same relational connection, one for Sales and another for HR.

Data Size

The pivot view provides an easy way to work with relational data, it is not an OLAP engine or database. As such, it can only handle a limited amount of data being retrieved from the data source. By default this is 100,000 rows.

Larger Data Sources

It is possible connect to data sources with far more rows using two techniques

Parameterised queries

If you add parameters to the SQL query they will be applied before the data is retrieved.

For example in a Sales report for a Product you could filter the pivot view by ProductID which would limit the number of rows retrieved. When the parameter is changed (e.g. with a Product slicer) the pivot view will be updated and any attached grids will be refreshed.

You could have a very large Sales table, but as only part of it is retrieved this would greatly reduce the row count.

Query Server

In this mode the aggregation of data is pushed on to the server, and the query is rewritten to include a Group By clause.

For example in a report of Sales with Product on Rows the number of rows retrieved will be the number of products which will be far smaller than the number of rows in the Sales table.

Performance will depend on the server, but can take advantage of all server hardware and indexes.

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.

Calculations

For Date fields you can easily create calculations for Year, Quarter, Month etc to allow summarisation of the data.

Currently the date data type in SQL Server is not supported, you should cast these to datetime if you wish to use the fields for calculations.

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 (fast build) - 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). Has low overhead so is useful when the data extracted changes often, e.g. when it is controlled by a slicer.
  • Extract (fast query) - The query is run once and the results stored. There is more overhead when the extract is performed, but queries are fast after this, and it can handle larger data sets. See #New in Version 9.1 for more details.
  • 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.
• The query should keep the columns fixed, for example a stored procedure running dynamic sql would cause issues if some columns used in the Pivot View definition can disappear