416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Caching Data

Hide navigation
RSS
Modified on Fri, 19 Jul 2013 04:25 PM Categorized as Data Connectors, Data Preparation, Virtual Tables
Click to return to: Documentation | Connecting to Data


Overview

From the Caching tab in the Data Connector Designer, you can configure the following methods of caching to help improve performance when viewing dashboards:


Caching tab of the Data Connector Designer.

Caching tab of the Data Connector Designer.


Memory caching

Memory caching lets you store data results in memory on the Dundas Dashboard server and/or client computers after initial data retrieval. Cached results remain in memory and are returned in place of querying the actual data source for live results until a configurable duration has expired. This can help to improve performance when viewing or previewing dashboards, and to reduce the load on your database server.

To configure memory caching on the server:

  1. Enter the Server data cache duration in minutes. You can enter a decimal value, such as 0.5 (&ie; 30 seconds), to represent a duration under 1 minute.
  2. Select the Preview option if you want the cache to be used when previewing dashboards during the dashboard design stage.
  3. Select the View option if you want the cache to be used when viewing dashboards.

Setting up memory caching on the server.

Setting up memory caching on the server.


To configure memory caching on the client:

  1. Enter the Client data cache duration in minutes.
  2. Select the Preview option if you want the cache to be used when previewing dashboards during the dashboard design stage.
  3. Select the View option if you want the cache to be used when viewing dashboards.

Tip: Client data caching also applies to analytical/OLAP data. Use this option to help improve performance when viewing analytical dashboards.

The Dundas Dashboard configuration file also contains several key settings that can be used to control in-memory caching:



It is useful to point out that client caching can be cleared off during testing by accessing the Refresh command in the Project Explorer.

Clearing client caching

Clearing client caching


Sync database caching

From the Caching tab in the Data Connector Designer, you can enable sync database caching for the virtual tables that are based upon your data connector. By caching the data resulting from a virtual table into the &dw; sync database, you can improve application performance as the virtual table query is only executed at pre-determined times.

By scheduling a refresh of your sync database cache, you can also address situations where your data source does not allow permanent connections, whether for security or other business reasons. For example, a database administrator may permit a line-of-business database to be queried by other applications only during the night.

A virtual table cannot be cached if it defines (or is based on another virtual table that defines) any of the following:

  • A public filter.
  • A public stored procedure or function parameter.
  • A user-based parameter.

Note that sync database caching does not apply to the following data providers (meaning you won't see this option in the Caching tab):

  • Microsoft SQL Server Analysis Services 2005 or above
  • SAP NetWeaver BW 7.0.
  • Other data sources (⪚ CSV, Excel, SharePoint Lists and Excel Services) - Data from these sources is already cached in the sync database based on an import schedule.

Sync database caching will also be unavailable if you are using dynamic connection strings.

Scheduling sync database caching

To schedule sync database caching (&ie; the initial building or subsequent refreshing of the cache):

  1. Go to the Caching tab of the Data Connector Designer. The following information is displayed:
    • Status of the cache (&ie; whether a cache currently exists or not).
    • List of virtual tables that rely on this data connector and are supported for caching (⪚ virtual tables with a public filter/parameter defined are excluded from this list).
    • Summary and execution information about the last scheduled caching operation that ran (if any).
      The Sync Database Caching section.
  2. Select the Caching scheduled option. The Define Caching Schedule dialog is displayed. Use this dialog to define a schedule for performing the caching operation. For example, you can configure caching to be performed daily at 2 a.m. See Setting up a Schedule for further details.
    The Define Caching Schedule dialog.
  3. Caching is now enabled and scheduled for the data connector, as shown in the bottom grid of the Caching tab.
    Caching is now scheduled.

Changing the caching schedule

To change the schedule for the sync database caching operation:

  1. At the bottom of the Caching tab, click the Edit button. The Define Caching Schedule dialog is displayed.
  2. Use the dialog to change the schedule as shown in the article, Setting up a Schedule.
  3. The summary information in the Caching tab will be updated to reflect your changes.

Running the caching operation manually

You can manually perform the sync database caching operation once as follows:

  1. Save your data connector.
  2. In the Caching tab of the Data Connector Designer, click the Run Now button. An information dialog lets you know that the caching process will run in the background and may take a long time to complete. Click OK to close this dialog.
    Caching process will run in background.
  3. A notification message is displayed at the bottom of the Caching tab in the Data Connector Designer when the caching process has completed.
    Notification of caching complete.
  4. The Caching scheduled grid shows the last date/time that the caching process ran and the corresponding last result. If the last run resulted in an error, you can click the copy to clipboard link to copy the error text to your clipboard.

Note: The timeout for the caching process is set to a period of 1 hour, or the value specified by the clientCommunicationTimeout entry in the &dw; Configuration File, whichever is larger.

Excluding virtual tables from caching

Once you've set up the virtual tables of a data connector for sync database caching, you can individually exclude one or more of the virtual tables from being cached. You may want to do this for reasons listed below:

  • If you have two virtual tables that are cached and one of them is large while the other is small, you may want to un-cache the smaller table because caching may not provide much benefit in this case. Additionally, caching a lot of small virtual tables puts an unnecessary load on the server.
  • When troubleshooting your dashboard, you can disable virtual tables from being cached in order to identify a specific cached table which might be causing a problem with your dashboard.

Excluding virtual tables from caching.

Excluding virtual tables from caching.


Additional notes

Some additional notes concerning check in and check out of virtual tables that have results cached in the sync database:

  • If a cache exists for your data connector and you subsequently modify the structure of one of the data connector's virtual tables, a re-caching operation will be started automatically when you check in the virtual table. Until this re-caching operation completes (which may take several seconds or longer), data for the virtual table will be retrieved directly from the associated data source.
    Re-caching when a virtual table is checked in.

    Re-caching when a virtual table is checked in.

  • If a cache exists for your data connector and you change its last (or only) virtual table so that it becomes non-cacheable (⪚ by enabling a public filter/parameter), caching will be disabled for the entire data connector.
  • If you check out a virtual table for editing, data retrieval will operate directly on the associated data source instead of returning cached data. This lets you work with the actual data during virtual table design.

Related topics


Click to return to: Documentation | Connecting to Data

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.