Summary


This article describes Cache Queries. A cache query is used to derive a subset of cache data, based on a query, for use in a dashboard. The query is invoked from a dashboard using the Cache Query Connector.


 Requirements


The XDC must be refreshed before Cache Queries are accessible.


 SQLite


InfoBurst uses the SQLite database engine to store and manage data caches. Cache Queries must use SQL sytax that is supported by SQLite. See the following guide for SQLite query syntax examples and usage.

 

http://www.tutorialspoint.com/sqlite/sqlite_quick_guide.htm


 Create a Cache Query
  • Select the Cache Queries tab
  • Select the green plus button
  • Select a data source

Details

  • Name: Enter a query name
  • Index on column (optional): Select an index column
  • Max Rows: Maximum number of rows to return when query is invoked (Default: 2048, Max: 9999)
  • Return Column Names: Uncheck box to exclude column names in data resulting from query

SQL
  • Define an SQL query against the existing data elements
  • Select the green arrow button to test the query
  • When the query contains a parameter, select the MEaTjOtIjrRgpYXR0Zc24qfYgAJcVy4dnA.png button to provide a parameter value prior to testing
  • Select the Add button to add the query

CrossTab

The CrossTab option enables creation of a simple crosstab using the available columns. The resulting data will be stored and provided to the dashboard in crosstab format. Select and drag a column to the appropriate section of the crosstab.

CrossTab Options
  • Select the mF8HkA4fsqMpktDvOVFDUY7szuQy4HiKmA.png button to expand the Options panel
  • Row/Column Totals: Select placement of results of row or column level calculations then select calculation type
  • Summary Formatting: Select formatting type of summary data
  • Date Formatting: Specify a date format (example: MM/DD/YY)
  • Abbreviated States: Check to abbreviate US states names
  • Suppress Zeros: Check to suppress zeros in resulting crosstab data
  • Select the Save button to commit selections

 Preview Cache Query Results
  • Cache Query: http://[SERVER]:8551/infoburst/rest/exec/xdcqry/[XDCID]?q=[QUERY]
  • Cache Query with Parameter(s): http://[SERVER]:8551/infoburst/rest/exec/xdcqry/[XDCID]?q=[QUERY]&[PARAMETER]=[VALUE]
  • Cache Query with Parameter(s) and Cache Key: http://[SERVER]:8551/infoburst/rest/exec/xdcqry/[XDCID]?q=[QUERY]&[PARAMETER]=[VALUE]&cachekey=[VALUE]