Hi,

I've 2 cubes:
* Employee absences
* Employee activities

There is 2 shared dimensions:
* Employees
* Time

My users as isolated a case:
I want to follow the employees with more then XX days of absence in the
month (or YY days in the year), and I want to see all the activities of
these employees.

How to do this?

Because I'm using the OWC pivottable, my user can't do complex queries and
is limited to the dimensions of the cube.

My "easiest" solution, but not powerfull, is to create 2 new Activities
cubes which contain only the information related to the flagged employees (1
cube for the year which contain the employees with more then YY days of
absences onyl; 1 for the month which contain the employees with more then XX
days of absences only)

Can I accomplish this through another method?

Any sample is appreciate.

Thanks.

Jerome.

Hi

Since your cubes share some common dimensions, I think you would be better
off using virtual cubes. Virtual cubes act for OLAP cubes just as views for
relational tables.

Asumming for simplification sake that your Absences cube only has the
Employee and Time dimensions with one measure (# of days off/month), while
the Activites cube also has the Employee, Time dimensions plus a Job
dimension describing your employee's jobs, and one single measure (# of days
on a job/month); you could create a virtual cube including both cubes, all
of their dimensions and also the two measures. This virtual cube can be
queried against using the OWC just like a regular cube.

Now, 2 issues remain: first the "# of days off" measure is not available at
the detailed level of the Job dimension within the virtual cube; second,
your client tool cannont easely handle complex filtering conditions.

Issue #1 can be addressed by creating a calculated member using the MDX
ValidMeasure() function in the virtual cube; doing so will make your count
of days off available at any position in your cube, even when you filter by
a specific job, so you can create reports using OWC with both the "# of days
on a job" and the "#of days off" for any combination of dimensions.

Issue #2 can be worked around by using the OWC's ability to hide empty
rows/cols by defaut. You will need to create a 2nd calculated member in your
virtual cube (let's call that one "Display") that either returns Null if
your condition ("# of days off" < XX) is met, else returns "# of days on a
job"; the formula will look sthg like:

Iif([Measures].[DaysOff] < 10,Null,[Measures].[DaysOnJob])

Using this single measure in your report, employees not meeting the criteria
will be automatically stripped off. Of course you can elaborate on that
formula to make it work also at the Year level...

HTH

Olivier.


"Jéjé" <willgart (AT) _A_hAotmail_A_ (DOT) com> wrote

Thanks
its a good way.

what do you think if I do this:
creating a new dimension called "Targeted population".
this dimension will contain the list of my predefined filters, like :
* All population (no filter)
* Employees with more then XX absences in the month
* Employees with more then YY absences in the year
* employees failing a particular training session
* etc... (because there is a lot of targeted population)

When the user select the filter (or the targeted population), then a
calculated cells formula can apply the MDX formula in the cube.
And to complete this "complex" problem I've a lot of distinct count measures
in my cubes!!!!

I've tested this on the foodmart warehouse and sales cube:
* creating a calculated cell. the targeted subcube is "all members" for
everything except the Marital status dimension where the subcube is limited
the the "All marital status" member.
The calculation formula is :
Aggregate(filter(Descendants([Customers].currentmember, [Customers].[Name]),
[Customers].currentmember.Properties("Education") = "Graduate Degree"),

CalculationPassValue(measures.currentmember, -1, relative)

)

So when the user select the "all martial status", only the sales of the
customers "graduate degree" is calculated, whe nthe user select a specific
marital status, then the standard calculation is applied.

If I use the same technic in my case, I'll can filter or isolate something.



What do you think about this?



Thanks.



Jerome.






"Olivier Matrat" <olivier.matrat_nospam (AT) winsight (DOT) fr> a écrit dans le message
de news:OjvuPg9bEHA.796 (AT) TK2MSFTNGP09 (DOT) phx.gbl...