The Voyager crosstab implements a data loading strategy that balances the need for quick scrolling against query execution time and memory usage when fetching large amounts of data from the OLAP server. Voyager uses the concept of fetching a data window of a certain size within which scrolling is fast. The size of this data window can be configured therefore it is important to understand how Voyager implements scrolling so as to choose the correct size data window for Voyager to operate effectively in a particular scenario.
This article relates to the following SAP notes: 1339949, 1207386, 1183329 and 1204698.
Understanding the Data Window
The Voyager web application component fetches data in windows from the MDAS (Multi-Dimensional Analysis Services) server and scrolling requests within these data windows are serviced quickly with the browser only having to request the data from the web application server rather than having to hit the OLAP database.
Scrolling outside a data window will trigger the web application server to request a new data window from the MDAS server. Depending on the data provider and the nature of the query, this may in turn trigger a data request to the OLAP database.
By default the windows of data requested by the web application server are set to a size of 40 columns by 60 rows. This is usually multiple ‘screens’ worth of data where a screen is the data that is visible in the crosstab. How many screens worth of data this is depends on how many rows and columns can be displayed in a cross tab. If you have a maximized browser window with a single crosstab on a high resolution 20 inch monitor you can accommodate much more data in a single screen than if you have four crosstabs on a single page, a browser that is not maximized on a 17 inch low resolution monitor. A single cross tab on a maximized browser window on a screen with resolution of 1280 by 800 pixels with the meta-data explorer visible is able to display approximately eight columns and twenty seven rows. For the default 40 by 60 data window, this is equivalent to ten full screens of data. The following diagram demonstrates this:
Here the black box indicates the data window requested by the web application component. The red boxes indicate screens of data that could be represented in a crosstab in the example above. The number across the top indicate numbers of members along the column axis and the numbers down the left hand side indicate numbers of members along the row axis. The diagram demonstrates that there are ten possible screens of data for the user to scroll around before a new data window will need to be requested.
In the above example, if the user scrolls a full screen to the right six times they will now find themselves beyond the boundary of the data window and a new data window will be requested. The new data window requested will be such that the current screen that user is viewing is in the center of the new data window. The reason to place the user at the centre of a data window rather than the edge of the window is so that if the user wants to scroll back in the direction they just came from or in another direction, the first few scrolls will again be quick. This makes it easy for the user to quickly explore a localized region in the data. If the user wants to jump to an entirely different region of the data it is best to drag the scroll bar to desired region rather than navigating there one page at a time. Dragging the scroll bar will only incur one data window fetch where as scrolling a screen at a time may incur multiple fetches as the crosstab has to display each intermediate area of data.
To better understand how the data windows are fetched it is good to look at a diagram.
Here the user has defined a reasonably large query. This is represented by the green box. The user starts, in step one, in the top left hand corner of the screen. The data they can see on the screen is represented by the orange block. The red box indicates the current data window. As the user starts at the top left hand corner of the query, rather than being in the middle of the data window, the user starts at the top left hand corner as well.
In step two, the user scrolls down one screen. As this is still within the data window, no data fetch is made. A further scroll in step three is still within the data window. The next scroll, in step four, needs to request a new data window. Notice that the viewable window is in the middle of the data window so the user can quickly scroll backwards.
When the user scrolls to the very bottom of the query in step eight, the viewable window is of course not in the middle of the new data window as there is no data beyond where the user currently is.
Example workflow using Microsoft Analysis Services
For Microsoft Analysis Services you can track the effects of scrolling by examining the MDX generated.
The Voyager data driver for Microsoft Analysis Services (MSAS) implements a query generation strategy known as query chunking. Query chunking means that even if the user has defined a very large query, the Voyager data driver will only issue queries for small pieces of this large query at a time. Because the MSAS uses this query chunking strategy, you can see the effects of scrolling in the MDX queries that have been issued.
Here is an example workflow using the sample Adventure Works cube provided for Microsoft Analysis Services.
First build up crosstab with a reasonably large numbers of members selected on each axis so that there will be enough data to scroll around to demonstrate the various behaviors. This example has selected the members from the City level of the Customer Geography hierarchy (587 members) and the Product level from the Product hierarchy (397 members).
This defines a query larger than the 40 by 60 default data window size so we should be able to scroll and notice the MDX corresponding to a new data window.
If we look at the MDX for this current view you will notice you can see the 40 columns by 60 rows being requested.
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { DISTINCT( { [BC1AF020-CAD6-4CCF-8F,ED,2A,EE,FB,67,6C,92] } ) }
SET __CRYSTAL_SET_ON_AXIS_1__ AS { DISTINCT( { [E74E899B-57E0-4BB0-A8,82,14,3D,C,61,4D,DC] } ) }
SELECT
SUBSET( __CRYSTAL_SET_ON_AXIS_0__ , 0, 41 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
SUBSET( __CRYSTAL_SET_ON_AXIS_1__ , 0, 61 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
WHERE
[Measures].[Internet Sales Amount]
Notice the use of the SUBSET MDX function asking for the top left hand chunk. The two numeric arguments specify the starting ordinal of the tuple, zero, and the number of tuples to return, forty one or sixty one.
Doing a number of successive scrolls to the right brings returns reasonably quickly. At a certain point a scroll will take a little longer as the next data window was requested. Here is the MDX executed to retrieve the next data window.
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { DISTINCT( { [BC1AF020-CAD6-4CCF-8F,ED,2A,EE,FB,67,6C,92] } ) }
SET __CRYSTAL_SET_ON_AXIS_1__ AS { DISTINCT( { [E74E899B-57E0-4BB0-A8,82,14,3D,C,61,4D,DC] } ) }
SELECT
SUBSET( __CRYSTAL_SET_ON_AXIS_0__ , 20, 41 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
SUBSET( __CRYSTAL_SET_ON_AXIS_1__ , 0, 61 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
WHERE
[Measures].[Internet Sales Amount]
Notice how the numeric arguments to the on columns now requests data starting from the 20th tuple. As the scrolling was just to the right and not up or down the MDX for the rows set is exactly the same. Starting the new data window on the 20th tuple places the user in the centre of the current data window so they can scroll either left or right without causing another data window to be requested.
Scrolling down a number of times from this position will again prompt and MDX query to be issued:
WITH
SET __CRYSTAL_SET_ON_AXIS_0__ AS { DISTINCT( { [BC1AF020-CAD6-4CCF-8F,ED,2A,EE,FB,67,6C,92] } ) }
SET __CRYSTAL_SET_ON_AXIS_1__ AS { DISTINCT( { [E74E899B-57E0-4BB0-A8,82,14,3D,C,61,4D,DC] } ) }
SELECT
SUBSET( __CRYSTAL_SET_ON_AXIS_0__ , 20, 41 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
SUBSET( __CRYSTAL_SET_ON_AXIS_1__ , 32, 61 ) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
WHERE
[Measures].[Internet Sales Amount]
Notice this time that the MDX for the columns axis is the same as the previous query, but the MDX for the row axis is now requesting data starting at the thirty second tuple.
Behavior for other data sources and workflows
The example above outlined the query behavior for Microsoft Analysis Services where the Voyager query chunking mechanism kicks in. The Voyager SAP BW and Oracle Essbase drivers also have a similar mechanism. So if you were conduct a similar experiment for these drivers you should notice the SAP driver issuing MDX when the next data window is requested and the Essbase driver issuing new report scripts.
The initial report in the above example was fairly simple. It did not contain any sorts, filters, calculations or NULL filtering. Adding such things may mean that it is not possible for the query chunking mechanism to function and the entire query will be retrieved from the data source when the crosstab is initially displayed.
For example, from fix pack 2.1 on XI 3.1 you can optionally turn off query chunking in the Analysis Services driver for queries that contain NULL suppression.
Configuring the size of the data window
From service pack two for XI 3.1 and service pack five for XI R2 the size of the window can be configured. This is done by editing an XML configuration file. The file is called sdk-faces-config.xml and is located for XI R2 here:
[install location]\Program Files\Business Objects\Tomcat\webapps\businessobjects\enterprise115\desktoplaunch\WEB-INF\sdk-faces-config.xml
and for XI 3.1 here:
[install location]\Program Files\Business Objects\Tomcat55\webapps\VoyagerClient\WEB-INF\sdk-faces-config.xml
In both cases look for the following section in the file:
ScrollingConfiguration
com.businessobjects.multidimensional.sdk.jsf.common.scrolling.ScrollingConfiguration
session
Configure the number of rows the crosstab will request before making another request to the server.
crosstabRowFetchSize
java.lang.Integer
60
Configure the number of columns the crosstab will request before making another request to the server.
crosstabColumnFetchSize
java.lang.Integer
40
Look for the default value of 60 near the crosstabRowFetchSize property and the default value of 40 near the crosstabColumnFetchSize property.
To edit these properties, stop the web application server, make the changes and save the file, then restart the web application server.
Determining the correct data window size
In order to determine the correct size for a particular Voyager deployment the following factors needs to be examined:
• Responsiveness of queries to the OLAP server
• Expected memory consumption in the web application server and MDAS server
• Usual and extreme sizes of queries that the users are going to using
• Expected scrolling usage patterns of the users
Small data window sizes work best if you have memory restrictions on your web application server and incremental queries to return additional data windows return relatively quickly.
Larger data sizes are beneficial if memory concerns are not such a big issue, the users are expected to be doing a large amount of data exploration using scrolling and the incremental queries to get additional data windows are not responsive enough. This may mean however that the query to load up a data window, in particular the first one, may be slower as it is retrieving a much larger amount of data.
As each deployment is different due to user profiles, cube design and infrastructure, it is prudent to do some testing of different window sizes to find the most appropriate size to suit a particular deployment.
3 Comments
