Re: Filtering a dimension based on value from another cube?
From: Jéjé (willgart_at__A_hAotmail_A_.com)
Date: 07/22/04
- Next message: Scott Jucovics: "Except Grouping MDX Syntax"
- Previous message: KLEIOS: "Re: Third party software for viewing cube"
- In reply to: Olivier Matrat: "Re: Filtering a dimension based on value from another cube?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Jul 2004 09:08:01 -0400
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@winsight.fr> a écrit dans le message
de news:OjvuPg9bEHA.796@TK2MSFTNGP09.phx.gbl...
> 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: Scott Jucovics: "Except Grouping MDX Syntax"
- Previous message: KLEIOS: "Re: Third party software for viewing cube"
- In reply to: Olivier Matrat: "Re: Filtering a dimension based on value from another cube?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
