How to handle “dynamic fact table” using #prompt in framework manger

Check list of all posts

Context
Based on Kimball methodology, there are three different kinds of fact tables: transactional, periodic snapshot and accumulating snapshot. The case in this document is a very special case, as records in transactional fact table can be used based on user selection. Such fact table is related with trade life cycle.   For the sake of explanation, assume that there is a trade which can be amended multiple times. It is requested to use the last amended trade for calculation of amendment count. There is no way that you can use cube or simple metric to handle it, as all calculation are dependent on selected time frame from user.

Sample

Given trade fact table for month August

Dimension columns	amended date	count
Trade xyz	August 1, 2012	1
Trade xyz	August 5, 2012	1
Trade xyz	August 31, 2012	1
Trade xyz	September 5, 2012**	1

** last amended

If user choose until August 31  time frame, the amendment count = 1 for Trade xyz;  

Dimension columns	amended date	count
Trade xyz	August 1, 2012	1
Trade xyz	August 5, 2012	1
Trade xyz	August 31, 2012	1
Trade xyz	September 5, 2012	1

if user choose until September  30, the amendment count  still = 1 for Trade xyz;  

Dimension columns	amended date	count
Trade xyz	August 1, 2012	1
Trade xyz	August 5, 2012	1
Trade xyz	August 31, 2012	1
Trade xyz	September 5, 2012	1

This basic logic drive all related performance metrcis 

Solution

The solution is to left fact as it is, however, measure, such as amendment count will be defined in framework manager as

select *
 from (select row_number() over(partition by F.TRADE_ KEY order by F. AMENDMENT_DATE_KEY  desc) LAST_AMENDED_TRADE,
              1 as Amended_Trade_Count,
               Dimension keys
        from FactTable  F
        inner join D_DATE dt on F.AMENDMENT_DATE_KEY = dt.DATE_KEY
        where F. COUNT = 1
        and dt.WORKDAY_INDICATOR = 1
        and to_char(dt."DATE",'yyyy-mm-dd') <= #prompt('fmParam_EndDate, 'date')#
        and to_char(_add_months(dt."DATE",14),'yyyy-mm-dd') > #prompt('fmParam_EndDate’, 'date')#
    ) F1
where F1.LAST_AMENDED_TRADE = 1

(note: DB2 based SQL code)

Based on this definition, amended count will be calculated from end date selected by report user