Parameters

It's all about being flexible ..

Dynamic queries are essential for flexible data retrieval. By using variable criteria instead of hard-coded values, you can filter records differently each time without modifying the query structure. This makes your queries more reusable and adaptable to changing requirements.

SQL

select {[Measures].[Sales], [Measures].[Quantity]} ON COLUMNS,
NON EMPTY [Time].Children ON ROWS
from [SteelWheelsSales]
where ([Order Status].[${status}])

MDX

SELECT
NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
NON EMPTY {[Markets].[${markets}].Members} ON ROWS
FROM [SteelWheelsSales]

SQL

A parameter acts as a dynamic variable in your queries, allowing you to change values without modifying the query structure itself. Let's explore how they work and how to use them effectively.

Instead of using fixed values in your queries, you can use parameters that change based on user input or other conditions.

Parameters use the syntax: ${parameterName}.

-- Static query
select * from customers where country in ('USA');

-- Parameterized query
select * from customers where country in (${country});

In CDA, parameters are defined using XML syntax under a Parameters element:

<Parameters>
    <Parameter default="USA" name="country" type="String"/>
</Parameters>

So now when defined in the CDA, the Query attribute needs to match the name in the parameter:

<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor>
   <DataSources>
      <Connection id="sqlSample" type="sql.jndi">
         <Jndi>SampleData</Jndi>
      </Connection>
   </DataSources>
   <DataAccess access="public" connection="sqlSample" id="sqlSample"
type="sql">
      <Cache duration="3600" enabled="true"/>
      <Columns/>
      <Parameters>
         <Parameter default="USA" name="country" type="StringArray"/>
      </Parameters>
      <Query>select * from customers where country in (${country})</
Query>
   </DataAccess>
</CDADescriptor>

Prepared statements in SQL are a security and performance feature where query parameters are replaced by placeholders. When the JDBC driver supports it, these queries are precompiled in the database during preparation; otherwise, precompilation happens at execution time.

The key benefits are twofold:

First, the database can reuse the access plan for similar queries, making execution significantly faster than regular statement objects.

Second, prepared statements automatically protect against SQL injection attacks by properly escaping and handling input parameters.

---

Defining Multiple Parameters in a Query

You can define several parameters within a single query using the Parameters tag. Each parameter needs its own Parameter element with three key attributes:

• Name - Must match the parameter reference in your query

• Default Value - Used when no specific value is provided

• Type - Determines what kind of data the parameter accepts

Available Parameter Types:

Single Value Types:

• String: Text values

• Integer: Whole numbers only (e.g., 10, 365)

• Numeric: Numbers with decimals (e.g., 15.39)

• Date: Date values

Array Types (for IN conditions):

• StringArray: Multiple text values

• IntegerArray: Multiple whole numbers

• NumericArray: Multiple decimal numbers

• DateArray: Multiple date values

MDX

MDX query parameterization in Mondrian offers a robust and secure approach to handling dynamic queries. Unlike SQL, where parameter handling requires careful sanitization to prevent injection attacks, MDX parameters can be used freely anywhere in the query structure. This flexibility extends to allowing parameters to represent either portions of a query or entire queries themselves, without compromising security.

The security model in Mondrian operates at a fundamental level - it evaluates permissions before returning any data. This means that even if a user attempts to craft a query to access unauthorized data, Mondrian's security layer will automatically filter out any results they don't have permission to view. This makes it inherently more secure than traditional SQL approaches.

<Parameters>
    <Parameter name="markets" default="Territory" type="String"/>
</Parameters>
<Query>
SELECT
NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
NON EMPTY {[Markets].[${markets}].Members} ON ROWS
FROM [SteelWheelsSales]
</Query>
</DataAccess>
</CDADescriptor>

The MDX query is configured with a parameter named "markets" (default value: "Territory")

The query structure retrieves:

• Quantity measures on the COLUMNS axis

• Market members based on the parameter value on the ROWS axis

• Data is sourced from the "SteelWheelsSales" cube

The parameterization is particularly powerful because the "markets" parameter can be dynamically set to different geographic levels (Territory, Country, State, Province, or City), allowing the same query to provide different levels of detail without requiring separate query definitions.

This makes it especially useful for building flexible business intelligence dashboards and reports where users need to drill down through different levels of data.

<DataAccess access="public" connection="mk" id="mk" type="mdx">
<BandedMode>compact</BandedMode>
<Parameters>
      <Parameter name="myQuery" default="SELECT {[Measures].[Sales]}
ON COLUMNS FROM [SteelWheelsSales]" type="String"/>
</Parameters>
<Query>
    ${myQuery}
</Query>
</DataAccess>

The query system uses parameters to control data aggregation levels. By default, it shows sales quantities by Territory, but you can change this parameter to view data by Country, State, Province, or City instead.

Parameters are flexible and can be placed anywhere within a query, or the entire query itself can be parameterized.

The example shows how to create a parameterized MDX query that retrieves sales data from the SteelWheelsSales database, with the query structure defined in a parameter called "myQuery."

Kettle

When working with Kettle queries in CDA (Community Data Access), you don't need to define a query explicitly for CDA, though you may need one within the transformation itself. Instead, you can pass parameters directly to the Kettle transformation.

<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor>
<DataSources>
      <Connection id="1" type="kettle.TransformFile">
        <KtrFile>sample-trans.ktr</KtrFile>
        <variables datarow-name="myRadius"/>
        <variables datarow-name="ZipCode" variable-name="myZip"/>
      </Connection>
    </DataSources>
<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>

The key feature is parameter mapping between CDA and Kettle transformations. When parameter names differ between CDA and the Kettle transformation, you must map them using the datarow-name and variable-name attributes in the connection definition. If parameter names are identical in both CDA and Kettle, no mapping is needed - CDA will pass these parameters directly to the transformation.

For example, if a Kettle transformation uses a parameter named myZip but your CDA component refers to it as ZipCode, you would map them in the XML configuration using:

<variables datarow-name="ZipCode" variable-name="myZip"/>.

Both parameter definitions must also be included in the DataAccess section of the CDA descriptor.

x

Private

Private parameters in SQL queries are server-side values that cannot be overridden by client-side inputs. This is particularly useful for security purposes, especially when handling sensitive data like user identities. For example, when using

${[security:principalName]}

as a private parameter, the system will automatically use the logged-in user's username, regardless of any attempts to modify this value from the client side.

This prevents potential security breaches where a user might try to impersonate another user by manipulating parameter values. The parameter is defined with

access="private"

in the XML configuration, ensuring that only the server-side value is used in the SQL query.

<Parameters>
    <Parameter default="${[security:principalName]}" name="username"
type="String" access="private"/>
</Parameters>
<Query>SELECT * from Employee where id=${username}</Query>