When publishing a package, you have the option to externalize query subjects and dimensions into formats that you can use in Transformer or other applications. Special considerations must be given when externalizing models based on SAP BW metadata. For more information, see Guidelines for Working with SAP BW Data in Other Applications.
You first define how each object will be externalized by specifying a method to use. When you publish the package, you specify that the query subjects and dimensions are to be externalized.
If you specified a maximum number of rows to be retrieved in the Governors dialog box, this setting is ignored.
You have several options for the externalization method.
Use the Default method to specify the objects in a package that you do not want to be externalized. To improve performance, you may not want to externalize all objects in a package.
Use the CSV method to generate a comma separated file that contains the results of the query subject. In a CSV file, the first row represents the column names and each of the following rows contains one record from the query result set. One file is generated for each query subject or dimension that is set to be externalized.
With the CSV method, you can use locally processed functions to create a dataset for use in Transformer. You can process Cognos SQL locally or on the data source, and capture the result set in a file that can be used in Transformer.
The generated file is restricted to 2 GB in size and contains data based on the native encoding of the current operating system. For example, for Windows 2000, this is specified by the default system locale in the Windows regional settings. For Windows XP and 2003, this is specified by the language for non-Unicode programs option in the Windows regional settings.
This option is intended for use only with Transformer. For any other purpose, we recommend that you use the tab method.
To externalize SAP BW query subjects, use the CSV method. For more information, see Guidelines for Working with SAP BW Data in Other Applications.
Use the tab method to generate a tab delimited file that contains the results of the query subject or dimension. The generated file can be used directly as a data source. The generated file contains data based on Unicode using UTF-16 LE (Little Endian) encoding with BOM (Byte Order Mark). One file is generated for each query subject or dimension that is set to be externalized.
This method does not work with Transformer because Transformer does not support Unicode. Use the CSV method to create files for Transformer.
Use the IQD method to generate a query definition file to be used in Transformer. One file with Native SQL is generated for each query subject or dimension that is set to be externalized. The generated file contains data based on the native encoding of the current operating system. For example, for Windows 2000, this is specified by the default system locale in the Windows regional settings. For Windows XP and 2003, this is specified by the language for non-Unicode programs option in the Windows regional settings.
The query subject must not require any local processing. It must be able to be run entirely on the data server. We recommend that you test the query subject by setting the query processing for this data source to database only. An error message then appears if the query subject requires local processing.
If you must use locally processed functions to create a dataset, we recommend that you use the CSV method. With the CSV method, you can process Cognos SQL locally or on the data source and capture the result set in a file that can be used in Transformer.
Stored procedure query subjects can be externalized for use in Transformer. The stored procedures must not contain any parameters.
Note that the IQD method will continue to be supported in this release but will not be enhance. For more information, see Deprecated Features in Version 8.3
You can specify that the output be aggregated or grouped or both. By default, Framework Manager returns rows at the detail level without applying any aggregation or grouping. This property is used when you want to have relational data aggregated when it is externalized. Specify determinants for the query subject before externalizing it.
Use the Externalize Auto Summary property to apply the setting of the Regular Aggregate property to query items whose Usage property is set to fact.
If you want to have a specific order of items in the Group By clause, specify determinants first, and then set the Externalize Auto Summary property.
You can use the Externalize Auto Summary property with all externalize methods.
Framework Manager supports strings, integers, and dates. It does not support time dimensions. We recommend that you use a date key on the fact query subject in Framework Manager, and let Transformer generate the time dimension.
If a shortcut is included in a package and it points to a query subject that has been externalized, the shortcut will also be externalized. The name of the data file is the name of the query subject to which the shortcut points. If more than one shortcut points to the same query subject, then the query subject is externalized each time the shortcut is encountered.
Native SQL is used to generate an IQD, so the native SQL produced when externalizing must run successfully.
Some queries cause more than one query to be issued, or local processing to be performed to retrieve data, or both. To prevent this, ensure that the Query Processing property for all data source objects in the model is set to Database Only. For more information about query processing, see Improve Performance by Setting Query Processing Type.
We recommend that you follow this process to externalize dimensions:
 | Create a model query subject or a data source query subject
that contains the dimensions you want to externalize |
| | Add any filters that you require. For information about filters
for relational metadata, see |
| | In the Properties pane, set the Externalize Method property to the method you want. |
| | Publish the package to externalize the dimensions you selected |
