J
JT
Hi. I have an OLAP Cube Design <to> Excel Pivot Table rendering 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 into Excel, where we create a PivotTable.
(No OLAP used in this portion....just data right well below the 65k rec
max loaded into a worksheet from SQL SERVER)
Let's say it looks something like this:
http://home.comcast.net/~murdockdesign/PivotOlap.jpg (*screenshot of
pseudo Pivot table)
**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 bigger 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 only a few pages long containing a few hundred
TxID's.
We've tried using Automatic Grouping on the huge TxID dimension, but it
times out when trying to retrieve the Member Count (we have set SQL
SERVER TimeOut Property to 0).
We are clear on the fact that a large dimension is considered bad
design practice, however we have a real business need to render our
data in this way.
We would appreciate it if anyone could suggest a way of creating this
report in Excel against a data set with millions of unique transaction
ID's?
Thanks!
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 into Excel, where we create a PivotTable.
(No OLAP used in this portion....just data right well below the 65k rec
max loaded into a worksheet from SQL SERVER)
Let's say it looks something like this:
http://home.comcast.net/~murdockdesign/PivotOlap.jpg (*screenshot of
pseudo Pivot table)
**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 bigger 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 only a few pages long containing a few hundred
TxID's.
We've tried using Automatic Grouping on the huge TxID dimension, but it
times out when trying to retrieve the Member Count (we have set SQL
SERVER TimeOut Property to 0).
We are clear on the fact that a large dimension is considered bad
design practice, however we have a real business need to render our
data in this way.
We would appreciate it if anyone could suggest a way of creating this
report in Excel against a data set with millions of unique transaction
ID's?
Thanks!