Framework Manager, need modelling tips

[udaykamble]

Projects (table1) in database, have a number of project code classifications. These are stored in a table with project-code-value combinations (table2).
How do we convert this to have a separate column for each code? (project-code1-code2-…-codeN)

Right now as per my data model my results shows like this

Project Code1 Code2
======================
P1 V1 blank
P1 blank v2

I am expecting to get just one row per project. I dont want to include multiple copies of same table for each project code in the model (I know that is one solution, but not good solution)

 

[blom0344]

Use an SQL query model to 'roll up' the table to one row per project. You need to apply a min/max operator on the codes.

Then create a model query subject on the SQL object.

Ties Blom

 

[udaykamble]

Thanks blom.

Frankly speaking i did not get the resolution u mentioned. Could you please elaborate with example.

Just to tell u the way I am doing right now....

I have added query item in my project table and for each project code, I wrote the CASE statement to filter the respective code value. But that is giving me multiple rows for a single project.

Please reply.

 

[blom0344]

Rolling up to one record for each project involves an aggregate at least:

Project Code1 Code2
======================
P1 V1 blank
P1 blank v2

SELECT PROJECT,MIN(Code1) as code1,
min(Code2) as Code2
Group by Project

yields:

Project Code1 Code2
======================
P1 V1 V2


You need to create an SQL object (or a database view), as Cognos doesn't like to wrap aggregates around non-numericals..



Ties Blom

 

[udaykamble]

Thanks blom,

Yes, this will work for sure.

But as I am new to ETL and Cognos, I was assuming that I should only play with denormalised tables while doing modelling.

Is it a good practise to write views and SPs on denormalised database same as OLTP database?

I was expecting cognos to do some trick to get the result that way.

 

[blom0344]

You need not use a database view and for certain not a SP.
Assuming you have imported the tables into a database layer you can add an SQL defined object which will serve as a sort of Cognos SQL/view. So ,the logic to flatten the original table is stored as a Cognos definition in the framework.
However, do not use an SQL query subject to report against, but use it as a basis for a model query subject.

Ties Blom

 

[udaykamble]

Where is the option in Cognos FM to add SQL defined object?
As I am new to the tool, I am not able to find that.

 

[blom0344]

Copy one of the imported tables (which definition will read like: Select * from [datasource].sometable)
Then just adjust to the SQL you want..

Ties Blom