Sounds like you're building a pretty large cube and have a pretty powerful server to run it on.

Couple of things that worries me.

1. Why do you have a 5 million record dimension? Is this 5 million record dimension expected to grow? If it grow linearly as the fact grows, you're going to have an issue since the dimension only supports a little over 2 billion members. You'll also face extremely high processing times on the dimension unless your doing process add.

2. Why do you need to pull 1 million rows from SSAS? Can you just pull it form SQL?

SSAS is meant to provide a way to quickly analyze aggregate data. I can't imagine a scenario where any person is doing an analysis and needs to look at a million records at a time.

I have a feeling you're designing a cube to support something like a SalesOrder with SalesLine fact data, where you have a single SalesOrder with mulitple SalesLines and have made the SalesOrderID into a dimension. I'm assuming in this case, the business wants to be able to look analyze the data and be able to get the specific SalesOrders related to the slice of data. I've dealt with something like this by removing the SalesOrderID dimension and using ASSP[/url] in the drilldown actions to run a SQL query to retrieve the detailed SalesID form the Fact table. The idea is that the SSAS allows you to slice and dice aggregate data and then from there drilldown to the detailed records by passing in the Dimension filters as a parameter to a stored proc that returns the detail records.