Cognos User Group Framework Manager and Transformer Tips

Cognos User Group Framework Manager and Transformer Tips August 6, 2010

Topics • Framework Manager – – Using filters Governors Diagram settings “As View” query subjects vs. “Minimized” • Transformer – – Using categories for counts Creating “Bucket Dimensions” Time dimensions When to use a DMR model (in Framework Manager)

FM - Filters Filter Options • Embedded (within query subject) – Required – Optional – Design Mode • Stand-alone – Only way to make hard-coded filter optional – Use screen tip so that you know what the filter is ! • Studios

FM – Governors • Use to improve performance by reducing system requirements • “Big 5” Infolink has found useful: – – – Max Rows Retrieved Query time limit Large Text (BLOBS) limit Cross-Product joins Usage of local cache

FM – Diagram Settings • Context Explorer vs. Diagram tab – Use Navigate Diagram to control defaults • Shrink to fit • Layout control • Settings control

FM – “As View” query subject • Default is “Minimized” • How does “As View” work? – Creates in-line view – Controls order of query processing • Why use “As View” – Accuracy – Merged query subject outer join example

Transformer – Category Counts • 3 types of measures in Transformer – Column – Calculated – Category Count • Category Count type measure is used to provide a distinct count of members among dimension levels of a level you specify in the Dimension Map. (note 65, 536 limit)

Trans – Category Counts continued • You can create a category count using multiple columns from a data source. • Example: Unique employee is determined by Employee ID + Employee Record. We want to count this within our dimension levels

Trans – “Bucket Dimensions” are useful when you want to categorize measures (like employee count) into pre-determined buckets that you define within Transformer. In our example, we create our buckets using calculated columns in the data source. (Note: you can do this in FM as well)

Trans – Time Dimension • Standard Time Dimension – Cognos automatically builds dimension levels from a date using a wizard. – Relative time – Adding additional drill path • Custom Time Dimension – Is a regular (non-time) dimension – Create your own time categories and sorting

FM - DMR • Dimensionally Modeled Relational Data (DMR) – Provides OLAP Cube style reporting – Useful for real time reporting on small data sets that have a good framework model – Drawbacks: • Performance (typically slower than Power. Cubes which are pre-aggregated) • Consumes database server resources with report queries