Measure with time

Question

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 · Answer

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)

Hesh · Answer

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_DTTABLE_NAMESTATUSERROR_DESCCNT6-Oct-16T_ORDERSOpenInvalid gender1576-Oct-16T_ORDERSClosedInvalid gender156-Oct-16T_ORDERSClosedwithout a standardisation 27-Oct-16T_ORDERSOpenInvalid gender1707-Oct-16T_ORDERSClosedInvalid gender207-Oct-16T_ORDERSClosedwithout 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_DTTABLE_DESCSTATUSCNT6-Oct-16T_ORDERSOpen1576-Oct-16T_ORDERSClosed177-Oct-16T_ORDERSOpen1707-Oct-16T_ORDERSClosed24

MONTHTABLE_DESCSTATUSCNTOctT_ORDERSOpen327OctT_ORDERSClosed41

Thanks

Hesh

Gianni Ceresa · Answer

+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 ...