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.
Before we begin our CTools journey, let's review some CDA samples ..
Log into Pentaho User Console as Administrator.
Select Browse Files.
Navigate to: 'Public - CTools Dashboard - CDA' folder
Highlight the CDA folder.
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
Highlight the sampledata-queries.cda.
Under 'File Actions' click on 'Open'.
Select a Data Access ID in the CDA dashboard.
Previewer
Let's test a few of the API's ..
Click on the Query URL, to retrieve the API call.
Copy & edit the URL to access the previewer - editFile
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
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.
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:
