Search code examples
sql-servercognos-10cognos-bi

Is there a way to combine / overwrite data when using two sources in Cognos Report Studio?

I am using two sources in Cognos.

Source #1 - Old source
Source #2 - Current/New Source

Previously there was only one source, that's source1. Then people from DW team started migrating/mirroring data from Source1 to Source2, eventually some data were added in Source2 that aren't available in Source 2. Same thing with Source1, some data are available in Source1 that are not yet migrated in Source2 but the people/users are already requesting a report using Cognos. they can't wait to have the report, so they suggested that. If data is available from both sources, overwrite data from old source (SOURCE 1) with data from (SOURCE 2)

Is there a way to overwrite data from the other source? If data is available from both sources, overwrite data from old source (SOURCE 1) with data from (SOURCE 2)

The column names that I used are not the actual columns.

If   
    SOURCE 1
    Policy_Number  Policy_Holder_Name    
    1234           GroceryStore
    5678           ToyShop
    5555           BeautyShop
    4444           Booksale
    8899           Cards
    7777           Electronics
    
and SOURCE 2
    Policy_Number  Policy_Holder_Name    
    1234           GroceryStore
    5678           ToyShop
    5555           BeautyShop
    4444           Booksale
    8899           Cards
    2468           FreshMeat
    1357           Hardware
    
    Results should look like this.
    
    Policy_Number  Policy_Holder_Name    
    1234           GroceryStore
    5678           ToyShop
    5555           BeautyShop
    4444           Booksale
    8899           Cards
    7777           Electronics
    2468           FreshMeat
    1357           Hardware

Should I create two query subjects in query explorer? Source1 and Source2. Then should I left outer join Source 2 on Source1?

enter image description here

Should I use the cardinality? Im not quite sure on this move. I could try it however I am dealing with 160 rows here. Or should I try Union? I need help please. Thank you.

       Source 1 = Source 2
1..1 Inner Join = 0..1 Left outer Join

If you have a better idea I'd be more than happy to try that!

Solution

  • I would use a full outer join, 0..1 to 0..1 assuming both subqueries are rolled up to the same grain. This will give you access to all values and you can pick and choose in custom data items with a simple CASE statement:

    CASE 
WHEN [Source1].[Policy_Number] is not missing and [Source2].[Policy_Number] is not missing THEN [Source2].[Policy_Number]
WHEN [Source1].[Policy_Number] is not missing and [Source2].[Policy_Number] is missing THEN [Source1].[Policy_Number] 
WHEN [Source1].[Policy_Number] is missing and [Source2].[Policy_Number] is not missing THEN [Source2].[Policy_Number]
END

    You can use this pattern to enforce any logic you like. The key thing is to cover all scenarios and indicate what value to use in each situation.