Because the New Model wizard lets you specify only one data source, including one query at a time for each package or report data source, we recommend that you use the wizard to add the structural hierarchy (dimensional data) that is important to your business.
If your data source is an IBM Cognos 8 package or report, you can use the Add Query From Package or Report option to add an additional query to the data source. For all other data source types, you can use the New Data Source wizard to add all the other data sources required for your model.
As the modeler, you must ensure that each data source has a unique name and specify, if true, that all your category codes and source values are unique. This precaution significantly reduces
the possibility that end user reports will be negatively impacted by changes in category codes
drill-through problems
other errors that can arise in distributed production environments
For more information, see Recommendation - Resolve Uniqueness Problems in Your Data Source.
Tip: If you imported your dimensions to the Dimension Map from a package using the Insert Dimension from Package option, you can add another dimension from that package to the Dimension Map using the same option. This adds the additional query to your package data source and completes the AutoDesign process on the Dimension Map. If you add a query to an OLAP package data source using the Add Query From Package or Report option, you will need to manually construct the dimension.
Right-click the empty space in the Data Sources list for your model and click Insert Data Source.
Tip: If the Insert Data Source option is unavailable, you may have right-clicked a package or report data source.
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 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.
Click Next.
In the Query Name box, type a name for the new query.
In the New Data Source wizard, 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 you want Transformer to create preliminary dimensions in the Dimension Map, select the AutoDesign check box.
Click Finish.
You will be prompted to add another query. If you want to add another query, click Yes and repeat steps 6 and 7. If you click No, a new data source containing the specified metadata appears. The source columns appear in the Data Sources list and, if you selected Run AutoDesign, a preliminary dimensional structure appears in the Dimension Map.
Tip: Use the Data Source Viewer to view sample data and, for supported data sources, the Cognos SQL or the native database SQL.
Right-click the empty space in the Data Sources list for your model and click Insert Data Source.
Tip: If the Insert Data Source option is unavailable, you may have right-clicked a package or report data source.
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.
IBM Cognos 8 package and report data sources are shown in the Data Sources list as a single data source with multiple associated queries. When you use the New Model wizard or New Data Source wizard, you can add only one query at a time.
To add a second query to your IBM Cognos 8 package or report data source, use the Add Query From Package or Report option. Add Query From Package or Report opens the Query Definition dialog box, where you can create the new query. When you create the new query, it is imported as an additional query under the package or report data source.
Tip: If you imported your dimensions to the Dimension Map from a package using the Insert Dimension from Package option, you can add another dimension from that package to the Dimension Map using the same option. This adds the additional query to your package data source and completes the AutoDesign process on the Dimension Map. If you add a query to an OLAP package data source using the Add Query From Package or Report option, you will need to manually construct the dimension.
You should not import query items from different query subjects unless the query was intended for or works well with the data for that scenario. Adding query items from multiple query subjects in a single import should be avoided, as it can result in cross-joins or queries that are not valid.
When adding queries from an IBM Cognos 8 report, Transformer displays the source query using the Query name specified in the query definition (for example, Query 1.0). Transformer does not display the query name as defined by the Query Name property in Query Studio or Report Studio.
In the Data Sources list, right-click a package or report data source, or a query under the package or report data source and click Add Query From Package or Report.
Tip: If Add Query From Package or Report is not available, you may have right-clicked the area outside the data source.
The Query Definition dialog box opens, showing the metadata from your source package or report data source.
In the Query name box, type a name for the new query.
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 Query definition details box.
Click Finish.
A new data source containing the specified metadata appears under the original data source. The source columns appear in the Data Sources list.
Tip: Use the Data Source Viewer to view sample data and, for supported data sources, the Cognos SQL or the native database SQL.
As the modeler, you must ensure that the columns in your model reflect the current state of your data sources. There is limited error-handling. For example, an error message is issued if you try to add a query item that is already referenced in your model.
You can quickly resynchronize your model columns after a data update. You can add data source items to the model or remove columns from the model. You can also resolve mismatches between data source items and columns in the model. For example, if you rename data source items, Transformer may no longer be able to match those items with their corresponding columns in the model.
You can choose to resolve mismatches manually or let Transformer attempt to automatically resolve mismatches. For IBM Cognos 8 data sources, Transformer presents options for matches for your review. If Transformer determines that mismatches are caused by a structural change in the data source, Transformer provides a list of locations in the data source that may be possible matches for mismatched columns. If none of the locations provided are appropriate, or if Transformer is unable to suggest any locations in the data source, Transformer provides a list of items in the model that are similar to the source reference of the column. For each possible match, Transformer shows how closely the references match by using a percentage. If none of the items are appropriate, you can leave columns unmatched.
You can quickly resynchronize your model columns after a data update if your model uses a single query based on a text data file, an IQD, or an IBM Cognos 8 package or report. Resynchronize your model by invoking the Modify Columns command from the Tools menu.
In the Data Sources list, select the query whose columns you want to modify, and from the Tools menu, click Modify Columns.
For data source items that do not appear as columns in the model, select the items in the Source list and click Add.
For columns that you want to remove from the model, select the columns in the Model list and click Remove.
For unmatched columns identified by a plus sign (+) in the Matched to Source column in the Model list, do one of the following:
To manually match query items to columns, select a data source item in the Source list and a column in the model, and click Match.
To allow Transformer to automatically match query items to columns, click Auto Match. Review any messages that appear and click OK.
Data source items are now synchronized with the model.
In the Data Sources list, select the query whose columns you want to modify, and from the Tools menu, click Modify Columns.
If there are columns in the model that cannot be matched to data source items, you will get a warning message. Click No to keep these unmatched columns in the model.
If you click Yes, Transformer deletes the unmatched columns.
For data source items that do not appear as columns in the model, select the items in the Source list and click Add.
Tip: Click Refresh Source to refresh the source list for the data source. Click Validate to check whether columns in the model violate any Framework Manager governor settings.
For columns that you want to remove from the model, select the columns in the model list and click Remove.
For unmatched columns identified by and X in the Matched to Source column in the model list, do one of the following:
To manually match query items to columns, select a data source item in the Source list and a column in the model, and click Match.
To allow Transformer to automatically match query items to columns, select the columns in the model and click Auto Match.
If Transformer provides one or more locations in the data source that may be appropriate for unmatched columns, do one of the following:
If one of the locations is an appropriate match, select the location and click Next or Finish.
If none of the locations are an appropriate match, click Match by reference instead and click Next. From the Candidates list of data source items, click the one that you want to match to the column or click Leave unmatched.
Repeat step 6 for each mismatched column that Transformer attempts to resolve.
For any mismatched items that Transformer cannot suggest locations for, Transformer presents possible name changes for your review. From the Candidates list, click the item that you want to match to the column to or click Leave unmatched.
Data source items are now synchronized with the model.