Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX equivalent of group by MAX Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 1:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 9:07 AM
Points: 47, Visits: 299
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)
);
go

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)
);
go

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

constraint pk_product primary key(product_id)
)
go


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

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

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
go

--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.

Thanks!
Post #1441466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse