Pivot View

This article refers to the latest XLCubed version. For other versions, see Pivot View/Versions.

XLCubed Pivot views are designed to provide a flexible way of reporting on flat data, typically retrieved from a multidimensional database such as SQL Server. Pivot views allow you to create Grids and Small multiples from non-OLAP data sources. You define the data source and query using normal XLCubed designer, and can then use the standard XLCubed functionality against the view.

Data Size

Pivot views provide an easy way with no IT overhead to work with relational data, however they are not an OLAP engine or database. As such, they can only handle a limited amount of data being retrieved from the data source. By default this is 100,000 rows, but this setting can be varied. They will initially run a query against the relational source, so performance is dependent on the server that backs the queries.

See the Larger Data Sources and Data Store Modes sections for how to use Pivot Views with much larger volumes of data.

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, initially treating text fields as hierarchies and numeric fields as measures.

Connections

When a Grid or Small Multiple is created using an existing connection to a relational database, you will find a second connection (the Pivot View) has been created for you. Otherwise you can right click on an existing connection and choose 'Add Pivot View'.

Here connection 1 is the original relational 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.

Larger Data Sources

While there is a default row limit of 100,000 , it is possible connect to much larger data sources of any size 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 Pivot Views

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 drag a field to Hierarchies to create a new hierarchy.

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

From version 9.1, Multi Level Hierarchies can be created.

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

In Manage Connections, right clicking the Pivot View connection and choosing properties allows for changing of the Database and Cube captions can be changed if required.

Advanced - Data Store Modes

Pivot Views have 3 data store modes, suitable for different scenarios. The storage mode can be changed by editing the current Pivot View connection, in the Properties section. Each mode is described below

• 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, and also enabled multi-level hierarchies and session calculations. 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.
    • As XLCubed adds "where" and "group by" clauses to the original query stored procedures can not be used in this mode.

• 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.
  • This mode can not be used with stored procedures as XLCubed will try to create sub-selects on the cube query when accessing the database.

New in Version 9.1

Extract (fast query) storage mode

In this mode there is an extra overhead when extracting the data, which makes it less suitable for interactive scenarios where data extract happens often (e.g. when a slicer is clicked).

However it has many advantages such as

• Larger data volume handling
  • There is no strict row limit, but the time taken to transfer the data from the server to the client sets a practical limit as millions of rows take many minutes to transmit.
  • The extract can be cached on the web for a set period of time which you can configure. This should not be done if the query is parameterised as the cache will be for the incorrect parameters.
• Multi-level hierarchies can be created
• Custom Calculations are supported

This mode adds an extra option

• Use Direct Connection
  • When set, the pivot view will be built by querying the database directly where possible. For example when using SQLServer this can reduce the extract time. If you are using a stored procudure you should turn this option Off which adds an intermediate step to allow processing to succeed.
  • If you use Direct Connection on a published report, the Application Pool user must have access to the data source in order to build the pivot view.

Important: This mode is not compatible with other modes of operation. The member names will be different, so reports made in one mode will break if you switch mode. You should chose which mode you want to use before authoring your report.

Additional Prerequisites

If you are running in 64bit mode (e.g. on a web server) you will need to install

• Microsoft SQL Server 2016 Analysis Services 13.0 OLEDB
• Microsoft SQL Server 2016 Analysis Services 13.0 ADOMD.NET
• Microsoft Access Database Engine 2010

packages from the Prerequisites page.

Custom query

The query used to generate the pivot view can now be driven from an Excel range to allow more dynamic data selection. If choosing to do this, the columns returned by the query must remain the constant as the pivot view hierarchies and measures are built on the column names.

Refresh when driving cells change

If you have a Pivot View based on an Excel range you may want to edit the data without having the Pivot View rebuilt after every cell edit. In this case you can turn off this option.

To refresh the data you add an XL3RefreshConnections formula that refreshes the pivot view connection. When you have finished your edits you can click the "Refresh" link to update your grids and charts.

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