Why cardinality is so important in a Framework Model

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.

Disabling interactivity on a per control basis in Cognos 11

One of the main selling points of the 11.x release has been one of the more frustrating features. There are times where you may want a standard interactive report, but one of the controls should be absolutely static.

Fortunately there’s a (somewhat) easy way to disable the interactivity on any control you want. If you examine the HTML of a report, you’ll see something like this:

lots of lovely HTML to help keep you warm over the cold winter.

See that roid=”i160″? That’s the attribute that tells Cognos the DOM Object is interactive! Simply remove that and the user won’t be able to do anything with it.

Of course it’s easier said than done. There’s no way in Report Studio to tell Cognos NOT to generate a specific attribute. Instead we have to use JavaScript.

The JavaScript itself is fairly easy. We call the control passing the name of the object we want to deinteractivify, and it will find all elements that have the roid attribute. Removing them all in one painless snip.

It’s important to note that this will not work on the 11.1 JavaScript graphs, only on the server rendered one.

The control configuration is designed to accept a single object name, or an array of them. Use

{
    "controlNames": [
        "myList",
        "myGraph"
    ]
}

Report XML: removeInteractivityReport.txt (1332 downloads)
JavaScript: removeInteractivity.js (1410 downloads)

RFE Roundup – January 2019

As part of my monthly Request For Enhancement Roundup list, I present January 2019. This is the third month, please look at December’s list for more.

1. Cascade Prompts on filters on Dashboards like reports
Uncommitted Candidate with 24 votes.

A straightforward mechanism to creating cascading prompts would improve the usability of dashboards substantially. I feel like the prompting side needs an overhaul.

2. Share pins : to be able to create a pin library for certain target groups
Uncommitted Candidate with 9 votes.

My biggest concern with adhoc reports has always been the same, most users simply want to be given the data they need. This RFE would make a nice stopgap, it would allow a user to create a generic library of pins for the end users, thus giving the end users the data they need in an adhoc environment, without them having to work hard for it.

3. List and Crosstab Visualization Lagging Behind Power BI and Tableau
Submitted with 19 votes

This really speaks for itself. There are a lot of things Cognos struggles with in terms of lists and crosstabs. Embedding a microchart is possible, but it’s clunky and slow. Something built-in would be wonderful. It is possible to make a widget that pulls the data in and builds a modern datatable on the fly, but that wouldn’t work when exporting to PDF.

4. Adding images to reports and dashboards
Submitted with 10 votes

A simple uploader for images! Why can’t we do this? And while we’re at it, let’s do the same thing for JavaScript controls.

5. Make map visualization viewable in PDF format
Submitted with 11 votes

PDF support is pretty limited, but having a report ready visualization that doesn’t export cleanly is a big problem.

6. Allow user to add extra properties in vizualisation (Dashboard)
Submitted with 10 votes

I’ve been struggling a lot of with the dashboard visualizations. A colleague said something along the lines of “Dashboards are great if you’re willing to give up control”. I want to make my bed and eat it too!

An update about Think 2019. I will not be attending this year unfortunately, but my company is planning a major presence at Analytics University. The trend at Think has been to reduce focus on the business analytics sides of things drastically, so it’s just not as important to us as it once was.