Macros

Quickie: How to use the new macro function queryValue

| cognospaul

A very pleasant surprise when building reports. There’s a new function called “queryValue”.

On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. There are many times where I want a way of pulling a default value from a database in a filter. For example, my report might need to let the user select a year, but last date with data is unknown. Normally we can do something like:

[Sales (query)].[Time].[Year] = #prompt('Year','integer',
'maximum([Sales (query)].[Time].[Year] for report)'
)#

WITH 
"TQ0_q_SelectedMonth1" AS 
    (
    SELECT
        "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0", 
        MAX("GO_TIME_DIM"."CURRENT_YEAR")
            OVER(
            ) AS "Max1"
    FROM
        "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM"
    )
SELECT
    "TQ0_q_SelectedMonth1"."Year0" AS "Year0"
FROM
    "TQ0_q_SelectedMonth1" 
WHERE 
    "TQ0_q_SelectedMonth1"."Year0" = "TQ0_q_SelectedMonth1"."Max1" 
GROUP BY 
    "TQ0_q_SelectedMonth1"."Year0"

It works, but I don’t like it. It’s creating a window function, and actually hits every row in that table. One way around would be to create a parameter map in the framework model. Create a model query that has the value you want, add a data item with a static 1, and create a parameter map based on that. You could then do:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
sq($lastYearWithData{1})
)#

Then the SQL will look like: 

SELECT
    "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0"
FROM
    "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM" 
WHERE 
    "GO_TIME_DIM"."CURRENT_YEAR" = 2013 
GROUP BY 
    "GO_TIME_DIM"."CURRENT_YEAR"

This is much better but still way too clunky. It requires access to the framework model, and a new parameter map for each and every scenario that we need. What if we want the last date of data for a specific product line? Or product?

Instead, let’s take a look at queryValue()

[Fact Business].[Dates].[Year] = #prompt('Year','integer'
, queryValue('maximum([Sales (query)].[Time].[Year])')
)#

This behaves exactly like the parameter map, and the SQL is identical. Cognos is actually making a query that runs maximum([Year]), and returns that value to the macro – exactly like a parameter map would. We can add filters to the query like this:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
queryValue('maximum([Sales (query)].[Time].[Year])','[Sales (query)].[Sales].[Quantity]>0 and [Sales (query)].[Products].[Product]=''Trail Star''')
)#

Notice using a double quote to escape the single quote. Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt.

This is also especially useful in Data Sets and Data Modules, where parameter maps simply don’t exist.

Uncategorized

Extensions in Cognos Analytics

| cognospaul

When compared to Cognos 10, Cognos 11 has a very different portal system. Called Glass, this extensions system allows a much greater flexibility in terms of adding additional buttons and menus. Pretty much every aspect of the portal can be modified, including the ellipsis menus. In addition to adding, we can also prevent users from seeing specific menu items.

1. Opening a webpage in an iFrame
2. Running a report or a dashboard
3. Opening a folder
4. Add a dashboard shape
5. Run a script
6. Adding a menu for any of the above actions
7. Hiding specific glass features or views

Let’s start with a basic extension – opening a website in an iFrame. 

{
	"name":"incident_Reporter_iFrame",
  "comment":"Opens the incident reporter in Cognos.",
	"schemaVersion": "1.0",
	"extensions": [
	{
		"perspective": "common",
		"comment": "There is a special meta perspective called COMMON. Adding contributions to this perspective will cause the extension to be applied to All perspectives.",
		"features": [{
			"id": "incidentOpener",
			"toolItems": [
			{
				"comment": "This code will display a custom Website button that opens the specified URL in an iFrame.",
				"id": "ops.iframeOpener.incidents",
				"containerId": "com.ibm.bi.glass.navbarTrailingGroup",
				"label": "Incidents",
				"icon": "images/incidentReporter.png",
				"type": "Button",
				"weight": 100,
        "coachMark":{"title":"Incident Reporter","contents":"Use this link to open new incidents"},
				"actionController": "bi/glass/api/IFrameOpener",
				"options": {
					"url": "http://SERVER/incidentReporter.aspx",
					"title": "Incidents"
				}
			}
			]
		}]
	}]}

Let’s go through this.

Top level has name, schemaVersion, and contains the extensions array. In all places you can add a comment field.
Name should be descriptive so the next person working on this can figure this out. And then use the comment to explain what you’re doing!
Comments are just that, they’re ignored by Cognos and they’re a great place to defend your code.
The extension allows you to handle different perspectives in different ways. So you might want this to appear in one way in authoring, and another way when the user is in dashboards.

Inside extensions we start getting into how the extension appears on the page.
Perspective lets you apply this everywhere (“common”) or to a specific view (“home”, “authoring”, “dashboard”, or “modeller”).
Features defines the items in the extension.

And inside features…
id needs to be unique.
toolItems are the actual buttons and menu structure.

And inside the toolItems…
This is the place where we define what appears in the locations.
Each item needs a unique ID.
The container ID references various places in Cognos. It’s the parent of item you’re creating. The help documentation lists five placement options, but I’ll give you a hint. You can use developer toolbars to examine various elements in Cognos to see if you can put an item there:

You could also do what it says on the IBM article here, but that would be far too easy.

Label and Icon are self-explanatory.
Type can be menu, menuItem, or button. If you have a menuItem, you need to make sure the containerID is a menu. It doesn’t need to be a menu that you’ve created.
Weight determines the position of the item in the container, higher numbers making it have a higher position.
Push turns the button into a toggle.
coachMark is an object containing title and contents that creates those nice blue hint bubbles.
actionController tells Cognos what this button does. The one above opens an iFrame, but you could also use ReportOpener, DashboardOpener, FolderOpener. In my next example further down I’ll show how to run a custom controller.
Options give context to the action. In this case we want the iFrame to open incidentReporter.aspx on SERVER. If you point against an external website please note that not all websites allow themselves to be viewed in iFrames. Google for example just won’t work.

To use it you need to paste the JSON into a file called spec.json. The icon is looking for images/incidentReporter.png. Zip the spec.json and the images folder and you’re done!

Now when we upload it we see the incidents button under the Manage on the bottom left:

Now let’s do something a little more complex fun.

One of things that always bugged me about using Cognos in Chrome was the lack of clipboard access. While it’s true that you can now copy objects from report to report in C11.1, there are times when I want to modify the XML. Local classes, for example, have a very limited list of options. If I want to add something non-standard, like position:absolute, I’d have to open it in IE or copy the entire report XML.

We’re not going to get into the JS behind my Clipboard Editor, but we’ll go into the JSON of the object.

{
	"name":"CognosPaul_Bag_o_Tricks",
	"schemaVersion": "1.0",
	"extensions": [
	{
		"perspective": "authoring",
		"comment": "This is a collection of extensions aimed at easing the life of a developer.",
		"features": [{
			"id": "CognosPaul.BagOTricks",
			"toolItems": [
{
				"id":"custom.appbar.trailingGroup.trickMenu",
				"containerId": "com.ibm.bi.glass.appbarTrailingGroup",
				"type": "Menu",
				"label": "Custom Tools",
				"icon": "images/bag.png",
				"weight": 650
			},
			{
				"id": "custom.appbar.trailingGroup.menuItem2",
        "comment":"Clipboard editor will allow you to copy an object in Cognos and see the XML in a memo input. You can then edit it and paste back into Cognos.",
				"containerId" : "custom.appbar.trailingGroup.trickMenu",
				"comment": "The containerId is the ID of the parent menu.",
				"type": "MenuItem",
				"actionController": "v1/ext/CognosPaul_Bag_o_Tricks/js/controllers/ClipboardEditor",
				"comment": "action controller is looking at the js embedded in the zip.",
				"label": "Edit Clipboard",
				"icon": "images/clipboard.png",
				"weight":800
			}
			]
		}]
	}]}

In this case I’m creating a menu in the appbarTrailingGroup. It will appear in the upper right menu bar, next to the ellipsis menu.

The menu named “CognosPaul_Bag_o_Tricks”, and that same name will be used further down in the actionController. It contains one extension, which is only valid from the authoring perspective.

The extension itself contains a menu, and a single menu item. Notice the containerId for both items. The only major difference between this and the iFrame viewer is the actionController. Notice the call is without js, and uses the name of the object: “v1/ext/EXTENSIONNAME/folderstructure/JSFILENAMEWITHOUTTHEJSEXTENSION”.

I personally find the clipboard editor incredibly useful, but because I’m using undocumented functions in Cognos I can’t guarantee that it will work for all future versions. Copying from one report to another is possible with this, but you have to initialize the report clipboard by copying something.

iFrames.zip (673 downloads)
Clipboard Editor (700 downloads)

And finally, on a personal note, I’m a sucker for free high-fives. Thanks GoTeamJosh!

Framework

Why cardinality is so important in a Framework Model

| cognospaul

I recently had a report that a dashboard was returning incorrect results. At first glance this isn’t exactly unexpected, and I had initially attributed it to user error. But then I tested it, first in Dashboards then in Report Studio, and I was getting the same results.

The request is simple. Show a list of product lines, revenue, and revenue per working day. The revenue fact is joined to the product dim and the date dim. Working days fact is joined only to the date dim.

I know that 2011 had around 200 working days, but let’s see what I get when I recreate the query

That seems wrong, 87,000 working days in a single year is a little excessive, even compared to my own workaholism. What is going on in the query?

WITH 
DaysFact0 AS 
    (
    SELECT
        DaysFact.DAY_KEY AS DAY_KEY, 
        CASE DaysFact.DAY_OF_WEEK
            WHEN 1 THEN 0
            WHEN 7 THEN 0
            ELSE 1
        END AS "Working Days", 
        DaysFact.DAY_OF_WEEK AS DAY_OF_WEEK
    FROM
        great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM DaysFact
    )
SELECT
    SLS_PRODUCT_DIM.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, 
    SUM(DaysFact0."Working Days") AS Working_Days, 
    SUM(SalesFact.SALE_TOTAL) AS SALE_TOTAL
FROM
    great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order
    INNER JOIN DaysFact0
      ON GO_TIME_DIM_Order.DAY_KEY = DaysFact0.DAY_KEY
    INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_SALES_FACT SalesFact
      ON GO_TIME_DIM_Order.DAY_KEY = SalesFact.ORDER_DAY_KEY
    INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_PRODUCT_DIM SLS_PRODUCT_DIM
      ON SLS_PRODUCT_DIM.PRODUCT_KEY = SalesFact.PRODUCT_KEY 
WHERE 
    GO_TIME_DIM_Order.CURRENT_YEAR = 2011 
GROUP BY 
    SLS_PRODUCT_DIM.PRODUCT_LINE_CODE

With a multiple fact query, I should be getting a stitch query in the SQL. In effect, it should be creating two queries – one for the revenue measures and one for the days. It should then join them together on the conformed dimension. In this case, there’s no conformed dimension, so the same number should repeat for each product line.

The problem must be in the physical layer – let’s take a look at the joins.

Do you see the problem? The join from the Date Dim to the Date Fact is a 1..1 on both sides. This instructs Cognos that the date fact is actually a snow-flaked dimension. But, you may argue, it is a 1 to 1 join! The join itself is on the day key, how much more 1 to 1 can you get? The answer is simple – cardinality simply describes wether a table is a fact (at the end of a 1..n join) or a dimension.

Switching that join to 1..n results in the following SQL.

WITH 
DaysFact0 AS 
    (
    SELECT
        DaysFact.DAY_KEY AS DAY_KEY, 
        CASE DaysFact.DAY_OF_WEEK
            WHEN 1 THEN 0
            WHEN 7 THEN 0
            ELSE 1
        END AS "Working Days", 
        DaysFact.DAY_OF_WEEK AS DAY_OF_WEEK
    FROM
        great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM DaysFact
    ), 
FS1 AS 
    (
    SELECT
        SUM(DaysFact0."Working Days") AS Working_Days
    FROM
        great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order
            INNER JOIN DaysFact0
            ON GO_TIME_DIM_Order.DAY_KEY = DaysFact0.DAY_KEY 
    WHERE 
        GO_TIME_DIM_Order.CURRENT_YEAR = 2011
    ), 
FS2 AS 
    (
    SELECT
        SLS_PRODUCT_DIM.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, 
        SUM(SalesFact.SALE_TOTAL) AS SALE_TOTAL
    FROM
        great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.GO_TIME_DIM GO_TIME_DIM_Order
            INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_SALES_FACT SalesFact
            ON GO_TIME_DIM_Order.DAY_KEY = SalesFact.ORDER_DAY_KEY
                INNER JOIN great_outdoors_sales.CognosSampleDev_GOSALESDW.gosalesdw.SLS_PRODUCT_DIM SLS_PRODUCT_DIM
                ON SLS_PRODUCT_DIM.PRODUCT_KEY = SalesFact.PRODUCT_KEY 
    WHERE 
        GO_TIME_DIM_Order.CURRENT_YEAR = 2011 
    GROUP BY 
        SLS_PRODUCT_DIM.PRODUCT_LINE_CODE
    )
SELECT
    FS2.PRODUCT_LINE_CODE AS PRODUCT_LINE_CODE, 
    FS1.Working_Days AS Working_Days, 
    FS2.SALE_TOTAL AS SALE_TOTAL
FROM
    FS1, 
    FS2

It’s a little longer, but it makes much more sense now. It’s a single value on the days fact side with no conformed dimension, so there’s no join on the two FS queries.

And the results:

In this example it was fairly obvious where the problem was, but in a real-life example you may have dozens of tables connecting to many facts. If you are running into data problems, a careful review of the joins may often reveal the problem.