Problem with mdx where cluse

Post Reply
goja09
Posts: 4
Joined: Thu Mar 05, 2009 8:27 am
Version: abc
Excel Version: 2003

Problem with mdx where cluse

Post by goja09 »

Hi,

I've just started using OLAP and i hava a problem with one mdx querry.

In general:

My cube looks in MSSQL Server Management Studio like:

Cubes -> StatCube -> Measure Groups -> "different measures"
Dimensions -> "different dimensions"

In dimensions among other things i have two dimensions:

1) TimeByMonth->year->month->day
2) TimeByHour->hours (24 members 00-01, 01-02 etc.)


My query looks like:

SELECT {[Dim1].[Dim1].&[value1],[Dim1].[Dim1].&[value2],[Dim1].[Dim1].&[value3],[Dim1].[Dim1].&[value4]}
on columns,
{
[Measures].[some measure],
}
on rows
from StatCube
where (
{[TimeByMonth].[TimeByMonth].[Day].&[93563]},
{[TimeByHour].[Hour].&[5]:[TimeByHour].[Hour].&[16]}
)

And I have a problem with clause where:

I can as it is in query above take statistics for one day [TimeByMonth].[TimeByMonth].[Day].&[93563]} and for different hours of this day {[TimeByHour].[Hour].&[5]:[TimeByHour].[Hour].&[16].

But, i haven't any idea how to do this for different days and different hours of each day.

For example, i want to have in clause where: one day from 03:00 to 13:00 and another day from 6:00 to 10:00?
"and operator" doesn't work.

Could you help me?

Regards
Jacek
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Problem with mdx where cluse

Post by Marcus Scherer »

The dimensions/hierarchies in the where-clause define the slices of your cube view. You can only have one slice per hierarchy.
Be aware that the where-clause is used for overriding default members of dimensions. Check what are your default members and measure.

I suggest you calculate your sum on the row axis with calculated members and put the measure in the where clause.

I will think once more about your issue, but first one question. Why do you have a TimebyHour dimension with only hours? Why don't you create one time dimension with all hierarchies (from hour to year) and if needed use this dimension twice (this is called "role playing dimension" in SSAS)?

Feel free to continue the discussion.

M.
goja09
Posts: 4
Joined: Thu Mar 05, 2009 8:27 am
Version: abc
Excel Version: 2003

Re: Problem with mdx where cluse

Post by goja09 »

At first, i want to thank you for your answer.

You wrote: "I suggest you calculate your sum on the row axis with calculated members and put the measure in the where clause."
Could you give me code example how to do this? I'm not fluent in olap and mdx queries yet.

You wrote: "Why do you have a TimebyHour dimension with only hours? Why don't you create one time dimension with all hierarchies (from hour to year) and if needed use this dimension twice (this is called "role playing dimension" in SSAS)?"
I didn't design this database by myself. It's a commercial product delivered by external company and i have no influence on database hierarchies and dimensions.

Regards
Jacek
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Problem with mdx where cluse

Post by Marcus Scherer »

Jacek,

you're really in a mess if you have to work on a foreign model.
See this small example with calculated members. It may help you though my model does not have two time dimensions. Try to transfer it to your model.

Code: Select all

with set s1 as '{[Time].[Calendar].[Calendar Month].&[12]&[2003]
,[Time].[Calendar].[Calendar Month].&[1]&[2004]}'
 
member [Time].[Calendar].m1 as 'Sum(s1)'

select [Time].[Calendar].m1 on 0,
 [Store].[Geography].[Region].&[2] on 1
from [REAL Warehouse]
where ( measures.[Sale Amt],[Buyer].[Buyer].[Buyer Name].&[260])
I may have confused you with the role-playing dimension. Just read somewhere what it means and if it applies to your business case.

M.
Post Reply