Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

MDX equivalent of group by MAX Expand / Collapse
Posted Thursday, April 11, 2013 1:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 19, 2015 2:24 PM
Points: 51, Visits: 319
Hello, I am pretty new to MDX --- I usually use sql to create reports or reporting procedures. However, there is a growing need for faster/flexible reporting so I'm looking into SSAS as a possible solution. However, I am really having trouble with simple queries. For example, we have a summary table that summarizes millions of transactions by grouping by a baseline and standard group of columns that all queries would be based on. In this table we have sum/count/mean/stdev/min/max. Then we use this table as the base for rollups or further groupings after joining to attribute tables. So this is like our fact table. Anyway, I have a desire to select the MAX of a group based on this table. Below is an example:

create table purchases
trans_id int not null,
dept_id int not null,
product_id int not null,
cost int not null,

constraint pk_purchases primary key(trans_id)

create table summary
dept_id int not null,
product_id int not null,
sum int not null,
max int not null,
min int not null,
count int not null,

constraint pk_fact primary key(dept_id, product_id)

create table product
product_id int not null,
vendor_id int not null,

constraint pk_product primary key(product_id)

insert into purchases(trans_id, dept_id, product_id, cost)
(1, 1, 1, 100),
(2, 1, 1, 300),
(3, 1, 3, 300),
(4, 2, 1, 400),
(5, 2, 3, 400),
(6, 3, 2, 100);

insert into product(product_id, vendor_id)
(1, 1),
(2, 1),
(3, 2);

insert into summary(dept_id, product_id, max, min, sum, count)
select dept_id, product_id, max(cost), min(cost), sum(cost), count(1)
from dbo.purchases
group by dept_id, product_id

--query to get max of summarized sum by dept id, how can I do this in MDX?
--my SSAS project has the summary/product/purchases tables as dimensions and the summary table stats as measures
select max(sum), dept_id
from dbo.summary
group by dept_id

I have tried creating a member calculation using max. But it seems like its summing instead of maxing. I think it is taking the max of the depts including a <null> dept that is the sum of all dept, so the max is always the <null>, which is the sum of all depts. Just to be clear I don't have any null depts, I assume the cube creates this set as part of its pre-processing.

Post #1441466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse