Measure with time

Hesh

Hi,

I have to create a calculated measure depending on the date from time dimension, like following .

Case when TDQ_FACT.STATUS=’Open’ or TDQ_FACT.CORRECTED_DT<TIME_DIM.CALENDER_DT then ‘Open’ Else ‘Closed’ End

Can you please help me how to model this in BMM layer?

Thanks

Hesh

Thomas Dodds

Did you try implementing your logic into a new logical column?  post what you did in that column and what you are receiving as 'not working right' or error messages.

Gianni Ceresa

+1 Thomas

And just a detail: that's not really a measure (how do you plan to aggregate a column having only "Open" or "Closed" as value?) but an attribute. So if you want to make things in the right way it must not be in your logical fact table but in a logical table acting as dimension ...

Hesh

Thanks Thomas, Gianni

May be I need to organize my thoughts for the solution.

I thought of this mentioned logic as the current table can not hold history, it is updated daily for status and dates. I was trying to get history indirectly by using some logic as I mentioned.

Now I have changed my thoughts . What I think now is to create an aggregate table( to hold the daily snapshot of the current table and use this further to model measure in RPD.

I would like to have measure at different level of aggregation... Following is the new aggregated table followed by further levels of aggregation.

This new aggregation can be directly used as is to trend the data at date level along with TABLE_NAME and ERROR_DESC level.

     

CAL_DT	TABLE_NAME	STATUS	ERROR_DESC	CNT
6-Oct-16	T_ORDERS	Open	Invalid gender	157
6-Oct-16	T_ORDERS	Closed	Invalid gender	15
6-Oct-16	T_ORDERS	Closed	without a standardisation	2
7-Oct-16	T_ORDERS	Open	Invalid gender	170
7-Oct-16	T_ORDERS	Closed	Invalid gender	20
7-Oct-16	T_ORDERS	Closed	without a standardisation	4

What I am not sure is how to get further aggregations like following? do I need to create different views at Physical layer of RPD and model this in BMM or a hierarchy along with Aggregation level as SUM at measure level can do this automatically?

   

MODIFIED_DT	TABLE_DESC	STATUS	CNT
6-Oct-16	T_ORDERS	Open	157
6-Oct-16	T_ORDERS	Closed	17
7-Oct-16	T_ORDERS	Open	170
7-Oct-16	T_ORDERS	Closed	24

   

MONTH	TABLE_DESC	STATUS	CNT
Oct	T_ORDERS	Open	327
Oct	T_ORDERS	Closed	41

Thanks

Hesh

Thomas Dodds

Now that you have a snapshot you can configure the logical table sources and hierarchies to control your summaries (so you won't be adding up snapshot elements over time but using the last aggregation rule and you can add up like summary levels such as Status or Error)