|
Home > Archive > MS SQL Server OLAP > September 2005 > Difficult MDX-Statement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Difficult MDX-Statement
|
|
| lvpaul@gmx.net 2005-09-27, 11:24 am |
| Difficult MDX-Statement
I have a cube with a dimension DATE (Year, Quarter, Month) and a
measure "turnover".
I need data to create a chart of the course of the turnover for each
month of the last 3 years.
My result table should look like this.
Jan Feb Mar ...
2003
2004
2005
Is this possible ?
Thanks for your help
aaapaul
| |
| SQL McOLAP 2005-09-27, 8:24 pm |
| Paul -
Unfortunately you can't do it with a single dimension. There's no MDX you
can write to do what you want. You'll either have to create a new physical
time dimension with just year, a year hierarchy off of the existing time
dimension (which for all intents and purposes creates the same storage as a
seperate physical dimension), or a virtual dimension off of a member property
for year. (Depending on your front end, you may not be able to put different
hierarchies of the same dimension on different axes. If that's the case, you
can't use the extra hierarchy off of an existing dimension option.)
If you don't have a huge cube, I'd recommend using a virtual dimension.
However, with a really large cube, the lack of aggregations from a virtual
will make queries using the virtual dim very slow.
Good luck.
- Phil
"lvpaul@gmx.net" wrote:
> Difficult MDX-Statement
>
> I have a cube with a dimension DATE (Year, Quarter, Month) and a
> measure "turnover".
>
> I need data to create a chart of the course of the turnover for each
> month of the last 3 years.
>
> My result table should look like this.
>
> Jan Feb Mar ...
> 2003
> 2004
> 2005
>
> Is this possible ?
>
> Thanks for your help
> aaapaul
>
>
| |
| lvpaul@gmx.net 2005-09-27, 8:24 pm |
| Thanks !
aaapaul
| |
| Word 2003 memory Leakage 2005-09-27, 8:24 pm |
| What you want is the measure in each month of each year. You just need to
write a simple MDX query and then treat the results in the way you want.
select
[dim_time].[year].[quarter].[month] on columns,
[measures].[turnover] on rows
from Cube_a;
this should work?
Guangming
"SQL McOLAP" wrote:
[color=darkred]
> Paul -
>
> Unfortunately you can't do it with a single dimension. There's no MDX you
> can write to do what you want. You'll either have to create a new physical
> time dimension with just year, a year hierarchy off of the existing time
> dimension (which for all intents and purposes creates the same storage as a
> seperate physical dimension), or a virtual dimension off of a member property
> for year. (Depending on your front end, you may not be able to put different
> hierarchies of the same dimension on different axes. If that's the case, you
> can't use the extra hierarchy off of an existing dimension option.)
>
> If you don't have a huge cube, I'd recommend using a virtual dimension.
> However, with a really large cube, the lack of aggregations from a virtual
> will make queries using the virtual dim very slow.
>
> Good luck.
>
> - Phil
>
>
> "lvpaul@gmx.net" wrote:
>
| |
| Darren Gosbell 2005-09-28, 3:24 am |
| >
> Unfortunately you can't do it with a single dimension. There's no MDX you
> can write to do what you want.
>
Not quite right, I have some that can do it :)
While you cannot have the same dimension on both axis at the same time
you can create calculated measure that return only the value for a given
month. This approach is kind of like what you have to do in SQL to pivot
data.
I have provided an example that works in Foodmart below. (I have only
done the first 6 months as it involves a lot of repetitive typing)
====================
=======
WITH
MEMBER Measures.[Jan] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="1"),Measures.[Unit Sales])'
MEMBER Measures.[Feb] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="2"),Measures.[Unit Sales])'
MEMBER Measures.[Mar] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="3"),Measures.[Unit Sales])'
MEMBER Measures.[Apr] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="4"),Measures.[Unit Sales])'
MEMBER Measures.[May] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="5"),Measures.[Unit Sales])'
MEMBER Measures.[Jun] as 'sum(filter(descenda
nts(time.currentmember,
[time].[month]),Time.currentMember.name ="6"),Measures.[Unit Sales])'
SELECT
{Measures.[Jan]
,Measures.[Feb]
,Measures.[Mar]
,Measures.[Apr]
,Measures.[May]
,Measures.[Jun]
} ON COLUMNS,
time.year.members ON ROWS
FROM [Sales]
====================
==========
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
| |
| lvpaul@gmx.net 2005-09-28, 3:24 am |
| Thanks Darren. It works. Another question:
I only want the TOP 20 of [PCENTER].[KT] not of all customers. How can
I integrate this condition in the TOP-20 Selection ?
This is my MDX-Statement.
With
MEMBER Measures.[Jan] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Januar"),measures.[Menge])'
MEMBER Measures.[Feb] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Februar"),measures.[Menge])'
MEMBER Measures.[M=E4r] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"M=E4rz"),measures.[Menge])'
MEMBER Measures.[Apr] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"April"),measures.[Menge])'
MEMBER Measures.[Mai] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Mai"),measures.[Menge])'
MEMBER Measures.[Jun] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Juni"),measures.[Menge])'
MEMBER Measures.[Jul] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Juli"),measures.[Menge])'
MEMBER Measures.[Aug] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"August"),measures.[Menge])'
MEMBER Measures.[Sep] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"September"),measures.[Menge])'
MEMBER Measures.[Okt] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Oktober"),measures.[Menge])'
MEMBER Measures.[Nov] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"November"),measures.[Menge])'
MEMBER Measures.[Dez] as
'SUM(Filter(descenda
nts(rdatum.currentmember,[rdatum].[monat]),rdatum.curre=
ntmember.name=3D"Dezember"),measures.[Menge])'
SET Top20 AS
'{TOPCOUNT([Kunde].[kunde].members,20,[Measures].[Umsatz])}'
select
{
Measures.[Jan],
Measures.[Feb],
Measures.[M=E4r],
Measures.[Apr],
Measures.[Mai],
Measures.[Jun],
Measures.[Jul],
Measures.[Aug],
Measures.[Sep],
Measures.[Okt],
Measures.[Nov],
Measures.[Dez]
} ON COLUMNS,
crossjoin(TOP20,{rdatum.[2003],rdatum.[2004],rdatum.[2005]}) ON ROWS
from faktura
where ([inex].[extern],[PCENTER].[KT])
| |
| Darren Gosbell 2005-09-29, 3:24 am |
|
>
> I only want the TOP 20 of [PCENTER].[KT] not of all customers. How can
> I integrate this condition in the TOP-20 Selection ?
>
Use a tuple in the topcount expression. To only return the value of the
measure where it intersects with the [PCENTER].[KT] member. I think the
example below should work for you.
SET Top20 AS
'{TOPCOUNT([Kunde].[kunde].members,20,([Measures].[Umsatz],[inex].
[extern],[PCENTER].[KT]))}'
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
| |
| SQL McOLAP 2005-09-29, 9:24 am |
| Paul and Darren -
Forgive my misinformation. Darren's code, although having to be hardcoded
for each existing member, does indeed work. A virtual dim will still be
more dynamic and require no coding, but I'm still impressed with the solution.
My apologies.
- Phil
"Darren Gosbell" wrote:
>
> Not quite right, I have some that can do it :)
>
> While you cannot have the same dimension on both axis at the same time
> you can create calculated measure that return only the value for a given
> month. This approach is kind of like what you have to do in SQL to pivot
> data.
>
> I have provided an example that works in Foodmart below. (I have only
> done the first 6 months as it involves a lot of repetitive typing)
>
> ====================
=======
>
> WITH
>
> MEMBER Measures.[Jan] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="1"),Measures.[Unit Sales])'
> MEMBER Measures.[Feb] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="2"),Measures.[Unit Sales])'
> MEMBER Measures.[Mar] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="3"),Measures.[Unit Sales])'
> MEMBER Measures.[Apr] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="4"),Measures.[Unit Sales])'
> MEMBER Measures.[May] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="5"),Measures.[Unit Sales])'
> MEMBER Measures.[Jun] as 'sum(filter(descenda
nts(time.currentmember,
> [time].[month]),Time.currentMember.name ="6"),Measures.[Unit Sales])'
>
> SELECT
> {Measures.[Jan]
> ,Measures.[Feb]
> ,Measures.[Mar]
> ,Measures.[Apr]
> ,Measures.[May]
> ,Measures.[Jun]
> } ON COLUMNS,
> time.year.members ON ROWS
> FROM [Sales]
>
> ====================
==========
>
> --
> Regards
> Darren Gosbell [MCSD]
> < dgosbell_at_yahoo_do
t_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
| |
| Darren Gosbell 2005-09-30, 8:24 pm |
| No Problems Phil, your advice regarding using a virtual dimension
definite more flexible and a better long term solution.
The technique I showed does not really "scale" well and is really only
good for "one offs" or short term fixes. It comes in handy though when
you need to get a report out quick and don't have time to restructure
your cube ;)
--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell
> Paul and Darren -
>
> Forgive my misinformation. Darren's code, although having to be hardcoded
> for each existing member, does indeed work. A virtual dim will still be
> more dynamic and require no coding, but I'm still impressed with the >> solution.
>
> My apologies.
>
> - Phil
|
|
|
|
|