Time state "Rolldown"?

[KasperKmb]

I'm looking for a solution for the following problem in Cognos Transformer:

My fact table is set up as follows:

date fact1 fact2
---- ----- -----
20090101 0 7
20090102 0 6
20090103 0 7
20090104 0 4
........ . .
20090131 80 8

Fact1 is only filled for the last date of the moth. Fact2 is filled for every date. Both measures have a different time-state rollup. Fact1 is last-period, fact2 is sum. I would like to know if it's possible to spread fact1 so that the value for the last day of the month is also shown for all dates in that month. In the cube this would look as follows:

date fact1
---- -----
20090101 80
20090102 80
20090103 80
20090104 80
........ .
20090131 80

 

[blom0344]

Create a view on the fact that stores 1 record for every month:

CREATE VIEW V_LAST_DAY_OF_MONTH AS
SELECT
DATE AS LAST_DAY_OF_MONTH,
SUBSTR(DATE,1,6) AS J,
FACT1 AS F
FROM
FACTTABLE
WHERE FACT1 > 0

Add this view to the IQD by performing a join on:

V_LAST_DAY_OF_MONTH.J = SUBSTR(FACT1.DATE,1,6)

assuming your date is stored as a string

Value F will then be present in all records for a given month..

Ties Blom

 

[KasperKmb]

Thanks for your answer. I was hoping for a Transformer solution, but that's not possible. Thanks again.