Linking IBM Cognos Portal Pages through Prompts - QuontraSolutions

To demonstrate this technique, we will explain how to pass your prompt selections from one dashboard portlet to other report portlets within the same dashboard page.

Create the main portal tab

The first step in enabling this functionality is to fuse all the different dashboard pieces together by creating another, master dashboard page that will encapsulate two things:

1. The prompt.
2. The two report outputs.

To do this, start by navigating to the “Global Filters” folder, created in the previous article, in Cognos Connection and click the New Page button.

Name the page “Returns Analysis Dashboard” and click Next.

This time around, our page will have two columns, so we will have to select the appropriate radio button in the Number of columns: section at the top. Then, using the Columns width: drop-downs, set the left column to a width of 20% and the right column to 80%.

Click the Add… link in the left column and add a Cognos Viewer object just as we did in the previous step.

Then, in the right column, add a Multi-page object from the Dashboard entries.

Your portal page should now be laid out like the image below. If this is the case, click Finish.

From Cognos Connection, click the “Returns Analysis Dashboard” page and click the properties icon of the left

column.

Just like we did in Step 2, click the Select an entry… link and navigate to the “Prompt Report” (inside the “Global Filters” folder). Then, in the report’s properties, set it to communicate using the “promptChannel” channel.

Click OK twice.

Now click the properties button of the right column. Click the Select an entry… link and navigate to the “Detail Pages” folder as shown below.

Click OK.

The prompt and reports are displayed side-by-side.

Test out work by making a selection in the prompt.

To create a new tab in Cognos Connection using your new portal page, click the Add to my portal link (circled in red below).

A Tool to Standardize The Look and Feel of Your Reports - QuontraSolutions

Having reports with a common look and feel promotes user adoption as well as trust in the tools they are using. Have you ever noticed that some of your reports looks similar to others but may have some slight formatting variations? Text justified to the left instead of center? Font size or style not quite matching? Table formatting inconsistent? As is often the case with multiple report developers, over long periods of time you may find that your old reports are starting to look significantly different than your new ones.

Many developers use some sort of customized template to begin report development. While that is a great idea and a general best practice, templates may be too generic for every type of report, not to mention a big undertaking to maintain. Imagine having a collection of 50, 100 or even 300 reports made with a common template; Your legal team informs you that the disclaimer in your report footers require an immediate change. How would you go about accomplishing that in a time and cost efficient manner? The short answer is that you can’t!

A layout component reference is a widely underutilized item available in the tools menu. It allows one to create reusable objects within reports then store and maintain these objects all in one convenient location. Changes made to layout components are reflected in every report that refers to them. All of a sudden, changing the disclaimer in the footer for your 300+ reports went from a multi-resource, multi-day error prone low-level development nuisance to a 5 minute quick fix.

Customizing a layout reference is also easy and intuitive. A report developer can choose whether they want their objects to update automatically upon detected changes, or simply ignore future changes altogether. A developer is also able to nest objects, allowing them to create large multi-component objects with complex formatting. Child objects can be individually modified, especially useful in headers where a title may need to be changed per report.

While there is some cost (time) associated to converting all of your old reports to make use of this feature, the future benefit will outweigh the investment in the near term especially as the number of reports continues to grow. When time permits, schedule a time to convert your older reports. Next time you need to make broad changes you’ll be thankful you did.

Below is a screenshot of the Insertable Objects Pane where the Layout Component Reference can be found.

What’s new in IBM Cognos 10 Framework Manager - Quontra Solutions

While Framework Manager didn’t receive the dramatic changes in IBM Cognos 10 that some other areas did, there are still some interesting and useful new features, which might have slipped under the radar with the new release.

The main new features are:

• The ability to create “Durable Models”
• Model Design Accelerator
• Dynamic query Mode

There have also been several minor improvements in the way the IBM Cognos 10 handles SAP BW sources. If that’s an option that you are using, either contact Ironside Group and speak to one of our expert IBM Cognos/SAP consultants or check out the Framework Manager manual for details.

There is one notable feature that is deprecated in Cognos 10. Native Support for CVS and Microsoft Visual Source Save has been removed. Support for these and other code repositories is still available, but in a more generic fashion.

Let’s take a look that the major changes.

Durable Models

This is probably my favorite of the new features. The problem this feature addresses, is the situation where you want to rename Framework Manager objects, such as query subjects, query items, filters or namespaces after reports have already been written. If you change the name in the model and republish the package, the report will error out with references to data items which cannot be found. While there have been some workarounds for a while, this feature provides an elegant solution.

The key to making this work is to start with a new model, and select an unused language or dialect for the design language. I usually choose “English (New Zealand)” (EN-NZ). Then choose standard English (EN) as the active language. Finally, there is a new project property Use Design Local for Reference. You must also set this to true. This causes the report object names to be referenced internally in the report using the design language (EN-NZ or English–New Zealand) rather than the language of the report author (EN or base English). This is similar to authoring reports in multi-language environments, but requires no special actions on the part of the report author.

In this example, we added a data item called EXPENSE_GROUP_EN to the report

The Framework modeler never applied a user friendly name to the column. We add it to the report anyway, and save the report. The modeler then uses Framework Manager to correct the “Active Language” (EN) data item name to a business friendly name, such as “Expense Account Group”. The design language (EN-NZ) object name must not change.Republish the package. By simply running the report with the updated package, the renamed query item name and column heading is now shown in the report. This because the internal object reference in the report is set to the design language (NE-NZ or New Zealand ) name, which did not change. At run time, the report executes using the language of the user, and so displays the updated object name.

Looking at report studio, you will see that the report structure will reference the original EN-NZ description of the object (EXPENSE_GROUP_EN), which did not change. At run time, it will execute using the active language (EN or English base) name and column header, and therefore isolate the name change. Essentially, the report is built in one language (English – New Zealand) and run in a different language (English – base language). But this is all done without any special actions by the report author. And regardless of the language in which the report was authored, the object names are always stored internally using the model design language.

There are a couple of caveats to this method. Most obvious is that once you set the Design language name for an object, you must never change it. Therefore, you’ll need to have meaningful object names from the start. I suggest using the native database column names, where possible, as they are meaningful, not likely to change and are not required to be “business friendly” at this point.

Also, this techniques works for renaming objects only. It will not compensate for major structural changes, such as moving an object from one namespace to another.

Model Design Accelerator

The Model Design Accelerator is a graphical “wizard” which walks you through the design process for a relational model, complete with physical, logical, and presentation layers. Like most wizards, it doesn’t do much that you can’t do on your own, but does make the steps easier, while enforcing good design principles. You have the complete ability to modify your design manually, as usual.

You launch Model Design Accelerator from the Tools menu. The wizard presents you with a blank star schema model. You drag-and-drop items from your data source onto the fact and various dimension tables as needed. Simply add or delete as many new dimensions as you need. You can rename query subjects and items within the wizard, or later. When you are finished, Model Design Accelerator presents you with any warnings, then proceeds to generate the model, complete with the three layers, in separate namespaces.

Interestingly, it doesn’t automatically create a Dimensionally Modeled Relational (DMR) layer. That step is made easier, by directing you to think dimensionally about the model based on the star schema, but you still need to manually apply the various levels to the hierarchies, which can take some thought.

While this tool doesn’t technically add any functionality that didn’t already exist, it’s a nice feature, targeted towards the mid-level or beginner model designer. It will assist the occasional or new model designer think in dimensional terms and develop a solid three-layer model with minimal effort, and it does that admirably.

Dynamic Query Mode

Dynamic Query Mode (DQM) is an option to speed up query performance of certain cubes, by providing in-memory data caching. Dynamic Query Mode is limited to three specific OLAP data sources in the current release, namely TM1 version 9.5.1, Essbase versions 9 and 11, and SAP BW, version 7.1. Furthermore, the specific performance improvements you can expect differ according to the data source. In general terms, they support better null suppression, and improved repeatable performance, which is useful for the typical ad-hoc analysis style of query for which these cubes are often used.

Determinants – The Answer to a Framework Manager Mystery

Determinants can play a crucial role in the overall performance and consistency of your Framework Manager model but remain one of the most confusing aspects of the application to most developers. This article will attempt to end the confusion.

Determinants are used so that a table of one grain (level of detail) behaves as if it were another actually stored at another grain. They are primarily used for dimension tables where fact tables join to dimension tables at more than one level in the dimension. (There are other cases where you could use them, but they are less common and fairly specific situations.)

The Situation

Let’s use the example of a date dimension table with day level grain. If all the fact tables join at the day level, the most detailed level, then you do not need determinants.  But as many of us know from experience, this is not always the case. Fact table are often aggregated or stored at different levels of granularity from a number of reasons.

The Problem

The trouble arises when you wish to join to the dimension table at a level that is not the lowest level. Consider a monthly forecast fact table which is at the month level of detail (1 row per month). A join to the month_id (e.g. 2009-12) would return 28 to 31 records (depending on the month) from the date dimension, and throw off the calculations. Determinants solve this problem.

The SQL

Often when modeling, it’s useful to think about the SQL code you would like to generate. Without determinants, the incorrect SQL code would look something like this.

SELECT

F.FORCAST_VALUE,

D.MONTH_ID,

D.MONTH_NAME

FROM SALES_FORECAST F INNER JOIN DATE_DIM D ON

F.MONTH_ID = D.MONTH_ID

This code will retrieve up to 31 records for each of the sales forecast records. Applying mathematical functions, for example Sum and Count, would produce an incorrect result. What you would like to generate is something along the following lines, which creates a single row per month, AND THEN join to the fact table.

SELECT

F.FORCAST_VALUE,

D1.MONTH_ID,

D1.MONTH_NAME

FROM SALES_FORECAST F INNER JOIN

( SELECT DISTINCT

D.MONTH_ID,

D.MONTH_NAME

FROM DATE_DIM D ) AS D1

ON F.MONTH_ID = D1.MONTH_ID

As shown  above, the trick is to understand which columns in the dimension table are related to the month_id, and therefore are unique along with the key value.  This is exactly what determinants do for you.

Unraveling the Mystery in Framework Manager

Following Cognos best practices, determinants should be specified at the layer in the model in which the joins are specified.

Here we see a date dimension with 4 levels in the dimension, Year, Quarter, Month and day level.

This means we can have up to 4 determinants defined in the query subject depending on the granularity of the fact tables present in your model.  The first three levels, Year, Quarter, Month, should be set to “group-by” as they do not define a unique row within the table and Framework Manager needs to be made aware that the values will need to be “Grouped” to this level. In other words, the SQL needs to “group by” a column or columns in order to uniquely identify a row for that level of detail (such as Month or Year). 

The Day level (often called the leaf level) should be set to “Uniquely Identified”, as it does uniquely identify any row within the dimensional table. While there can be several levels of “group by” determinants, there is typically only one uniquely identified determinant, identified by the unique key of the table. The “uniquely identified” determinant by definition contains all the non-key columns as attributes, and is automatically set at table import time, if it can be determined.

The Key section identifies the column or columns which uniquely identify a level.  Ideally, this is one column, but in some cases may actually need to include more than one column.  For example, if your Year and Month values (1-12) are in separate columns.  In short, the key is whatever columns are necessary to uniquely identify that level.

Using our aforementioned table, the setup would look like this:

The Attributes section identifies all the other columns which are distinct at that level.  For example, at a month_id  (e.g. 2009-12) level , columns such as month name, month starting date, number of days in a month are all distinct at that level. And obviously items from a lower level, such as date or day-of-week, are not included at that level.

Technically, the order of the determinants does not imply levels in the dimension. However, columns used in a query are matched from the top down which can be very important to understanding the SQL that will be generated for your report. If your report uses Year, Quarter and Month, the query will group by the columns making up the Year-key, Quarter-key and Month-key. But if the report uses just Year and Month (and not the Quarter) then the group by will omit the Quarter-key.

How Many Levels Are Needed?

Do we need all 4 levels of determinants? Keep in mind that determinants are used to join to dimensions at levels higher than the leaf level of the dimension. In this case, we’re joining at the month level (via month_id). Unless there are additional joins at the year or quarter level, we do not strictly need to specify those determinants. Remember that year and quarter are uniquely defined by the month_id as well, and so should be included as attributes related to the month, as shown.

The Result

Following these simple steps the following SQL will be generated for your report. The highlighted section is generated by the determinant settings. Notice how it groups by the Month_ID, and uses the min function to guarantee uniqueness at that level.  (No, it doesn’t trust you enough to simply do a SELECT DISTINCT.)  The second level of group by is the normal report aggregation by report row.  So the result is that the join is done correctly, which each monthly fact record joined to 1 dimensional record at the appropriate level, to produce the correct values in the report.

What are Dimensional Tables in Cognos ?

Dimension Tables are also tables and contains descriptive information used to describe the rows in the fact table. Look at the following fact table row….

There is no product Name, no staff name, no Customer name. Without having these names, how do you describe the qty_sold as 1 and unit_sales_price as 200..?  We can’t..!

By looking prod_id (3), customer_id (5) in the product dimension and Customer dimension, we will get product name as “Santoor” and customer name as “Jhon“. So, by using dimensional tables we can describe fact rows with information such as product name, customer name etc..

Fact less Fact Table in Cognos – QuontraSolutions

Fact less Fact table does not contain any facts. Generally Fact less fact tables are used to record the events such as students attendance, attendance of participants for an Event like a Meeting. Actually this is not a Fact table but due its position we should call it as Fact table without facts.. Look at the following image..

See the attendance Table in the Image, There is no measure or fact but Attendance Table sits like a Fact table and also have the connections to dimension tables and hence we can say that it is Fact Table and since it does not contain any measures so that we can call it as fact less fact table.

Now Let us understand the use of the Attendance Fact less Fact table….

If a student attended a class then we enter a Record in Attendance table else no row will  be stored. Only aggregation here is possible only count.

Mouse over pop up with our own text box using java script in Cognos – QuontraSolutions

Cognos: Mouse over pop up with our own text box using java script

Sometimes we need a popup message box when we over the mouse on some image like below..

Take Html object into RS

paste the following code

<style>
.tooltip
{
text-decoration:left;
position:relative;
}

.tooltip span
{
display:none;
-moz-border-radius:6px;
-webkit-border-radius:6px;
border-radius:6px;
color:black;
background:#f2f2f2;
}

.tooltip span img
{
float:left;
margin:0px 8px 8px 0;
}

.tooltip:hover span
{
display:block;
position:absolute; top:-40px;  left:5px;
width:300px;
height:75px;
max-width:400px;
min-height:75px;
border:1px solid #888888;
margin-top:12px;
margin-left:32px;
overflow:hidden;
padding:8px;
}

</style>

<a class=”tooltip” href=”#” style=”border:none”>
<img src=”../samples/images/red.jpg” style=”border:0px solid #cc3300;” />
<span style=”text-align=left”>
Insert definition here
</span>
</a>

you can put your own image in the above script