April 11, 2013 at 1:44 pm
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!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply