question

KP-5037 avatar image
0 Votes"
KP-5037 asked OlafHelper-2800 answered

MDX conversion

I have a sql query which i want to convert into MDX for ssas (not too strong in MDX). below is the query.

select appmn (varchar), programid (int), period (datetime), sum(amt) from tableA
group by appmn, programid, period

in cube appmn, programid and period are in separate dimension. I want to add this functionality in calculated members in cube as well.

Thank you,
Kash

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @KP-5037,

Thanks for posting here. In SSAS multidimensional mode, you did not have to create a calculated member in cube. We just need to define relationships between Dimensions and Measure Groups, then we just need to drag the dimension attributes and measure, the results will show as your SQL query does.

Regarding how to define relationships between Dimensions and Measure Groups, please refer to below official document:

lesson-5-defining-relationships-between-dimensions-and-measure-groups

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KP-5037 avatar image
0 Votes"
KP-5037 answered

@Zoehui-MSFT
The sum of amt is 1st by Program ID, AppMN and then Period but until period gets into view, the sum is not working properly that's why i am trying to bind. The total of AMT supposed to be 100, but if period is not there then it shows more then 100 ! In below table, i m just showing January but if there is Feb and so on then sum goes higher then 100 without date. I want to show with or without date 100 !
201507-forcube.jpg



forcube.jpg (16.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

I have a sql query which i want to convert into MDX

There is no straight ahead way to "convert" SQL to MDX.
You have to write the MDX on your own. Without knowing the cube design we can only give some hints.

sum(amt) from tableA

A aggregation like SUM in SQL is a measure in a cube, you get it automatically in MDX by cube desighn; which we don't know.
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.