Help on cube design

[Nchd15]

Hi All,

We are trying to build a cube with data from two tables sharing Master-Detail relationship. The master table stores the static information like Case_type, Received_date etc. about 'Cases' which go through different phases. The changes in the phase along with Date_Phase_change are stored in the child table.

The measures requested are:
*Number of Cases in a particular phase at any time( the catch is if Case is "Opened" in Mar 2002 and "Trial" in Jun 2002 : it should be counted under "Opened" in Mar,Apr & May 2002 and "Trial" from Jun 2002 till date until the file is closed)
*Average number of days in a particular Phase since CaseReceived


My question is :does this require doing any major work ( calculated columns) at the .IQD level or even modifying the table structure at database level to ease building the cube or can Transformer handle this. As I am new to Cognos, I apologize if it seems too simple a thing to ask.

Thanks in advance.

 

[dennydm]

hi,
this can be easily done in the SQL's that you use for your IQD's. In that, for each month, for a particular case, you would have to calculate the STATE of the case as to whether it is Active etc.. based on the date of the state and the running month in the SQL. The condition you can use is
database table month <= your running month. So this way you will have a result set wherein you will have for a given month say Jan, the status of the Case... then for the next month say Feb.. the status may or may not change depending on the data in the table. Essentially its the logic you use in your IQD... transformer will just blindly use your IQD results for the cube build.
hope this helps.
denny

 

[Nchd15]

Thanks Denny.
As I am novice in Cognos, would you please elaborate how to use running month(is it a function in Impromptu ?)in the Report Query. We are adding a record for intermediate months at database level and doing this at Impromptu will save us a lot of time and effort.
Thanks in advance for your help