I added a datatable to spotfire using information link and I'm wondering the best way to do pivot/unpivot transformation,
I found three way to do that : - I can add transformation at information link level - Using insert > transformations - By adding new table that use my first table and transform it.
I'm wondering when to use each of these three possibilities
Thank you
At the Information Link level: I would use this when creating an aggregate information link that many people will want to use. In this way, you have an aggregated version of an information link out there that can be pulled in to many reports, potentially reducing strain on both your Database and Spotfire Server.
Using Insert > Transformations: This is the one I use the most. This is nice because it still uses the existing link, but once the analysis is opened, Spotfire is only holding the data it needs (as defined by your pivot) in memory, and nothing more. Note, however, that unlike the method above, this still pulls all data from the database and across the network through the spotfire server. So while the end result analysis may be slender, depending on the amount of data it pulls, it still could take some time to run/use up resources.
By adding a new table that uses the first table and transforming it: I use this second most. This is good for when you need all the data for one type of graph/table, but you have other tables that are at a higher grain and you want to join to those. In that case, aggregating up just one table and joining it, while still retaining all details in a different table can be handy and allow for better interactivity. Note that this will be the largest analysis of the three.
There are probably other differences I am not thinking of at the moment, but these are the basics. It is important to know how spotfire processes and holds data in memory. This is one of the biggest drivers for picking one over the other.