How to handle single report based on two cubes with shared prompts and combined data

Check list of all posts

Context
As known, one of key data warehouse design concepts is to make dimension be confirmed. There is no any problem with relational and DMR packages to handle multiple facts, as Cognos engine will generate stitched query. However, it won`t work if you want to apply the same concept to multiple cubes. In addition, even it is the best way to generate a single cube to contain multiple fact tables, but sometimes, you do have multiple cubes as data sources. Therefore, to solve report issues with multiple cubes becomes necessary.  This document is intended to provide a sample to walk through the solution to handle single report based on two cubes with shared prompts and combined data. Please see attached screenshot below,  Given there are two cubes, sell through cube and inventory cube

Framework manager package

Shared prompt

Create a prompt p_Province from sell through cube



Create a query for sales list

Create a query for inventory



Apply for the same filter for inventory, you will get error below


The solution is to turn MUN to be OLAP-Inventory related MUN


[OLAP-Inventory].[Location].[Location].[Province] =#substitute ( 'OLAP-Sell Through', 'OLAP-Inventory', prompt('p_Province', 'MUN')) #

Now you will see list as

Combined data from both cubes

To bring both data together, join CAN NOT be used, instead, you need to use UNION, which is a very consistent approach with relational package to handle relative time for crosstab. see ex-post for detail reference How to handle relative time (relational model focused)

Add addition data item query as `Sales` for Sales query, add addition data item query as `Inventory` for inventory query, and then union both query together


Define two new data items as sales amount and inventory amount


Sales Amount:  case when [Union1].[DataFrom] = 'Sales' then [Union1].[Amount] end
Inventory Amount:  case when [Union1].[DataFrom] = 'Inventory' then [Union1].[Amount] end

Now bring three columns together as list below

NOTE: 
Below is another blog to handle the same issue
https://senturus.com/blog/drilling-one-cube-another-cognos-report-studio/

set(
#join(';',
    substitute('Cube1′,'Cube2',
        split(';',
            promptMany('SelectedProducts','memberuniquename','[DefaultProduct]')
        )
    )
)#
)