You want to create a dimensionally modeled relational model from a relational data Data Sources, references to data sources defined in Cognos Manager. Get indepth overview of different types of models i.e., relational model and DMR model in Cognos and their features in detail. Read for More!. Creating a Framework Manager Dimensional Model – DMR. 9- Import the tables/views, Known as Query Subject in Cognos world. Identify.
|Published (Last):||20 May 2014|
|PDF File Size:||14.65 Mb|
|ePub File Size:||14.96 Mb|
|Price:||Free* [*Free Regsitration Required]|
Since then, this page has become the most visited page on our website, with over We thus are proud to present: IBM Cognos Business Intelligence Server offers 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 zero footprint web portal: Zero Footprint means no additional software or applets are installed on the client PC. It provides a single point of entry for all corporate data and the tools to analyse this data. The portal contains all available reports, analysis, dashboards and offers advanced sharing, publishing and security features. Each one has a specific functionality focus:. The purpose of a semantic layer is to create a business representation of corporate data.
This representation hides database complexity to the end-user by creating an intuitive model. The semantic layer maps complex data into familiar business 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. Business users are insulated from underlying data complexity while IT maintains governance over cogmos use of data sources. By using a single version of the truth midelling the use of consistent terminology, end-user productivity is increased as the self-servicing aspect of business intelligence is strengthened.
This enables the use of different databases even from different vendors- or OLAP cubes dmt a single semantic layer, enabling the ability to use these transparently in a single report. Framework Manager and Cube Designer.
DMR – Dimensionally Modeled Relational Model
The metadata cognoa tools within Cognos Business Intelligence are client-server applications. All end-user based tools are accessed from D,r Connection. Model flexibility can be defined from two different points of view. How easily can the model be adapted to cgnos conditions and how easily can the user generate ad hoc query requests? Both questions can be answered by using star schema modeling.
The dimensionally modeled database is ideal for reporting and is often referred to as a data warehouse. In a data warehouse facts and dimensions are established and data is stored at the lowest granular modellling.
In every data warehouse a number of star schema’s modellimg present. The central table represents the fact table and only contains numeric and additive measures.
The satellite tables represent the set of dimensions that can be used to look at the measures from different angles. By using conformed dimensions, a “data warehouse bus 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 can be quite easily added to the model, representing a new star schema.
Sometimes a data warehouse is not available 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 lead to dozens of tables in a single SQL statement, leading to large execution plans and modellign performance. Doing such queries on a production environment could even lead to problems with the applications operational performance.
Relational data sources also pose a number of modeling challenges for the framework modeler to fmr predictable query results. Therefore it is recommended to always use a data warehouse with star schemas as source for reporting.
When a framework is published, a compiled version of it is made available on Cognos Connection, called a package. This package can support 2 dmd modes: All objects available in the database can be easily queried at the lowest grain. The other way of querying is OLAP styled reporting based on a cube. A cube is a multidimensional store of data. The drawback is that a cube usually does not contain all the fields available in the database.
Most often figures in a cube are summarized so the lowest grain is not available. Reporting on a cube is however very fast. Frameworks provide a mechanism that allow for OLAP styled emr without the need cognps an actual physical cube.
With the introduction of Dynamic Query Mode, performance of DMR models can be boosted to the level of native cubes by using the advanced caching features Dynamic Query Mode offers. Both relational models and DMR models can be supported from a single framework. 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 will structure frameworks. In a namespace a number of query subjects are added.
They represent the moddelling in a framework. There are three different types of query subjects: Standalone filters are pre-designed filters that can easily be re-used in the reporting tools by the author.
For OLAP functionality, two additional objects are available: A Measure Dimension contains a collection of numeric values. The Regular Dimension provides the accompanying set of descriptions and identifiers. The Measure and Regular Dimensions are linked with Scope Relationships to define the level at which the measures are available for reporting.
While creating a model it is important to create a proper structure. The use of a multi-tier structure will shield the end-user from changes at database level such as 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 step in creating the Framework Model is importing the metadata.
Creating a DMR model
This can be handled by using the Metadata Wizard. It 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: Q uery subjects are linked together using Relationships.
When all data source objects are imported, the model should be scrutinized to verify all relations between the cognox subjects are correct.
It is good practice not to blindly import the relationships. By manually creating the relationships, a much higher level midelling control is achieved. Relations should always and only be created in the Database Foundation View. Mixing relationships at different cognoe will only cause confusion and incorrect results. For maintenance purposes, it is however recommended never to make any changes in freehand SQL.
If you do, the query subject has to be manually adjusted if changes are made at database level. When changes are made in the database, importing is ddmr far the easiest way to update the query subjects.
You can also use the Update command in the Tools menu to update a single query subject. Although it is possible to import data from different data sources, the reflection should be made that there 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.
Therefore it is highly recommended to use only 1 data source modellint physical database platform. In the Data Foundation view some other tasks need to be done. By using the proper tab pages, calculations and determinants can be added to the query subjects without making changes to the SQL code.
For every query item, the modeler should 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-additive data. All indexed columns or columns containing business keys should be set as identifier. Attributes are typically all other strings. For every fact column, the aggregate should be set.
Other options that should be set are the format, screen tip, description These properties are inherited by derived objects at a later stage in the modeling process. The greatest challenge for the model developer is creating a model that returns proper query results at all times, no matter dkr columns were selected in the report by the user.
When importing from a relational data source, cardinality is detected based on a set of rules. This means it is possible that a query subject will behave as a dimension in one query and as fact in another query. This is typically the case vmr snowflake dimensions. This situation can be handled by using model query subjects.
Relational and DMR modeling in Cognos Cube Designer
The model query subject 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 force Cognos to retrieve the entire snowflake even when no fields are modellinh from the underlying tables. Therefore it is better not to condense the snowflake using a model query subject.
Instead, model the snowflakes with 1: