MDX Usage Examples

Session-Named Set Examples

The data that is used in these examples is from a company that sells electronics and outdoor and sporting goods equipment.

Example 1

This example creates the session-named set called "prod in SE" in the sales cube. This named set shows the crossing of the product family with the customer members in the Southeast.

CREATE SESSION 
   set sales.[prod in se] as '
    CROSSJOIN
      (
        [customer].[all customer].[southeast].children, 
        [product].[family].members
      )'

Nothing is returned when you create a session-named set.

Example 2

This example creates the session-named set called "prod in NE" in the sales cube. This named set shows the crossing of the product family with the customer members in the Northeast.

CREATE SESSION
   set sales.[prod in ne] as '
    CROSSJOIN
      (
        [customer].[all customer].[northeast].children, 
        [product].[family].members
      )'

Nothing is returned when you create a session-level named set.

Example 3

This example uses the session-named set called "prod in SE." It shows the quantity and total sales for products that customers in the Southeast purchased.

SELECT
    {[measures].[qty], [measures].[total]} ON COLUMNS,    
    [prod in se] ON ROWS 
FROM sales

Here is the resulting output:

State	Product	Qty	Total
FL	Doing	21,091	550,672.41
FL	Electronics	31,056	794,730.61
FL	Health & Fitness	16,321	415,708.57
FL	Outdoor & Sporting	30,065	742,907.85
GA	Doing	1,907	44,360.08
GA	Electronics	2,316	61,577.03
GA	Health & Fitness	1,318	35,589.84
GA	Outdoor & Sporting	2,458	68,438.03
NC	Doing	235	5,404.65
NC	Electronics	3,727	101,688.42
NC	Health & Fitness	1,228	31,310.45
NC	Outdoor & Sporting	835	21,312.83
SC	Doing 1	,266	31,596.69
SC	Electronics	2,646	66,565.97
SC	Health & Fitness	3,483	89,633.82
SC	Outdoor & Sporting	2,936	73,092.30

Example 4

This example uses the session-named set called "prod in NE." It shows the quantity and total sales for products that customers in the Northeast purchased.

SELECT
   {[measures].[qty], [measures].[total]} ON COLUMNS,
   [prod in ne] ON ROWS
 FROM sales

Here is the resulting output:

State	Product	Qty	Total
CT	Doing	844	20,961.12
CT	Electronics	2,659	69,540.52
CT	Health & Fitness	969	22,995.63
CT	Outdoor & Sporting	2,569	61,528.35
MA	Doing	7,918	206,472.36
MA	Electronics	11,184	281,371.34
MA	Health & Fitness	4,339	105,356.59
MA	Outdoor & Sporting	10,076	250,323.21
ME	Doing	1,362	35,151.55
ME	Electronics	4,496	110,153.94
ME	Health & Fitness	2,218	58,342.02
ME	Outdoor & Sporting	3,014	79,426.68
NH	Doing	141	4,207.76
NH	Electronics	466	10,750.48
NH	Health & Fitness	1,095	26,158.29
NH	Outdoor & Sporting	603	14,893.73
NY	Doing	17,493	435,513.26
NY	Electronics	29,246	759,166.44
NY	Health & Fitness	13,880	347,481.77
NY	Outdoor & Sporting	26,714	692,416.36
RI	Doing	265	6,437.18
RI	Electronics	833	22,723.54
RI	Health & Fitness	693	17,760.85
RI	Outdoor & Sporting	857	19,320.02

Example 5

This example uses both of the session-named sets called "prod in NE" and "prod in SE". It shows the quantity and total sales for products that customers in the Northeast and the Southeast purchased.

SELECT
    {[measures].[qty], [measures].[total]} ON COLUMNS,
    {[prod in ne], [prod in se]} ON ROWS
 FROM sales

Here is the resulting output:

State	Product	Qty	Total
CT	Doing	844	20,961.12
CT	Electronics	2,659	69,540.52
CT	Health & Fitness	969	22,995.63
CT	Outdoor & Sporting	2,569	61,528.35
MA	Doing	7,918	206,472.36
MA	Electronics	11,184	281,371.34
MA	Health & Fitness	4,339	105,356.59
MA	Outdoor & Sporting	10,076	250,323.21
ME	Doing	1,362	35,151.55
ME	Electronics	4,496	110,153.94
ME	Health & Fitness	2,218	58,342.02
ME	Outdoor & Sporting	3,014	79,426.68
NH	Doing	141	4,207.76
NH	Electronics	466	10,750.48
NH	Health & Fitness	1,095	26,158.29
NH	Outdoor & Sporting	603	14,893.73
NY	Doing	17,493	435,513.26
NY	Electronics	29,246	759,166.44
NY	Health & Fitness	13,880	347,481.77
NY	Outdoor & Sporting	26,714	692,416.36
RI	Doing	265	6,437.18
RI	Electronics	833	22,723.54
RI	Health & Fitness	693	17,760.85
RI	Outdoor & Sporting	857	19,320.02
FL	Doing	21,091	550,672.41
FL	Electronics	31,056	794,730.61
FL	Health & Fitness	16,321	415,708.57
FL	Outdoor & Sporting	30,065	742,907.85
GA	Doing	1,907	44,360.08
GA	Electronics	2,316	61,577.03
GA	Health & Fitness	1,318	35,589.84
GA	Outdoor & Sporting	2,458	68,438.03
NC	Doing	235	5,404.65
NC	Electronics	3,727	101,688.42
NC	Health & Fitness	1,228	31,310.45
NC	Outdoor & Sporting	835	21,312.83
SC	Doing	1,266	31,596.69
SC	Electronics	2,646	66,565.97
SC	Health & Fitness	3,483	89,633.82
SC	Outdoor & Sporting	2,936	73,092.30

Example 6

This example removes (drops) the session-named set called "prod in SE" in the sales cube.

DROP SET sales.[prod in SE]

Nothing is returned when you drop a session-named set.

Example 7

This example removes (drops) the session-named set called "prod in NE" in the sales cube.

DROP SET [sales].[prod in NE]

Nothing is returned when you drop a session-named set.

Additional Named Set Examples

Example of a session set using SQL pass-through and CREATE SET:

proc sql;    
  connect to olap (host=host-name port=port-number         
          user="userid" pass="password");       
      execute             
        (        
        create set booksnall.threeyears as       
        {[YQM].[All YQM].[1999] :[YQM].[All YQM].[2001]}     
   ) by olap;        
      create table temp as select * from connection to olap 
(    
   SELECT  threeyears ON COLUMNS  ,         
       {[products].[all products].[books]} ON ROWS          
       FROM [booksnall]     
   );    
   disconnect from olap; 
quit;

Example of how to drop a set by using DROP SET:

proc sql; 
connect to olap (host=host-name port=port-number user="userid"                    
                 pass="password");  
execute  

(    
   DROP SET booksnall.threeyears  
) by olap;  

disconnect from olap; 
quit;

Top of Page