Multiple Databases, 1 package

[Lady_Lisetta]

Hi, I totally admit I'm a newbie with Cognos and Framework manager.  But my company's BI team cannot seem to assist my team with this problem, so I'm hoping that y'all can help us out with suggestions on how to fix the issue.

Facts:

• We have 3 database sources, Teradata, SQL Sever, and Oracle.
  
• Our current reporting tool is SAP BO.
  
• Corporate decided to end SAP BO relationship and move to Cognos.
• BI team started to migrate our BO universes and created packages using Framework Manager.
  
• BI Team started to recreate our BO reports in Report Studio.
  
• Simply reports such as list reports with one data source work fine.
  
• My team is not SQL savvy and it was determined that using Cube based solutions is not the right path for us as the data sets are Big Data and would violate cube size.
  
• The BI team created one mega package with all our data sources in that package.
  
• The BI Team did not link any of the data sources to each other thus making a mega package here Teradata tables sit in one package, Oracle tables in another package, and SQL Server tables in a third package within the mega package.
  

Issues:

The BI team now tells us they cannot link the data sources within the mega package nor can they get the various data sources to talk to each other in a separate package.

We can pull individual queries into Report Studio, but linking the various queries together via primary keys results in varying errors when we run the queries.  Sadly, my team is so new and we are still learning Cognos without the benefit of formal Cognos and Framework training (don't ask, it's a corporate decision to keep costs down).  So I'm trying to find answers to help my team and direct the BI team on finding a solution that will work for us.

Thank you for reading and your assistance.

[CognosPaul]

Migration projects like these are always !FUN!.

You absolutely can link multiple databases together, it's just a terrible idea. Each time you do, you will cause the dataset to be loaded into memory or disk on the Cognos server before being joined together.

The comment about cube based solutions honestly confuses me. Large volumes of data is usually a driver for cubes. Especially when dealing with multiple data sets.

There are two basic types of reports, higher level aggregated reports and detailed individual row type stuff. Cubes are ideal for handling the aggregated reports and dashboards. Relational is preferred for more details.

A few questions.
1. Is there a plan for a master data warehouse?
2. How often do you need to merge the different databases into a single query? Do you have customer information in one database but order in another?
3. If cube based reports meet your requirements, do you have the permissions to make views as a base for the cube? I've often found that it's easier to make materialized views to meet the needs of a cube than going through the process of building a proper data warehouse.

Can you post examples of the errors you get when doing local joins in report studio?

[Lady_Lisetta]

Paul, Thank you for the quick turn around.

A few questions.
1. Is there a plan for a master data warehouse? The data sources are already in their own data warehouses and likely will not be moved into a master, one stop shopping solution.  My organization is a small fish in the larger corporate pond and our needs often go unnoticed or unaddressed.
2. How often do you need to merge the different databases into a single query? Do you have customer information in one database but order in another?We use these merged sources daily for existing and new reports.
3. If cube based reports meet your requirements, do you have the permissions to make views as a base for the cube? I've often found that it's easier to make materialized views to meet the needs of a cube than going through the process of building a proper data warehouse. Most of what we do are detail reports to an agent level and across all the sources.  We do have several common primary keys.

Unfortunately, the folks creating the new packages are not familiar with our data sources or how our businesses work within this site.  Likewise, they wait until they get errors before consulting with my team and by that time they've wasted time and money.  I start to sound like a broken record when I ask my questions because all of this works in Business Objects, but they cannot seem to get it to work in Cognos for us and their explanations frankly do not make sense as they blame the measures and dimensions for the errors.

[CognosPaul]

You're really in a no-win situation here. It sounds like a bad practice (disinterested developers) on top of another bad practice (poor warehouse design) on top of bad practice (no company support). In cases like these I strongly recommend bringing in a consultant or two for a few days to get you running.

So, to sum up your sitution, you're stuck with a data model that doesn't work and the only tool you have to work with is Report Studio. You can't really expect much support from the framework or data modellers. And just to confirm, you don't have permissions to create tables or materialized views?

In order to help, I'll ask about the structure of the reports and the usage patterns.

Do the reports have to be 1 to 1 migrations? Can you change the structure of the reports at all?
Are the reports primarily composed of a single list, or multiple smaller data containers?
Can you post an example of the reports? (remembering, of course, to blank out any data)?
Are the reports interactive, with prompts?
Do the reports link to each other?


Do you actually need to merge the queries? Could you, for example, have one part of the report point to SQL Server, and another part point to Oracle? For example, Agent information might be in SQL Server, so the header of the report is one query, while the daily sales list is from Oracle. No need for a local join at all.

When you do try to join the queries, exactly what errors are you getting? We can go through those one at a time to resolve them.