Show TOC

Creating Input-Ready QueriesLocate this document in the navigation structure

Prerequisites

You can define an input-ready query on any of the following InfoProviders:

  • Aggregation levels (see Aggregation Levels)

  • MultiProviders that contain at least one simple aggregation level

The aggregation levels are created in the Planning Modeler. MultiProviders are defined in the modeling functional area in Data Warehousing Workbench.

Context

Procedure

  1. Define an input-ready query.

    Once you have defined a query on one of the types of InfoProvider listed above, choose the Planning tab page under the properties of structural components (for example, in key figures or restricted key figures). You can use the options here to set which structural components of an input-ready query you want to be input ready at runtime. With structural components that are not input-ready, you can also specify whether these components are viewed as reference data or are just protected against manual entry.

    For the structural components, you also have the following options:

    Option Description
    Not input-ready (not relevant for locking)

    The structural components are not protected by data locks for exclusive use by just one user, as this data servers as a reference for a large number of users.

    This is the default setting.

    Not input-ready (relevant for locking)

    If you want to protect structural components against manual entries, but allow changes by planning functions, you can use locks to protect this data for one particular user. This allows you to ensure that the planning function works with the displayed data only and not with data that has been changed by other users.

    Input-ready (relevant for locking)

    The data is locked for a user and is input-ready for manual planning.

    These default settings can be overridden by the system state, for example if no disaggregation takes place (see below).

    Example

    You want to create an input-ready query for manual planning for a plan/actual comparison of revenues for a set of products. You want to have the plan data in a real-time InfoCube and the actual data in a standard InfoCube.

    1. Create a MultiProvider that includes the InfoCubes for the plan and actual data.

    2. Define an aggregation level on the MultiProvider that contains characteristic Product and key figure Revenue.

    3. On the aggregation level, create two restricted key figures Plan Revenue and Actual Revenue. Choose characteristic 0INFOPROV and restrict it to the plan or actual InfoCube.

    4. Add the restricted key figures to the key figure structure. Insert Product into the rows. For Plan Revenue, choose Input Ready for the input-readiness option. For Actual Revenue, choose the option Not Input Ready (Reference Data).

    5. In the query properties, set the flag to define whether the queries are started in display or change mode as required.

    Product

    Plan Revenue

    Actual Revenue

    P01

    20

    P02

    30

    If you want to keep actual and plan data in a real-time InfoCube, you do not need a MultiProvider for the task described above. Create an aggregation level on the InfoCube and define the input-ready query for the aggregation level. In the example above, a version characteristic acts as the InfoProvider. Create restricted key figures with the plan or actual version and proceed as in the previous example.

  2. Specify whether you want the query to be started in change mode or in display mode.

    You can also set whether to start an input-ready query in change mode or in display mode. This property is in the Query Properties on the Planning tab page. If there is at least one input-ready query component, the query is started in display mode provided that no setting has been made to the contrary.

    Note

    The Start Query in Display Mode setting overrides the default setting. If you have set the data as input ready and lock relevant on the structural component, but the Start Query in Display Mode setting is set in the query properties, the query will therefore not be input ready when started. Users can active input readiness at runtime, and the settings for the structural component made here take effect.

  3. Specify whether and how you want data records to be disaggregated.

    In input-ready queries, normally only cells at the detail level of the underlying aggregation level can be changed. Any cells that contains aggregated values, result cells or internal hierarchy nodes for example, are not input-ready. To be able to change an aggregated value, this value must be disaggregated on all data records that contribute to it.

    Note

    It is not possible to disaggregate using the unit characteristic for a key figure or using characteristic 0INFOPROV. The unit characteristic for the key figure and 0INFOPROV are therefore also required characteristics for structural components with disaggregation, meaning that they always need to be set explicitly for the input-readiness of a cell.

    You can choose from the following options for disaggregation and distribution type:

    Setting

    Description

    No Disaggregation

    The values in the structural component are not disaggregated. Cells containing values that are aggregated for the aggregation level cannot be changed.

    Disaggregate entered value

    Cells containing values that are aggregated for the aggregation level can be changed. The newly entered value is distributed to all data records that contribute to the changed cell. The type of distribution depends on your entries, as described below.

    Disaggregate difference

    Cells containing values that are aggregated for the aggregation level can be changed. The difference between the old entered value and the hew one is distributed to all data records that contribute to the changed cell. The type of distribution depends on your entries, as described below.

    Distribution type

    Description

    Equal Distribution

    Distribution is performed equally to all data records that contribute to the changed cell (including data records with zero values).

    Analog Distribution (Self-Reference)

    Distribution is performed analogously to all data records that contribute to the changed cell.

    Analog Distribution (With Reference to Following Structure Element)

    You can specify a structural element as a reference. Distribution is performed analogously to all data records that contribute to the relevant cell of this referenced structural element. You can only select a structural component from the same structure as the reference for analog distribution.

  4. If you want to use this, you can make the formulas input-ready.

    To make it possible to enter values for formulas, Average Price as a quotient of Amount and Quantity for example, you can make the formulas input-ready. To do this, go to Change Data on the Planning tab page and select Input Ready (Lock Relevant) for the formula in question.

    The input-ready formula becomes the carrier of a formula group. In our example, the quotient Average Price and the two operands Amount and Quantity belong to the formula group. A rule (in the form of an inverse formula) needs to be defined for every input-ready operand in the formula. This rule specifies how the system calculates back to this operand if the value for the Average Price is changed. To do this, choose Create Inverse Formulas in the context menu. By double-clicking on an inverse formula, you can call the Change Formula screen. Define the rules for the recalculation.

Results

Now you can use different clients to create planning applications, which use an input-ready query as a data provider.

Related Information