performance - MDX Selecting date dimension consumes huge amount of memory -

a following query takes 1 or 2 seconds execute:

select { [measures].[premium], [measures].[revenue] }  on columns, non empty { ( [insured].[pk insured].[pk insured].allmembers  * [insured].[named insured].[named insured].allmembers  * [producer].[name].[name].allmembers  * [producer].[pk producer].[pk producer].allmembers  * [coverage category].[name].[name].allmembers  * [company].[name].[name].allmembers  * [company].[pk company].[pk company].allmembers  * [market ownership group].[pk market ownership group].[pk market ownership group].allmembers  * [agency ownership group].[pk agency ownership group].[pk agency ownership group].allmembers * [company].[type].[type].allmembers ) } on rows ( select ( { [marketing rep].[pk non broker].&[820676] } )  on columns ( select ( [expiration date].[date hierarchy].[date].&[2013-08-27t00:00:00] : [expiration date].[date hierarchy].[date].&[2013-11-25t00:00:00] )  on columns ( select ( { [policy].[status].&[pif], [policy].[status].&[bif] } )  on columns [crumpcrc])))  ( [policy].[status].currentmember, [marketing rep].[pk non broker].&[820676] ) 

if add date dimension (expiration date) query takes minutes execute, return memory overload error. 1 problem:

select { [measures].[premium], [measures].[revenue] }  on columns, non empty { ( [insured].[pk insured].[pk insured].allmembers  * [insured].[named insured].[named insured].allmembers  * [producer].[name].[name].allmembers  * [producer].[pk producer].[pk producer].allmembers  * [coverage category].[name].[name].allmembers  * [company].[name].[name].allmembers  * [company].[pk company].[pk company].allmembers  * [market ownership group].[pk market ownership group].[pk market ownership group].allmembers  * [agency ownership group].[pk agency ownership group].[pk agency ownership group].allmembers * [company].[type].[type].allmembers * [expiration date].[date hierarchy].[date].allmembers ) } on rows ( select ( { [marketing rep].[pk non broker].&[820676] } )  on columns ( select ( [expiration date].[date hierarchy].[date].&[2013-08-27t00:00:00] : [expiration date].[date hierarchy].[date].&[2013-11-25t00:00:00] )  on columns ( select ( { [policy].[status].&[pif], [policy].[status].&[bif] } )  on columns [crumpcrc])))  ( [policy].[status].currentmember, [marketing rep].[pk non broker].&[820676] ) 

i monitoring memory behaviour in task manager: when first query runs nothing special happens memory, when run second 1 (with date dim included) memory jumps 2gb 8gb. noticed start removing columns (with date included) query starts executing faster , memory consumption starts fall off. have feeling adding each new column multiplies number of operations needed execute query.

difference between date , other dims date set hierarchy. i've included warning message see year/month attribute on hover

any idea did wrong?

the attribute type error message indicates no have date dimension set type time. here's link explaining dimension types: http://technet.microsoft.com/en-us/library/ms175452.aspx.

i believe when query executed against cube, looks @ possible combinations of members each selected dimension find intersections, removing dimensions improve performance because there aren't many possible tuples. if large (wide) query need run regularly, may want review aggregations in cube. sqlcat has great whitepaper on performance tuning both cube design , query design explains engine , happens when query executed.

ssas performance tuning not strong suit, recommend bids helper validating , optimizing aggregations overall dimension design.