June 21, 2015 at 6:53 am
Hi I'm having an issue that is probably caused by my inexperience.
We have a Cube that tracks calls answered from 2 different companies. There a three teams that answer calls from both those companies.
For some reports we need to break it down so that we know, Team Member 1 with Team Lead 1 answered 10 calls for Company 1.
That's easy as the cube is set up to do precisely that.
Company 1 - Team 1 - Team Lead 1 - Agent 1 - Sum of Calls
Company 1 - Team 1 - Team Lead 1 - Agent 2 - Sum of Calls
. . .
Company 2 - Team 1 - Team Lead 1 - Agent 1 - Sum of Calls
However we have a separate report required that doesn't care about which company was involved.
So the report should just be
Team 1 - Team Lead 1 - Agent 1 - Sum of Calls (Company 1 & 2)
Team 1 - Team Lead 1 - Agent 2 - Sum of Calls (Company 1 & 2)
But no matter what I try it comes out as
Team 1 - Team Lead 1 - Agent 1 - Sum of Calls (Company 1)
Team 1 - Team Lead 1 - Agent 1 - Sum of Calls (Company 2)
Team 1 - Team Lead 1 - Agent 2 - Sum of Calls (Company 1)
Team 1 - Team Lead 1 - Agent 2 - Sum of Calls (Company 2)
I'm beginning to feel quite stupid as I feel this must be a common issue for which there is a simple solution and I'm just not seeing it.
I'm trying to achieve this in MDX. Should I be doing it as part of the cube design itself in one of the calculations or is MDX the right place.
Can anyone help me?
June 21, 2015 at 7:31 am
When you created the cube, did you define Team to have a dependency on Company with the key? This would make 'Team 1' for each company distinctly different even though they have the same description. If this wasn't done, it seems to me like the behavior you're looking is the default.
June 21, 2015 at 11:34 am
Brian Carlson (6/21/2015)
When you created the cube, did you define Team to have a dependency on Company with the key? This would make 'Team 1' for each company distinctly different even though they have the same description. If this wasn't done, it seems to me like the behavior you're looking is the default.
Yes because that was the structure and is need for some of the reports.
It's just these other set of reports that we need as if the Company didn't exist.
I'm sort of looking for a way to overcome the default behavior in MDX and create a query that will bring the values together.
EDIT:
With
member [Dim Sc Agent Teams].[Team].[Team 1] as
Aggregate(
filter (
[Dim Sc Agent Teams].[Team].members,[Dim Sc Agent Teams].[Team].currentmember.name="Team 1"
))
member [Dim Sc Agent Teams].[Team].[Team 2] as
Aggregate(
filter (
[Dim Sc Agent Teams].[Team].members,[Dim Sc Agent Teams].[Team].currentmember.name="Team 2"
))
member [Dim Sc Agent Teams].[Team].[Team 3] as
Aggregate(
filter (
[Dim Sc Agent Teams].[Team].members,[Dim Sc Agent Teams].[Team].currentmember.name="Team 3"
))
Select
{[Dim Ticket Close Date].[Month Close].&[May 2015] }
*
{ KPIValue("Case Resolution Rate 1 KPI") ,KPIGoal("Case Resolution Rate 1 KPI") ,KPIGoal("Case Resolution Rate 1 KPI") }
on COLUMNS,
Non Empty
{ [Team 1],[Team 2],[Team 3]
}
on ROWS
from [Cube]
where
([Dim Sc Agent Teams].[TEAM GROUP].&[XAPI])
I've made a little headway, This will give me the values for all Teams Called the same. However I'm sure there must be a better way than explicitly specifying the name you're looking for.
Also this will be unworkable at the lower levels with the employees working on 2 Teams 🙁
June 30, 2015 at 2:46 am
Because of the (correct) design of your dimension what you are seeing is distinct composite key selections. The easiest way to get around this is to introduce new attributes in your dimension for things like company and team that are not part of a composite key. An example can be found in the Date dimension of the AdventureWorks cube (http://msftdbprodsamples.codeplex.com/releases/view/55330). Here you will see that in the hierarchies you have attributes called "Month"; these are not actually "Jan", "Feb", Mar" etc. they are "Jan 2013", "Feb 2013", "Mar 2013" etc. There is a separate attribute for "Month of Year" which is the distinct month names without year. The hierarchical Month attribute has a composite key so that it always belongs to a year whereas the Month of Year attribute only has itself as a key, therefore when processing occurs (SELECT DISTINCT x FROM x) it only returns the 12 month values.
In short, to avoid confusion, put your hierarchical attributes in a hierarchy and disable normal browsing of them as this may confuse users. Have separate attributes outside of hierarchies if a distinct non-hierarchical value is what you need. You can always cross join these later.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply