Metadata Modeling Best Practices -


Metadata Modeling Best Practices

June 19, 2014

Data Prep & Modeling

IBM Cognos Framework Manager

Don’t be intimidated by metadata modeling!  During this webinar, our expert trainer Albert Valdez breaks down the role of metadata modeling with Framework Manager. 

Albert explains how, and more importantly why, we implement:

  • Multi-tiered metadata.
  • Row-level security using session parameters.
  • Dynamic Query Mode (DQM) and JDBC for OLAP Over Relational (OOR), or Dimensionally Modeled Relational (DMR) Packages.
      • See the benefits of the dimensional package from the user’s perspective.
      • Unravel the mysteries of JDBC and the benefits of the DQM.

Increased productivity for data model development. The data model is the Business Intelligence (BI) foundation for performance management, operational and financial reporting. Speedier development means earlier realization of the business benefits of timely decision support information. It also means shorter IT queue times for business’ service requests for data access.


IBM Cognos BI Developers; Business Intelligence Managers; IT Cognos and BI Support Staff

Framework Manager Demonstrations: Context

  • All demonstrations are based on the standard IBM Cognos 10.2.1 Great Outdoors Sample package.
  • Demonstrations are valid for ALL supported versions of IBM Cognos Business Intelligence.
  • These exercises are part of what we teach in our classroom, specifically from our Metadata Modeling Best Practices with Framework Manager course.

Best Practices for Project Design: Advantages for Multi-tiered Metadata

  • Metadata Modeling for Best Practices
    • Use a multi-tiered design.
    • Establish query item properties and relationships early on (in the first tier).
    • Plan your design with consideration to how the IBM Cognos query engine generates SQL.
    • Define Model Query Subjects as either a “true” Dimension or a “true” Fact.
    • Relate Facts to one another through one or more Conformed Dimensions.
    • Review and carefully define Determinants.
    • Define Package and Object-level Security.
    • Create a user-friendly Presentation.
    • Use Dimensional Modeling techniques where appropriate.
  • Multi-tiered Design
    • Use Namespaces and Folders to organize your Query Subjects.
    • Start with an Import View.
    • There should be at least two tiers, however a three-tiered design offers some advantages.
  • Advantages of Three-tiered Designs
    • Import Layer
      • All database objects are represented here exactly as they appear in the physical data, making references between this metadata and the database simple and obvious.
      • All base relationships should be defined in this layer.
    • Design Layer
      • Establish the “dimensional design” that will guide the query engine toward more predictable results.
      • Use Model Query Subjects, Merging, Aliasing, and other techniques to enforce desired query behaviors.
    • Business Layer
      • Create value-added features such as embedded and stand-alone Calculations and Filters.
      • Implement user-accepted naming and presentation conventions.
  • Why?
    • Use metadata to your advantage.
      • Query logic is controlled by our design, not restricted by the physical limitations of the database.
    • Referential metadata creates a “buffer” between the physical model and what is used by our authors.
      • Changes to our Database layer in order to address changes in the physical data can be made without impacting existing reports, or the end-users’ view of the model.
    • Simplify the modeling process by isolation certain tasks within their respective layer.

Best Practices for Project Design: Advantages of Multi-tiered Metadata

  • Demonstration

Row-Level Security: Using Session Parameters and Query Macros

  • Parameterized SQL
    • Typical application of this technique is to create dynamic filtering based on user attributes.
    • We can use a Parameter Map and Query Macros in combination with unique user account attributes to enforce these rules in the metadata.
    • Session Parameters:
      • Attributes unique to each user’s session.
      • Derived from your Authentication Provider.
  • Parameter Map: Three Types
    • Manually key in the values in the project.
    • Import from an external text file (.txt, .csv).
    • Reference a query subject (db table).
  • Query Macros
    • #$UserLookup{$account.personalinfo.userName}#
    • Several functions available, allowing for the creation of dynamic expressions.

Row-Level Security: Session Parameters and Query Macros

  • Demonstration

OLAP in Framework Manager: Setting Up for Success

  • OLAP in Framework Manager
    • In the Cognos version 8 release, we were introduced to Dimensionally Modeled Relational (DMR) capabilities.
    • What happened?
      • Run-time OLAP presents significant query response challenges in large data environments.
      • Guess what? We ALL live in large data environments!
    • What’s changed?
      • There is a shift in the architecture strategy, adding more query processing capacity on the application tier.
      • Dynamic Query Mode presents a new approach to query processing.
    • Dynamic Query Mode
      • Next-generation, Java-based query mode.
      • Query optimizations to address query complexity, data volumes and timeliness expectations with improved query execution techniques.
      • Significant improvement for complex OLAP queries through intelligent combination of local and remote processing and better MDX generation.
      • Security-aware caching.
      • New data interfaces leveraging 64-bit processing.
      • Take advantage of increased capacity of 64-bit in-memory processing.
      • Ease of maintenance with query visualization.
    • Why are OLAP sources important?
      • True MDX-style analytics and reporting provide tremendous benefits to end-users.
        • Drill-up/ drill-down functionality is built-in to the data source, encouraging simple, intuitive data investigation.
        • Access to MDX functions and gestures in reporting tools allows for more dynamic reports, dashboards, and analyses.
      • Data Exploration and Data Discovery are the next level of analytics (move beyond reporting!).

OLAP in Action: OLAP Vs. Relational for Self-Service Authors

  • Demonstration
  • OLAP in Framework Manager
    • What needs to be done to enable DQM?
      • Cognos 10.x? You own it.
      • The Query Service spawns its own java process, consumes resources whether you use it or not.
    • Data Sources
    • Must configure the JDBC Connection and copy the requisite driver to the Cognos library.
    • Framework Manager
    • DQM can be switched on at the Project or the individual package level.
    • System Administratior
    • Configure Query Service resources, optimize application tier components to maximize throughput.
    • JDBC Drivers – what to do?
    • Basically, you download and copy the requisite JDBC driver to both the ..v5dataserverlib and the ..p2pdweb-inflib directories.
    • Make sure you only have ONE version of the driver in the library!
    • Specific instructions for each database type are detailed in the above document.

IBM Cognos Framework Manager 10; Cognos BI 10


Albert Valdez
Vice President of Learning Solutions, Senturus

Albert has spent the last 13 years as a technical trainer focused on business intelligence and performance management.  Before joining Senturus, he was a Senior Education Specialist at Cognos for five years.  He is an IBM Cognos Certified Trainer and has his lifetime CTT and certification from CompTIA.  Since 2007, Albert has acted as a Senior Consultant, Solutions Architect and Director of Education for Senturus.