MDX equivalent of group by MAX

  • 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