Creating a CDA

Data Connection & Access in one file ..

CDA (Community Data Access) is a CTools component that provides data abstraction for diverse data sources through web services. While it was initially developed to serve as a bridge between data connections and the Community Dashboard Framework (CDF), its functionality has expanded.

Now, it can also be integrated with Report Designer to incorporate data into third-party applications.

Key features of CDA include:

• Multiple configurable output formats

• Performance optimization through customizable caching

• Server-side sorting and pagination capabilities

CDA's data flow works as follows:

When a dashboard (CDF/CDE) or external application requests data through CDA's endpoints

- CDA first checks if caching is enabled

- If enabled, it verifies:

• Whether results for this specific query and parameters exist in cache

• If cached results are still valid (not expired)

• If cache keys match

- Only if no valid cached data is found does CDA query the underlying data sources

This architecture makes CDA an efficient middleware layer that minimizes unnecessary database queries while providing flexible data access to various front-end applications.

As we can see in the diagram, the available data sources for CDA are:

• SQL over JDBC or JNDI.

• MDX queries over Mondrian or olap4j.

• MQL queries over a Pentaho metadata connection.

• Kettle transformations.

• Scripting (only Beanshell and JavaScript are currently supported).

• XPath over XML files.

• Compound queries.

CDA examples: /public/CTools-Dashboard/CDA

There are multiple ways to create CDA data sources. One of the ways is to use CDE, where no code or XML is needed, and we will cover this later in the CDE workshop.

There is another way, the hard way, which is editing the file by hand.

The CDA files that are XML files with a .cda extension. This way, Pentaho will recognize the file extension and will provide the capability to preview the results or edit the file. The main structure of a CDA file is the following:

<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor>
   <DataSources>
      <!—- HERE LIVES EACH ONE OF <Connection>-->
   </DataSources>
   <!—- HERE LIVES EACH ONE OF <DataAccess> -->
</CDADescriptor>

xml files begin with an XML declaration, followed by structured elements that define connections and data access configurations. The primary element is CDADescriptor, which contains data source definitions that can be shared across multiple queries.

Rather than repeatedly defining database connections within individual queries, we centralize connection settings in the data source element. This makes sense since multiple queries often use the same connection parameters.

Each data access definition requires specific attributes to be set. Here's how the structure works:

• XML declaration at the top

• CDADescriptor as the root element

• Data source elements containing shared connection settings

• Individual data access configurations with their required attributes

Mandatory Connection Attributes

• Defines a unique identifier

• Used to reference specific connections in queries

• Must be unique across all connections

Type

• Specifies the connection type

• Determines required internal elements

• In CDE: Automatically configured based on selected data source

Creating Data Access

Once connections are established, proceed to create data access queries. Each query should follow this structure:

<DataAccess id="1" connection="1" type="sql" access="private"
            cache="true" cacheDuration="300">
   ...
</DataAccess>

Attribute

Description

This is used to define the data access identifier that will be used in the components.

connection

This is the identifier of the connection created previously. Different DataAccess id can share the same connection id.

access

This defines whether the data access is visible. Here we can have one of two values: private or public. Private will say that the data access will not be visible, and public says the opposite. You may want to define that a data source is private when it is just to be used inside compound queries to create unions or joins between queries.

cache

This defines whether the results of the query will be cached. Possible values are true and false. You should set it to true if you want your query to be cached. The default value is true.

cacheDuration

This defines the cache duration in seconds. The query will be executed again after the specified seconds have passed. The query will be executed and the results cached again. This attribute will be ignored when the cache is set to false. The default value is 3600, the same as one hour expressed in seconds.

type

We have the same goal when defining a connection and a Data Access, but they have different purposes, so we also need to specify the query type.

Here's an outline of a connection and data access using a JSON query.

query in a new type, and it uses the JSON syntax:
<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor>
   <DataSources>
      <Connection id="query" type="scripting.scripting">
         <Initscript></Initscript>
         <Language>beanshell</Language>
      </Connection>
   </DataSources>
   <DataAccess access="public" connection="query"
               id="query" type="jsonScriptable">
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters/>
      <Query>{
            "resultset":[["row1", 0]],
            "metadata":[
               {"colIndex":0,"colType":"String","colName":"value"},
               {"colIndex":1,"colType":"Integer","colName":"name2"}
            ]}
      </Query>
   </DataAccess>
</CDADescriptor>

SQL databases You can use this type of connection to get data from any source that uses Structured Query Language (SQL) and that can be reached using a JNDI connection or a JDBC driver.

You can use one of these two kinds:

• sql.jdbc: To be utilized when using SQL over JDBC

• sql.jndi: To be utilized when using SQL over JNDI

When creating a connection of the sql.jdbc type, we should also specify the following properties:

° Driver: The Java class name to use (for example, org.postgresql. Driver)

° URL: The URL to connect to (for example, jdbc:postgresql:// localhost:5432/database)

° User: The username to use

° Pass: The user's password

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="sql.jdbc">
            <Driver>org.hsqldb.jdbcDriver</Driver>
			<Url>jdbc:hsqldb:mem:SampleData</Url>
            <User>sa</User>
            <Pass></Pass>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="300">
		<Name>Sql Query on SampleData - Jdbc</Name>
        <Query>
			select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact o
			where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
			group by o.YEAR_ID, o.STATUS
		</Query>
        <!-- All parameters are passed as string from the outside. Their
		type is defined here. If needed, a conversion pattern must be specified

		Supported types: String, Date, Integer, Numeric
		-->
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
        </Parameters>
        <!-- 
		Columns block allow specifying name and CalculatedColumn the creation of new
		columns based on formulas
		-->
		<Columns>
			<Column idx="0">
				<Name>Year</Name>
			</Column>
			<CalculatedColumn>
				<Name>PriceInK</Name>
				<Formula>=[PRICE]/1000000</Formula>
			</CalculatedColumn>
		</Columns>
        <!-- Settings block here overrides defaults and general settings -->
        <Settings>
            <Export type="csv" includeTotals="true">
                <Column idx="0" aggregator="None"/>
                <Column idx="1" aggregator="Average"/>
            </Export>
        </Settings>
        <!-- Output controls what is outputed and by what order. Defaults to everything -->
        <Output indexes="1,0,2,3"/>
    </DataAccess>
</CDADescriptor>

When defining the connection for a sql.jndi connection, you would need to set the following properties:

° jndi: The connection's name as defined in the context.xml file

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="sql.jndi">
            <Jndi>SampleData</Jndi>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="3600">
		<Name>Sql Query on SampleData - Jndi</Name>
        <Query>
			select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact o
			where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
			group by o.YEAR_ID, o.STATUS
		</Query>
        <!-- All parameters are passed as string from the outside. Their
		type is defined here. If needed, a conversion pattern must be specified

		Supported types: String, Date, Integer, Numeric
		-->
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2004-03-01"/>
        </Parameters>
        <!-- 
		Columns block allow specifying name and CalculatedColumn the creation of new
		columns based on formulas
		-->
		<Columns>
			<Column idx="0">
				<Name>Year</Name>
			</Column>
			<CalculatedColumn>
				<Name>PriceInK</Name>
				<Formula>=[PRICE]/1000000</Formula>
			</CalculatedColumn>
		</Columns>
        <!-- Settings block here overrides defaults and general settings -->
        <Settings>
            <Export type="csv" includeTotals="true">
                <Column idx="0" aggregator="None"/>
                <Column idx="1" aggregator="Average"/>
            </Export>
        </Settings>
        <!-- Output controls what is outputed and by what order. Defaults to everything -->
        <Output indexes="1,0,2,3"/>
        <Output id="2" indexes="0,1,3"/>
        <Output id="3" indexes="1,0,2"/>
    </DataAccess>
</CDADescriptor>

When specifying the type for an MDX connection, we have the following available types:

• mondrian.jdbc: To be utilized when using MDX over JDBC

• mondrian.jndi: To be utilized when using MDX over JNDI

• olap4j.defaultolap4j: To be utilized when using MDX over olap4j

To set a connection of a mondrian.jdbc type, the following properties must be defined:

• Driver: The Java class name to use (for example, org.postgresql.Driver)

• URL: The URL to connect to (for example, jdbc:postgresql:// localhost:5432/database)

• User: The username to use

• Pass: The user's password

• Catalog: The Mondrian schema to use

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="mondrian.jdbc">
            <Driver>org.hsqldb.jdbcDriver</Driver>
			<Url>jdbc:hsqldb:mem:SampleData</Url>
            <User>sa</User>
            <Pass></Pass>
			<Catalog>mondrian:/SteelWheels</Catalog>
			<Cube>SteelWheelsSales</Cube>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="mdx" access="public">
		<Name>Mdx Query on SampleData - Jdbc</Name>
        <Query>
			select {[Measures].[Sales], [Measures].[Quantity]} ON COLUMNS,
			NON EMPTY  [Time].Children ON ROWS
			from [SteelWheelsSales]
			where ([Order Status].[${status}])
		</Query>
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
        </Parameters>
    </DataAccess>
</CDADescriptor>

When creating a connection of the mondrian.jndi type, use the following properties:

• jndi: The jndi identifier

• Catalog: The Mondrian schema to use

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="mondrian.jndi">
			<Jndi>SampleData</Jndi>
			<Catalog>mondrian:/SteelWheels</Catalog>
			<Cube>SteelWheelsSales</Cube>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="mdx" access="public">
		<Name>Mdx Query on SampleData - Jndi</Name>
        <Query>
			select {[Measures].[Sales]} ON COLUMNS,
			NON EMPTY  [Time].Children ON ROWS
			from [SteelWheelsSales]
			where ([Order Status].[${status}])
		</Query>
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
        </Parameters>
		<Columns>
			<Column idx="1">
				<Name>Year</Name>
			</Column>
			<Column idx="2">
				<Name>price</Name>
			</Column>
			<CalculatedColumn>
				<Name>PriceInK</Name>
				<Formula>=[price]/1000000</Formula>
			</CalculatedColumn>
		</Columns>
    </DataAccess>
</CDADescriptor>

The Pentaho metadata data sources are used when acquiring data using a Pentaho Metadata Schema. When specifying a metadata query, we need to set the metadata. metadata type. This allows Pentaho metadata to be accessed from a dashboard through an MQL query.

To do this, and when defining the connection, we should provide the following properties:

• DomainId: The domain used when creating the metadata schema

• XmiFile: The path and name of the file of the metadata schema When creating the Data Access, we also need to specify:

• Query: A valid metadata query to be used to get data to the dashboard

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="metadata.metadata">
			<DomainId>steel-wheels</DomainId>
			<XmiFile>metadata.xml</XmiFile>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->

	<DataAccess id="1" connection="1" type="mql" access="public">
		<Name>Mql on SampleData - Metadata</Name>
		<Query><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
			<mql>
				<domain_type>relational</domain_type>
				<domain_id>steel-wheels</domain_id>
				<model_id>BV_ORDERS</model_id>
				<model_name>Orders</model_name>
				<selections>
					<selection>
						<view>CAT_ORDERS</view>
						<column>BC_ORDERS_ORDERDATE</column>
					</selection>
					<selection>
						<view>CAT_ORDERS</view>
						<column>BC_ORDERS_ORDERNUMBER</column>
					</selection>
					<selection>
						<view>CAT_ORDERS</view>
						<column>BC_ORDER_DETAILS_QUANTITYORDERED</column>
					</selection>
				</selections>
				<constraints>

					<constraint>
						<operator>AND</operator>
						<condition>[CAT_ORDERS.BC_ORDERDETAILS_QUANTITYORDERED] &gt;70</condition>
					</constraint>

					<constraint>
						<operator>AND</operator>
						<condition>[CAT_ORDERS.BC_ORDERS_ORDERDATE] &gt; "2003-12-31 00:00:00.0"</condition>
					</constraint>
				</constraints>
				<orders/>
			</mql>]]>
		</Query> 
		<Parameters>
			<Parameter name="status" type="String" default="Shipped"/>
		</Parameters>
	</DataAccess>
</CDADescriptor>

Kettle transformations serve as a versatile ETL (Extract, Transform, and Load) data source tool that simplifies working with complex data ecosystems. Through its coding-free GUI interface, users can easily connect and transform data from multiple sources, run jobs within transformations, and even incorporate predictive analytics.

The system, which uses kettle.TransFromFile attribute type, requires only two main properties - KtrFile for specifying transformation paths and Variables for parameter mapping between kettle and dashboards.

This flexibility allows seamless integration with various tools like MongoDB, Weka, and R, while maintaining ease of use through its graphical interface and parameter customization capabilities.

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition.

	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
      <Connection id="1" type="kettle.TransFromFile">
        <KtrFile>sample-trans.ktr</KtrFile>
        <variables datarow-name="myRadius"/>
        <variables datarow-name="ZipCode" variable-name="myZip"/>
      </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
  <DataAccess id="1" connection="1" type="kettle" access="public" cache="true">
		<Name>Sample query on SteelWheelsSales</Name>
        <Query>Report Columns</Query>
        <Parameters>
          <Parameter name="myRadius" type="Integer" default="30"/>
          <Parameter name="ZipCode" type="Integer" default="32771"/>
        </Parameters>
    </DataAccess>
</CDADescriptor>

Scriptable data sources provide a valuable solution for dashboard development when actual queries aren't yet available, which often happens in team environments where front-end and back-end development occurs simultaneously. In cases where MDX queries are planned but require time-consuming prerequisites like Mondrian schemas, data warehouses, and ETL processes, these scriptable sources allow development to begin with dummy data.

CDA (Community Data Access) supports scriptable queries through a connection configuration that requires setting the attribute type to 'scripting.scripting' and specifying either 'beanshell' or 'JavaScript' as the language. This flexibility enables developers to start building and testing dashboards while waiting for the actual data infrastructure to be completed.

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition.

	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="scripting.scripting">
            <Language>beanshell</Language>
            <InitScript/>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
  <DataAccess id="1" connection="1" type="scriptable" access="public">
		<Name>Sample query on SteelWheelsSales</Name>
        <Query>
import org.pentaho.reporting.engine.classic.core.util.TypedTableModel;

String[] columnNames = new String[5];
columnNames[0] = &quot;Region&quot;;
columnNames[1] = &quot;Q1&quot;;
columnNames[2] = &quot;Q2&quot;;
columnNames[3] = &quot;Q3&quot;;
columnNames[4] = &quot;Q4&quot;;
Class[] columnTypes = new Class[5];
columnTypes[0] = String.class;
columnTypes[1] = Integer.class;
columnTypes[2] = Integer.class;
columnTypes[3] = Integer.class;
columnTypes[4] = Integer.class;

TypedTableModel model = new TypedTableModel(columnNames, columnTypes);
model.addRow(new Object[]{ new String(&quot;East&quot;), new Integer(10), new Integer(10), new Integer(14), new Integer(21)});
model.addRow(new Object[]{ new String(&quot;West&quot;), new Integer(14), new Integer(34), new Integer(10), new Integer(12)});
model.addRow(new Object[]{ new String(&quot;South&quot;), new Integer(10), new Integer(11), new Integer(14), new Integer(15)});
model.addRow(new Object[]{ dataRow.get(&quot;status&quot;), new Integer(10), new Integer(11), new Integer(14), new Integer(15)});
return model;
        </Query>
        <Parameters>
            <Parameter name="status" type="String" default="In Process"/>
        </Parameters>
    </DataAccess>
</CDADescriptor>

Scripting data sources should be used cautiously for two key reasons.

First, they execute extremely quickly, which can mask potential performance issues in your dashboard that would be apparent with real data sources. This could lead to overlooking important optimization opportunities.

Second, scripting data sources typically return static results, unlike real queries that respond dynamically to parameter changes. This makes it difficult to test how your dashboard will behave with varying data conditions. Therefore, it's recommended to primarily work with real data sources to ensure your dashboards are properly optimized and functioning as intended.

Another type of data source is XPath over XML. This will allow you to grab specific nodes from a specified XML file. When defining the connection, the type should be xpath.xPath.

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

  <!-- DataSource definition.

  Type controls what the datasource type is.
  The connection and query controls how the data is fetched. These values
  are specific to each access type

  -->
  <DataSources>
    <Connection id="1" type="xpath.xPath">
      <DataFile>customer.xml</DataFile>
    </Connection>
  </DataSources>
  <!-- DataAccess object controls the query itself

  Access controls if the datasource can be seen from the outside or is to
  be used from a Compound datasource only

  -->
  <DataAccess id="1" connection="1" type="xPath" access="public">
    <Name>Sample query on SteelWheelsSales</Name>
    <Query>/*/*[CUSTOMERS_CUSTOMERNUMBER=103]</Query>
    <Parameters>
      <Parameter name="status" type="String" default="In Process"/>
    </Parameters>
  </DataAccess>
</CDADescriptor>

Compound queries allow you to combine results from multiple queries through either joins or unions. When using compound queries, you don't need to create new database connections since they utilize existing Data Access definitions. Since these data sources are specifically created for use within compound queries, they can be set with private access attributes. This means they won't be visible in the previewer but remain accessible within the compound queries themselves.

When creating a Data Access, you must specify either a joins or union attribute type. For joins, you need to define three key properties: JoinType (which can be Inner, Left Outer, Right Outer, or Full Outer), Left (specifying the data source ID and keys for the right side), and Right (similar to Left but for the left side of the join).

For unions, you need to specify two properties: Up (the query for the top part) and Bottom (the query for the bottom part). It's important to note that when using unions, both queries must have the same number of columns.

While you can use compound queries for joins or unions, you also have the option to use Kettle (also known as Pentaho Data Integration) transformations. Kettle allows you to perform more complex operations with data from multiple sources, including joins, before returning the results to CDA in your chosen format.

Compound Join

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>

    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="sql.jdbc">
            <Driver>org.hsqldb.jdbcDriver</Driver>
			<Url>jdbc:hsqldb:mem:SampleData</Url>
            <User>sa</User>
            <Pass></Pass>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="sql" access="private" cache="true" cacheDuration="300">
		<Name>Sql Query on SampleData - Jdbc</Name>
        <Query>
			select o.YEAR_ID, o.STATUS as status, sum(o.TOTALPRICE) as totalprice from orderfact o
			group by o.YEAR_ID, o.STATUS
		</Query>
        <!-- Settings block here overrides defaults and general settings -->
        <Settings>
            <Export type="csv" includeTotals="true">
                <Column idx="0" aggregator="None"/>
                <Column idx="1" aggregator="Average"/>
            </Export>
        </Settings>
        <!-- Output controls what is outputed and by what order. Defaults to everything -->
        <Output indexes="0,1,2"/>
    </DataAccess>
    <DataAccess id="2" connection="1" type="sql" access="public" cache="true" cacheDuration="5">
		<Name>Sql Query on SampleData</Name>
        <Query>
			select o.YEAR_ID, o.status, sum(o.TOTALPRICE * 3) as tripleprice from orderfact o
			where o.STATUS = ${status} and o.ORDERDATE &gt; ${orderDate}
			group by o.YEAR_ID, o.STATUS
			order by o.YEAR_ID DESC, o.STATUS
		</Query>
        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
        </Parameters>
        <!-- 
		Columns block allow specifying name and CalculatedColumn the creation of new
		columns based on formulas
		-->
		<Columns>
			<Column idx="0">
				<Name>Year</Name>
			</Column>
		</Columns>
        <!-- Settings block here overrides defaults and general settings -->
    </DataAccess>
	<CompoundDataAccess id="3" type="join">
		<Left id="1" keys="0,1"/>
		<Right id="2" keys="0,1"/>
		<Columns>
			<CalculatedColumn>
				<Name>PriceDiff</Name>
				<Formula>=[TRIPLEPRICE]-[TOTALPRICE]</Formula>
			</CalculatedColumn>
		</Columns>
		        <Parameters>
            <Parameter name="status" type="String" default="Shipped"/>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
        </Parameters>
		<Output indexes="0,1,2,5,6"/>
	</CompoundDataAccess>

</CDADescriptor>

Compound Union

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>
    <!-- DataSource definition. 
	
	Type controls what the datasource type is.
	The connection and query controls how the data is fetched. These values
	are specific to each access type

	-->
    <DataSources>
        <Connection id="1" type="sql.jdbc">
            <Driver>org.hsqldb.jdbcDriver</Driver>
            <Url>jdbc:hsqldb:mem:SampleData</Url>
            <User>sa</User>
            <Pass/>
        </Connection>
    </DataSources>
    <!-- DataAccess object controls the query itself

	Access controls if the datasource can be seen from the outside or is to
	be used from a Compound datasource only

	-->
    <DataAccess id="1" connection="1" type="sql" access="private" cache="true" cacheDuration="300">
        <Name>Sql Query on SampleData - Jdbc</Name>
        <Query>
			select o.YEAR_ID, sum(o.TOTALPRICE/1000) as totalprice from orderfact o
			where o.YEAR_ID = ${year}
			group by o.YEAR_ID
		</Query>
        <!-- Settings block here overrides defaults and general settings -->
        <Parameters>
            <Parameter name="year" type="Numeric" default="2003"/>
        </Parameters>
        <Settings>
            <Export type="csv" includeTotals="true">
                <Column idx="0" aggregator="None"/>
                <Column idx="1" aggregator="Average"/>
            </Export>
        </Settings>
        <!-- Output controls what is outputed and by what order. Defaults to everything -->
        <Output indexes="0,1"/>
    </DataAccess>
    <DataAccess id="2" connection="1" type="sql" access="public" cache="true" cacheDuration="5">
        <Name>Sql Query on SampleData</Name>
        <Query>
			select o.YEAR_ID, sum(o.TOTALPRICE/500) as price from orderfact o
			group by o.YEAR_ID
			order by o.YEAR_ID DESC
		</Query>
        <Parameters>
            <Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
        </Parameters>
        <!-- 
		Columns block allow specifying name and CalculatedColumn the creation of new
		columns based on formulas
		-->
        <Columns>
            <Column idx="0">
                <Name>Year</Name>
            </Column>
            <CalculatedColumn>
                <Name>PriceInK</Name>
                <Formula>=[PRICE]/1000000</Formula>
            </CalculatedColumn>
        </Columns>
        <Output indexes="0,2"/>
    </DataAccess>
    <CompoundDataAccess id="3" type="union">
        <Top id="2"/>
        <Bottom id="1"/>
        <Parameters>
            <Parameter name="year" type="Numeric" default="2004"/>
        </Parameters>
    </CompoundDataAccess>
</CDADescriptor>

Common Properties

There are some common properties that should or can be used when defining a Data Access. These properties are:

• Cache: The cache can also be defined as an attribute when defining a Data Access. When defining the cache as an element, we should also specify the two attributes, duration and enabled. The first attribute is used to define the time that the query will be cached since the last execution. The enabled attribute will be set to true or false depending on whether you want to enable it or disable it.

• Name: This is the friendly name of the data access being defined.

• Columns: This is an element that can create a different output by changing the name of a column or just by adding new ones using calculated columns. To change the name of columns, you would just need to specify the columns' idx, starting from 0, and the desired name, as shown in the following example:

<Column idx="0">
   <Name> Region </Name>   
</Column>
<Column idx="1">
   <Name> Quantity </Name>   
</Column>
<Column idx="2">
   <Name> TotalPrice </Name>   
</Column>

To create a calculated column, we need to specify the name of the new column and the formula to be used. The formulas should match the open formula specification.

<CalculatedColumnidx="0">
   <Name> Unit Price </Name>
   <Formula>=[TotalPrice]/[Quantity]</Formula>   
</CalculatedColumn >

• Query: Almost all Data Access makes use of this element. Refer to each one of the the data sources types referred earlier to get more information.

• Parameters: These are the parameters to be sent/used in the query. This element lets us define a different output other than the one defined in the queries.

PreviousCommunity Data Access NextParameters

Last updated 4 days ago

Was this helpful?