Pivot views

Anaplan XL pivot views enable you to report on simple data. You can get them from a database that has many dimensions, like SQL Server. Pivot views enable you to create grids and Dynamic charts from non-online analytical processing (OLAP) data sources. You define the data source and query using the normal Anaplan XL designer, and can then use the standard Anaplan XL functionality against the view.

Pivot views are a simple way to work with relational data, however they aren't 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'll initially run a query against the relational source, so performance is dependent on the server that backs the queries.

If you add a grid and select a relational connection as the data source, for example, SQL, you are prompted to create the query that'll be used for the pivot view.

Anaplan XL automatically creates hierarchies and measures based on the query results. It treats text fields as hierarchies and numeric fields as measures.

When you create a grid or dynamic chart using an existing connection to a relational database, you find a second connection. This connection is the pivot view. Otherwise, you can right-click an existing connection and select Add Pivot View.

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 relationship. One would be for Sales and the other would be for HR.

While there's a default row limit of 100,000, you can connect to much larger data sources of any size using two techniques.

If you add parameters to the SQL query, they'll 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, like 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.

In this mode, data aggregation is pushed 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. This is much smaller than the number of rows in the Sales table. Performance depends on the server, but can take advantage of all server hardware and indexes.

In the Connections screen, select the Pivot View connection and select Edit.

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.

You can create calculations for Year, Quarter, and Month in Date fields. This helps you to summarize the data.

The date data type in SQL Server isn't supported right now. Use datetime if you want to use the fields for calculations.

By default, Anaplan XL creates:

• 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.

In Manage Connections, right-click the Pivot View connection and select properties. You can change the database and cube captions.

Pivot views have three data store modes, suitable for different scenarios. The storage mode can be changed by editing the current Pivot View connection, in the Properties section.

• Extract (fast build): The query is run once and the results are 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, for example when a slicer controls it.
• Extract (fast query): - The query is run once and the results are stored. The extract takes more time, but queries are faster after this. It can handle bigger data sets and allows for multi-level hierarchies and session calculations.
• Query Server: A query is run for every grid, which will contain a suitable group by clause based on what is selected. Good for larger datasets as less data is transferred, and the work of aggregating the results is pushed down to the database server.
• As Anaplan XL adds where and group by clauses to the original query, stored procedures can't be used in this mode.

• Anaplan XL will rewrite some queries to reduce the load on the server. For example, if the view contains a Sales table linked to Products, Anaplan XL may remove the join to Sales when requesting a list of all products available.
• Can increase performance of the designer screens, but products won't be filtered only to those with Sales.
• This mode can't be used with stored procedures, as Anaplan XL will try to create sub-selects on the cube query when accessing the database.

In this mode, there's more work when extracting the data. This makes it less useful for interactive situations where data extraction happens often, for example, when a slicer is selected. However, it has many advantages such as

• Larger data volume handling
  There's no strict row limit, but the time it takes to transfer the data from the server to the client sets a practical limit. Millions of rows take many minutes to send.
  The extract can be cached on the web for a set period of time which you can configure. This shouldn't be done if the query is parameterized as the cache will be emptied 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 extraction time. If you are using a stored procedure, 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 to build the pivot view.

If you are running in 64-bit mode, for example, on a web server,  install these packages from the Prerequisites page.

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

The query used to generate the pivot view can now be driven from an Excel range to allow for 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.

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 select the Refresh link to update your grids and charts.

When Data store is set to Query Serve:

• Distinct counts for the All member.
  • You can get the equivalent number using a query that doesn't include the "All" hierarchy, but can't have All and specific members combined in a grid.
• Advanced Member selections aren't available, but axis operations are still supported.
• "All" member aggregates use Visual totals. They are the totals 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 problems if some columns used in the Pivot View definition could disappear.