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.

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.

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.

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:

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:

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:

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.

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.

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;