A question related to CrossTable/Performance

[#1]

Hi,

I am wondering if someone can point me to the right direction.

Suppose I have a table or qvd file with a few dimensions and several hundred measures m1, m2 and etc. (see example below). What would be a good way for me to compute aggregate values of these measures without modifying the table or using crosstable?

I originally used crosstable to turn m1, m2, m3 and etc. into values (see my sample code below) so that I could easily compute my measures in my pivot table as Sum(mValue). CrossTable works great for me. However, I am expecting that there will be several hundred measures in my underlying data, and when I do crosstable, there will be more than than 3 billion rows, and not sure how that will impact performance. Without CrossTable, my QVD file will have less than 40 million rows which sounds a lot better, and hence my hesitation on using CrossTable.

If someone has a good workaround or suggestion on how to deal with large dataset and performance, please let me know. I am always interested in your thoughts. Thank you.

=========================================

Dim1     Dim2     m1     m2     m3     m4     m5....     m600

a               x           3          4          5          1     2               -1

a               x           3          1          6          1     4               -1

a               y           1          2          0          1     9               -1

b               y           3          0          4          1     8               -1

b               y           4          2          1          1     7               -1

b               z           90        5          1          1     2               -1

=========================================

CrossTable(Name, mValue,2)

Load * from mytable(QVD);