CDA configuration files basics (part 2)

Last time we started talking about the basics of CDA configuration. Today we'll continue our series of articles on CDA better at DataAccess element configuration.

A little ricap

CDA is a BI Platform plugin that that allows to fetch data in various formats. It works defining a configuration file where we will define connections to our available datasources and a number of data access objects. The data access objects defines the strategy used to access the data using a specified connection. In our previous post we talkes briefly about how to configure Connections a we give a brief overview about the DataAccess element. Today we will complete our introduction to the basics of  CDA DataAccess configuration.

Adding parameters to our DataAccess element

The Parameters element, nested into the DataAccess element, let you define a set of  parameters required to get the data from you connection. Every parameter is associated to a Parameter element which accepts at least the following attributes:

• name, to specify the Parameter name.
• type, lets you specify the Parameter type. Allowed types are String, Date, Integer, Numeric.
• pattern, lets you specify a display pattern required to let the CDA better understand the parameter's value as inputted by the user. Samples of usage for this attribute are dates dates or numeric parameters.
• default, lets you specify a default value for the parameter so that it will be automatically assigned every time we don't give that value

Below you've a sample about configuring a Parameters element

   <DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="300">  
                .  
                .  
                <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>  
                .  
                .  
                <Columns>  
                     <Column idx="0">  
                          <Name>Year</Name>  
                     </Column>  
                     <CalculatedColumn>  
                          <Name>PriceInK</Name>  
                          <Formula>=[PRICE]/1000000</Formula>  
                     </CalculatedColumn>  
                </Columns>  
                .  
                .  
    <!-- Output controls what is outputed and by what order. Defaults to everything -->  
    <Output indexes="1,0,2,3"/>  
    .  
    .  
   </DataAccess>  

For your reference I've also shown you the query definition. Look at the Columns element declaration: you can see that we're going to rename the column with ordinal 0 from "YEAR_ID" to "Year". To do that we used a Column element where we set the new name.
Then, using the CalculatedColumn element, we added a new calculated column name PriceInK.
Consider that:

• New calculated columns are always added at the end of the columns set
• CDA uses Pentaho reporting libraries to apply formulas so that means you need to identify streams columns in your formula using square bracket notation and that you can use all the construct found in that libraries.

Last but not least we changed the positional layout of the output columns through with an Output element. In the indexes attributes we set the columns position using column indexes. You have two consider two things here

• The total columns number is 4 because 3 came from the query and one was the calculated column lastly added to the stream
• The index for the first leftmost column is 0 and NOT 1