Developing a Single Report for Multiple Databases

[sophiadevi]

Hi All,

Below is my req:
We have 7 different databases for 7 different countries. Each DB contains its own country specific table of same structure (same number of columns but with country specific data). Instead of developing 7 different reports for each country, I would like to know what is the best way for developing a single report for all 7 countries.

Is it possible in FM that we can Model in such a way, to use a single package and build a single report for 7 different countries.

Please help me in finding out best way for achieving the same.

Thanks in Advance.

[CognosPaul]

What type of database are you using? The reason I ask is that the standard solution can cause issues in some types of databases. Also, how do you want your users to select the correct country? I'll describe how I solved this in a situation where users could only ever access one database, there were no cases where a user could ever see another database's information.

The first step is to create a connection to one of the databases, give it a generic name. Build your model normally on that. Next, in the data source, create a new connection for each database. It's not a problem if they're on different servers.

At this point if you attempt to run a report on this model, it will prompt you to select one of the connections. By giving users permission to only one of the connection, they won't see that prompt. We did it by creating an AD group for each database, and giving permissions based on that group.

There are other solutions, such as putting a macro into the data source in the framework model. The problem with using macros in the physical layer is that it forces Cognos to make meta-data requests to the database. For Oracle or SQL Server this isn't a problem, but a database like Greenplum will choke.

[sophiadevi]

Hi Paul,

The DB which we use is SQL server. All different databases reside on single server and DB tables share the same structure and table name. (DB1.table, DB2.table, DB3.table and so on).

In our case, the user will be entitled to a single or multiple countries. There will a country prompt being populated based on users entitlements.

Please elaborate more on putting a macro in FM and achieving it.

Thanks in Advance.

[CognosPaul]

Will users need to see multiple databases in the same report?

[sophiadevi]

yes if they have access .

Thanks.

[CognosPaul]

This makes things significantly more complicated. By having them in multiple databases, they will need to have some sort of union between the tables. Is there any reason why you can't merge the databases into one, and then use filters or pass-through security to ensure they can only see the countries to which they are permitted?

[sophiadevi]

Merging databases is again the last option and looking for solution if it could be achieved from FM or report level.

Thanks.

[CognosPaul]

So you need a way of dynamically unioning the databases your users see. The easiest would be to simply make a view of the union of each table, with an additional field signifying the country, and simply filter by that field. The difficult bit with that is not all databases play well with that. Which database are you using? Can you have a DBA do a trace on a test query:

select * from
(select *, 'Country A' as "Country" from countrya.table
union all
select *, 'Country B' as "Country" from countryb.table) t
where t.country = 'Country B'

Is your database scanning through countrya? If not, then we can get away with making a series of views and filtering those. If not, there is some magic we can do in Framework to get it done, but it's really a not so great solution.