Cognos Framework Manager Best Practices

FRAMEWORK MODEL

Business Views should be created which has the Shortcut from the base view that has only the required easily identified query subjects and Item names. This reduces the maintenance and makes the model more understandable.

• 4 layered model is recommended as it helps in following way: 

1. Suitable for transactional databases. 
2. Can be used separately for Report Studio/Query Studio and Analysis Studio. 
3. Can segregate relational objects (Query Subjects) and dimensional Objects ( Regular and Measure dimensions) properly. 
4. Supports drill up/drill down functionality. 
5. Provides better scalability and supports new object inclusion. 

• Database layer should not be used for reporting. 
• Shortcuts should be used wherever possible, instead of copies. Shortcuts make it easier to maintain metadata because changes to the target object are reflected in the shortcut.
• While publishing the package, base view should not be included. Only the Business Views with Filters should be included. 
• Cardinality describes the association between two query subjects and is set at each end of the relationship. It should be ensured that the cardinality addresses business needs. Following are the considerations to take while defining cardinality: 

1. Dimension – Dimension (During a SnowFlake) – Could be (1..1 for a inner join or 0..1 for a outer join. 
2. Facts - Dimensions (For a inner join specify only 1..n (1 on the dimension table side) if any of the reports involve measures from the more than one Fact Table, specifying 1..1 does not form the correct query when the report involves more than one fact table. 
3. For outer joins, 0..n may be used 

• Use dimensional information to specify the relationship between levels in a multi-level dimension. In order to define the behavior expected when querying at a one or more levels of time for example, dimensional information is used. Levels are defined for years, quarters, months, and days. A key is defined for each level and in this example, that key is sufficient to uniquely identify the level.

• Security Implementation: There may be following types of security through Framework Manager Model 

1. Data Level Security restricts the data relevant to the user logged in. 
2. Object Security enables user to access only relevant Report Objects 

• Specify user and administrative access to the package. 
• Only the required tables and DB objects (Views, Stored Procedures and Functions) should be imported into the model. 
• Avoid having Orphan Tables 
• Filters should be generic so that they may be used across all levels. 
• All the calculations should be at the ETL (Data Loading). In case of customized requirement, it should be done at Framework Manager and not at Report Level. 
• Separate namespaces should be used when two objects of the same type having the same name is required. 
• Source code control repository should be used whenever possible to restrict access and track changes to the projects and segments. 
• Separate views for each user group should be created. 
• Many to Many relationships should be avoided as they generate stitched queries. Instead Star Schema grouping should be used. 
• Recursive relationship should be converted to fixed hierarchy using shortcuts. 
• In a fact table query subject, exposing any query items that are not facts (measures with an aggregation rule) should be avoided. If necessary, they should be exposed as a separate model query subject that looks like a dimension. 
• Whenever possible reduce the number of query subjects. Represent each discrete business concept with a single model query subject. 
• Conformed dimensions should be given the same name in each namespace where they appear. That’s the only way the users can tell that they are conformed. 
• If two fact tables have conformed dimensions, but different levels of granularity for that dimension, then for each fact table only the relevant levels should be included. 
• Only the objects that will be reported on by users should be published in a package. 
• Surrogate keys should be hidden while publishing a package. 
• Although there isn’t a hard coded limit to the number of objects that can exist in a model, but it’s always recommended to keep it as smaller as possible to avoid the complexity and to improve the performance. 

• It is recommended to use determinants to identify certain levels of aggregation within the query subject. This is particularly useful when dealing with multi-fact and multi-grain queries. 
• The “usage” property should be set properly to avoid ambiguous results while reporting 
• Query Subjects and Query Items can be renamed for better understanding. 
• Avoid loops while creating relationships, by using shortcuts, as they behave unpredictably while reporting. 
• Generic filters or calculations should be created in Framework Manager. 
• It’s not recommended to allow outer joins as it decreases the report performance. 
• Its recommended to create a view that caters to a group of reports, which makes the reporting easier to maintain.