Model structure issue with date filtering.

[KentOlsen]

Our business needs don't fit neatly into the time-based definitions readily available in the cubes.

Is there a practical way to filter or drill based on weekday/weekend or last-day-of-month?


Thanks,
Kent

 

[yksvaan]

Is it PowerPlay cubes you are referring to?

You shouldn't (I think so) mix this with time dimension.
Instead produce in your query flags that show these things and make new dimensions based on them. Then you can see for instance the whole year vs. weekdays/weekends.

Now you have a database query problem, how to produce those flags Well there is last-day-of-month function in transformer for datasource columns.

Cheers

 

[KentOlsen]

>>Is it PowerPlay cubes you are referring to?

Yes.


The data source already has End-of-Month and Weekend flags so that part's out of the way.

If the reports are layered by either of these flags they actually do quite well in Reporter mode. But my user base is still pretty green. They mostly understand drilling.

We actually have quite a few locally defined time periods. It would make my users lives a lot simpler if one of the periods could be selected as an element of a time dimension instead of each of them being their own (often mutually-exclusive) dimension.


Kent

 

[yksvaan]

Nothing prevents from making the "Time" dimension yourself as a regular dimension, basicly it is a simple hierarchy. Of course then you loose all nice things like Relative time dimension, but if you wouldn't need those. I guess, then you could have your LastDofM/not-LastDofM as an alternative drill-down.

But one thing which will prevent this approach is if your measures need Time state rollup: Last period (or similar). Or you would at least have a big workaround to invent.

Cheers