ioscat wrote:Hello, Форум!
What is the best practice in turbo integrators - to make complicated quiry to DB or to make variables in TI manager? For example if I need to last symbol from string or multiply a number by some factor.
I don't think that there ever is a 'best".
If there is a consistent and unchanging requirement (such as you
always needing the last character of a string, never the last two or the last three, or you
always need to multiply a value by some constant factor, I'd be inclined to have a view created on the SQL server application if for no other reason than it'll (probably) cut down the amount of network traffic. You won't be sending redundant bytes across the wires.
But if the requirement can change, especially if it can change based on other values in your TM1 cubes, then you obviously have to pull down the full data and manipulate it within TI.
That having been said... the SQL query itself should be no more complex than it absolutely needs to be, with as few joins as possible. Ideally it should be nothing more than a simple SELECT query. As SQL Server guru Paul Nielsen says (often)... Fear Complexity!
Assuming that you have a decent RDBMS (such as SQL Server) you should still put as much of the heavy lifting onto it as possible by creation of a view rather than building it into the ad hoc SQL code... if for no other reason than that provides the opportunity to (possibly, depending on the query) re-use execution plans to help speed performance. (Not to mention the fact that it makes your query much less vulnerable to any changes to schema structure on the server, and to the possibility that it may one day stop running for no apparent reason.)