0 Replies Latest reply: Nov 5, 2012 1:51 AM by Ta Khongsap RSS

    A question related to CrossTable/Performance



      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);