 | |
| | |
| |
You can leverage SAP BW data in Transformer version 8.4 by using an SAP-based package created in Framework Manager and published to Content Manager. This is the recommended method to leverage your SAP BW data. There are special considerations when using SAP-based packages created in Framework Manager. For a complete set of procedures about how to create an SAP BW query, create a package in Framework Manager, and create a model using the SAP package in Transformer, see Importing Data from an SAP BW Query Source.
For general information about creating packages, see "Create or Modify a Package" in the Framework Manager User Guide.
You can use Transformer to import both dimensional and fact data from an SAP BW query source. The following instructions describe how to rebuild an SAP BW cube as an IBM Cognos Transformer cube. To do so, the SAP BW query package must be in a specific format.
This extract process is limited to SAP BW data sources only.
The data source must be a specifically constructed query defined in the SAP BW data source.
There are three stages to importing a SAP BW query to access both dimensions and facts using IBM Cognos 8:
The following sections describes each of these stages:
You must create a query that includes the cube that you wish to import. We recommend that you base the query on a single InfoCube in the database. A query based on multiple sources may result in SAP BW errors during data retrieval.
In Query Designer, click New Query.
In the New Query dialog box, select the information provider that contains the cube that you want to import.
Click the Tools icon 
to view the technical name of the InfoObject.
Drag a characteristic that you wish to import from the InfoObject catalog on the left column to one of the fields on the right-hand side of the page. For example, Columns or Rows.
The characteristics you select will define the metadata in the Transformer cube. The characteristics must adhere to the following restrictions:
You must have at least a single optional variable to segment the data.
Select a characteristic that is representative of the data source. The characteristics can be either key figures, which will become measures in Transformer, or dimensions, which will become the Transformer dimensions.
Do not assign any of the characteristics a display hierarchy, either explicitly or by a variable.
All key figures in the SAP BW query must be numeric.
Do not select the Currency/Unit characteristic.
Ensure that all selected key figures use the same currency.
Only include characteristics in the SAP BW query that you wish to extract using Framework Manager. Including unnecessary characteristics increases data volume, thereby adversely affecting performance.
Characteristics must be copied to the Dimensions or Rows fields of the query definition. If copied to the Free Characteristics or the Filter fields, the characteristics show as dimensions when importing from the package but the stream extract processing is not able to fetch the values.
If you have filters defined, they must reference only dimensions that have been included elsewhere in the query definition.
If you include 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 returned SAP BW data. You can use this as a filter to define a subset of an InfoCube.
Use a picklist prompt, rather than a type-in prompt for the query. A picklist prompt provides values for segmenting the data.
To define the metadata that will populate the Transformer cube, you must change the properties of each characteristic that you have selected for inclusion. Right-click a characteristic, and select Properties.
In the Properties of Characteristic dialog box, change the Display As value to Key, and the Suppress Results Rows value to Always. Note that any restriction or filter applied here will be carried forward in Transformer.
Repeat steps 5 and 6 for each characteristic that you selected in step 4.
Note: You should only select the characteristics that you require. To avoid excessive memory consumption, and decreased system performance or failure, carefully consider what characteristics you want to include in the query. We recommend that you consult an SAP BW administrator to ensure that the data volumes are not exceeded.
Click the Queries Properties icon 
, and in the Extended tab
select the Allow External Access to this Query check
box. This exposes the query to Framework Manager.
Click Save, and provide the new query with a Description and a Technical Name. We recommend that you use the SAP BW naming convention in the Technical Name field. That is, begin the entry with the letter "Z" followed by an intuitive name or your standard naming convention. It is important to write down this technical name, as you will need it to find the query in Framework Manager.
You are now ready to create a variable. For more information on using the SAP Query Designer, see your SAP BW documentation.
You must now create an optional prompt parameter for the query so 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 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, Transformer will first fetch all members that exist for the dimension against which the variable is defined. After this, 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 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] dimension that contains three countries 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.
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 variables. 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).
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.
In the New Variable Wizard General Information page, type a Variable Name and Description, and select Market as the characteristic. Click Next.
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.
In the Default Values page, ensure that the Default Value field is empty.
Click Next until you are returned to Selection for … dialog box. The new variable appears in the Description window.
Select the variable and click the right arrow 
to move the selected variable over to
the Selection window, and save the query.
You are now ready to import the query in Framework Manager.
To create a package in Framework Manager you must
Import the SAP BW metadata using the MetaData wizard
Create a package
Framework Manager imports the SAP BW query into a model, and defines a package that it exports to Content Manager. When importing, note the following:
The dimensions selected in the SAP BW query are available in the Dimension Folders in the Import dialog box.
Each dimension will contain at least one hierarchy.
Always select the primary hierarchy whose name matches the hierarchy.
If other hierarchies are available, select one that gives the desired set of levels within the hierarchy.
Framework Manager imports time dimensions into the model from the SAP BW data source only if a configuration parameter is turned on. Setting the configuration as a time dimension is a global entry; every imported dimension will then be treated as time strings.
In Framework Manager, click Create a new project.
Complete the fields in the New Project dialog box. Click OK.
Complete the steps in the Metadata Wizard. When prompted to select a data source, if you need to create a new data source, click New…
In the Select Objects page, locate
the query that you defined in SAP BW query Designer in the previous
stage 
.
Scroll the list for the technical name that you provided when you
created the variable. The folder structure is as follows: Hierarchies
> Level definitions > Query Item definitions.
Select the main query items that directly relate to the level. That is, those labeled (Key), (Name), and so on.
Tip: Secondary or additional attributes are removed on import to Transformer. Only items that are needed are imported. However, to improve performance, we recommend that you do not select secondary or additional attributes. If you select all the attributes here, you can exclude unwanted query items when publishing the package.
Complete the remaining screens in the Metadata Wizard, accepting the default values, and click Next. This will generate dimensions and import the metadata.
At the final wizard screen, verify the results, and click Finish.
You have imported the SAP BW metadata into Framework Manager.
When creating the package for publishing to Content Manager, hide the primary hierarchy in those dimensions where you imported two hierarchies. The primary hierarchy is necessary, and must be in the package for querying to work correctly. You can hide the hierarchy if you don't want it visible.
Click the Packages folder, and from the Actions menu, click Create, Package.
In the Provide Name page, type the name for the package and, if you want, a description and screen tip. Click Next.
Select the query that you imported in the previous section.
For more information, see Creating a BW Query in SAP Business Explorer Query Designer.
In the Define objects page, when hiding or excluding child objects from the package, you must select each of them individually. Excluding parent objects also exclude all of its children. Note that excluding (or unselecting) many objects from larger cubes will require a significant amount of time.
Note: Framework Manager supports ctrl+shift and alt+shift functionality. Use these keystrokes to select multiple objects that you wish to include or hide in the cube. For example, if you wish to only include two items in a large branch, select the entire branch, then use ctrl+shift to de-select the items you wish to include, and hide the remaining selected items.
For more information about including, excluding and hiding objects, see "Create or Modify a Package" in the Framework Manager User Guide.
Choose whether to use the default access permissions for the package:
To accept the default access permissions, click Finish.
To set the access permissions, click Next.
When you are prompted to open the Publish Package Wizard, click Yes.
Select the default values, and click Publish. This will publish the package to the content store, and will allow you to access the package in Transformer.
At the final screen verify the results, and click Finish.
You are now ready to create a model in Transformer. For more information on creating a package, see "Create or Modify a Package" in the Framework Manager User Guide.
Use Transformer to access a published SAP-based package and use it as a data source to create a model. After the model is created, you can create PowerCubes for use with the desired IBM Cognos 8 component, accessing the dimensional and fact data from the original SAP BW source. In addition, you can combine the SAP metadata in a Transformer model with other corporate metadata or personal sources provided you have the necessary matching key information to join the data during cube building.
When you create the Transformer model, you must use the Insert Dimension from Package wizard rather than the New Model Wizard. You use the Insert Dimension from Package wizard because it
creates a single query for each dimension and for the facts.
imports facts and dimensions in the same manner as dimensionally-modeled relational models. That is, facts and dimensions are imported at the same time.
ensures that the scope is set properly between the dimensions and facts.
populates the dimension with the appropriate business key and caption information.
only imports the necessary items from the BW package required for cube building, when the metadata is imported. This reduces the number of attributes and keeps the data volumes to only the necessary items for cube building.
If you want to define business rules, do so in the Transformer model rather than in Framework Manager. Calculations that you define in Framework Manager are not imported into Transformer.
In Transformer, click Create a new model.
In the New Model Wizard, click Cancel.
With the Dimension Map pane selected, from the Edit menu, click Insert Dimension from Package.
Click Browse to open the metadata browser.
In the Browse Metadata dialog box, select the package that contains your SAP BW query and click OK.
In the Insert Dimension from Package dialog box, click Finish.
In the Select Dimension and Measures from Package dialog box, click the dimensions and measures to include in the data source.
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.
If there are errors or warnings, you are notified. In the Data Sources pane, expand the package to view the data source queries and query items. Key figures or measures appear in 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.
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.
If you want to add another query, repeat steps 3 to 7.
Save the model.
You can now use this model to create PowerCubes for use with the desired IBM Cognos 8 component, accessing the dimensional and fact data from the original SAP BW data source. For more information, see the section "Create a Model" in the Transformer User Guide.
