performance tuning

[Nithin007]

interview question:

i have 14 tables in data model(snow flake)

then i found that performance is too slow, then what are the steps i need to check and how can i improve performance?

[marcus_sommer]

The official recommendation is the use of a star-scheme - means a single fact-table with n dimension-tables - as the best compromise in regard to developing/maintaining efforts and script/UI performance.

- Marcus

[Nithin007]

hi marcus

then how can we convert snow flake to star schema(even after doing transformations), could you please explain different solutions or methods ? 

[marcus_sommer]

There is no general way for all scenarios. In many cases the fact-tables could be simply concatenated (union in sql) by harmonizing different field-names, cleaning the data, filling missing values, adding an extra field to flag the source from where the data are coming and of course various other actions, too. Sometimes it's also sensible to join/map multiple fact-areas together.

- Marcus

[Nithin007]

hi marcus

could you please explain

what is

1.harmonizing different field-names.

2.adding an extra field to flag the source from where the data are coming

[marcus_sommer · Accepted solution]

It means something like:

fact:
load F1, F2, SalesMonth as Month, 'Sales' as Source from Sales;
concatenate(fact) 
load F1, F2, BudgetMonth as Month, 'Budget' as Source from Budget;

- Marcus

[Nithin007]

thank you markus

you mention other actions 

can you give some links or concepts to get knowledge on them.

[marcus_sommer]

There are many measures possible. Quite important in regard to the performance is knowing how the data are stored especially to the cardinality of the distinct field-values, see for it: The Importance Of Being Distinct - Qlik Community - 1466796. Another point goes to the UI in which no (nested) if-loops, aggr() or interrecord-functions should be used - ideally everything is viewable with native dimensions and simple functions like sum/count(Field).

- Marcus