Join of Tables in Query Studio - Cognos 10

[aks]

Hi..I was looking for joining of data from different tables - in ad hoc reporting through Cognos Query Studio...But I am not yet successful as I am not able to find any tool in the tool box of query studio for joining of tables...

Wanted to whether it is possible or not through Query Studio cos through Report Studio, it was .. And if yes , then how.. gotta limited knowledge of it...  :-[

[MFGF]

Hi,

Query Studio is designed as an authoring tool for non-technical business people. For this reason it has nothing in the UI which exposes the query. If you want two tables to be joined and use the result in Query Studio, the join needs to be defined as a relationship in the underlying Framework Manager model, and the published package for use in Query Studio will need to include these tables.

Regards,

MF.

[aks]

Thanx MF for your response..

The issue arising here is, before publishing the package and handing it to the user, I will have to know all the possible tables that the end user thinks he can join, which will result in a conundrum situation cos. if I am working with 52 tables ( in a Data warehouse )..

If you can suggest something on this or else I ll have to give user , training of Report Studio which might create other issues as the business users are Non- IT personnel.  :-\

[Lynn]

I would suggest that you go back to the drawing board with regard to your data warehouse. It sounds to me like you don't really have a properly architected warehouse....you have simply a collection of tables that happen to reside in the same database.

Typically a star schema should exist with a fact table surrounded by dimensions. Different fact areas can share dimensions (conformed dimensions) and can be used to produce reports that include multiple facts, such as a sales forecast compared to actual sales.

I suggest you take a look at Ralph Kimball's book "The Data Warehouse Toolkit" and other books in this field to understand how creating this type of structure can provide an elegant solution for business users who are non-IT personnel.

It is also possible to revisit your Framework Manager model to address the shortcomings that seem to be present in your database. You would want to create those star schema groupings there, but this can be difficult to achieve while getting appropriate performance. Although your idea of giving them a tool to do the joins within Cognos could potentially perform very poorly because the joins in that scenario will all be done locally rather than at the database level.