Hi. I have an OLAP Cube Design combined with rendering to an Excel
Pivot Table issue here.
Our fact table is composed of sales transactions from a website.

We routinely take a relatively small number (say 10,000) of these
transactions and load them directly into Excel from SQL SERVER and then
create a PivotTable from that spreadsheet.
Let's say it looks something like this:
http://home.comcast.net/~murdockdesign/PivotOlap.jpg (small pic of a
pseudo Pivot View)

**Note that we are using the transactionID (TxID) as a dimension.

We want to support this kind of report in an OLAP cube so that we can
build the same kind of report against much larger Fact Tables (say
1,000,000+ transactions). The problem we run into is that Analysis
Services (and perhaps OLAP in general) doesn't like dimensions that
have millions of members, which is what happens when we let TxID be a
dimension in our cube, since each TxID is (of course) unique.

In our reading, we've seen that it's a bad idea to let dimensions have
more that a few thousand members, and one reason stated is because
"users can't easily comprehend a report with that many members."
However, we don't typically create big reports. We use the other
dimensions in the pivot to filter down the result set so that the
resulting PivotReport is a few pages long, and only a few hundred
TxID's.

We've tried using Automatic Grouping on the huge TxID dimension, but it
times out while trying to perform the Member Count (SQL SERVER TIMEOUT
Connection property has been set = 0). We realize that extremely large
dimensions are considered a bad design practice in Data Warehousing
however, we have a real business need here to render our reports in
this manner.

Could anyone suggest a way to go about creating this report in Excel
against a data set with millions of unique transaction ID's?

Thanks!