0

I'm quite new to MDX and I'm having a bit of an issue with the aggregation of one of my measures.

In my DSV I have an "Events" table. We track the agents that run these events, and since multiple agents can be involved in running a single event, I have split this out into a separate table of "Agent" with a bridging table in the middle:

https://i.stack.imgur.com/ELWpc.jpg

I want to track what is called "Coverage", which is the number of Events held in a particular week and also each agent who ran the event. So if there were 3 events held one week, and one of these events were run by two agents, that would be a coverage of 4.

When I go to analyse the cube, dragging on Week Commencing and my count of Events, I note that it isn't right - It only considers individual events and not the number of agents. Dragging on Agents solves this but I still want to see an overall figure without having to drag on Agents.

So I created a calculated member like so:

CREATE MEMBER CURRENTCUBE.[Measures].[Visit Coverage] AS IIF([Agents].[Agent].currentmember.parent IS NULL, SUM([Agents].[Agent].[Agent], [Measures].[Events Count]), [Measures].[Events Count]);

So basically, if all agents are selected (parent is null), sum up all of the events count for each agent, otherwise just give me the events for each individual agent if I'm analysing by agent. This works great...and also works if I want to filter by one particular agent, but then falls over if I try to filter by more than one (but less than all) agents, giving me a null value.

I'm completely stumped on how to solve this one, could anyone help me out?

Chris

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
SeeBeeOss
  • 79
  • 11

1 Answers1

0

Your table design covers the requirement to count the number of events, avoiding a double-counting of agents via the many-to-many bridge table between the main fact table and the agent dimension table. If you want to have a measure that does not avoid double-counting, then its fact table should directly link the dimension tables.

Hence, I would create a view or named query that has foreign keys to your three dimension tables, and use this as the base of a new measure group in addition to the existing ones. This view or named query could just be built as a join from the main fact table and the bridge table. Then add a count as the coverage measure to this measure group.

FrankPl
  • 13,205
  • 2
  • 14
  • 40