Sean wrote:In general is it better to do the business rules and formulas and all the data manipulation upfront say in the database and use that data into QLikview. Or use scripting / expressions for this.
My personal opinion is that BUSINESS rules should be applied within the business system, which is to say they should be applied before loading into QlikView, which is the REPORTING system. However, if the rules are actually just REPORTING rules, then I would apply them within QlikView rather than within your business system.
An an example, let's say we're doing a cost system. At a basic level, we have business facts - I paid $100 each for this part, I had to scrap 30 items due to this problem on this machine. On top of that, we have business rules for how the costs are calculated - the cost of scrapped items should be allocated across all of the same type of items produced in the month, fixed costs should be allocated by the time spent on some particular machine, this type of part should use a 12 month rolling average of the price paid rather than the actual price paid. And finally, we have reporting rules for how the costs are to be displayed - group all part costs into a single category because the users don't care about the details, use the date shipped rather than other dates like the date ordered or date finished, allow users to select whether or not to include the cost of scrapped items.
The categories are pretty nebulous, as may be evident in the example. Saying for certain whether a piece of information is a fact, a business rule or a reporting rule may be difficult. But to the extent you can split them into these categories, that's what I like to do.
Most systems I've built don't have much complexity in the reporting rules, to the point where you could hardly call them rules at all. I believe only one system I've built has had complicated reporting rules. While I COULD have staged the data for QlikView in the business system by executing the reporting rules on that side, it seemed like a cleaner separation to me to execute them in the QlikView script itself. As a result, that particular script is long and complicated, and I believe takes over an hour to load. The speed could certainly be improved with some performance tuning; I've learned a lot about script performance since then, but it hasn't been worth revisiting since it only loads once per day.
Still, there's nothing strictly wrong with staging the data for QlikView by executing the reporting rules within your business system. If you find that to be more convenient for you, I don't see that it would cause any problems.
As far as doing the formulas in the expressions, from a performance standpoint, it is best to keep that to a minimum. The more you can do in your business system or in the script, the faster the various charts will display for your users, and so the happier they'll be with it.
I am been advised to use QLikView powerful scripting etc and not to use complex sql statements for loading data, however being new to QLikView i am finding it very difficult to get used to the scripting. So is it OK to do all of this upfront and not in QLikView?
While I don't see anything specifically wrong with using complex SQL, if you're dealing with large volumes of data, I would recommend staging it in QVDs, and then having the applications load from the QVDs. Doing that, I've found that my SQL tends to be very simple, and all of the complicated joins are done in the QlikView script.
Lastly some general expression help:
If i have a fact row as below:
TimeKey Measure DateValue1 DateValue2 DateValue3
20090112 100.12 2009/03/01 2009/02/13 2008/12/09
In QLikView i need to build expression with business rules as mentioned above.
The rule is that if various combinations of the DateValues 1-3 are filled the Measure value must be added or subtracted, how do i do that?
If I understand what you're asking, something like this:
LOAD
...
,if(your date value conditions,+1,-1)*Measure as Measure
...
And then your expression would just be something like sum(Measure).
If there is a reason you CAN'T apply the rule in the script, and must apply it in the expression, something like this:
sum(if(your date value conditions,+1,-1)*Measure)
Does the QlikView cookbook have this information ?
It has a lot of examples, but I'm not sure if it specifically addresses where and how business and reporting rules are to be applied.
The expression examples i have found are more around Year to Year and variance expressions, not logical rules based on other fact attributes.
OR as mentioned above is it a better practice to do this upfront in the database and then bring that data into QLiview ???
From the sound of it, I'd classify this as a reporting rule rather than a business rule. If so, then I think the load script is the best place to apply the rule.
