Architecture Design vs Evaluation of (Factual) Resource Consumption

[atsushi_saijo]

There is often discussion in the BI architecture group about resource consumption of QlikView modelling. Each developer has own view on what is most "efficient" without factual statistics in QlikView server. It is often his belief rather than tested result. (As the result, discussion sometimes turnes into mithology.)

How would you record resource consumption per reload and during user operations?

For example, when we need implementing QlikView dashboard Foreign Exchange with currency selector, several ways exists.

•a developer claims pre-calculated field must be populated (such as 26 basket of currencies!). He uses long if-statement in frontend, like "if GetFieldSelections(Currency)='EUR', AmountFieldEUR, ..."

In this option, we would see data model with fields like AmountEUR, Amount JPY, AmountUSD......., all pre-calculated based on transaction amount.

•but using if-statement in the frontend is normally anti-pattern, so this is re-written in single statement: sum(Amount$(SelectedCurr)). But this looks a bit scary, playing field name.

Creating pre-caculated transaction amount to target currencies craetes redundant fields and records; but the developer claims QlikView handles such cases in-memory efficiently.

•Another developer created a linking field to currency exchange table, using Currency&'|'MMDD as a key (note it omits YYYY), and create set analysis:

     sum({TxnCurrency={$(SelectedCurrency)},FXYear={$(SelectedFiscalYear)}} TransactionAmount)

Although this set analysis allows users to select desired FX scheme in specific year (or even forecast rates), on modelling level, 1 transaction record will have 26 currencies * 10 years of rows of exchange rates. Set analysis is merely filtering these assocaitions.

It is much faster to reload in the last option, and I am in favour of last option, but I would need to show factual result that it is better.

I should highly appreciate for your feedback how you evaluate the server-side resource consumption/efficiency as application. (I also would be happy if there is such book as QlikView Anti-Pattern, like Java development circle.)

[marcus_sommer]

It's quite expensive to measure the various ways of doing things and optimizing it in a certain direction meant often to get any disadvantages in other places. Therefore you will always need a compromise between your system-resources and the efforts of developing and maintaining an application respectively a whole environment and the requirements from the business-side. And there are so many different requirements and factors to consider that it is impossible to say which way is really better than the others.

In your above described scenario I would probably start by using a dimension-table für the currencies like:

Currencies:

load Date, [Currency Name], [Curreny RateValue] from Source;

which is just connected per Date to the fact-table and my expressions within the GUI would look like:

sum(Value * [Curreny RateValue])

That's not optimal from a RAM point of view in which a crosstable of the dimension-table would lead to a much lesser pointer - but as far as you don't run into serious performance-issues it's not really essential. To get the right field from the crosstable you could use an island-table of the Currencies and creating the fieldname with $-sign expansion like:

sum(Value * [$(=Currency & 'RateValue')])

- Marcus