Do We Have Define Joins Again in Business Layer in Cognos Fm

In Oct 2008, we published the Insight "Best Practices in Modelling IBM Cognos 8 Semantic Layers" on our website. Since then, this page has become the most visited folio on our website, with over 50.000 pageviews since publication, conspicuously showing the need for the topic. Given IBM Cognos 10 now is on the market for a while, nosotros decided to update the Insight to incorporate new IBM Cognos 10 functionality. We thus are proud to nowadays : "Best Practices in Modelling IBM Cognos 10.2 Semantic Layers".

IBM Cognos Business organisation Intelligence Server offers written report authors a single platform to create reports, dashboards, events and perform analysis on multidimensional data.

All users connect to the Cognos BI server using a cypher footprint web portal: IBM Cognos Connectedness. Cipher Footprint means no boosted software or applets are installed on the client PC. It provides a unmarried point of entry for all corporate data and the tools to analyse this data. The portal contains all available reports, assay, dashboards and offers advanced sharing, publishing and security features. IBM Cognos Connection volition provide interaction with other modules from the Cognos family unit such every bit IBM Cognos Insight or IBM Cognos Controller.

Effigy 1: Cognos x.2.1 Cognos Connection Portal

From IBM Cognos Connexion all the end user applications can be launched. Each 1 has a specific functionality focus:

• Cognos Workspace: build corporate dashboards using pre-made components
• Cognos Workspace Advanced: perform multi-dimensional analysis and create basic reports
• Written report Studio: perform avant-garde, pixel perfect reporting with complex queries
• Consequence Studio: create agents to follow-upward on triggers
• Cognos Insight: practise self-service analysis and share these insights with the enterprise
• Query Studio (legacy product): perform basic reporting using basic queries and formatting
• Analysis Studio (legacy production): perform multidimensional analysis

All these tools share the aforementioned semantic layer built with IBM Cognos Framework Manager.

Semantic Layers

The purpose of a semantic layer is to create a business representation of corporate data. This representation hides database complication to the end-user by creating an intuitive model. The semantic layer maps complex data into familiar concern terms and shields cryptic database language from the end-user. This makes it very easy for a business user to create his own reports as the terminology used is very recognizable.

Concern users are insulated from underlying data complication while IT maintains governance over the use of data sources. Past using a single version of the truth and the apply of consistent terminology, cease-user productivity is increased equally the self-servicing aspect of business concern intelligence is strengthened.

A semantic layer can handle multilingual features and consolidate different database sources and/or OLAP cubes. This enables the use of different databases fifty-fifty from unlike vendors- or OLAP cubes in a unmarried semantic layer, enabling the ability to use these transparently in a unmarried written report.

IBM Cognos 10 Business concern Intelligence uses 2 metadata tools: Framework Managing director and Cube Designer. The metadata modeling tools within Cognos Business organisation Intelligence are client-server applications. All end-user based tools are accessed from Cognos Connection.

• Framework Manager is used to create relational and dimensionally modeled relational models (DMR), called frameworks.

• Cube Designer will model Dynamic Cubes, a recently introduced cubing engineering that replaces Powercubes. Cube Designer is across the scope of this insight, but in the near time to come a new insight concerning Dynamic Cubes will be released.

Effigy 2: Cognos 10.two.ane Framework Managing director

Flexible Models

Model flexibility can exist defined from ii different points of view. How easily can the model be adapted to changing conditions and how easily can the user generate ad hoc query requests? Both questions tin be answered by using star schema modeling.

The dimensionally modeled database is ideal for reporting and is frequently referred to as a data warehouse. In a data warehouse facts and dimensions are established and information is stored at the lowest granular level. In every data warehouse a number of star schema's are nowadays. The central table represents the fact table and only contains numeric and condiment measures. The satellite tables correspond the set of dimensions that tin can be used to expect at the measures from different angles.

By using conformed dimensions, a "information warehouse motorbus is established. Conformed dimensions are dimensions used by multiple fact tables. This method of modeling enables executing multi-fact, multi-grain queries ensuring a predictable, clean set of results. When new facts or dimensions are added, they tin can be quite easily added to the model, representing a new star schema.

Even so not all IBM Cognos Frameworks need to be build on dimensionally modeled databases. Sometimes a data warehouse is not bachelor and reporting is enabled directly on an OLTP (On Line Transaction Processing)-database, used in an operational system like an invoicing or order entry system, or an operational data store. These types of databases are modeled relationally and are highly normalized.

There are a number of drawbacks to do reporting on a relational model. The first drawback is query performance, a highly normalized model will pb to dozens of tables in a single SQL argument, leading to big execution plans and slow performance. Doing such queries on a production environment could even lead to problems with the applications operational performance. Relational information sources also pose a number of modeling challenges for the framework modeler to create predictable query results.

Therefore information technology is recommended to always apply a data warehouse with star schemas as source for reporting.

Query Flavour

When a framework is published, a compiled version of information technology is made available on Cognos Connection, called a package. This package can support 2 query modes: Relational Querying and OLAP-fashion reporting.

With Relational Querying, IBM Cognos will build an SQL statement when a user drags and drops objects on a written report. All objects available in the database can be hands queried at the everyman grain. Drill up/down is not possible in this query mode.

The other way of querying is OLAP styled reporting based on a cube. A cube is a multidimensional store of data. The most common utilize of a cube is to do drill upward/down assay. The drawback is that a cube commonly does non contain all the fields available in the database. Nearly oft figures in a cube are summarized so the lowest grain is not available. Reporting on a cube is still very fast. Frameworks provide a mechanism that permit for OLAP styled reporting without the need of an bodily physical cube. Cognos will emulate OLAP behaviour and will write SQL to call up the data in the background. These types of frameworks are chosen Dimensionally Modeled Relational or in short DMR. With the introduction of Dynamic Query Mode, performance of DMR models tin be boosted to the level of native cubes by using the advanced caching features Dynamic Query Way offers.

Both relational models and DMR models can be supported from a unmarried framework.

Figure 4: Cognos 10.two.1 OLAP Mode Reporting

Figure 5: Cognos ten.two.1 Relational Way Reporting

Framework Objects

A framework uses a number of objects to create a structured model. A namespace creates a qualifying container for objects, avoiding naming conflicts. Within a namespace, the modeler can use folders to group standalone filters or query subjects. Namespaces volition structure frameworks. In a namespace a number of query subjects are added. They correspond the tables in a framework. In that location are three different types of query subjects:

• Data Source query subjects: performs a query on the underlying information source
• Model query subjects: refers to an existing query subject in the model
• Stored Procedure query subjects: used to call up information from stored procedures.

Standalone filters are pre-designed filters that can easily be re-used in the reporting tools by the author.

Figure six: Cognos ten.2.1 Measure out Dimension and Regular Dimension

For OLAP functionality, 2 boosted objects are available: a Measure Dimension and a Regular Dimension. A Measure Dimension contains a collection of numeric values. The Regular Dimension provides the accompanying set of descriptions and identifiers. The Measure out and Regular Dimensions are linked with Scope Relationships to define the level at which the measures are available for reporting.

Creating Durable Models

While creating a model it is important to create a proper structure. The use of a multi-tier structure will shield the stop-user from changes at database level such equally migration to a different database technology, or changes to column or table names. By creating an efficient layered structure, relational models can be modeled into virtual star schemas, providing predictable and reliable query results to the end user.

The first footstep in creating the Framework Model is importing the metadata. This tin can be handled past using the Metadata Wizard. Information technology is good practice to create a separate namespace for every data source that is needed in the framework. On top of the namespace for the data source, a global namespace should be created: the Data Foundation View.

Q uery subjects are linked together using Relationships. When all data source objects are imported, the model should exist scrutinized to verify all relations between the query subjects are correct. It is good practice not to blindly import the relationships. By manually creating the relationships, a much higher level of control is accomplished. Relations should always and only exist created in the Database Foundation View. Mixing relationships at different levels will only cause confusion and incorrect results.

A query subject tin be edited by replacing the standard SQL with custom written SQL. For maintenance purposes, it is even so recommended never to make whatsoever changes in freehand SQL. If you do, the query subject has to exist manually adjusted if changes are made at database level. When changes are made in the database, importing is by far the easiest manner to update the query subjects. You can too use the Update command in the Tools bill of fare to update a single query subject.

Although it is possible to import data from different data sources, the reflection should be made that in that location is a performance penalty in doing this. When the data sources are on different servers or use different technologies, IBM Cognos will not be able to write SQL-statements that will contain objects from both data sources. Instead, IBM Cognos will write 2 queries and sew together these together locally at the IBM Cognos BI server. Therefore it is highly recommended to apply only 1 data source per concrete database platform.

In the Data Foundation view some other tasks need to exist done. By using the proper tab pages, calculations and determinants can be added to the query subjects without making changes to the SQL lawmaking. Embedded/standalone filters should be added and database column names are translated to more understandable business organisation names.

Figure 7: Cognos x.2.1 Calculations on database query subjects

For every query item, the modeler should bank check if the usage is set correctly. The usage of a field can be an identifier, attribute or fact. Facts are numeric, usually additive or semi-condiment data. All indexed columns or columns containing business keys should be set every bit identifier. Attributes are typically all other strings.
For every fact column, the aggregate should be gear up. Other options that should be set are the format, screen tip, clarification These properties are inherited by derived objects at a later stage in the modeling process.

Model for predictable results

The greatest challenge for the model programmer is creating a model that returns proper query results at all times, no matter what columns were selected in the study past the user. When importing from a relational information source, cardinality is detected based on a set of rules.

IBM Cognos uses the following rules:

• cardinality is e'er applied in the scope of a query performed by the user
• i or 0-to-n relationship implies a fact only merely if all relationships to that query subject are 1 or 0-to-n
• 1 or 0 to 1 implies a dimension

This means it is possible that a query subject will comport equally a dimension in one query and as fact in another query. This is typically the case with snowflake dimensions. This state of affairs tin can exist handled by using model query subjects. The model query subject area will logically condense the snowflake into one object, thus enforcing the correct context in every query. However, there is a performance drawback. Condensing multiple tables in a single model query subject will forcefulness Cognos to recall the entire snowflake even when no fields are needed from the underlying tables. Therefore it is meliorate non to condense the snowflake using a model query subject. Instead, model the snowflakes with 1:one relationships. Tables in the snowflake can exist joined using ane:1 relationships instead of one:n relationships. This volition allow the usage of Minimized SQL, retrieving merely the objects that are needed and ensure the proper usage of the query subjects.

Figure 8: Cognos 10.two.1 Context Explorer

Handle Cryptic Relationships

There are two types of relationships that can provide inconsistent outcome sets if not handled by the modeler. The beginning occurs when in that location are multiple valid relationships. This typically occurs betwixt facts and dimensions. In a fact table, a dissimilar dates are present: invoice date, transport date, order date all point to the engagement dimension. Combining multiple dates in a single query will no longer return results.
Another issue occurs when treatment recursive relationships. The archetype example is the managing director employee relation. An employee has a manager. The director is an employee and as well has a manager that once more is an employee.

These situations can be handled by creating multiple model query subjects for every occurrence. You would however have to reset all the backdrop of every model query subject created leading to unnecessary work. A convenient solution to this problem is using shortcuts. In that location are 2 types:

• Regular Shortcut: reference to the source objects but inherits all backdrop including relationships
• Alias Shortcut: behaves independently of the source object, so different relationships tin exist prepare

The creation of multiple alias shortcuts on a table that use unlike relationships will handle these ambiguous relationships graciously. Regular shortcuts volition be used while creating the Presentation View.

Multi fact multi grain queries

A determinant is needed to place levels of aggregation within the query subjects. This is a particularly useful characteristic when dealing with multi-fact, multi-grain queries. When you have a sales fact at day level and a target fact at calendar month level, combining both facts in a single query would lead to incorrect results. The targets would be multiplied several times as they are stored at calendar month level and non at day level. Determinants volition change the default behaviour of the query. Cognos will recognise the deviation in grain and will write ii queries that will be stitched together to return proper results at the proper grain.

Figure 9: Cognos ten.2.i Determinants

The key element in performing multi-fact multi-grain queries is by using a conformed dimension shared between both fact tables. When retrieving 2 measures from two unlike fact tables using a different granularity, Cognos can decide the right aggregation when determinants are specified. A determinant will specify what prepare of columns will uniquely define a set up of columns. Each level is specified identifying the key and attributes that belong to a level. The lowest level is marked unique.

This volition enable the study developer to create a report showing revenue at week level versus month figures without double counting the everyman grain fact. Cognos uses the machinery of stitch queries to perform these types of requests. A stitch query volition perform a full outer join to break queries into multiple selects, one for each fact table and so run up the data back together. Determinants are specified at the Data Foundation Layer.

Consolidate

When all data related issues and reporting traps are handled, the next step in the modeling process is creating a
Consolidation View. The consolidation view commonly is split up up into two namespaces: a Relational View and a Dimensionally Modeled Relational (DMR) view of the metadata.

The first is used for normal reporting and generates SQL that is fired to the database. The second is used in multi-dimensional analysis and resembles an OLAP cube. The main difference between a Dimensionally Modeled Relation model (DMR) and an OLAP-cube is that the latter is physically stored in a multidimensional mode. The DMR-model is a virtual fashion of modeling the information source and does not physically stores data.

Figure 10: Cognos ten.2.ane Consolidation View

Relational

In this view model query subjects will be created using the query subjects in the Information Foundation View. The structure will, dissimilar the Data Foundation View, not resemble the database. The chief goal of this layer is to provide an easy to understand construction that is recognisable to business users. It is perfectly okay to combine a snowflake into a single model query subject as this would be a logical point of view of the business users. Normally technical meaningless objects such as load dates or sequence numbers like primary keys and foreign keys to dimensions should be removed or subconscious. Facts should only incorporate measures and degenerate dimensions. All strange keys to dimensions should be hidden to the business concern user. It is good practice not to remove these technical fields merely to go on them separated/hidden in a subfolder in the model query subject. While debugging reports, it can exist quite handy to exist able to include the primary fundamental of a table to place exactly which tape has issues.

In this layer, no relationships between query subjects should be laid, ever. A model query subject is also the best place to employ macro functions and parameter maps to handle multilingual tables. By using the calculations in Model Query subjects, modelers tin can avoid inbound freehand SQL in data source query subjects, which should be avoided at all times for maintenance purposes.

DMR

Dimensionally Modeled Relational models are virtual OLAP cubes. The information is presented in an OLAP-style, but is not physically stored on the server. Instead at every user request, a query is executed. This way of modeling is used when you want to enable analysis, using drill upwardly / drill down in Analysis Studio. Since the introduction of Dynamic Query Mode that also supports relational databases like Microsoft SQL Server, Oracle and IBM DB2, an advanced caching mechanism was put in place. This caching mechanism will provide similar performance as a physical cube when primed correctly.

DMR models are made up out of Regular Dimensions (dimensions) and Measure Dimensions (facts). A Regular Dimension consists of one or more defined hierarchies containing levels, keys, captions and attributes. Level information is used to roll up the measures. Each level should accept the cardinal and explanation defined. If there is a Unique key, the bottom level should exist marked Unique, otherwise, the combination of all upper levels is used to identify a member. If the star schema is modeled in its last grade, Regular Dimensions can exist speedily generated past using Merge in New Regular Dimension on the relational view of the Consolidation view.

Figure eleven: Cognos x.2.1 Detail of regular dimension

If a Regular Dimension is based on a query subjects that has determinants specified, it is recommended that one level corresponds to each determinant and that the order of levels is equal to the gild of determinants. Create a determinant for every level needed. Multiple hierarchies can be specified, but you cannot use them together in a unmarried study query. If this would be a requirement, create a regular dimension for every hierarchy.

A Measure Dimension is a logical grouping of facts which enables OLAP-styled querying of a relational database. Measure Dimensions and Regular Dimensions are joined through scope relations. These scope relations are only logical, the underlying query subject joins remain in use. A scope relation will specify what levels of a dimension are in telescopic' for a certain measure. A scope relation is mandatory and will exist created automatically using the underlying query field of study joins.

Presentation

The final stride in modeling a framework is creating a Presentation Layer. The Presentation Layer is congenital from several Star Schema Groupings. Star Schema Groupings make the model more intuitive to the end user by showing only related facts and dimensions. For every star schema a different namespace is created, showing the cease user functional business areas of which to select elements in the query. Using the wizard, star schema groupings can be created chop-chop.

Multilingual

Framework Director allows the modeler to interpret static report content such as field names and descriptions. This is done by adding languages to the framework. Not only static content can exist translated transparently for the end-user quite often product descriptions are kept in multiple languages in the data source.

There are two methods of storing this information:

• dissever column for every linguistic communication for example PRODUCT_EN, PRODUCT_DU, PRODUCT_FR
• separate row for every language

Whatever solution was chosen, macro functions enable the modeler to create the proper SQL at runtime, by using the linguistic communication options set by the user.

When there are multiple columns for every language, the modeler tin can specify that the column proper name retrieved at runtime is dependent of the user linguistic communication. Sometimes the languages are not mapped correctly. Therefore a mapped value is chosen from the Language_lookup parameter map.

• #'[NAMESPACE].[QUERY Discipline].[QUERY ITEM_'+ $Language_lookup{$runLocale}+ '] '#

If the multilingual data is stored in rows, a filter can be added:

• [NAMESPACE].[QUERY Subject].[LANGUAGE] = #$Language_lookup{$runLocale}+ '] '#

Figure 12: Cognos 10.2.one Translations

Choosing the proper Blueprint Language at the outset of a projection is crucial. One time set, it cannot be easily changed without modifying the source XML of the framework. It is recommended to always cull a dialect as design language. For example if the main language of the framework is English, use English Zimbabwe as design linguistic communication. Besides keep the original database column name in the design language in the Data Foundation View and Consolidation View. Doing then will enable you to see what database columns are in a written report when debugging the written report. Off grade y'all will take to alter your language in Cognos Connection to the design language of the framework.

When cavalcade names are changed in the database, only change the column names in the Data Foundation View. Changing the column proper noun in the design language in the Consolidation View would interruption the study. This only applies to the pattern linguistic communication (English Republic of zimbabwe), all other languages (English language) can be changed freely without affecting the report. And so clever use of the design linguistic communication volition let you to be able to easily change column names without breaking existing reports.

If multiple languages are used, translation files (Projects-Actions-Export) will make information technology easy to interpret the model. An Excel-file tin can exist exported containing all language values. When properly translated, the file can be re-imported just as easily.

Enhance performance

Aggregate tables are probably the single most toll effective measure in boosting reporting and data warehouse performance. Unlike competing products, Framework Manager does non have native functionality to facilitate aggregates. Nonetheless nearly all major vendors offer functionality within the database to transparently rewrite queries to aggregate tables. In Oracle for example, this functionality is called query rewrite and materialized views, in DB2 these are chosen materialized query tables. By using a query rewrite functionality, the database will transparently rewrite the query to the aggregate or detail table, depending on what level of detail the user requested.

Part of the aggregate lacuna in Framework Manager tin can be filled past using Dynamic Cubes. Dynamic cubes are a new in-memory cubing engineering. Currently only star schemas are supported as a data source, which is perfectly fine in our information warehouse context. What is different about this cubing engineering science is that information technology is cocky-tuning. After using the cube for a while, in memory aggregates will be proposed and congenital automatically.

It is too possible to allow Cognos to employ caching mechanisms to cache recurrent query results by leveraging Dynamic Query Mode. To enable this, the governor Allow usage of local cache should be enabled at the framework. The cache of DMR models is stored until it is cleared or refreshed. The enshroud for relational models is stored as long as the data source connection is open up, which is typically 5 minutes. This caching mechanism greatly improves performance of relational information sources, even to a comparable level as cubes.

To enable Dynamic Query Fashion a switch should exist fix at either packet level or projection level. As switching from compatible to Dynamic Query Mode could cause issues, careful report migration testing is needed. It is also possible to create two packages: 1 using Dynamic Query Mode and the other still using Compatible Query Mode. Legacy report will continue running and new reports tin exist built using DQM. From the maintenance perspective, all packages apply the same metadata.

Figure 13: Cognos 10.ii.1 Setting DQM at package level

While developing the model, it can be annoying to query the full database while testing the model. For that purpose it is possible to use Blueprint Mode filters that can be accessed from the query subjects Filter tab.

Reuse metadata

Framework Manager Packages tin be used in all IBM Cognos client tools, making it the universal glue that binds everything together. IBM Cognos Framework Query subjects tin be directly re-used in Cognos Insight to build self-service insights. Even when y'all are using Microsoft Function as the front end tool, the framework packet is used to recall data. Information technology is however a compassion that is not possible to generate a dynamic cube by using the DMR definitions. In essence both describe an OLAP layer, so inter-changeability would be a nice feature for a future release.

Security

Using the aforementioned framework, multiple package tin be defined each containing one or more star schemas. Granting or denying access to a packet a very effective and easy way to implement a basic level of data security. However, each individual object at any level can be secured.

Figure 14: Cognos 10.2.one: Setting information security

Information security will restrict users to query data they are not immune to. For example, a district manager volition only be able to query data of his district. Row level security tin can be put in place in two different ways. It is possible to hard code the values for every group. However a more than generic approach is to create embedded filters using security macro functions such every bit the LDAP username.

Segments

Segments are a very interesting characteristic to minimize maintenance. A segment is a new projection that is linked past a shortcut to the principal projection. The master projection has access to the entire model, including all segments. Suppose an organisation has a centralized information warehouse. The data warehouse team created a framework. The solution is very popular and other teams want to use office of the framework model or the framework model entirely in their surround, likely making small changes such as calculation new facts or dimensions.

Instead of copying the framework for every team, a segment can exist created for the other teams. This has a number of advantages:

• when the centralized information warehouse changes, changes only need to propagated in one case to the Framework Model
• the chief project has access to the unabridged model, the central data warehouse team tin can analyse impact immediately
• the centralized team can see how other teams are using the original framework

In short, segmenting allows a durable solution for like models used by unlike teams.

Determination

IBM Cognos 10 Framework Manager manages the semantic layer of a Cognos ten deployment. The main focus is creating a convenient, comprehensive model that provides in repeatable and predictable query results. Modeling a metadata layer requires an all-encompassing business and underlying data source knowledge. To create a model that offers repeatable and predictable results, the model should ever, physically or virtually exist conceived as 1 or multiple star schemas. By structuring the framework in a layered approach, any downstream furnishings of database changes can exist minimized. IBM Cognos Framework Manager offers a well featured metadata modeling tool that allows a durable approach in modeling semantic layers. The introduction of Dynamic Query Mode is a game changer apropos performance. Using advanced caching mechanisms, performance of DMR models tin be boosted up to the level of concrete cubes.

lockhartbriat1944.blogspot.com

Source: https://www.element61.be/en/resource/best-practices-modeling-ibm-cognos-102-semantic-layers-framework-manager

Share this post