TM1 Forum

Posted: **Thu Mar 05, 2009 4:30 pm**

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

Posted: **Sun Mar 08, 2009 9:44 am**

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.

Posted: **Wed Mar 11, 2009 8:12 am**

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

Posted: **Sun Mar 15, 2009 5:40 pm**

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.

TM1 Forum

Problem with mdx where cluse

Problem with mdx where cluse

Re: Problem with mdx where cluse

Re: Problem with mdx where cluse

Re: Problem with mdx where cluse