To create the cubes that you need for OLAP reporting, you begin by creating a model. This involves
specifying the data sources for the model and any required security credentials
defining dimensions, levels, and measures based on the selected query objects in your IBM Cognos 8 package or report, or the tables, rows, or columns of your other data sources
defining cube objects that use the contents of the model to create PowerCubes or cube groups
You can have multiple data source queries associated with each package or report you create as a data source.
When the IBM Cognos 8 data source is a relational or dimensionally modeled relational package, you can import query items to create your structural or transactional queries.
You can create data source queries using IBM Cognos 8 reports created in Query Studio or Report Studio using relational or DMR packages. You cannot create data source queries using IBM Cognos 8 OLAP reports. Data source queries using IBM Cognos 8 reports perform most efficiently when the report is a list. Graphs, dashboards, crosstabs, and complex reports do not provide appropriate data to cube builds and therefore cannot be used for data source queries.
Do not import dimensional packages using the Insert Data Source command, if you want the dimensional structure maintained. Use the Insert Dimension from Package command instead.
When using an IBM Cognos 8 report created in Query Studio or Report Studio, or an IBM Cognos 8 package as a data source, you may be asked to provide values for existing prompts. For more information about prompt support, see Edit Existing Prompts in IBM Cognos 8 Reports and Packages.
When you create a data source using an IBM Cognos 8 report, and the report includes data in multiple languages, some of the language characters do not display properly in the Transformer Data Source Viewer. These characters are displayed as -- .
Transformer does not support reports with multilingual data as a data source. When the operating system locale is properly set, Transformer displays the characters for that locale.
SAP BW packages can be used to import fact and dimensional data. For information about preparing SAP queries and creating packages in Framework Manager for use in Transformer, see Guidelines for Working with SAP BW Data for Use in Transformer. For information about creating an SAP package-based model, see Steps to Create a Model Using an SAP BW Package.
From the Transformer Welcome page, click Create a new model.
Tip: If you are already in Transformer, click New from the File menu to open the New Model wizard.
Type a name for your new model and click Next.
In the Data source name box, enter the name of the IBM Cognos 8 data source and, in the Data source type box, select Package or Report.
Tip: If you want the data source name to default to the name of the package or report that you select in the next step, leave the Data source name box blank.
Click Browse to open the metadata browser and select a package or report from the available list.
Tip: You can also click the drop-down arrow to select a recently used package or report from the list.
In the Browse Metadata dialog box, select the package or report to use for the data source, and click OK.
Click Next.
If you select a report as a data source and it contains mandatory prompts, provide values for the prompts.
Transformer cannot execute queries if values are not provided for mandatory prompts. For information about prompts, see Edit Existing Prompts in IBM Cognos 8 Reports and Packages.
In the Query name box, type a name for the new query.
Note: When you use the New Data Source wizard to create a new data source, you create one query at a time.
In the Source list, select the query items to import and click Add to add the selected query items to the Transformer query.
If the data source is a report and the report contains multiple queries as in the case of some Report Studio authored reports, each query will be shown with its relevant query items.
Tips:
Report Studio authored reports will show queries that are associated with the list. The relevant query items for those queries will be available for use in Transformer.
If the data source is a package with dimensions, you can import the dimensions using the Insert Dimension from Package option.
Click OK.
If you want Transformer to automatically create a preliminary dimensional structure for you, on the last page of the New Model wizard, select the Run AutoDesign check box.
Click Finish.
If you are prompted for data source connection and signon information:
Select the connection and click OK.
Choose whether to enter a valid user name and password for the current session, or create a Transformer signon for the current and subsequent sessions:
When you enter a valid user name and password and click OK, the signon will be used only during the current Transformer session. The signon will not appear in the Signons list, and is not saved in the model.
To create a Transformer signon that appears in the Signons list and is saved in the model, enter a valid user name and password and select the Create a Transformer signon from the user name and password or select an existing one for use with this data source check box. In the Transformer Signon box, click the drop-down arrow to select an existing signon, or click the Add button to open the Signon dialog box to create a new signon. In the Signon name box, type the signon name and specify whether to prompt for a password and click OK. If you do not select the Prompt for password check box, in the Confirm Password dialog box, re-type the password and click OK twice.
For information about creating Transformer signons, see Create a Signon.
If you want to add another query to the package or report data source, click Yes when prompted to add another query, and repeat steps 8 to 9.
Save the model.
Tips:
By default, Transformer saves models in the My Documents/Transformer/Models directory. You can set the default location to which Transformer saves models by changing the Models directory setting on the Directories tab of the Preferences property sheet on the File menu.
On Windows Vista, Transformer saves models in the Documents/Transformer/Models directory.
When prompted to save in binary (.py?) or text (.mdl) format, use the latter when exporting models or, to avoid possible fragmentation problems, when you have made a lot of changes since your last save action.
In addition to saving your models in .mdl format, you should regularly use the Check Model command from the Tools menu to help you diagnose and resolve any problems in your model design.
In Windows, the .pyj model file extension is not automatically associated with Transformer version 8.x. To open a model in Transformer by double-clicking the .pyj file, you must first create the association in Windows.
Use the Data Source Viewer to view sample data and, for supported data sources, the Cognos SQL or the native database SQL.
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.
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.
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.
From the Transformer Welcome page, click Create a new model and click Next.
Tip: If you are already in Transformer, click New from the File menu to open the New Model wizard.
Type a name for your new model, and click Next.
In the Data source name box, type the name of the data source and, in the Data source type box, select one of the available options.
Click Next to specify information about the data source.
The parameters depend on the data source type that you selected on the previous page.
If you selected an IQD data source, set the Isolation level. When you use .iqd files generated from IBM Cognos Impromptu, or externalized query files from any version of Framework Manager, ensure that the Series 7 IQD Bridge component is installed. This component must be installed on IBM Cognos Series 7 version 7.4 supported platforms. For more information, see Isolation Levels for an IQD Data Source.
For a Microsoft Access or Excel database, a table name or a named range of cells from the Excel worksheet must be specified.
For more information about the parameters required for each data source type, see Data Source Types.
Click Browse to open the data source browser and select a data source from the available list.
Click Next.
Specify whether or not to Run AutoDesign, and click Finish.
Confirm that your selected items appear as expected in the Data Sources list.
Tip: To view sample data or the SQL from your data source, from the View menu, click Data Source Viewer.
Save the model.
Tips:
By default, Transformer saves models in the My Documents/Transformer/Models directory. You can set the default location to which Transformer saves models by changing the Models directory setting on the Directories tab of the Preferences property sheet on the File menu.
On Windows Vista, Transformer saves models in the Documents/Transformer/Models directory.
When prompted to save in binary (.py?) or text (.mdl) format, use the latter when exporting models or, to avoid possible fragmentation problems, when you have made a lot of changes since your last save action.
In addition to saving your models in .mdl format, you should regularly use the Check Model command from the Tools menu to help you diagnose and resolve any problems in your model design.
In Windows, the .pyj model file extension is not automatically associated with Transformer version 8.x. To open a model in Transformer by double-clicking the .pyj file, you must first create the association in Windows.
If you imported measures that are not in scope for a particular dimension, or that apply to more dimensions than your report users need, remove the extra items. Alternatively, ensure that the scope is set correctly between the dimensions and the fact query before you proceed.