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
Load * from mytable(QVD);