Note: "Sub Query" mode is only applicable to Microsoft Analysis Services data sources.

Microsoft SSAS has a limitation that the "where" clause of the MDX statement can't accept more than one element from the same hierarchy. So for example,  there is no natural way to set a query to show "sales by product where year is 2008 OR year is 2009".
To overcome this there are three options.

1. Aggregation:
   Aggregate the multi-selection of the where statement into a Custom Member. This is the default behavior of Pyramid. If you look at the MDX, you will see something of the following vain.

   MEMBER [date].[year].[combox] as Aggregate([date].[year].[2008], [date].[year].[2009])

   and then see:

   where ([date].[year].[combox])

   The benefit of this option is that the multi-select CAN include custom members and custom sets. However, there are scenarios where this query either takes a VERY long time to return or never returns at all.

2. List:
   It is also possible to supply a list into the "where" clause.
   
   {[date].[year].[2008],[date].[year].[2009]}
   
   However, that might not work well if there is a very large list of items or if specialized logic or aggregations are needed.
    
3. Sub-Query:
   The "From" clause of the MDX statement looks like this:
   
   FROM ( SELECT StripCalculatedMembers({[date].[year].[2008], [date].[year].[2009]}) ON 0 FROM [Pyramid Sales Demo] )
   
   This approach can sometimes perform better than the first approach but has the downside of not being able to include custom or even calculated members (created in Pyramid or created in the SSAS engine respectively). For this reason, the Aggregate option is the default.
   
   Aside from perhaps performance, and that the subquery mode doesn't allow for custom elements, there should be no difference in the results.

Unfortunately, when working against SSAS the cube designs and data configurations are vast, and it's never "one size fits all" when querying it.

For more details on enabled subquery mode see online help here.