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
Problem with mdx where cluse
-
- 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
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.
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.
Re: Problem with mdx where cluse
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
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
-
- 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
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.
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.
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])
M.