How to slice a factless fact table on time dimension? (SSAS)

Question

Starting with the customary - "please excuse me as this is my first post and i'm a relative beginner" disclaimer, i have the following question...

I work for a not for profit campaigning organisation, I've set up an SSAS solution to measure campaigning actions (e.g emailing the priminister) taken by a set on campaigners (customers) the main fact table has a count of actions as its measure, and is sliceable by say time and geography....

... but I also want to have another factless fact table that can show a count of how many campaigners are in what mailing segment... so i think what i need to do is basically dump a copy of my campaigner dimesion (which is slowly changing for people moving geography etc) into its own factless fact table... columns being FK_campaigner, segment_id, start_date, end_date but then how do i link that into the time dimension as it doesn't have an FK_time (merely a start and end time)... i guess what i want to do is relate the factless table to the time table on a "when PK_time > start_date and < end_date" then slice for me... but HOW? and is this possible or do i have to go down the route of loading one fact for each day that someone was in a segment?

many thanks to anybody who can point me in the right direction either structurally (is the broad approach wrong?) or even better in the practicalities of actually doing it in SSAS..

AJ

Answer 1

If you just want to analyse this data for a single point in time e.g. show me what what my numbers looked like at point x. Then you could have the time dimension be the "effective date" . This would be semi additive and you would not be able to aggregate the data across time.

However, if what you interested in is analyzing the transition between time periods, than there is a "Many to Many" solution that would allow this: Many to Many revolution white paper

The whitepaper provides several models the one that would be relevant in your scenario would be either the "Cross Time" or "Transition Matrix"

Good luck