CDA Data Sources

Community Data Access ..

A CDA (Community Data Access) file in Pentaho CTools is a configuration file written in XML format that defines how to access and retrieve data for dashboards and reports. It acts as an abstraction layer between the dashboard and various data sources, allowing developers to define multiple database queries, cached results, and parametrized data access without exposing the underlying complexity to the frontend.

CDA files support various data sources including SQL databases, MDX queries, scripting languages like JavaScript, and even Kettle transformations. They include features like query parameterization, result caching, and data transformation capabilities, making them a crucial component for efficient data handling in Pentaho dashboards.

CDA Dashboard - Top 50 customers

CDA

Before we begin our CTools journey, let's review some CDA samples ..

1. Log into Pentaho User Console as Administrator.

http://localhost:8080/pentaho/Homelocalhost

Link to Pentaho Repository.

1. Select Browse Files.

2. Navigate to: 'Public - CTools Dashboard - CDA' folder

3. Highlight the CDA folder.

CDA samples

CDA - sampledata

We are defining a data source that points to the sample data source that is created during the Pentaho installation.

We also have four MDX queries: in the first 3 the MDX query returns unique members for territories, countries, cities - filtering out undesired values. The order of the columns is change from 0, 1 to 1, 0.

The last MDX query returns the top 50 customers based on Sales across all the geographical markets. The query passes a parameter - ${marketQueryParam} - which returns All Markets, but could be used to filter for a specific Market.

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>
    <DataSources>
        <Connection id="SampleData" type="mondrian.jndi">
            <Jndi>SampleData</Jndi>
            <Catalog>mondrian:/SteelWheels</Catalog>
            <Cube>SteelWheelsSales</Cube>
        </Connection>
    </DataSources>
    <DataAccess id="territories" connection="SampleData" type="mdx" access="public">
        <Name>territories</Name>
        <BandedMode>compact</BandedMode>
        <Query>
            WITH 
                MEMBER [Measures].[UID] AS [Markets].CURRENTMEMBER.UNIQUENAME
            SELECT 
                UNION([Markets].[All Markets], DESCENDANTS([Markets].[All Markets], [Markets].[Territory])) on ROWS,
                {[Measures].[UID]} on COLUMNS
            FROM [SteelWheelsSales]
        </Query>
        <Output indexes="1,0" mode="include"/>
    </DataAccess>   
    <DataAccess id="countries" connection="SampleData" type="mdx" access="public">
        <Name>countries</Name>
        <BandedMode>compact</BandedMode>
        <Query>
            WITH 
                MEMBER [Measures].[UID] AS [Markets].CURRENTMEMBER.UNIQUENAME
            SELECT 
                UNION([Markets].[All Markets], DESCENDANTS(${marketQueryParam}, [Markets].[Country])) on ROWS,
                {[Measures].[UID]} on COLUMNS
            FROM [SteelWheelsSales]
        </Query>
        <Parameters>
            <Parameter name="marketQueryParam" type="String" default="[Markets].[All Markets]"/>
        </Parameters>
        <Output indexes="1,0" mode="include"/>
    </DataAccess>   
    <DataAccess id="cities" connection="SampleData" type="mdx" access="public">
        <Name>cities</Name>
        <BandedMode>compact</BandedMode>
        <Query>
            WITH 
                MEMBER [Measures].[UID] AS [Markets].CURRENTMEMBER.UNIQUENAME
            SELECT 
                UNION([Markets].[All Markets], DESCENDANTS(${marketQueryParam}, [Markets].[City])) on ROWS,
                {[Measures].[UID]} on COLUMNS
            FROM [SteelWheelsSales]
        </Query>
        <Parameters>
            <Parameter name="marketQueryParam" type="String" default="[Markets].[All Markets]"/>
        </Parameters>
        <Output indexes="1,0" mode="include"/>
    </DataAccess>   
    <DataAccess id="top50Customers" connection="SampleData" type="mdx" access="public">
        <Name>top50Customers</Name>
        <BandedMode>compact</BandedMode>
        <Query>
            WITH 
                SET CUSTOMERS AS TopCount([Customers].Children, 50.0, [Measures].[Sales])
            SELECT 
                NON EMPTY {[Measures].[Sales]} ON COLUMNS,
                NON EMPTY CUSTOMERS ON ROWS 
            FROM [SteelWheelsSales]
            WHERE ${marketQueryParam}
        </Query>
        <Parameters>
            <Parameter name="marketQueryParam" type="String" default="[Markets].[All Markets]"/>
        </Parameters>
    </DataAccess>

</CDADescriptor>

This example will be used in some samples during the next set of workshops. Don't forget to preview the results and confirm that you are able to return the results for both queries.

---

Preview Results

1. Highlight the sampledata-queries.cda.

2. Under 'File Actions' click on 'Open'.

sampledata.cda

1. Select a Data Access ID in the CDA dashboard.

sampledata.cda - cities query

---

Previewer

Let's test a few of the API's ..

1. Click on the Query URL, to retrieve the API call.

1. Copy & edit the URL to access the previewer - editFile

http://localhost:8080/pentaho/plugin/cda/api/editFile?path=/public/CTools-Dashboard/CDA/sampledata-queries.cda

Previewer

The Editor Interface The interface consists of a central editor pane with three action buttons positioned above it on the right side:

Save - Preserves any changes made to the XML file

Reload - Refreshes the file content to its latest saved state

Preview - Opens a preview window to view data source execution results

CDA - Cache

We're going to come back to this topic .. Optimization

CDA is able to cache the queries that have been executed. Every query that runs will be cached or not cached, and by the time defined in the Cache property element when defining the Data Access.

You can also set the interval of time to grab results from the cache, avoiding new requests to the server.

1. If you still have your Query open, then click: 'Cache this' button.

To enable caching for a query in your CDA file, you need to add cache-related settings to your dataAccess element. Here are the key cache parameters:

<DataAccess id="your_query_id" cache="true" cacheDuration="3600">

The main cache parameters are:

cache="true" - Enables caching

cacheDuration="3600" - Sets cache duration in seconds (3600 = 1 hour)

You can also use these additional cache parameters:

cacheKeys - Defines specific keys for parameterized queries

outputIndexId - Sets a unique identifier for the cached output

executeAtStart - Pre-loads the cache when the server starts

Example of a complete cached query configuration:

<CDADescriptor>
  <DataAccess id="myQuery" 
              connection="myConnection"
              type="sql" 
              cache="true"
              cacheDuration="3600"
              executeAtStart="false">
    <Name>My Cached Query</Name>
    <Query>
      SELECT * FROM my_table
    </Query>
  </DataAccess>
</CDADescriptor>

1. Set your schedule.

Set a schedule

If you prefer to use CRON, click on the (advanced) link (top right)

CRON

Cache Manager

1. Click: 'Cached Queries'.

Cached Queries

Notice all the Queries are executed / cached.

---

Click on the link below to access the Cache Manager.

http://localhost:8080/pentaho/plugin/cda/api/manageCachelocalhost

x

Schedule Queries & Cache Manager