Implement Parameters for Datasources

To make Datasources more flexible, you can implement runtime parameters in the query to allow the Datasource to use variables.

These may be substitution variables defined in Essbase, runtime parameters defined by the grid context when Smart View users drill through to external data, or user defined functions written in an external source system.

Whenever you plan to use variables in Essbase Datasources, you first need to

1. Include variable syntax in the Datasource query. For example, the Datasource query must include ? in its syntax, where the ? is a placeholder for some variable to be defined at runtime.

2. Do one of the following:

   • Set a fixed, default parameter value in the Datasource for Essbase to use as a fallback in case the variable has an invalid context at runtime, OR

   • Set a substitution variable to be used by the Datasource

   • Pass an external, user-defined function (or stored procedure) as a parameter

To define Datasources and implement parameters for them, you must be an application manager or higher.

Enabling the use of variables in Essbase Datasources helps you streamline operations by requiring fewer Datasources to maintain. Implementing variables to Datasources enables you to specify a runtime query context that will be applied whenever a user accesses a Datasource associated with an Essbase cube.

For example, assume the following use cases.

• A database manager oversees a recurring data load job that loads data to the cube on a monthly basis. The database manager can now use a substitution variable to load data for the current month, instead of maintaining a load rule for each month.

• An application manager maintains drill through report definitions for different business use cases. The application manager implements variables in the underlying Datasource that Smart View users pull from in their drill through operations. As a result, the application manager has fewer drill through report definitions to maintain and debug.

Set a Default Parameter in a Datasource

Set a default parameter in a Datasource if you want to enable the use of variables in the queries Essbase generates when it works with data stored outside the cube.

To set the default parameter,

1. Obtain or create a connection to the external source of data (for example, create a connection to Oracle Database).

   1. You can use a global connection, if one already exists in the Sources page of the Essbase web interface, OR,

   2. You can create an application level connection:

      
      
      
      
      

2. Create a Datasource over the connection you will use to access Oracle Database.

   Again, note that you can define a Datasource globally if it should be available to all applications, or you can define it at the application level.

   1. In the General step, for Connection, select the Oracle Database connection you created.

   2. For Name, give a name to the Datasource.

   3. For the Query, provide a query (this example uses SQL). To make it a parameterized query, you must include a filter condition (WHERE clause) that maps a relational column in your source to a placeholder. You indicate the position of the variable by using a placeholder, ?, in the query syntax. The placeholder is for a parameter you will pass in a later step.

      select * from SB_DT where DIMENSION_YEAR=?

      For example, assume your relational database has the following table, named SB_DT. The table has DIMENSION_YEAR column with months as values:
      
      

      To use a variable for the selection of month values from the DIMENSION_YEAR column, apply the following filter syntax in the query: where DIMENSION_YEAR=?
      
      

   4. Click Next.

   5. In the Columns step, apply the appropriate data type that Essbase should associate with each column from your relational source data.

      For example, set the numeric columns to type Double, and leave the alphanumeric columns as type String.

      
      
      
      
      

   6. Click Next.

   7. In the Parameters step, Param1 is created – this parameter exists because you used a ? in the query on the General step.

      Leave Use Variables unchecked, double-click the text field under Value, and type in a default value for the runtime parameter. The purpose of this default value is for Essbase to use as a fallback in case the parameter has an invalid context at runtime. This step is important if you intend to use runtime parameters as part of drill through report definitions.

      You can also rename Param1 to a name that is meaningful for your use case. For example, you can rename it to param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_<appName>_month to indicate that the parameter uses an application-level variable for the current month. Customizing the parameter names can be helpful when debugging parameters using Essbase server log files.

      
      
      
      
      

      If you want to customize the parameter to reference a substitution variable, then you do not have to provide a default value. See Use Substitution Variables in a Datasource instead of this topic.

   8. Click Next.

   9. In the Preview, notice that the default parameter has been applied to your query. As a result, the preview is populated only with external source records in which the value of DIMENSION_YEAR column is Jan.

      
      
      
      
      

      Although the preview only displayed values with the default parameter applied, later, when you implement runtime parameters for drill through report definition, you will have access to more external data than what was visible in the preview.

   10. Click Create to create the Datasource based on this query of your external source data. The Datasource is enabled for implementation of runtime parameters.

Use Substitution Variables in a Datasource

The following workflow illustrates how to create an Essbase Datasource from a query of external source data, using a substitution variable defined in Essbase. The substitution variable adds more flexibility to how you design the query that pulls from your source data.

In this example, you will use a substitution variable in Essbase to declare the current month. Instead of updating Datasources monthly to pull in data for the current month, you can leave the Datasources alone, and only update the substitution variable you defined.

1. Create a global or application level substitution variable.

   
   
   
   
   

2. Obtain or create a connection to the external source of data (for example, create a connection to Oracle Database).

   1. You can use a global connection, if one already exists in the Sources page of the Essbase web interface, OR,

   2. You can create an application level connection:

      
      
      
      
      

3. Create a Datasource over the connection you will use to access Oracle Database.

   Again, note that you can define a Datasource globally if it should be available to all applications, or you can define it at the application level.

   1. In the General step, for Connection, select the Oracle Database connection you created.

   2. For Name, give a name to the Datasource.

   3. For the Query, provide a query (this example uses SQL). To make it a parameterized query, you must include a filter condition (WHERE clause) that maps a relational column in your source to a placeholder. You indicate the position of the variable by using a placeholder, ?, in the query syntax. The placeholder is for a parameter you will pass in a later step.

      select * from SB_DT where DIMENSION_YEAR=?

      For example, assume your relational database has the following table, named SB_DT. The table has DIMENSION_YEAR column with months as values:
      
      

      To use a variable for the selection of month values from the DIMENSION_YEAR column, apply the following filter syntax in the query: where DIMENSION_YEAR=?
      
      

   4. Click Next.

   5. In the Columns step, apply the appropriate data type that Essbase should associate with each column from your relational source data.

      For example, set the numeric columns to type Double, and leave the alphanumeric columns as type String.

      
      
      
      
      

   6. Click Next.

   7. In the Parameters step, Param1 is created – this parameter exists because you used a ? in the query on the General step. To customize Param1 to reference a substitution variable, click Use Variables, and select a substitution variable from the Value drop-down list.

      If you are creating a Datasource within an application, both global and application-level substitution variables are available to select. The application-level variables are prefixed with the application name. If you are creating a global Datasource, only global substitution variables are available to select.

      You can rename Param1 to a name that is meaningful for your use case. For example, you can rename it to param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_<appName>_month to indicate that the parameter uses an application-level variable for the current month. Customizing the parameter names can be helpful when debugging parameters using Essbase server log files.

      
      
      
      
      

   8. Click Next.

   9. In the Preview, notice that the substitution variable is applied to your query. As a result, the preview is populated only with external source records in which the value of DIMENSION_YEAR column is Aug.

      
      
      
      
      

   10. Click Create to create a Datasource based on this query of your external source data.