cognos framework manager presentation layer

Best Practices in Cognos 8 Framework Manager Model Design

For those starting out with Cognos 8, or even those who have worked with it for quite a while, Framework Manager model design can seem like a daunting or even overwhelming task. With so many options available, it can be hard to know what to consider during design, and how to promote stability and sustainability.

This is the first in a two-part article that will focus on the best practices, organization and overall methodology of Framework model development. As is often the case with complex tools, even developers who have worked with Cognos for a while can be confused on at least some of the concepts and considerations of model design. The focus of this first article will start from the beginning, and describe an overall 4 layer approach, detailing the purpose and reason for each of the four layers. Next month’s article will describe advanced best practices, and other tips.

Four Layer Approach

One of the basic tenets for best practice model design is to segment the model into four specific sections or layers (Data, Logical, Presentation and Dimensional). Each layer has a specific function and set of modeling activities. Generally, the layers build upon one another, with the data layer being the foundation of the model. Other areas, such as packages and connections are also important, but fall outside of the layers. Many of the best practices can be thought of in terms of what development activities should or should not be performed in each of the layers.

The data layer, also called the import layer, contains the data source query subjects, based directly on the underlying database objects. Whenever possible, use unmodified SQL ( “ select * from Table Name”) to retrieve the table information. If you modify the SQL code or add a filter or a calculation to the data subject, it eliminates Framework Managers meta-data caching capabilities. This means that Cognos 8 will validate the query subject at report run time, adding overhead to the report load time. In some circumstances, this may be worth the trade-off, but should be avoided when possible.

Add joins, cardinality and determinants at this level. Cardinality, the definition of how joins should behave, is critical to a well developed model and can be confusing, even to seasoned veterans. In lieu of a lengthy discussion within this article, consider other IBM documents which offer a thorough discussion of cardinality, and the typical types of situations you may see.

Expect 20 to 40 percent of the model development to take place in the data layer.

Logical Layer

The logical layer adds the bulk of the meta-data to the model, and consequently is where most of the work is likely to occur. It provides business context and understanding to the data objects. Tasks include but are not limited to:

• Organizing elements into logical query subjects. This may mean combining elements from multiple tables, as in a snowflake dimension.
• Renaming element names, including descriptions and tooltips, and adding multiple language definitions if needed. Assign standardized and business-defined terms to the database columns, giving them business context.
• Add calculations, and filters including stand-alone filters, embedded filters and security-based filters. Base these on the underlying data layer objects to make them less susceptible to errors when copying and reusing the query subject.
• Arrange query subject items in a user-friendly manner. Make use of folders to group similar items, or when there are too many items. I suggest 12 or fewer objects per folder, although I have used more when the logical breakout calls for it. For example, if there are many dates in a query subject, it might be more intuitive to have all the dates in a single large folder, than to try to subdivide each into smaller, random folders. Arrange the contents of the folder in an intuitive manner, such as alphabetic, or with the most commonly used items at top. HINT: It can be useful to use the “Reorder” command available within FM for this purpose.

• Assign output formats and usages to the reporting elements. This is easier if you create a small folder of trivial calculations, used only to provide standardized object format templates. The formats can easily be copied to target items by multi-selecting, and dragging the topmost format through the list.

• Add prompts, including cascading prompts, and prompt-based calculations.

Roughly 50 to 70 percent of the modeling work occurs in this section.

Presentation Layer

The importance of making information easy for report writers to use is frequently underestimated, but is a critical component to driving user adoption. Fortunately, this can be a simple step. The presentation layer is used only as an organization structure in order to make it easier for report writers to more easily find their needed information. This layer includes only shortcuts to existing items in the logical layer, plus organization items such as folders and namespaces.

For example, create a namespace called Orders and include shortcuts to the ten or twenty relevant query subjects, out of perhaps a hundred or more query subjects in the logical layer. Also include shortcuts to relevant filters. Commonly used query subjects (such as Items or Customers) will appear in multiple areas. Rename the shortcuts to something which provides helpful business context.

For organizing major groupings, you can use either folders or namespaces. However be aware that namespace names must be unique, and that items within a namespace must likewise be unique. So, if you use folders for your major groupings, you cannot have a shortcut named “Items” in more than one folder. You must rename them to unique names, such as “Order Items”. For this reason in particular, I generally prefer to use separate namespaces.

The presentation layer takes approximately 10% of the overall model design effort.

Dimensional Layer

The dimensional layer is required only for models which include dimensionally modeled data. Leaving aside the trivial situations where cubes are simply imported into the model, this includes dimensionally modeled relational data (DMR).

Specifically, this is for creating Dimensional and Measure Dimension Query subjects. Much like the presentation layer, this layer also is built upon the logical layer, which leverages the effort put into that layer. Apply the element renaming, descriptions, tooltips in the logical layer, and they can be reused in the dimensional layer, with some help from the search tool.

Finally, note that when you are creating the final package, you should hide the data and logical layers so that the user will only see the presentation and/or dimensional layers.

Coming next month… Advanced best practices for Cognos modeling.

Ironside helps companies translate business goals and challenges into technology solutions that enable insightful analysis, data-driven decision making and continued success. We help you structure, integrate and augment your data, while transforming your analytic environment and improving governance.

• AWS Ascent Solutions
• IBM Data Analytic Services

GET IN TOUCH

781-860-8840

For inquiries: [email protected]

For HR: [email protected]

Office Address 131 Hartwell Ave Lexington, MA 02421

Corporate Mailing Address Ironside Group, LLC 177

101 Great Rd, Suite #130 Bedford, Massachusetts 01730

Regional offices in Atlanta, Austin, Boston, Charlotte, New York City and Orlando

>> Managed Services Support

• Best Practices Archives - Page 2 of 2
• Determinants – The Answer to a...
• IBM Cognos 8 Framework Manager Training...

IBM Cognos 10 Framework Manager by Terry Curran

Get full access to IBM Cognos 10 Framework Manager and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

Chapter 6. Creating the Business and Presentation Layers

This chapter will cover the creation of the business and presentation layers. The business layer is where we apply various business information rules to our model. The presentation layer is what the report author will see in Report Studio, Query Studio, and Analysis Studio.

This chapter will cover the following topics:

• Specifying attributes
• Renaming columns
• Adding prompts
• Adding filters
• Adding calculations
• Adding formatting to data items
• Using folders and namespaces for grouping information
• Using shortcuts to include the same information in different places

By the end of this chapter, users will have created the business layer and extended the usefulness of their model by adding prompts, filters, ...

Get IBM Cognos 10 Framework Manager now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

Best Practices in Modeling IBM Cognos 10.2 Semantic Layers in Framework Manager

In October 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 page on our website, with over 50.000 pageviews since publication, clearly showing the need for the topic. Given IBM Cognos 10 now is on the market for a while, we decided to update the Insight to incorporate new IBM Cognos 10 functionality. We thus are proud to present : "Best Practices in Modelling IBM Cognos 10.2 Semantic Layers".

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: IBM Cognos Connection. 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. IBM Cognos Connection will provide interaction with other modules from the Cognos family such as IBM Cognos Insight or IBM Cognos Controller.

Figure 1: Cognos 10.2.1 Cognos Connection Portal

From IBM Cognos Connection all the end user applications can be launched. Each one 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
• Report Studio: perform advanced, pixel perfect reporting with complex queries
• Event Studio: create agents to follow-up on triggers
• Cognos Insight: do 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 product): perform multidimensional analysis

All these tools share the same 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 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 the use of data sources. By using a single version of the truth and the use of consistent terminology, end-user productivity is increased as the self-servicing aspect of business 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 even from different vendors- or OLAP cubes in a single semantic layer, enabling the ability to use these transparently in a single report.

IBM Cognos 10 Business Intelligence uses 2 metadata tools: Framework Manager and Cube Designer . The metadata modeling tools within Cognos Business 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 technology that replaces Powercubes. Cube Designer is beyond the scope of this insight, but in the near future a new insight concerning Dynamic Cubes will be released.

Figure 3: Cognos 10.2.1 Cube Designer

Flexible Models

Model flexibility can be defined from two 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 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 level. In every data warehouse a number of star schema's are 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. However not all IBM Cognos Frameworks need to be build on dimensionally modeled databases. 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 slow 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 create predictable query results.

Therefore it is recommended to always use a data warehouse with star schemas as source for reporting.

Query Flavour

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

With Relational Querying , IBM Cognos will build an SQL statement when a user drags and drops objects on a report. All objects available in the database can be easily queried at the lowest 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 use of a cube is to do drill up/down analysis. 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 reporting without the need of an actual physical cube. Cognos will emulate OLAP behaviour and will write SQL to retrieve the data in the background. These types of frameworks are called Dimensionally Modeled Relational or in short DMR. 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.

Figure 4: Cognos 10.2.1 OLAP Style Reporting

Figure 5: Cognos 10.2.1 Relational Style 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 will structure frameworks. In a namespace a number of query subjects are added. They represent the tables in a framework. There are three different types of query subjects:

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

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

Figure 6: Cognos 10.2.1 Measure Dimension and Regular Dimension

For OLAP functionality, two additional 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 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 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. 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: the Data Foundation View .

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 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 achieved. Relations should always and only be created in the Database Foundation View. Mixing relationships at different levels will only cause confusion and incorrect results.

A query subject can be edited by replacing the standard SQL with custom written SQL. 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 by 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. Instead, IBM Cognos will write 2 queries and stitch these together locally at the IBM Cognos BI server. Therefore it is highly recommended to use only 1 data source per 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. Embedded/standalone filters should be added and database column names are translated to more understandable business names.

Figure 7: Cognos 10.2.1 Calculations on database query subjects

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.

Model for predictable results

The greatest challenge for the model developer is creating a model that returns proper query results at all times, no matter what 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.

IBM Cognos uses the following rules:

• cardinality is always applied in the scope of a query performed by the user
• 1 or 0-to-n relationship implies a fact but only 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 behave as a dimension in one query and as fact in another query. This is typically the case with snowflake dimensions. This situation can be handled by using model query subjects. 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 needed from the underlying tables. Therefore it is better not to condense the snowflake using a model query subject. Instead, model the snowflakes with 1:1 relationships. Tables in the snowflake can be joined using 1:1 relationships instead of 1:n relationships. This will allow the usage of Minimized SQL, retrieving only the objects that are needed and ensure the proper usage of the query subjects.

Figure 8: Cognos 10.2.1 Context Explorer

Handle Ambiguous Relationships

There are two types of relationships that can provide inconsistent result sets if not handled by the modeler. The first occurs when there are multiple valid relationships. This typically occurs between facts and dimensions. In a fact table, a different dates are present: invoice date, ship date, order date all point to the date dimension. Combining multiple dates in a single query will no longer return results. Another issue occurs when handling recursive relationships. The classic example is the manager employee relation. An employee has a manager. The manager is an employee and also has a manager that again 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 properties of every model query subject created leading to unnecessary work. A convenient solution to this problem is using shortcuts. There are two types:

• Regular Shortcut: reference to the source objects but inherits all properties including relationships
• Alias Shortcut: behaves independently of the source object, so different relationships can be set

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

Multi fact multi grain queries

A determinant is needed to identify levels of aggregation within the query subjects. This is a particularly useful feature when dealing with multi-fact, multi-grain queries. When you have a sales fact at day level and a target fact at 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 month level and not at day level. Determinants will change the default behaviour of the query. Cognos will recognise the difference in grain and will write 2 queries that will be stitched together to return proper results at the proper grain.

Figure 9: Cognos 10.2.1 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 different fact tables using a different granularity, Cognos can determine the correct aggregation when determinants are specified. A determinant will specify what set of columns will uniquely define a set of columns. Each level is specified identifying the key and attributes that belong to a level. The lowest level is marked unique.

This will enable the report developer to create a report showing revenue at week level versus month figures without double counting the lowest grain fact. Cognos uses the mechanism of stitch queries to perform these types of requests. A stitch query will perform a full outer join to break queries into multiple selects, one for each fact table and then stitch 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 usually is split 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 way. The DMR-model is a virtual way of modeling the data source and does not physically stores data.

Figure 10: Cognos 10.2.1 Consolidation View

In this view model query subjects will be created using the query subjects in the Data Foundation View. The structure will, unlike the Data Foundation View, not resemble the database. The main goal of this layer is to provide an easy to understand structure 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 hidden. Facts should only contain measures and degenerate dimensions. All foreign keys to dimensions should be hidden to the business user. It is good practice not to remove these technical fields but to keep them separated/hidden in a subfolder in the model query subject. While debugging reports, it can be quite handy to be able to include the primary key of a table to identify exactly which record has issues.

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

Dimensionally Modeled Relational models are virtual OLAP cubes. The data is presented in an OLAP-style, but is not physically stored on the server. Instead at every user request, a query is executed. This style of modeling is used when you want to enable analysis, using drill up / 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 have the key and caption defined. If there is a Unique key, the bottom level should be marked Unique, otherwise, the combination of all upper levels is used to identify a member. If the star schema is modeled in its final form, Regular Dimensions can be quickly generated by using Merge in New Regular Dimension on the relational view of the Consolidation view.

Figure 11: Cognos 10.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 order of determinants. Create a determinant for every level needed. Multiple hierarchies can be specified, but you cannot use them together in a single report 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 scope' for a certain measure. A scope relation is mandatory and will be created automatically using the underlying query subject joins.

Presentation

The final step in modeling a framework is creating a Presentation Layer . The Presentation Layer is built 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 end user functional business areas of which to select elements in the query. Using the wizard, star schema groupings can be created quickly.

Multilingual

Framework Manager allows the modeler to translate static report content such as field names and descriptions. This is done by adding languages to the framework. Not only static content can be 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:

• separate column for every language 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 language options set by the user.

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

• #'[NAMESPACE].[QUERY SUBJECT].[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}+ '] '#

Choosing the proper Design Language at the start of a project is crucial. Once set, it cannot be easily changed without modifying the source XML of the framework. It is recommended to always choose a dialect as design language. For example if the main language of the framework is English, use English Zimbabwe as design language. Also keep the original database column name in the design language in the Data Foundation View and Consolidation View. Doing so will enable you to see what database columns are in a report when debugging the report. Off course you will have to change your language in Cognos Connection to the design language of the framework.

When column names are changed in the database, only change the column names in the Data Foundation View. Changing the column name in the design language in the Consolidation View would break the report. This only applies to the design language (English Zimbabwe), all other languages (English) can be changed freely without affecting the report. So clever use of the design language will allow 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 it easy to translate the model. An Excel-file can be 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 cost effective measure in boosting reporting and data warehouse performance. Unlike competing products, Framework Manager does not have native functionality to facilitate aggregates. However 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 called 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 can be filled by using Dynamic Cubes. Dynamic cubes are a new in-memory cubing technology. Currently only star schemas are supported as a data source, which is perfectly fine in our data warehouse context. What is different about this cubing technology is that it is self-tuning. After using the cube for a while, in memory aggregates will be proposed and built automatically.

It is also possible to allow Cognos to use 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 cache for relational models is stored as long as the data source connection is open, which is typically 5 minutes. This caching mechanism greatly improves performance of relational data sources, even to a comparable level as cubes.

To enable Dynamic Query Mode a switch should be set at either package level or project level. As switching from compatible to Dynamic Query Mode could cause issues, careful report migration testing is needed. It is also possible to create 2 packages: 1 using Dynamic Query Mode and the other still using Compatible Query Mode. Legacy report will continue running and new reports can be built using DQM. From the maintenance perspective, all packages use the same metadata.

Figure 13: Cognos 10.2.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 Design Mode filters that can be accessed from the query subjects Filter tab.

Reuse metadata

Framework Manager Packages can be used in all IBM Cognos client tools, making it the universal glue that binds everything together. IBM Cognos Framework Query subjects can be directly re-used in Cognos Insight to build self-service insights. Even when you are using Microsoft Office as the front end tool, the framework package is used to retrieve data. It is however a pity 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.

Using the same framework, multiple package can be defined each containing one or more star schemas. Granting or denying access to a package 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.1: Setting data security

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

Segments are a very interesting feature to minimize maintenance. A segment is a new project that is linked by a shortcut to the main project. The master project has access to the entire model, including all segments. Suppose an organisation has a centralized data warehouse. The data warehouse team created a framework. The solution is very popular and other teams want to use part of the framework model or the framework model entirely in their environment, likely making minor changes such as adding new facts or dimensions.

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

• when the centralized data warehouse changes, changes only need to propagated once to the Framework Model
• the master project has access to the entire model, the central data warehouse team 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 similar models used by different teams.

IBM Cognos 10 Framework Manager manages the semantic layer of a Cognos 10 deployment. The main focus is creating a user-friendly, comprehensive model that provides in repeatable and predictable query results. Modeling a metadata layer requires an extensive business and underlying data source knowledge. To create a model that offers repeatable and predictable results, the model should always, physically or virtually be conceived as one or multiple star schemas. By structuring the framework in a layered approach, any downstream effects of database changes can be 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 concerning performance. Using advanced caching mechanisms, performance of DMR models can be boosted up to the level of physical cubes.

Related insights

Planning analytics workspace guided planning, planning analytics workspace administration, what’s new in ibm planning analytics workspace 2.0.52 - 2.0.57 sc.

Published on

Getting started with Framework Manager

IBM® Cognos® Framework Manager is a metadata modeling tool that drives query generation for IBM Cognos software. A model is a collection of metadata that includes physical information and business information for one or more data sources. IBM Cognos software enables performance management on normalized and denormalized relational data sources and a variety of OLAP data sources. When you add security and multilingual capabilities, one model can serve the reporting, ad hoc querying, and analysis needs of many groups of users around the globe.

Before doing anything in IBM Cognos Framework Manager, you should thoroughly understand the reporting problem that you want to solve.

To get started, do the following:

• Analyze the reporting problem.
• Learn about the objects you will use.
• Create or open a project.
• Explore the panes in Framework Manager.
• Explore the sample models included with Framework Manager.

• Welcome to COGNOiSe.com - The IBM Cognos Community .
• COGNOiSe.com - The IBM Cognos Community

MetaManager - Administrative Tools for IBM Cognos Pricing starting at $2,100 Download Now     Learn More

• ► IBM Cognos 8 Platform
• ► COGNOS 8
• ► Framework Manager
• ► Why go with 3 layers in Framework Manager Model?

Why go with 3 layers in Framework Manager Model?

Started by alliejoy, 26 May 2010 10:50:52 AM

• Never knowingly correct
• Super Moderator

• Posts: 11,791
• Cognos Software Muppet
• Join Date: Jul 2005
• Location: Once long ago near England but now in New Jersey
• Global Moderator

• Posts: 1,912
• Join Date: Jan 2009
• Location: Israel

Quote This is especially visible if you create a report that selects 2 columns out of 10 in a table. The underlying query might be selecting all columns and then doing a subselect on the 2 required ones. Obviously this drives value of DB indexes (read performance) to nothing.

• Join Date: Apr 2008
• Help | Terms and Rules | Go Up ▲
• SMF 2.1.4 © 2023 , Simple Machines

• Cognos Framework Manager vs. Data Modules
• Knowledge Center
• Instructor-led online
• June 25, 2020

Each release of Cognos sees incremental improvements to data modules. Over time, the functionality gaps between data modules and Framework Manager have narrowed substantially. In fact, data modules are a powerhouse of a tool.

But does that mean you can use data modules as the primary Cognos metadata modeling tool? Now is your chance to find out.

In this on-demand demo and comprehensive comparison between Framework Manager and data modules, we explore data module advantages over Framework Manager and the feature and functionality gaps that exist. And answer participant questions.

Pedro Ining Senior BI Architect Senturus, Inc.

Pedro joined Senturus in 2010 and brings over 20 years of BI and data warehousing experience to his role. He has been instrumental in implementing data warehousing systems from scratch and has experienced the evolution of the BI industry through several iterations of BI products including Cognos, MicroStrategy and Tableau.

Questions log

Q: Can Cognos data modules call stored procedure like Framework Manager? A:  This is currently not supported. You can make custom tables based on SQL, but you cannot call a stored procedure.

Q: Can DMR be used in Cognos data modules? A:  As of Cognos release 11.1.6 DMRs cannot be created in data modules. This may be addressed in later versions, but we don’t think it is high on IBMs priority list.

Q: Is IBM considering migrating Framework Manager as a web-based tool? A:  No. FM will not undergo any further new development; however, It will be continually supported for a long time. (Think Transformer, that product is still supported.)

Q: Can Cognos data modules read synonyms? A:  Yes. If the logon to the data source connection can read the synonym then you should be able to read it.

Q: Is there a way to automate the refresh of Cognos data modules, instead of doing it manually? A:  If the datasets are contained in a data module, then yes you can. You just need to set a refresh schedule on each dataset’s properties.

Q: With Cognos data modules is there a way to control a version of the truth since the sources can come from anywhere and the transformations are different? A:  Since you can create your own data modules, the control of the version of the truth will need to rely on your internal organization’s processes and control to data sources. Even in very controlled environments the single source of truth goal can easily get lost as users can use Excel and other tools that integrate data on their desktops.

Q: We have filters on Cognos FM database Query Studio that are based on session parameters. Can we replicate that in data modules? A:  You can create filters based on expressions which in turn call macros. We have not tested this on a specific use case.

Q: Does the Senturus Analytics Connector for Tableau recognize data modules? A:  Yes, the Senturus Analytics Connector supports data modules! Learn more about the  Analytics Connector .

Q: Can Cognos data modules do the impact analysis like Framework Manager? A:  Currently no.

Q: Are easy hierarchy creation – navigation paths more like Cognos data modeling or dimension layers in Transformer? A:  Navigation paths are not a true dimension like Transformer. It simulates a dimension, but the flexibility of navigation paths is that you can create relationship among any attributes.

Q: Will Event Studio support Cognos data modules in the future? A:  No. There are no current plans to update Event Studio.

Q: How do we backup Cognos data modules? A:  Either manually via standard copy or you can use regular content store backups using a third-party such as Motio.

Q: If we are creating a view of tables and forget to include a field, is there a way to re-add the field again? A:  Simply edit the view and add the field. Cognos 11.1.6 adds a new refresh metadata button that will allow you to add the new field to the source table with a drag and drop.

Q: What’s the maximum number of tables Cognos data modules can handle? A:  There is no limit; however, you may want to architect the solution with multiple DMs if you have many tables.

Q: Are data servers the same as data sources in Cognos? A:  Data servers are very similar to traditional Cognos data sources. One big difference is that they only support DQM connections. Data servers are created from the main web Interface, while traditional data source connections are created in the administration console.

Q: Does IBM have any plans to have aggregate awareness available in Cognos data modules? A:  Not that we are aware. That feature was not available in FM either.

Q: Why do we have to define a join between the two custom tables? Are the original joins recognized by Cognos data modules? A:  If you create a view from several tables, it will inherit the joins of those tables in the view. If you create a view from one table, it will not inherit any joins defined from the base table.

Q: If I create a custom table and then decide I want to include an additional table, how can I add one? A:  You will need to recreate the custom table from scratch, sorry.

Q: Is there a verify model in Cognos? A:  Validation is automated and usually occurs every few seconds. You can control this behavior to be manual.

Q: Which is easier to use for creating data modules, Tableau or Cognos? A:  Tableau requires you to model data first before you can begin creating visualizations. It is a different paradigm. Cognos allows you to create reports/visualizations against packages or data modules that are available in Cognos. Both tools require you to understand data joins and both are fairly simple to use if you understand the basic concepts of data modeling.

Q: How do we move Cognos data modules between environments such as development, general availability and production? A:  The usual migration deployment package concepts will work.

Q: Is there a way for us to implement relative time calculations like PeriodToDate MDX functions in Cognos data modules? A:  Yes. relative time is now easy to implement in data modules. Check out my on-demand webinar,  Using the New Cognos KPI Capability & Relative Time Structures , to learn more.

Q: As an Admin – in the FM paradigm – I have knowledge and oversight of packages. What happens when a modeler leaves the company? And nobody has access to that user’s content? A:  Admins can always get access to a user’s My Content. But as you expand self-service across your organization, you’ll have some problems with decentralized content. This will become an organization process issue that you’ll need to address to monitor and track Cognos use and development.

Q: Can we go back and add fields to the products later as requirements change? A:  Yes, you can, they’ve made it easier in Cognos 11.1.7.

Q: How do I add descriptions and screen tips? A:  In the properties of a data modules table or fields you will find a screen tip entry field. Add your screen tips there.

Q: Where did the column names and no timeframe of week in the time dimension come from? A:  Column names came from the basetable. In the demo, I only selectively chose certain columns.

Q: If I use PA/TM1 as a source, does it slow things down? A:  We haven’t tried this source as of yet but you should get the same performance as a normal Cognos connection.

Q: Would it be a good practice if I always use views to simulate a physical and presentation layer in Cognos data modules like we did in Framework Manager? A:  End user modeling would probably only use views when they are needed. If you are deploying IT maintained data models that are typically locked down, it would be a good idea to use this practice. Then end-users would simply link to those data modules.

Q: Is it possible to convert an existing Cognos Framework Manager package into a data module? A:  There is no existing migration utility; although some folks are working on one. You would have to do this manually.

Q: How do we show the metadata for the table in custom product view? A:  Several different ways, with view definition, edit view or the properties pane.

Q: How do we add new fields to custom views? A:  You will need to edit the view definition.

Q: How would a master Cognos data module approach be done so that only one data module needs to be managed and propagated to all the sub modules? A:  This is a scenario where IT creates a master data module that contains all the necessary joins, calcs, etc. from a source database. You can then publish this data module as read only so no users can modify the data module. User can read the data module and then create other data modules from this source data module. This establishes a linked data module.

Q: Can I use session parameters and prompt macros within Cognos data modules? A:  Prompt macros are not supported, but you may be able to use session parameters in custom filter definitions.

Q: Cognos Framework Manager allows us to call stored procedures in databases. Are there any plans to implement invoking of stored procedures with parameters in data modules? A:  This is probably on IBM’s development list.

Q: To have multilingual reports with parameter maps do we need to use Cognos Framework Manager or is there any way to have multilingual reports using only data modules? A:  Parameter maps are critical to supporting multilingual reports. This is not yet supported in data modules.

Q: Is it possible to see SQL query from the report query built from Cognos data modules? A:  You can view the SQL query of each table or view it within the Cognos data module. That would then get interpreted in reports. You should be able to see the queries in that tool as well.

Q: Do we have Stich queries issued in Cognos Framework Manager? A:  Stitch queries using COALESCE are supported in data modules.

Q: Do we have an option to find report dependency in Cognos data modules like we have in Framework Manager? A:  No.

Q: How do I find the list of all reports that is using a particular field? A:  Data modules do not have that type of dependency checks yet.

Q: How do we manage the security of users on Cognos data modules? A:  The Cognos data module is like any other object in Cognos. You can grant the usual set of permissions on the data module.

Q: How is data security and object security done in Cognos data modules? A:  Currently object-level security is not possible inside a Cognos data module. Data-level security is supported by creating filters on the data source and applying specific Cognos or Admin user groups to the filters. Advanced row-level security via parameter maps, security tables, etc. is not yet supported.

Q: Can Cognos data modules read TM1 or planning databases? A:  Yes. Create a data server connection to TM1 and then create a data module from it.

Q: Are there governed settings like stopping cross join in Cognos data modules? A:  Data modules do not contain governor settings like that of Framework Manager.

Q: Do you or IBM have a recommendation for timelines in moving away from converting Cognos Framework Manager packages to data modules? A:  Framework Manager will be around for a long time. Our recommendation is that if your production packages are working fine and are complex you should not invest too much time converting those packages to data modules. Maybe specific areas of those packages could be converted so that users can take advantage of certain data module features.

For new modeling projects, you should start to spec out a data module implementation unless a specific FM feature that is not supported in data modules like perhaps parameter maps or complex security requirements.

Q: Can I add an additional table to a custom table after it is already built? A:  You would need to redefine the custom table.

Q: Can we extract data instead of a live connection in Cognos data modules similar to dataset schedules? A:  Yes, you can integrate datasets into data modules.

Q: Where can I get a recording of today’s presentation of Cognos Framework Manager vs. data modules? A:  This on-demand webinar is available at:  https://senturus.com/resources/cognos-framework-manager-vs-data-modules/ .

Machine transcript

0:08 Greetings everyone and welcome to this latest installment of the Senturus knowledge series. Today, we’re excited to be presenting to you on the topic of Cognos Framework Manager versus data modules. We’ll do a comprehensive comparison and discuss some key feature gaps.

0:25 Before we get into the presentation, a few housekeeping items, please feel free to use the GoToWebinar control panel to make this session interactive. We’re usually able to respond to your questions while the webinar is in progress, and encourage you to enter your questions via the question pane in that Control Panel. Which, you can minimize or restore using the orange arrow.

0:50 If however, we’re unable to respond to your question during the live webinar, we will cover it in a written response document that we post on senturus.com.

1:00 Which brings us to the next question we get early and often throughout the presentation, is, can I get a copy of the presentation? And the answer is absolutely.

1:09 It will be available shortly on senturus.com. You can select the resources tab and head to the resources library. Or you can click on the link that we’ll put in the GoToWebinar control panel if we haven’t already and be sure while you’re there to bookmark the resource library as it has tons of valuable content addressing a wide variety of business analytics topics.

1:31 Our agenda, today, after some quick introductions, we’ll do an introduction of those Framework Manager and data modules and provide some comparisons between the two.

1:42 Discuss some key framework manager model issues versus data modules, and demonstrate some solutions to give you an idea of what that looks like in real time. And then stick around for the Senturus overview, for those of you who may not be familiar with who we are, and what we do. Some additional, almost entirely free resources, and then at the end, we always have our great Q and A so, again, get your questions in the Q and A, and we’ll get to those at the end of the presentation.

2:09 So joining me today, I’m pleased to be joined by mister Pedro Ining , Pedro joins Senturus back in 2010 and brings over 20 years of BI and data warehousing experience to his role. In addition to being a regular contributor to our Senturus is Knowledge Series here. He’s been instrumental in implementing data warehousing system from scratch and has experienced the evolution of the BI industry through several iterations of BI products, including Cognos, MicroStrategy, and Tableau.

2:37 My name is Mike Weinhauer. I wear a number of different hats at Senturus. One of them being the emcee for the Knowledge Series, and I’m pleased to be here hosting you today. And with that, I’ll hand the floor and the mic over to Pedro.

2:48 Pedro floor is yours OK, hi, everybody. So today, Framework Manager versus data modules.

2:55 It’s worth mentioning that Cognos has been around for quite awhile since the mid nineties and has quite a bit of a modeling legacy as products that have been around for a while and have morphed with the industry contain.

3:09 So, we all remember modeling with transformer and then Framework Manager and with DMR, we’re just trying to make Framework Manager look like transformer who can never forget dynamic cubes and anybody using that out there. So, dynamic cubes hasn’t been used for awhile.

3:25 We have Cognos data modules with the Cognos Analytics 11.

3:36 The product has evolved quite a bit over the years and we always have to change our ways and how to work with these tools.

3:58 IBM has really pushed data Models.

4:13 It doesn’t really, really advertise Framework Manager as part of the 11, as a feature set, but it’s there.

4:20 And we’re kind of taking the point of, you know, if you’ve got existing Framework, manage your production packages that are working fine. In production you’ve got a 500 reports, again assembler fairly complicated packages and they’re working, OK. Yeah, there may not be a need to port a 1 for 1 implementation.

4:43 To a data module.

4:45 It may not be worth the cost benefit and IBM has no plans to remove product support for Framework Manager.

4:54 It’s not going to be deprecated. Much like transformer which has been around since the mid nineties. Transformers as product line, it can be used with Cognos Analytics 11. The same can be saId for a Framework Manager. Now, as you look across your existing models and framework packages, there might be some opportunity to take maybe portions of it.

5:15 And there might be some packages that you might want to really port over, so that you can take advantage of some of the new functionality with data models. But I would say that you don’t want to take a complete blanket approach and say, I have to move over. Because IBM is not going to support this at this particular product to any longer. So, that’s, kind of like a general statement. I wanted to get that question off the top.

5:39 So, if you are going to be starting a new modeling task, you might want to give a serious look at data modules.

5:50 If some of your modeling tasks don’t need some of the features that are still kind of considered gaps, you might want to give it a serious look. And, in fact, some of the product managers at IBM even stated that you look at data warehouses, maybe start with data models for new projects until you get to the point where you realize that you go through your requirements. Maybe it’s not the right tool yet for the job.

6:16 So, let’s talk quickly about Cognos Framework Manager.

6:19 I’m sure that a lot of you out there who are career manager guru’s developers, have been using it for quite a long time, but, in case of some people out there who wanted to see what’s available in terms of the cross, if they are Cognos landscape, let’s talk about these quick bullets.

6:33 But, a framework manager is been the primary modeling tool since Cognos Reports, for those of you who remember that initial release of Cognos on the web than Cognos 8 in Cognos 10.

6:46 And it’s still widely used in Cognos 11 and 11.1.

6:52 In fact, there are a lot of customers out there who had migrated from Cognos 10 to Cognos 11, who really still only use the framework manager package model paradigm, where the modeling is really IT controlled and centric, and they have not even scratched the surface of using some of the newer modeling tools, like data modules within Cognos 11.

7:14 And historically, Framework Manager has been that IT centric tool used by developers and data modules.

7:22 Data mining, data modelers.

7:24 And it was never really meant for end users, you know, but I’m sure there’s some organizations that I’ve tried.

7:30 They’ve actually let end users get framework manager install on a PC and try to do some packages, But, I think, in general, some users, power users, have kind of throw their hands up in the air.

7:43 So, this is too complicated for what I want to do because I don’t need all that complex functionality of that Cognos Framework Manager has had because its development. Genesis has been more for a very centrally maintained metadata structure for developers and data model modelers.

8:01 It’s kind of like from an era from the single source of truth data warehouse, we all remember, this was the, the, really the end goal, I think, of maybe 10, 15 years ago.

8:10 We need a data warehouse is going to have everything in it that’s going to have a pure, pristine metadata layer.

8:16 And everybody will go to that for all their analytics. And as we know, now, we do need data warehouses. That’s definitely there. We do need some controlled governance on metadata and data warehouses.

8:29 But I think it’s become kind of one of your new sources, one of your sources, to newer BI analytic processes to your data scientists, and your power users, who actually maybe extract data from that data warehouse through a framework package, and then integrate it in other tools that allow them to do that data modeling, much like Tableau and Power BI.

8:51 You know, they’ve come from the perspective.

8:53 Like, users want to model, users want to integrate different sources of data. They don’t want to be hamstrung by data warehouse. Where it takes months, weeks, whatever to add more tables. When I go and get the tables myself, so, it’s a kind of a changing landscape, and it’s good to be aware of that. And also the fact that IBM has stated there will be no future enhancements for this product.

9:18 It might be a good time to start looking at when how can I use a newer modeling tools like data modules, in Cognos Analytics 11?

9:30 Now, Cognos data models itself on the converse is a web based tool.

9:37 Unlike Framework Manager, which requires installation on the PC desktop, it’s end user focused.

9:44 It allows the end user to do their own data blending.

9:48 A term that you always hear in Tableau, we can data blend. Well, as what data models can do, it’s also a good modeling tool from even basic modeling, much like, framework manager against a relational database, and there’s some transformation things you can do within the tool.

10:04 And this tool debuted initially with Cognos 11.

10:08 And it’s really IBM’s response to the whole data democratization trend in the industry.

10:14 But we need to be able to have end users, power users, data, scientists, access, to more data, and have a more agile approach to blending and data modeling.

10:32 So, it’s IBM’s response, I believe to tools that really kind of got the mindshare over the last 5, 10 years like Tableau and Microsoft Power BI.

10:43 And, for those of you who might be struggling out there with people in your organization using Tableau on Power BI and maybe just using Cognos Analytics as an extraction tool, Allowing users to use the newer tools within Cognos will help you maintain that foot footprint if that’s something you’re interested in doing. And trying to maybe standardize a little bit more on Cognos analytics and give it a lot more use across the end user community.

11:12 11.1 significantly closed some on the lot of the technical gaps between FM and data modules. So you modelers out there who have looked at data modules and 11 point no, throw your hands up in the air and say, well, I can’t do this. Can’t do that. 11.1 was released. That really focused on that. We’re going to talk about some of those things here.

11:37 And, of course, all future developmental resources within IBM will be focused on data module enhancements.

11:44 So data modules will continually get better, and a framework manager will be, will be staying exactly where it is in terms of its current development life cycle.

11:59 This slide is just really trying to show you that data modules is an integration point of many different sources. Now.

12:07 On the right are the data modules.

12:10 We basically have databases that data modules can connect to directly, and bring in tables through data server connections.

12:17 We can actually use data modulates to go against your FM packages if you need to. So if you have a lot of FM packages out there already, you still want to leverage data models for certain use case that you could connect directly to the F and packages that are out there, which will eventually go back to your databases.

12:35 And, one of the big things, of course, is uploaded files.

12:38 Your users can upload your Excel files, CSV files, into data modules for datasets, data modules for data blending, And then the new dataset feature up there on the upper right allows people to extract data from Cognos database from databases that are exposed to Cognos create subsets of data that are now stored on Cognos or faster, faster performance and integrate that in the data modules.

13:08 And what’s really interesting so data models can feed other data module. So the permutations is quite large of what you can do and it just really a matter of architecting it properly. But it allows you, that functionality is it allows you a lot of different ways to model and integrate and blend and cleanse.

13:28 So the key thing a lot of FM folks are waiting for, potentially, like, what are the what, and the current state level zero point one, release six?

13:38 What our data module is still missing that might stop you right now, anyhow, to use data models?

13:47 Well, first of all, it does not do DMR demand dimensionally model, relational models, at this point.

13:53 Now, for those of you who have never used it, this may not be a point that you care about.

13:59 Personally, I think GMR models are not necessary anymore, especially with the way a lot of the features are available in Cognos, like, navigation past, you could kind of simulate that hierarchical model.

14:13 And DMR models require a different skill set and reporting, so you’ve got a certain set of report developers, who can write reports against relational models. But DMR is another different set of skill sets you have to have.

14:27 But, again, if you, if you are, if you have DMR requirements, you need to use it. Data Module, student that still did not do that.

14:35 Object based Security, and I hope they could really fix this one up soon. That doesn’t exist still in data modules.

14:43 You want to hide a particular table, are a particular field from within the model of data models, on only exposed at a certain groups, we pay can see this table, group B can’t see that table, that doesn’t exist. So, you have to do workarounds around that. And I’m thinking this is going to be on the upcoming release. I would love to see that there. We could simulate packages better with that one object based security is in place.

15:08 I don’t know how many people out there have used team based modeling.

15:12 I personally have only used team based modeling once where multiple modelers can model against the same FM model, using model of branches and bridges.

15:25 That’s not in there. I don’t know if that’s going to be in there, But, again, I don’t know how many teams out there have actually use it, but, if you absolutely have to have that, that’s not in there.

15:36 Another Big one is parameter maps. This allows for the, is, in one particular, use case for multilingual packages, depending upon your locale of Cognos. So, it could tell you where it could it could based on where the locale of Cognos is set up? If I’m using it for English, if I’m using it for France, He could dynamically change the views of the data through parameter maps. That’s not in there. Another case where I’ve seen parameter maps users for fairly complex row level data security implementations, you might have security tables. You might have to have some fancy macro substitutions in your query subjects. And then, leverage and Parent parameter Maps For that. That’s currently not there. I think that’s on the development timeline, but that’s not in there, either.

16:26 From a style perspective, FM style namespaces packages the whole way we model on FM is different and Data Module. This can become somewhat simulated.

16:39 But it’s a different kind of thinking.

16:41 If you’re trying to do a 1 for 1, Manage the data modules is not really there, but I think some of the functionality is there, sit through some of the newer features that are there in data models, and we’re going to go ahead and discuss that.

16:56 But what do data modules do, what data models do, that framework manager can’t do. Some of, these are pretty big.

17:02 Because as the development of data module’s continues, the gap between data models versus Fabric Manager gets bigger.

17:10 The number 1 big that I can see is the easy integration, uploaded files Like Excel CSV can’t do that in a famous manager.

17:18 And a lot of times, your data warehouse or your model out there, and the databases that are centrally maintained and govern don’t have other things that users need to be able to do analytics. They might have an Excel spreadsheet with a lot of different kind of roll ups from accounting codes to job categories are things like that that they maintain. And they want to be able to blend enjoined that in their queries. So what happens is they get the extract from Cognos bring it down to Excel. They’ve got there.

17:49 Roll up file on a different tab and they do their V lookups there. Well, now, with this weekend, have end users, bring those files up, and then do the data blending within data modules there.

17:58 Simple data cleansing is also built in, for, for debt, for data modules. We can create data groups in that area.

18:07 We can quickly make Uppercase, Lowercase, and Conversions, things like that are kind of built-in to data modules.

18:16 Easy hierarchy creation.

18:18 So, navigation paths, where I could go, create my Hierarchy from Product line, the product type to product, within the model itself. So, the natural drill down is in there.

18:30 When you write reports, are due dashboards, that’s already built into data modules, Automatic creation of relative time, filters are now in there.

18:39 I have a separate webinar that kind of shows you how to do that. You could look it up on the Senturus’s site.

18:45 This has always been kind of a head scratcher within framework manager to kind of do this with complex filters, neighbors, and database changes on a time dimension. But, this automatically is done for you with data modules. Once you put that in, A plugin is fairly simple.

19:01 With the use of datasets, you can bring datasets in and, as you use datasets extracts, did extracts from Cognos itself as you as users use it? They go into in memory. So, these highly formatted parquet files will be placed in memory on the Cognos server and they kind of a job in a round fashion. But, automatically, that’s built in once you integrate datasets into your, into your data module data grouping. We mentioned, and web based interface. The web based interface, I think, to me, is one of a good thing, and a bad thing, especially for those of you who have used Framework Manager for quite a while. Because the PC is, has a lot more finer control Things, like modeling tasks. But what, the web based interface. So, you don’t have to download a program. So I think the web based interface has come a long way since 11.2 release.

19:54 But I think that particular piece of the modeling tool is going to keep getting better and better, OK.

20:02 So, what this webinar is going to address in terms of a demo, are things that typical FM modeling tasks that have been maybe more of an issue within data modules, or they weren’t there 11.

20:17 And you might want to see how we can maybe work around that, or have the new features kind of remedy that.

20:25 Um, like I mentioned, the web UI is not as precise and snappy as FM, but it’s getting better. The three demos we’re going to kind of do is the fact that FM does not have the kind of, data models, do not have the cost of the layers, and namespaces and mala query subjects.

20:42 Alias shortcuts for time dimension type role playing things and determinants, OK? So, we’re going to go and talk about that real quick.

20:50 So, namespace is a model a query subjects. So, the typical FM paradigm of layers and namespaces are missing and data modules.

20:59 No, I want a physical layer. I want to be able to hide that. How can I publish a centrally controlled data module on the right you see?

21:07 And, after working with data models for quite a Bit and I keep looking back or fabric manager, I look at it as it looks pretty complex. Actually, if I was an end user, so, those namespaces are aren’t there, but for those of us who do Favorite Manager every day, this is something that maybe stops me from wanting to do this. So, let’s go over to Cognos right now.

21:31 OK, so, this is actually, we’re running 11.1 Release six and, for those of you who haven’t seen it, there’s some cleanup that they’ve done, Some of the icons Look a little Bit different.

21:42 I think it looks a lot better if, tighten it up and we’re going to go here and create a new data module right away.

21:49 OK, I’m going to go into my Data Source Connections data servers. And I’m going to go ahead and pick the Great Outdoors Sales database that’s available.

22:01 With Cognos.

22:02 I’m going to select my tables and I’m going to pick the order details, the order header, the product table, the product line table, and expand on that a little Bit here: the product name, lookup table, and the product type.

22:20 So, I’ve got details, header, product, line, name, and tight, and say, OK, brings us into the data modules in, if you haven’t seen data modules in awhile has been a lot of changes.

22:34 The relationship view is here and right away, data modules will inherit the joins that are in the database if the joins are defined by foreign and primary key constraints, and that’s what it has done. And you can delete them if you want.

22:53 I’m going to bring the product name. Organize this a little Bit better.

22:57 Product Name Lookup over here, Product line, product type, OK.

23:04 Then over here is Older Details and order header. So this is a typical scenario, right? Is more of a maybe an OLTP type schema basically brought in my physical tables? My database for you if you’d like.

23:20 I’ve got it over here, and generally what I want to do is try to make this schema more presentable to the user, and make it maybe more dimensional, right.

23:31 Let’s go through some of those activities then.

23:34 So, for example, I want to maybe, really just have a sales fact table.

23:39 Now, with the new release of data modules, and 11.1, we were, we were able to create custom tables.

23:48 The newest release, and I think it was five, they have custom tables over here, so, you can kind of see what they are.

23:54 But, I’m going to create a custom table across a two tables, details a header, with new table, and, I’m going to create a new view, when I create a view of the tables, it’s going to inherit the joints that are physically are recreated across those two tables.

24:11 If they’re not joined, I can create a join view here, as well.

24:14 There’s a lot of different areas where I can actually create different views with tables, but we’re going to go ahead and create a view of those tables.

24:23 And I’m going to create those beamed by just clicking Finish, and right away here is my new view of the tables.

24:31 This is kind of U 2 Release 5 custom tables. If I click on this, it shows me what those tables are made up of, OK.

24:41 And also, new to Release five is the ability.

24:44 And this was a big asked by FM modelers the query information underneath that.

24:49 So this didn’t exist for awhile and a lot of FM modelers, throw their hands up, and say, well, if I can’t see, what query is made up of that view, I can’t have fine grained control over that, OK?

24:59 So over here, it shows you the fact that it did inherit the join across those two tables. It shows you the SQL. So that’s kind of you, and that’s kind of really, what these features have been added in, to kind of addressed the gaps that FM has, versus Data modular data models has, versus FM.

25:16 OK, I’m going to go ahead and rename this table to Sales Fact.

25:26 And you can see by the icon, it’s a little different.

25:29 So this is basically a view. And what, basically, this is as a model, a query subject in a sense, create a model Query, subject across order, header and details.

25:39 And I have it over here. Now. If I could really draw a line across this here, I have my physical namespace layer. And here is my presentation layer in the sets.

25:49 Now I want to flatten these tables out here also, because I want to make a product dimension. So people don’t have to go to multiple tables. I could do a shift, click over this.

25:59 Highlights it over here, it highlights it here, right click. And I’m going to create a table. Again, I’m going to create via tables, and say next.

26:06 And I’m going to select the columns I want, because I don’t want all of these to these columns because these tables have a lot of support for multiple languages. And I don’t need all that stuff.

26:16 I guess. I just need, I’m going to go clear all and de select the columns.

26:20 I was like product number, base, introduction date, discontinued date, OK From product line.

26:29 I’m going to pick the product line curve the English name version of that from product name, look up.

26:35 I’m going to want the product name and description. I’m also going to want the product language. And we’ll talk about why I want that in a second.

26:45 And on product type, I’m going to want the product code, Type curd and the product type English name.

26:55 Do I got everything in here, Product line, everything, good things I’ve finished?

27:00 And there it shows you exactly what this view is made up of this Model query subject can assess. I’m going to rename this guy to my Product dimension.

27:15 Now one thing I want to show is the product name, look up lookup table. Then we have a grid panel where you can see that. The data, and that’s one of the cool things about it as I started working with as being of being an FM model for quite a while. The ability to just kind of look at data really quickly, is very nice. OK, you can see that we have multiple product languages for each product, and if I left it as that, I’d get double counting when I joined this to my sales fact table. So it wasn’t a pair when I edit that. When I created this View as well, where do I put that filter?

27:47 And it wasn’t really in the Create Table dialog box where that is actually over here now, Properties. So you see filters over here.

27:59 I could put a filter on that view, and it allows me to say, well, what kind of filter do you want to put as a column? Do you want me doing a column, which I do? But it also has this expression editor. Now, I’m going to click add a filter for expression editor. It basically comes up with the expression editor for reports.

28:16 I can even use macro substitutions in here. If I do want to do something more complex things for this view, OK? So that’s, that’s in there now.

28:26 But what I want to do is, I want to add a filter based on the product language.

28:32 I want to do add a filter is going to distinct on that, and I only want to filter on English.

28:39 And I’m going to say, OK. So right now, the icon now is showing you that we’re filtering this product Dimension view.

28:47 And this is the filter we’ve added. And if we again, look at the query information, we could see that as put the where clause right there, OK.

29:00 Going back to my relationships, the next thing I’m going to do is create a join relationship, a typical star schema type, Join relationships here, from product to Sales, fact, product number two, product number, match, my selected columns, and say, OK.

29:19 Yeah.

29:22 So I’ve done kind of the core thing of creating the start of a star schema. And within here, within here, I could much like a Model Query subject, presentation layer.

29:33 I could actually move my columns around within the product dimension here. I can rename my columns.

29:42 Just get rid of that, Ian, for example.

29:48 And I could also know Crepe folders here for measures.

29:57 Make it more, presentable.

30:04 To the end user, much like you do in the presentation layer and FM.

30:10 Like that, OK, I’m going to go ahead and save this guy, save as in my live webinar folder. I have a backup there, in case I need to, but I’m going to call this as DM two, for example.

30:26 Save that off.

30:30 And the other thing I’m going to do to make it more presentable to users, I have all these tables here which now these reviews off and Manager, I would create a package and hide the tables that I don’t want people to see.

30:44 What I could do here is create a New Folder, and we’ll call this the physical layer.

30:53 And I’m going to just pop all these physical table’s back into here.

30:57 And I’m going to go ahead and say Hi.

31:03 OK, so now I’m going to go, Dave, that one more time. I’m going to go back out here. Now you could, if you wanted to, click, try it, it’ll bring you right to the reporting tool. I’m going to go a little, some different I’m going to do, but a typical user would do, I’m going to go to my DM two. I’m going to right click on this and say create Report.

31:26 So now my data module looks kind of like a package, right?

31:30 And I’ve hidden the physical illness of that, and users can now write reports against this, any easier fashion, because my measures are over here. My product dimension is all one flat dimension.

31:44 And I want to, maybe, product line by, product type, insert that. I’m going to insert quantity.

31:55 And then run that query.

32:02 So there we go, OK.

32:06 Let me go back to my data module.

32:08 So we’ve, we’ve done that. So let’s go back to that, what we were talking about. So namespace is the mala query. Subs, OK? It’s not a 1 for 1, right? But it’s kind of a leaf. We’ve kind of fill some of the functional requirements to maybe do something like that.

32:22 The other option, as I say, how can I publish a simply controlled data module?

32:26 What some people might want to do is crater on data modules but they don’t want to know how to bring all the tables and do all the join from a physical perspective. But we can do is maybe just publish out a data model that only has a physical layer in it. It’s it has everything there. We might put a read only stamp on that and then people can work from there. So, if I go over here, let me go to.

32:56 This folder, I have a physical layer over here.

32:58 I could actually create another data module off of that.

33:03 These are links.

33:05 The end user will have no way to change that.

33:08 But then I could leverage the ones, the links here, that IT has created. And I could then create my table over here that I can create my sales fact this way.

33:22 This becomes my sales fact folder or query subject, model query subject, OK, another alternative way to do it, uh, in publishing centrally control type IT maintain data modules so that some of your more power users, your data scientists, whatever. Can use those. And create their own data modules and build their own presentation layer on top of that, OK.

33:53 So as you saw, one thing that I kind of glossed over, I kept changing this and saving it. And what’s the reporting to the right away? I didn’t have to publish a package, right. I saved it and published the package, I save, than I went to the reporting tool. I wrote my own reports real quickly.

34:06 So, the Prototyping the Agile ness of the tool is much faster than, obviously, framework manager, where I have to model everything on the PC. I’ve got to set up my package.

34:20 I got to publish it, you know, and then I go back into Cognos, then I look at the package and, you know, that whole thing is all within the web tool, OK?

34:29 That’s another differentiating factor.

34:31 OK, let’s go back to our slide deck real quick. The next one here is the concept of alias shortcuts. This is a very important piece of modeling the modelers love to use. If you look at the framework, manage your package, a model of there. We have the sales fact. They’ve got two dates over there. They have the order date, and they have the ship date, and I need to be able to analyze across two different time dimensions. I got one physical time dimension table.

34:59 And in Fabric Manager, I was able to right click and create alias shortcut off that physical time dimension, and use that and leverage that, OK?

35:08 There is no right click Create Alias shortcut in data modules. But let’s go back to data models again.

35:16 So I’m going to bring into my time dimension.

35:21 Over here, Add more tables, I’ll look for it.

35:27 Again see, the search is pretty nice, is pretty quick time dimension, and say, OK, there’s my time dimension. The one thing I also mentioned, as aside from a maintenance perspective, released six got really good to happy. From a usability to maintain these modules, because what we didn’t really have before as a way to reload the metadata to see what tables we have in our data model versus the physical tables.

35:58 Here in our sources, we see all the physical tables that are available. I’m going to go on my time dimension over here.

36:05 And I’m going to maybe delete some of these fields. I don’t need these fields.

36:12 From the physical table that I brought in.

36:15 There’s a new feature in here, which shows unused items.

36:19 And if I expand my time dimension over here, scroll down.

36:24 These aren’t there, so if I did, not, if I deleted them here, I can actually, now, individually, from a field perspective, just drag it in before there was a real pain to kind of get that done. And also before, somebody, physically, if the DBA changed a table, and added more fields, it was hard to refresh. That had to go back to the data source connection and refresh it. Well, now you have a right click here and you can see reload metadata.

36:50 OK, so another FM modeling issue was, Alice too hard to maintain the field’s command? It’s really hard to bring in a new fields on that, OK, so that was another thing that was kind of fixed. And basically major your life easier to use.

37:07 All right, so back to the original thing, the alias shortcut. I have a time dimension over here.

37:13 Aye, want to be able to create alias shortcut? Well, it’s not there but we can leverage this whole concept of custom tables. We’ve already created a couple of these. We can go to the time dimension.

37:25 Say, you table.

37:26 We’ll create a view of the table.

37:31 And I don’t want all these fields, for the purposes of this demo, I’m going to clear all, I want only the day date. I want the month key. I want the current month.

37:42 I want the core to key our current quarter, I want the current year, and maybe I want the month, English name.

37:51 I’m going to bring that in.

37:53 Say, Finish.

37:54 Now, I’ve got a view. Now, my views are kind of building up, I have another view over there, OK.

38:00 Um, and the one thing about this, and brought the current month number, and current core number and as Measures, by default, because it’s in America.

38:10 Variant Manager had a really nice feature where if I did that and drag down the attribute column and it’ll change that, thought, we have something similar here as well, I can multi select that column over here, go over to my properties, pain, which is over here.

38:29 And change the usage over here to say attribute, which is what I want to do. OK, and I want to make this, the ship dimension.

38:42 So I’m going to, I’m going to rename this, the ship date dimension.

38:52 Now I also want to be able to have an auto date dimension here as well. I could do the same thing, I could create a custom view of time dimension. But it’s a really nice copy and paste type feature over here. I’m going to go ahead and paste.

39:06 And I’m going to rename that guy to be order date dimension.

39:15 OK, then I can rename my fields, making them more pertinent to be whether it’s ship or to date like I would want maybe.

39:24 This one here to be, you know, rename this to be the ship date.

39:31 Etcetera, I want that to be the ship year.

39:38 Same thing with Order Date Dimension. I want this to be the order date.

39:45 And I want current year, to be order you.

39:53 These are my roleplaying dimensions.

39:57 Order date.

39:58 Ship Date, OK, get this filtered out of the way.

40:05 And, I want to now just simply join ship Date to my Sales Fact.

40:10 I’m going to clear relationship from here to my sales fact: Ship, date to ship date.

40:19 Or a date relationship, too.

40:22 Sales fact, or due date, or a date match?

40:27 Refresh.

40:28 We’re good, OK?

40:30 So, you can see my star schema is getting linsey built out over here.

40:35 I’m going to save this.

40:38 Also, what’s a nice feature? I’m going to go back to my reporting tool to test this out.

40:42 All I have to do is refresh loads backs back up.

40:48 And I’ll bring it back in.

40:55 Well, it should have.

40:58 Live demos.

40:59 So I did that, say, uh.

41:09 Say that again.

41:10 Let me go back out here and OK, I did bring it in. I just wasn’t waiting long enough. There we go. So there’s my ship date dimension.

41:20 And I’m going to go over here, delete that.

41:24 I’m going to do a list.

41:28 We’re getting an error as heroes.

41:31 I’m going to close this report down.

41:35 Going to go back over here to my live webinar, Enter, or write that report again, Create report.

41:49 OK, now let’s dial list.

41:52 OK, so my ship date, I’m going to bring in a ship beer.

41:58 I’m going to bring in order here.

42:03 And I’m going to bring in for my sales fact.

42:08 The measures?

42:11 Order quantity, OK, and I’m going to go ahead and run this.

42:24 I actually should have put order here. Let me do that one more time. I’m going to actually, put order for us to make it easier to look at.

42:33 Me too!

42:36 Order year! Over year.

42:38 All right, let’s rerun that again.

42:46 So I’m basically now running a query across two roleplaying time dimensions, so as you can see, things that were ordered in 2010.

42:57 Had things that were shipped in 2011, the next year. And things that are ordered in 2010 had things that were shipped, and also in 2010.

43:05 OK, same thing across here, so that shows you the fact that I’ve been able to slice across two time dimensions with that data module.

43:16 So going back to the data module.

43:18 Let’s clean it up again, and I’m going to put the time dimension back into my physical layer.

43:24 I’m going to go back over here.

43:26 Now, guys, ship date order, product sales, save.

43:33 Let’s test the Cognos gods and go back to that, you report and refresh that and see how fast now? It was faster? That was good, OK?

43:45 By the way, our instance of this is on Azure. We have Cognos on Azure installed so I am going to go into the cloud to run this. It’s pretty snappy, as you can see just a little side note, so I made my change the data models real-time. I saved it. I went back to my report refresh data and now I’m building out my model here. Now, I’ve got to ship date, order date, product dimension, sales fact that I’ve done fairly quickly in this modeling exercise.

44:14 Trying to address some of the key issues as you move from framework manager, how I do, for example, uh, roleplaying dimensions and namespaces, et cetera.

44:26 The last topic we did alias shortcuts, custom tailored views, and it’s always been kind of a head scratcher and but for those professional modelers are those Mahler’s power users and know how to do this and framework manager. It’s a really important feature to have. This feature did not exist in Cognos Data modules: Data Module 11.

44:50 It now, it started to exist and 11.1 and has done better for the releases, determinants, manager, versus columned dependencies.

44:59 We need determinants when we need to use one time dimension, as say, the day, grain, and we want to use that same time dimension and cross multigrain facts.

45:11 So, for example, I’ve the sales fact that I’ve created is at the date granularity, but I have sales targets at a month level.

45:23 I want to be able to do cross joins across those things across the two for cross fax multi queries with the same time dimension.

45:31 In Fabric Manager, we use determinants, and here’s the screenshot. And every time I don’t do framework manager modeling for awhile and I have to look at the determinant screen, I always have to scratch my head and say, how does this work? Again?

45:44 It was never really that intuitive.

45:47 And if you can imagine, end user looking at this screen, they would, again, probably just throw their hands up in there and say, that this is too hard, right? And that’s what we’re trying to get away from. Would not want that? We want this power to be enabled in the end user’s hands.

46:04 So Data module’s has simplified this concept with the concept of column dependencies.

46:11 So let’s go back over here, and I’m going to bring in another table.

46:19 Add more tables, select tables.

46:21 And I’m going to bring in my, look for it, look for it. Sales target, here we go.

46:30 It inherited a relationship. I got to get rid of that for the purposes of this demo.

46:35 Here’s my sales target.

46:37 If we look at this data from the Grid view, we have sales targets over here, at the grain of Year, month. And even at a higher level of product.

46:49 We could put determinants on product as well.

46:53 But what I want to do, and, again, for the purposes of this demo, I want to be able to use one time dimension, we’ll say, ship date dimension across the two, Right, if I were to create a relationship to sales target right now, and the grain of sales target is year and period.

47:14 And I went from ship year to sales year and current month to sales period, Mats selected column, and say, OK, right, and say I’m done.

47:27 The effect is this, I’m going to save this go back to my report, Refresh.

47:36 Sales target is now there.

47:39 We’re going to delete this real quickly here.

47:40 We’re going to create a two column report, over here.

47:45 I’m going to create a list, and I’m going to query my sales target table directly.

47:52 I’m going to bring in these three guys.

47:56 Over here, I did a join to my ship date dimension.

48:02 And I’m going to leverage the date field from ship date.

48:06 I’m going to put in that **** beer.

48:09 Click off of this. I’m going to put ***** Beer.

48:14 Current month number.

48:16 And I’m going to put in that sales target, and start over there. Let’s run that query.

48:28 Alright, so this left one is from the Ross, the raw sales fact.

48:34 Table by itself. This is the right number.

48:37 Sales target for January’s, $57 million.

48:43 But, over here, the sales target was inflated 31 times because the date dimension is at a granularity of the day. So for January, multiply 2 times 31, February maybe 28, et cetera. So how do I fix that is the question.

49:02 Let’s go back to Data Modules.

49:06 I need to be able to create a determinant on the date dimension.

49:09 And with data models, we now create column dependencies. If you right click on here, there’s an option called Specify, Column, Dependencies, and this is really easy.

49:20 Very intuitive.

49:22 So, for example, I’m going to start with ship year. That’s a top level.

49:27 And I’m going to bring in the key for quarter level is the quarter key.

49:33 And all you have to do is drag the relationship here. So ship goes to Quarter.

49:39 Quarter goes to month, and drag it over here.

49:43 Month goes to Day.

49:51 And one thing about data modules, it does a constant data validation, or validation of your model. And right away, it tells me there’s something still wrong.

50:00 If you click on this validation issue, and you show the details, what it shows you is that there are certain columns in your column dependencies that you have not accounted for, current month, current quarter, month, English name. If you actually went and query included, those who wouldn’t know what the dependencies are.

50:18 So, all we have to do is clean that up. So, current month.

50:22 Number is really an attribute of month key. We’ll put that over here.

50:28 It really validates and the list is getting smaller.

50:32 Current quarter is an attribute over here.

50:36 OK, and we’re down to month end, which is the month, English name or the short abbreviation, and that’s going to go over here, and the validation will go away.

50:48 I’ve established my column dependency, and there’s also a vertical view you can look at.

50:53 However you feel, it’s most useful, OK, so now, this relate this, this. This date dimension is now properly set up.

51:02 Let’s save it.

51:05 I’m going to go back into my report.

51:09 Refresh the data.

51:12 Let’s rerun the report.

51:20 I was hoping you would come up with the right answer, and I did this being a live demo.

51:25 So you see now, even though I’ve used a time dimension that’s at the day grAIn, across a fact table, that’s at the month green, I get the right answer.

51:40 OK, and now I can make proper queries with sales Face to get me a multi grain, a multi fact query. I’m just going to use Quantity for this example.

51:53 I’m going to put it over here, and then, run that. It’s not going to be really a good comparison of Sales target, MLIS. I just wanted to kind of show the query information on that.

52:07 OK, so that works, what does a query look like for that?

52:14 Query two?

52:18 Lot of query, But what I want to show you is, it has the Coalesce statement right here.

52:24 That’s what it’s doing.

52:25 Two queries against two fact tables, using a date dimension, that a different grain, then one of the fact tables, OK?

52:34 Go back to my data module.

52:36 OK, so in the span of, really, actually, if I were to do this without talking, and I knew what I was kind of doing. This is really a 15 minute deal. Right?

52:45 And I’ve kind of addressed some of those issues with Fabric Manager, Right? So back to my slides, over here.

52:54 To me, that was a lot easier than trying to figure this out, and then publishing the package.

52:59 As you can see here, there’s a determinist uniquely identified group by, what are the attributes for it? Very complicated, from an end user perspective.

53:10 So those were the three things I wanted to demo today. The takeaway from this webinar, as, I hope, that you can see that the gap between Data Module modeling from a professional modeling perspective. From those folks, who are you constantly used to using framework manager and creating a package?

53:30 That Gap is definitely narrowing release six with that refresh metadata, but there’s a simple button, but I remember I was working with customers and saying, until I can do that, I’m not going to touch it because I don’t want to be able to delete tables and bring things and all the mast and redo everything. There’s no way I’m going to put that out into production. I can’t, I can’t maintain it, OK. They fix that. That’s done, and that was a huge thing. Again, this webinar was kind of tailored to the FM folks and to show you what you can and cannot do.

54:05 You’re going to have to make your decisions on whether you want to start that new modeling project with data modules. I think you should definitely give it a hard look at first.

54:18 Before you think about maybe starting a brand-new project with framework manager packages, you go through the list of what it can’t do.

54:25 If you have slightly habitat parameter maps for link languages, complicated security models, you need object level security, for whatever reason, and I think that’s going to get fixed soon. Then, you go, you’re going and you have a deadline. You’re going to have to go back to your fabric Manager packages, you know, but it allows a lot more functionality and we didn’t really talk about the functionality that end users can use us for. This webinar was more geared for framework manager versus data models. But, there are probably a whole set of customers out there who are bringing coming in, maybe Cognos Analytics, 11 clean without any of the legacy history of it.

55:04 And using data models. Because that’s the modeling to know. And even consultants out there, you’re going to have to maybe go into customer bases where they’re going to maybe say, I don’t want to use Frank Manager and for whatever reason, to use a data modeling tool. But you have this correct, or you’re the kind of still leveraging your knowledge on…

55:20 Manager, and you want to be able to take those best practices that you’ve learned framework manager and possibly. Go and use it in the new modeling tool the modeling tool.

55:31 That’s going to be enhanced and all development resources are going to so that is I was able to do it.

55:40 At 11 55 I was a little worried about was a fairly complex demo with Pedro that up so I’ll get back to Mike.

55:50 We’re moving going to move pretty quickly here. We might run a little past the top of the hour mm stick around, folks. So there’s a there’s a million questions, Pedro. So go ahead and take a look at those projects working from the top-down. If you don’t mind random past the top of the hour, people will get into those questions in just a few minutes before. We do a quick poll in terms of asking you going forward. What do you plan to do in terms of Cognos metadata modeling? And you can select all that apply. So are you going to use framework manager data, modules something like Microsoft Analysis Services, or something else?

56:23 So go ahead and get your votes in there.

56:26 Give me all about Nan to do this, since we’re on pretty tight time here.

56:36 Great. Kind of a two thirds, you getting your votes in here. That’s great. Alright, I’m going to close it and show it. And so it’s still two thirds, and then a whole ton, almost 83, 83% here using data modules. And than a smaller percentage of Microsoft, and other undecided.

56:53 All right. Thank you very much.

56:55 So, before we get to the, if you could advance the slides, a couple of them, Pedro, to the gut cognitive challenges. So in your organization, one more if you have if you’re suffering from time consuming data prep, where you can’t drill down to from summary to detail, you have performance issues, or for user adoption, we can help you with an architectural assessment that we do, that involves best practices, modeling.

57:19 We do a health check on your metadata, performance tuning, tips on enabling self-service culture, and selecting the best tool for the job, for example, using framework manager and or data module’s as appropriate.

57:33 So a little Bit about send tourists here. We are the authority in business intelligence. We concentrate our expertise.

57:41 We go to the next slide, Pedro, on business intelligence with a depth of knowledge across the entire BI stack and next slide, please. Our clients know us for providing clarity from the chaos of complex business requirements, disparate data sources, constantly moving targets, and ever changing regulatory environments. We’ve made a name for ourselves because of our strength and bridging the gap between IT and the business, by delivering solutions to give you access to reliable analysis ready data across your organization, enabling you to quickly and easily get answers at the point of impact, in the form of the decisions made, and the actions taken.

58:15 Next slide, our consultants are leading experts in the field of analytics with years of pragmatic world expertise. And experience advancing the state-of-the-art, everything from front end dashboard, reporting, and visualization creation, through Data prep operation, all the way through infrastructure, and, of course, training and mentoring.

58:31 We’re so confident in our team and our methodology that we back our projects for the 100% money back guarantee that’s unique in the industry.

58:39 We’ve been doing this for awhile, but at it for almost two decades, Focus exclusively on business intelligence. We work across the spectrum, from Fortune 500 to mid-market companies, solving business problems, across every industry and functional area, including the Office Finance, sales, and Marketing, Manufacturing, Operations, HR, and IT. Teams both large enough to meet all of your business analytics needs, and yet small enough to provide personalized attention.

59:04 Again, we invite you to visit the Senturus’s website, the Senturus Resources tab, where you can find all our webinars and the recordings on the deck and everything behind those until all kinds of great information. Our blogs and tips and tricks and everything on that site bookmarked upcoming events. We’ve got another great webinar coming up on Tuesday, July 28th, same time, same Channel, Cognos analytics dashboards, or reports. So, come in.

59:31 Visit us for that. And, of course, we would be remiss if we didn’t talk about our fabulous training.

59:37 We offer training in the top three, three top BI platforms, IBM Cognitive Analytics, Power BI, and Tableau, right? Deal for organizations running multiple platforms, or those moving from one to another. And we provide training and many different modes, It can mix and match those to meet and suit the needs of your user community.

59:59 Senturus provides hundreds of free resources on our website, and we’ve been committed to sharing our BI expertise for over a decade.

1:00:07 And now we finally get to the illustrious Q and A and so as you can see, Pedro, there’s a whole ton of questions here. I know, are at the top of the hour, so I don’t we try to bang out a few of those, and anyone, you can continue to put questions in the question panel here.

1:00:23 We do save that question log, and we will answer any questions we’re not able to get to, via that login will posted, along with the recording and the deck on senturus.com, and that Resources section.

1:00:35 So go ahead, Pedro.

1:00:36 If we want to bang out a few of these, we can we can do that. Yeah, and stay on the moon.

1:00:42 See, some can expand this one box, but maybe you could address this. Mike is IBM at least considering, I think. It’s a migration tool.

1:00:50 I don’t, I’m not aware that they are It’s certainly something that we in our labs are actively pursuing because we know that it’s, it’s, it’s feasible. So we’re looking to do exactly that to help people if they want to migrate from Framework, Benadryl models to data modules and have some technology to help them with that.

1:01:10 So if you’re interested in that that’s a need in your organization, definitely give us a call OK, can data modules reads synonyms? Well, that’s an Oracle question, I think, more specifically as you connect to an Oracle database through the data server.

1:01:33 If it’s part of the schema that you’re trying to get to my initial gut, is it does.

1:01:38 I’d have to actually verify that and look at it from an Oracle database and see that it can read a synonym, but if the user that you are logged into, can see it, I’m pretty sure the data module can see it.

1:01:52 Procedures, stored procedures? That’s a good one. There is a widower, right?

1:01:59 I haven’t tried it was stored procedures yet. But you can write manual SQL and if you can make manual SQL, you can do a create table and I didn’t show this one.

1:02:08 Create a manual table with, create a table, with Manual SQL. You could potentially, maybe, call a stored procedure, but I’d have to try that out, that’s a good question, sure, Sure, and then, someone mentioned about DMR and framework manager can. And I know you can create hierarchies and data module. So that’s kind of similar to that functionality, but not exactly equivalent, right? Not exactly equivalent. I mean, the EMR is very specific corner section of bench they can do, which makes it look exactly like an old … cube. That kind of functionality is not built into data models. Whether it goes in there or not, I’m not sure. We haven’t got to read from that an IBM yet.

1:02:48 OK, we had a question about Automating the Refresh, the data modules, I’m not sure exactly what that means, necessarily, because they’re not really.

1:02:56 Yeah, I think maybe it might. Unless you have less, you’re referring to data, sets, extract data that can be put into data modules, those things can be automated to be refreshed, because you can set the set those on a schedule.

1:03:11 It’s very easy to do from the user interface.

1:03:13 You create your dataset, and you set a property about a schedule, you enable that. And then when, when people hit the data modular, which contains a dataset, it’ll show the most refresh data. So, that’s the only thing about refresh that related to data models. Yeah. Yeah, exactly. Someone asked about the Senturus’s analytics connector and if it supports data module. So for those of you an initiated, we have a product called the Analytics Connector that allows you to use Tableau or Power BI to access your cognitive metadata. So, Framework Manager Models, reports, and Data module. So, they asked if we supported data modules. And the answer is, yes, we do.

1:03:50 Filters on the FM, Database Query subject, based on session parameters. Can you replicate that in data models? And I’m guessing that you can’t do that because of that. We don’t support parameter maps.

1:04:02 We don’t support parameter masks what I haven’t tried out. It does create a filter with an expression editor. And I’ve seen that it can reference macro substitutions.

1:04:11 Um, So that’s something to look into and try.

1:04:16 We’ve done that I’ve done some very complicated thing was parameter maps Macro substitutions and FM, like getting to security table and just in the where clause with session parameters, based on your ID, the user IDs, and the security table, etcetera. I would think that’s going to get better. And I know parameter maps are number one thing. They want to add data modules, but you might want to take a look at the area of filters, where you do a filter by an expression editor and see if you could use the macro substitutions to get. Maybe this has parameters there.

1:04:44 And try, try to play with it there and see that that, and that, that satisfies your requirements, OK, we’ll do one more here, and then we’ll probably to let people go here, shared data modules, Can they do the impact analysis like FM does, in terms of determining and showing you impacted reports by changes that you make?

1:05:02 No. Not that I’m aware of. Right. So, yeah, you’re right.

1:05:05 If data modules are being used by a bunch of reports, that particular functionality is not there OK. Great. Well, again, we have a ton of questions here. We will save the question. Login will answer that posted to the website, if you want to go to the last site. The last slide, Pedro. I want to thank everybody for joining us today.

1:05:27 And thank our speaker, Pedro Ining, for a very informative and information filled session. On behalf of myself and Senturus, thank you for joining us. We look forward to seeing you on one of our upcoming events knowledge series. Or you can reach out to us for any of your business analytics, needs, your training at senturus.com.

Connect with Senturus

Sign up to be notified about our upcoming events

Subscribe to our newsletter