Multiple Data Sources (Design Question)

[VizWiz]

Hello fellow Cognos enthusiasts,

The organization I work for seems to be moving towards joining data across our multiple custom-coded applications. The idea of joining the data from these systems makes sense, but my issue is with the strategy for implementing this. Once management discovered that multiple data sources can be used in a Framework Manager model, they want to join everything in a model instead of creating a data mart with proper ETL. So I guess I have a few questions:

• Is this a bad strategy? My thought is the in-memory joins that occur on the Cognos servers due to joining disparate data sources can be very slow. Similarly, I've seen Tableau blending (joining on the application server instead of database) perform quite slowly. Shouldn't we be using ETL to join multiple systems and creating data marts that are more report-friendly?
• If I HAVE to implement this strategy, what are some best practices to make sure items aggregate correctly? The specific scenario I'm concerned about here is what happens when I have two databases, each with their own set of tables and joins. Separately, the tables in the two databases aggregate correctly in their calculations--but what happens when joining these two databases together? Does placing the two sets of tables in separate namespaces help this at all?
• Another concern I have is that we already have one framework model for the each of the two individual data sources. If we create another model that joins together the two data sources, this will duplicate a lot of the same joins and create maintenance pains. I've seen where you can import package data in the metadata wizard, but this just seems like more maintenance pains.

Thank you anyone for any help/advice/experience you can provide, I really appreciate it. Please let me know if I've not been clear on what I'm asking or any other questions.

[MFGF]

Hello fellow Cognos enthusiasts,

The organization I work for seems to be moving towards joining data across our multiple custom-coded applications. The idea of joining the data from these systems makes sense, but my issue is with the strategy for implementing this. Once management discovered that multiple data sources can be used in a Framework Manager model, they want to join everything in a model instead of creating a data mart with proper ETL. So I guess I have a few questions:

• Is this a bad strategy? My thought is the in-memory joins that occur on the Cognos servers due to joining disparate data sources can be very slow. Similarly, I've seen Tableau blending (joining on the application server instead of database) perform quite slowly. Shouldn't we be using ETL to join multiple systems and creating data marts that are more report-friendly?
• If I HAVE to implement this strategy, what are some best practices to make sure items aggregate correctly? The specific scenario I'm concerned about here is what happens when I have two databases, each with their own set of tables and joins. Separately, the tables in the two databases aggregate correctly in their calculations--but what happens when joining these two databases together? Does placing the two sets of tables in separate namespaces help this at all?
• Another concern I have is that we already have one framework model for the each of the two individual data sources. If we create another model that joins together the two data sources, this will duplicate a lot of the same joins and create maintenance pains. I've seen where you can import package data in the metadata wizard, but this just seems like more maintenance pains.

Thank you anyone for any help/advice/experience you can provide, I really appreciate it. Please let me know if I've not been clear on what I'm asking or any other questions.

Hi,

To take these in order:

1. I think this is a flawed strategy, yes. While Cognos can and does perform heterogeneous joins across databases, it's by no means an efficient process. Data sets need to be retrieved from each data source and joined locally on the Cognos server, which hits the network, increases the work and the memory/temp space needed for your reports to run, and forces Cognos to do work a database could normally handle internally if you were querying a single database. Additionally, you are missing out on data integrity and governance - there might be inconsistencies between rows in the different data sources meaning defined joins between them might not return expected results. ETL processes normally clean and validate data during the process of delivering it to a single reporting repository, so alleviate these issues. A far better solution is to deliver the data into star schemas in a data warehouse using ETL processes, and report off the star schemas in this single location.
2. If you really have to do this, firstly import each data source into a separate namespace. This removes the possibility that a table/column might have the same name in two databases and result in a uniqueness issue in your model. Also make sure you are joining appropriately between the data sources. If you have multiple joins between them, look out for loops, and resolve the same way you would if you had loops from a single data source. Model the data to look like star schemas linked via conformed dimensions in your model, and if measures link to different granular levels of a conformed dimension, use determinants to identify those granular levels. In all honesty the modelling piece is no different that modelling tables from a single source once the query subjects have been imported.
3. You can bring multiple models together in a higher-level model if you wish. Create the high-level model as an empty container, and use the Project > Link Segment option to bring in your existing FM models. A Segment is effectively a link to another model, so any changes you subsequently make in the child models will also be visible from the high-level model. This means you don't need to duplicate all your existing modelling.

Cheers!

MF.

[Invisi]

I agree with MFGF on point 1. The fact that Cognos can handle dealing with multiple data sources to come to one model doesn't make it good or well performing. It also puts logic from your data warehouse to your BI tool as to what is related to what. Whenever possible go to one data source that is to be queried by your BI tool(s).

[cognostechie]

This is a very common scenario and one of the reasons for failure of a BI implementation in many companies. The managers/directors in these cases are always found to be from another background with no knowledge of BI. For them, a car is a car and one car cannot be different than the other so if an engine is required then 'all we got to do is put any engine in it and it should just work'. 5 yrs down the road and with few million dollars burnt and 10 more developers hired to support poorly performing reports, the tool would be blamed for not performing proper and a new tool will replace Cognos with another initiative of burning few more million dollars !

The good part about this is that it helps build the economy as more people get jobs, more consulting companies make money etc. According to me, the problem always starts from the top. It is actually the fault of somebody higher up who appointed somebody as the manager for this initiative without determining if that manager has any knowledge of the subject. I have seen musicians getting appointed as the Director of Data Warehousing and Mechanical Engineers getting appointed as BI Managers. Per some people at the top, for a technical role, they have to make sure that the guy is an engineer regardless of what kind of engineer he is. Per some other C level executives, the ability to communicate is more important than a qualification of the subject ! 

[Francis aka khayman]

the cognos people do not have much people skills. so those guys gets appointed instead  ;D ;D ;D