Re: Filtering a dimension based on value from another cube?
From: Olivier Matrat (olivier.matrat_nospam_at_winsight.fr)
Date: 07/22/04
- Next message: Alexander Nenashev: "sorting doesn't work in excel pivottable when member properties are displayed."
- Previous message: Shaun: "Clustering and KB 308023"
- In reply to: Jéjé: "Filtering a dimension based on value from another cube?"
- Next in thread: Jéjé: "Re: Filtering a dimension based on value from another cube?"
- Reply: Jéjé: "Re: Filtering a dimension based on value from another cube?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Jul 2004 12:42:32 +0200
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@_A_hAotmail_A_.com> wrote in message
news:#C4nlN1bEHA.2944@TK2MSFTNGP11.phx.gbl...
> 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.
>
>
- Next message: Alexander Nenashev: "sorting doesn't work in excel pivottable when member properties are displayed."
- Previous message: Shaun: "Clustering and KB 308023"
- In reply to: Jéjé: "Filtering a dimension based on value from another cube?"
- Next in thread: Jéjé: "Re: Filtering a dimension based on value from another cube?"
- Reply: Jéjé: "Re: Filtering a dimension based on value from another cube?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
