Data Configuration Settings

XLCubed's data access behaviour can be configured using xml files. If you change these you will need to restart Excel or run IISRESET on the web server.

It is not recommended to change these without advice from XLCubed support.

File Locations

These files are found in the following places:

  • Excel Edition - Install folder e.g. C:\Program Files (x86)\XLCubed Ltd\XLCubed
  • Web Edition - Install folder\bin e.g c:\Inetpub\wwwroot\XLCubedWeb\bin

Config Files

XLCubed.Data.xml

This file controls XLCubed's cache behaviour. XLCubed will cache metadata to improve performance, but will not cache data (query results).

Here you can control when the cache is cleared, and which metadata to cache.

The cache clearing can be set to happen

  • Daily - at a given time
  • At a specified interval - every X minutes.

The cache settings can be configured per cube and schema. If a specific setting is found that will override a general one. (e.g. a specific schema setting will override that for * schemas).

Caches can have the policy type and size set.

Policy types are:

  • PublicChecked - Shared between users, but on the first request the cube will be queried to check the user has permission to read the object.
  • PublicUnchecked - Shared between users, on the first ever request the cube will be queried to get the metadata which is then shared between subsequent users.
  • Private - Cache is per user, cube is queried for metadata the first time each user requests it.
  • PrivateBySession - Cache is per user session, the cube will be queried for different connections from the same user.
  • Off - Cache is disabled for this schema.

Cache policy sizes can be:

  • 0 - Do not cache
  • -1 - Cache all objects
  • N - any positive number, maximum cache size before objects are removed. N is the count of objects stored.

XLCubed.Data.AnalysisServices.xml

This file controls some aspects of XLCubed's connectivity to Analysis Services.

Connections:

  • keepopen - For use on the web, keeps connections open between page loads. Occasionally useful for AS2000 servers.
  • keepsession - For use on the web, keeps sessions open between page loads.
  • usenativecom - Controls the internal implementation of some data reading.
  • useconnectionpooling - Shares connections between users, useful for .cub files where a maximum of 5 connections can be made at once.

Metadata:

  • hierarchies : useindexnameascaption - For backwards compatibility, returns the unique name with brackets removed as the hierarchy caption.
  • hierarchies : usefirstmemberasdefault - Used for some situations when the default member may not be visible to the users.