Return only the latest records from the universe

[Former Member]

Hi Guys

I am working in the Universe design tool and we have a requirement now to show only the recent records relating to a billing group which is scd type 2.

We have a scenario where customers can belong to billing groups and billing group in turn belong to a master group.

A customer in its life cycle can change billing groups within a master group or can change a master group all together.

All the group information is stored in a dimension table where we have a flag to show if its a current billing group /master group along with the date when it was last updated etc.

My challenge is to create a new class in the universe which only shows the recent billing or master groups for a member and keep the historical records as is...

Here is the structure as attached:

The Fact table is joined to group table on dim_group _contract_key which is the unique combination of customer/billing group/master group.

in dim_group table we have seq_group_id which updates according to  group_id

Any ideas how to implement this?

[Former Member]

Hello Nutty!

You can use the Filter function on DFX or on the BLX. For the DFX, use the bellow:

1. Create an Derived Table from the DIM_GROUP_CONTRACT using just the "max(MASTER_GROUP_ID)" clause

2. Create the relation as an filter to DIM_GROUP_CONTRACT

or

In the Business Layer:

1. Create in some of the Classes an Filter object

2. Use in the WHERE clause the follow: DIM_GROUP_CONTRACT in (select max(MASTER_GROUP_ID) from DIM_GROUP_CONTRACT)

3. The business users need to use this filter in their querys.

Hope it helps.

Regards,