Unleash the power of UNION QUERY at report level to achieve the same functionality of "stitch query" at framework manager level

Check list of all posts

Stitched query is the one of key concepts of Cognos framework manager. It handles multiple facts with same grain or different grains.  This article is introduce a solution that union query is used to achieve the same functionality of "stitch query” at report level

Context and solution

The power of stitch query is to archive a very good performance with multiple fact tables, as it brings two major advantages:

1) Retrieve very small amount of data from each individual fact table, as it dynamically summarize measure data with involved dimensions

2) Provides a report ready dataset for data container (crosstab, list and chart). This dataset is easily to be rendered by presentation engine.

Stitch query concept can only be used in framework manager design. However, there are many situations where stitch query is not possible to be applied based on following reasons:

1. It is not allowed changing framework manager,
2. It is not generic enough to change framework manager.  
3. It is impossible to model in framework using stitch query, as these fact  tables from different databases, or different namespace, or the logic for each fact table are very different.

These situations become more and more important for report studio, as business users want to get the overview across domain, such as sales data with inventory data.  (of cause, workspace could be a option to resolve this issue, but it is difficult to put all data into a good container).  Instead of using full outer join, we use union query to resolve the same problem at report level. Union query can generate very similar data set as full outer join.  The idea is to get data sets from fact table (can be from different namespace) with one or more conformed dimension, then union these data sets as report ready data.

Two samples below are used to demonstrate this idea. please note that we can use other solution to get such report, however, please consider that these report logic in real world is much more complicated. the sample is simply to explain the concept.

Union data from different aggregate methods

Please see screenshot below (download report specification v10.2)

Number of product is distinct product under each product type, and the total number of product at product line is the total of number of products from different product types. Revenue is rollup by both product dimension and years. As you can see, Measure number of product is to use distinct product at product type level, while revenue at product type level is total. The only solution is to bring both measures at product type level, and then rollup will be applied at product line level.

You can try to use single query, then the number of products will be inflated by number of year, as lowest level data is based on both year and product type. To solve this solve, we need to use union to bring a single record for each product type for measure number of product.

Step 1: create two queries and then union

In order to union two queries, both need to have same items with the same type. Use cast(null as integer) for not applied data items

Product line: [Sales (query)].[Products].[Product line]

Product type: [Sales (query)].[Products].[Product type]

Year:cast(null as integer)

Revenue:cast(null as integer)

Number of Products: count( distinct [Sales (query)].[Products].[Product])

Product line: [Sales (query)].[Products].[Product line]

Product type: [Sales (query)].[Products].[Product type]

Year:[Sales (query)].[Time].[Year]

Revenue:[Sales (query)].[Sales].[Revenue]

Number of Products: cast(null as integer)

Step 2 layout

Suppress columns is needed, as there will be an empty column for Number of products with empty year.

Union data from different grain datasets

Please see screenshot below  (download report specification v10.2)

All data stitched at product dimension, each fact has their own measure. In this sample, revenue is further broken down to year.  The initial thought is to use left join based on product line, however left join doesn’t work, as it brings more than one record for each product line for target and return, because revenue has 4 records for each product line. The best solution is to use union. As soon as you need to use union, then you need to keep number of columns are same and with same type. The idea is to use cast null to make empty data. When present at layout, you need to simple suppress column.

Step 1: create three queries and then union

In order to union three queries, all three query need to have same items with the same type. Use cast(null as integer) for not applied data items

Product line: [Sales (query)].[Products].[Product line]

Year: [Sales (query)].[Time].[Year]

Revenue: [Sales (query)].[Sales].[Revenue]

Sales target:cast(null as integer)

Return quantity:cast(null as integer)

Product line: [Sales target (query)].[Products].[Product line]

Year: cast(null,integer)

Revenue: cast(null,integer)

Sales target: [Sales target (query)].[Sales target].[Sales target]

Return quantity:cast(null as integer)

Product line: [Returned items (query)].[Products].[Product line]

Year: cast(null,integer)

Revenue: cast(null,integer)

Sales target: cast(null,integer)

Return quantity: [Returned items (query)].[Returned items].[Return quantity]

 

The combined

data looks like follows

Step 2 layout

Suppress columns is needed, as there will be an empty column for target and return with empty year.

Note

This concept is very generic, as it can be extended to

1. cube data source, while DMR doesn't support union.
2. Any data from different areas, such as difficult cubes

And this concept is very powerful, as report developer only need to focus on individual area to get the logic right, and with high performance.

The only drawback is that you need to make sure all individual query has the same number of query items, with the same sequence and data type.