When you externalize query subjects and dimensions into formats that you can use in other applications, there are special considerations. When extracting data from SAP BW using Framework Manager, you must understand the distinction that Framework Manager makes between different types of dimensions. Each type of dimension exhibits a different behavior when it is externalized, and can be modified before externalizing.
In Transformer versions 7.x, 8.1, and 8.2, you can leverage your SAP BW data using a Framework Manager package in which the query subjects and dimensions are externalized using CSV files. Transformer can use the CSV files as a data source to create a model and generate PowerCubes. CSV files are also supported in Transformer version 8.3 but it is recommended that you use package support for dimensional data and CSV files for fact data.
In Transformer version 8.4, using Framework Manager packages published to Content Manager is the preferred method to leverage SAP BW data. For general information about creating packages, see "Create or Modify a Package" in the Framework Manager User Guide. For SAP-specific information about creating packages, see Work with SAP BW Data Using a Package in Framework Manager.
The Extract Size data source property within Framework Manager controls the amount of data retrieved from SAP BW at any one time.
If this setting is negative, zero, or empty, a single query is issued to SAP BW to extract the characteristic data.
If this setting is a positive value, Framework Manager issues multiple queries to SAP BW, each of which returns approximately the number of megabytes specified by the Extract Size property.
This feature can reduce the overall size of the query result on the SAP BW server. Overall query execution may take longer, but for large characteristics, not using this feature may result in consumption of a user's allotted memory space on the SAP BW server.
The entire data for a characteristic dimension will be in memory within Framework Manager prior to the production of an extract file. It is important that only the required query items be extracted from SAP BW to ensure that an extract does not fail due to memory allocation errors within Framework Manager.
Model query subjects are extracted using the same mechanism by which queries are executed within IBM Cognos 8. Therefore, the Extract Size property has no effect on the query execution.
When extracting a measure dimension, you should create a model query subject containing the measures that you want. You should include the business key query item from each of the levels of each dimension, depending on the level of granularity that you are trying to achieve.
For information about externalizing model query subjects, see Framework Manager Considerations.
Characteristic dimensions are externalized independent of the type of SAP BW data source, such as InfoCube or SAP BW query.
Framework Manager uses a single approach to externalize all dimensions that do not contain fact query items. In these cases, the extract size configuration setting is used to control the manner in which data is extracted from SAP BW.
Note: Model query subjects are externalized in a different manner, regardless of whether they contain fact query items or not. For information about externalizing model query subjects, see Framework Manager Considerations.
When externalizing the key figures dimension from a model based on an InfoCube, Framework Manager uses exactly the same approach as used for externalizing model query subjects.
For an InfoCube containing more than a few thousand transactions, externalizing an InfoCube directly from Framework Manager can easily exceed both time and memory limits on either the client or server. In such cases, it is highly recommended that an SAP BW query be used as the basis for externalizing the SAP BW metadata.
Using a BEx query as the basis for externalizing key figures from an SAP BW data source is, in most cases, the best approach. By using a BEx variable to break the data of the key figure dimension into manageable sections, arbitrarily large volumes of transaction data can be extracted from SAP BW.
Note, however, that this approach incurs some restrictions as to what can be extracted from SAP BW, and how it can be extracted. The remainder of this section describes how an SAP BW query is used to extract data from SAP BW, including all known restrictions and limitations.
For the remainder of this section, we assume that an SAP BW query is being used as the basis for externalizing the data, not as the basis for reporting, and not with the intent of exceeding the memory and time limitations associated with extracting data directly from an InfoCube.
It is not possible to externalize an arbitrary SAP BW query. An SAP BW query must adhere to the following restrictions if you want to externalize it:
Set the characteristic display to Key. Setting the display to anything else may result in incorrect data.
To change what appears for a characteristic, right-click the characteristic and click Properties. In the Properties of Characteristic dialog box, change the Display As value to Key.
We strongly recommend that you use Key.
To reduce data volumes, as well as the amount of aggregation performed by the SAP BW server, we strongly recommend that summarization for all characteristics in the query be disabled in its property sheet.
To disable summarization for a characteristic, right-click the characteristic along the edge of the SAP BW query and click Properties. In the Properties dialog box, set the Suppress Results Rows value to Always.
If at least one characteristic in an InfoQuery is displayed as something other than Key, then summarization for all characteristics must be suppressed.
The query must not contain the Currency/Unit characteristic.
None of the characteristics may be assigned a display hierarchy, either explicitly or by a variable.
If a characteristic is included in an SAP BW query as a free characteristic, no values will appear for that characteristic in the key figures extract.
A filter on a free characteristic acts as a filter on the data returned by SAP BW. It is an efficient mechanism for defining a subset of an InfoCube.
Such a filter may also be applied to a characteristic along an axis of an SAP BW query, in which case the filtered values appear in the key figures extract.
All key figures in the SAP BW query must be numeric.
The values of each key figure should be in a single currency. A variable should not be used to drive the assignment of a target currency.
Include in the SAP BW query only those characteristics which are to be extracted using Framework Manager. Including unnecessary characteristics increases the volume of data transferred from SAP BW, thus affecting performance.
You must use CSV files when importing metadata from SAP BW cubes. For performance reasons, we recommend that you filter on geography, time periods, or some other dimension that limits the amount of data retrieved. Remember to apply your dimension filter to the related dimensions and their fact tables (measures). For more information, see "Create a Filter" in the Framework Manager User Guide.
Because SAP BW cubes are multidimensional, rollups are applied at the source. If you change the rollup type after importing the data into Transformer, your results will not be valid.
Missing data or metadata that is out-of-scope for a particular measure may yield different results, depending on the context. You may see:
NULL values
# symbols
REST_H
Not assigned
Because such duplicate tokens can cause problems in Transformer, in unique levels for example, we recommend that you assign filters to the dimension so that they do not appear in the imported data.
Finally, remember to select only those query items needed to generate your filtered data.
When extracting the measure dimension from an SAP BW query, the Extract Size property of the data source controls the amount of data retrieved from the SAP BW server at one time. Model query subjects are externalized in a different manner, regardless of whether they contain fact query items or not. In this scenario, the setting has no affect on the SAP BW server, but it does limit the amount of memory Framework Manager allocates at any one time to retrieve the data.
Note that filters defined on the key figures dimension are not enforced when extracting data from an SAP BW query. To obtain performance benefits of extracting data from an SAP BW query, filters must be defined in an SAP BW query.
In addition, any calculations defined within the key figures dimension are ignored. These may be defined either within the SAP BW query in BEx, or in a model query subject in Framework Manager.
Each characteristic extracted must contain at least one query item from the lowest level of its hierarchy (if there is one) to provide linkage with the key figures extract. You should include the business key query item from each of the levels of each dimension, depending on the level of granularity that you are trying to achieve.
The volume of transactions within an SAP BW query is such that, in most cases, the use of a single query to extract the data from SAP BW will exceed the memory allocated to a user on an SAP BW server. In Framework Manager, you can use a single optional variable to extract fact data from an SAP BW query in reasonably sized sections.
To use this feature, one characteristic included in the SAP BW query (but not included as a free characteristic) is assigned a variable that conforms to the following restrictions:
It must be a single value.
It must be optional.
It must not have a default value.
It can be defined on the characteristic or a presentation hierarchy.
If an SAP BW query contains such a variable and the key figures dimension is externalized, Framework Manager runs a query for each possible value associated with a variable. Thus, by choosing an appropriate characteristic, the key figures dimension can be extracted without exceeding the memory restrictions of either the client or server. Memory caches on the client and server are flushed after each query.
If a presentation hierarchy is used to drive the creation of extract sections, it is important that the values for a variable be obtained from a single level in the hierarchy, otherwise the extract will contain data summarized at different levels. To restrict the values for a variable to a single level of a hierarchy, edit the Level Restriction of the variable in Framework Manager. For example, using a value such as "2:2" indicates that only values from the second level of the hierarchy are to be used (level 0 is the root of a hierarchy).
In the presence of an SAP BW query with one such variable, the value of the variable is reset after each query.
If an SAP BW query contains anything more than a single variable, or one that is defined differently than described above, Framework Manager does not attempt to use a variable to break the extraction of the key figures dimension into smaller sections.
When externalizing a data source from Framework Manager, you may encounter an authentication error if
the model is published to Content Manager
externalizing the data takes longer to perform than the timeout period assigned to passports within IBM Cognos Configuration
Users are not prompted to re-enter their authentication credentials.
If an error occurs, the externalized data is still complete and valid. However, if the modeler chooses to actually publish the model, the modeler must re-authenticate and re-publish the model, but without externalizing the data.
Another solution is to publish the model to the network, in which case the authentication error does not occur.
You can build IBM Cognos PowerCubes from SAP BW data. There are guidelines to consider for both Framework Manager and Transformer.
When externalizing data for the purpose of creating one or more PowerCubes, keep these considerations in mind.
The extract of each characteristic must have a common key query item that is equivalent to a surrogate key query item in the key figures extract.
For an extract based on an SAP BW query, it is strongly recommended that all characteristics be displayed as Key in the SAP BW query.
If a characteristic does not have a presentation hierarchy, or a new one is desired, extract one or more query items that can form the basis for levels in a hierarchy.
During the import of SAP BW metadata into a model that will extract data, limit the model to only those query items that are absolutely required to build a PowerCube. This will improve data extract performance.
A practical limit for PowerCubes is 2,000,000 categories (values) for a dimension (characteristic).
When using the SAP BW data that you extracted from Framework Manager, keep these considerations in mind.
In Transformer version 8.3, you can insert regular dimensions from SAP data sources directly from a IBM Cognos 8 data source, using the Insert dimension from package option.
Using the model wizard in Transformer, insert a data source of type Delimited-Field Text With Column Titles and start by selecting the CSV file. Do not run auto-design.
Drag all the key figure columns from the Data Sources pane into the Measures pane. Ensure that the aggregation rule for each measure is correctly defined within Transformer to align as closely as possible with the aggregation rule defined in SAP BW.
It is recommended that the storage type for all measures be set to 64-bit floating point.
Using the date wizard, select a query item that will provide the dates for the PowerCube. Note that the dates for the PowerCube can be derived entirely from the transaction data.
Insert the various CSV files corresponding to the characteristics that were externalized using Framework Manager.
Each CSV file contains a column that corresponds to a column in the key figures CSV file. By right-clicking the various columns and editing the column properties, ensure the columns that provide the linkage between a characteristic and the key figures have the same name. For example, if a key figure column is named Customer and the corresponding column in the customer CSV file is named Customer - Key, then the name of the column in the key figures CSV file can be changed to Customer - Key.
For each characteristic, create a new dimension, using the key columns, or other attributes of a characteristic, to drive the levels of the dimension. For each level, ensure that the properties for the label, short name, and description are assigned source columns, if applicable.
For the root level of each characteristic (dimension), ensure it is marked as unique.
SAP BW presentation hierarchies may contain ragged paths, typically in association with the "not assigned" and "#" nodes in the hierarchy. The gaps in these hierarchies produce blanks at the associated level in the Transformer hierarchy.
In Transformer, it is possible to define the text that should be used for blanks (the default text is "<blank>"). A best practice is to define a more appropriate text for blank entries for all such levels.