Friday, February 17, 2012

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle
my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of custome
rs and number of different resources.
When the end user is creating a pivot table with Region en time on the axis,
the count of resources should reflect on the crossings. And I really don't
know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please helpF.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/d...r />
inct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tack
le my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of custo
mers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axi
s, the count of resources should reflect on the crossings. And I really don'
t know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

No comments:

Post a Comment