Create a Variable

You must now create an optional prompt parameter for the query so Cognos® Transformer can issue smaller queries to SAP, and thereby retrieve the entire data set.

There are no set rules for variable usage when extracting SAP BW data for use in Cognos Transformer. However, you must be careful not to request too much data that could potentially perform poorly or error out with out-of-memory messages within your SAP environment.

A basic guideline to follow is that when a variable is utilized for the extraction, Cognos Transformer will first fetch all members that exist for the dimension against which the variable is defined. After this, Cognos Transformer will perform individual data fetches to extract the fact data for each of the individual members within the dimension in order to satisfy the variable.

This allows Cognos Transformer to break down your data extraction into manageable chunks that the SAP BW server can handle. There are no set standards as to which dimension to apply it to. To achieve optimal performance, you must understand your SAP BW data and determine which dimension evenly breaks up the factual data.

You must choose carefully which dimension to define the variable on. It may require some experimentation to achieve optimal performance. For example, you may have a Country or region dimension that contains three countries or regions as members, United States (US), Canada (CA), and Mexico (MX). If most of the business is performed in the US (90%) and the remaining business (10%) is recorded against Canada and Mexico evenly, this dimension would not evenly split up the data. The resulting queries would have one very large request (US) and two small ones (CA and MX). Therefore, this dimension would not be a good candidate.

You do not want to apply a variable on a dimension that would cause too many very small requests. For example, [0MATERIAL], a dimension often utilized in SAP BW environments would probably not be a good candidate because it would cause too many small requests to be performed.

You may have a dimension defined for [COSTCENTER] that evenly divides up the data for 10 distinct cost centers that may serve to segment the data evenly. Another good alternative may be calendar year or calendar month because it may divide your data into sections that perform adequately.

It is not necessary to apply any variables to queries for data extraction. Some extraction will perform perfectly well when no variables are applied. For example, a good approach may be to apply a variable on a dimension which splits the data into 20 individual fetches and test the extraction. If this performs well, you may choose to apply a variable on a different dimension which may contain 5 distinct members and see how it compares.

No formula can be applied as no two environments are alike. However, a cautious approach is recommended to avoid disrupting your SAP BW environment.

Procedure

  1. In Query Designer, right-click a characteristic that you have selected in the previous procedure and select Restrict.

    To ensure that data is distributed evenly, select a characteristic that is representative of the cube and will not result in a large number of values. You want a resulting variable where the number of rows for each value of the variable is similar; you do not want a resulting variable that is too fine-grained (for example, not many rows per value resulting in an excessive number of queries), nor do you want a variable that is too coarse-grained (for example, more than one million rows per value).

  2. In the Selection for … dialog box, click the Variables tab, right-click anywhere inside the Description window and select New Variable.
    Note: If one of the characteristics that you have chosen already has a variable, you can avoid creating a new variable and skip to step 7 of this procedure.
  3. In the New Variable Wizard General Information page, type a Variable Name and Description, and select a dimension as the characteristic. Click Next.
  4. In the Details page, select Single Value, Multiple Single Values, or Interval in the Variable Represents field, Optional in the Variable entry is field, and select the Ready for Input check box. Click Next.
  5. In the Default Values page, ensure that the Default Value field is empty.
  6. Click Next until you are returned to Selection for … dialog box. The new variable appears in the Description window.
  7. Select the variable and click the right arrow to move the selected variable to the Selection window. Save the query. You are now ready to import the query in Framework Manager.