In order to create a Project (i.e. promotion) management function (which allows end users to add and edit promotions in Board), I've created two Date type cubes for a project Start Date and End Date respectively, which contain only one dimension - Project ID. The requirement is after both dates are entered by users, the month selection adjusted accordingly for the project so the dataview below only shows the relevant months.
The way I can think of is to introduce a cube as the dynamic selection for Month which has two dimensions - Project ID and Month. Each time when Start Date and End Date are saved, the values are extracted to SQL so we can generate month records for the project accordingly, so the values for those months within the date range will be set to 1, otherwise is 0. Then we load the data back to Board.
Does anyone have a better way to achieve this? Thanks a lot.