Wednesday, August 12, 2009

Migration: Best Practice to Replace IQD's

The first challenge in the post migration phase is to gradually reduce dependence on Impromptu Query Definitions (IQD) as a data source in Transformer.
The reasons for moving away from IQD’s are:
1. IQD’s are files and hence should be avoided
2. You might need to maintain Impromptu licenses
3. If you have created IQDs using Framework Manager then you still would be building cubes using an externalized file.

The IQD’s can be replaced using Report Studio Report or a Framework Manager Package. In both the cases the source has to be pre-validated to avoid cross joins and other resource intensive sql.

Using Report Studio Reports as source for Transformer Cubes
While working with RS reports, we should keep in mind that we should not use objects like crosstab, repeaters etc. We can only work with LIST type reports.
Pros:
-The report would have been tested and simplified.
-Any change or update to the query can be managed using Framework Manager or Report Studio.
-Complex Calculations can be performed

Cons:
- Transformer only picks the fields that are in the report layout and not all those in Query.
- If the query is not dedicated for transformer use then we could be querying unnecessary columns. Layout calculations and prompts would have to be modified
-Reports specifications have to be updated if we need to add new fields to the transformer model
-We would have to ungroup columns and any counts based on grouped values would be lost too
-If the Transformer is based on ( structured and Fact queries) we would have to create multiple queries


Relational Package as a source for Transformer Cubes:
Pros:
- Importing relational sources gives more flexibility to the transformer modeler.

Cons:
-If the package is not designed well and requires updates
- Cross joins could be executed, if for example a single package with multiple namespaces was published. We could be creating dimensions from completely different namespaces.

DMR Packages as source for Transformer Cubes:
Pros:
- DMR packages offer visibility and predictability
- Developers can import dimensions from DMR package directly into the Transformer dimension map area.
-This make sure that dimension structure is standardized across the company

Cons:
-If a particular Transformer model requires a different hierarchy then we will have to maintain multiple versions of the dimension in FM
-We have to make sure Measures are allocated to all the lowest levels in all dimensions
-It takes more modeling resource/time as we have to generate dimensions and star groupings from Relational schemas.