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.