How to design transformer Cube with Inventory management

Check list of all posts

When building cube for inventory management, the simple aggregation must not be applied. Therefore, I need to use the "Last Period” time state rollup so that for any given time period, the last value will be used for running total in that time period. This approach should be applied for month, quarter and year level. It should looks like below



The problem arises when I'm looking at my cube for instance, at a month by month level in the hierarchy. If the last day of the month happened to be a day when there was no record, then PowerPlay puts in either a zero or an N/A in the cube cell. This gives misleading information...it should show me the last non empty value instead of just the last value. By the way, "Last Non Empty" aggregation function is provided by SSAS and IBM cubing service.

The solution is to make sure that the lowest level DAY in Time dimension only exists when there is data. In this case, date dimension is built as below

To achieve this goal, you need to do two things below:

1. Create iqd for date dimension to only selected days when there is data
2. Set it as default or include when needed in the level Year/Quarter/Month/Day  in date dimension