Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i just wonder what are the effects of the joining tables in qlikview?
how does join affect the performance. which option would be better? joining tables in select statement or join the load statements?
Best Regards,
First of all, not joining is usually better than joining. QlikView handles a normalized data model very well.
The only case I see where the performance is slightly better with the tables joined instead of un-joined is if you have two large tables (many records) linked to each other. E.g. you have four tables Customers-Orders-OrderDetails-Products and both Orders and OrderDetails are potentially very large. Then you should try joining these two tables (but not Customers and Products) to see if the performance improves.
Secondly, an SQL JOIN is performed on the DB server, whereas the QlikView Join is performed locally in memory. In other words: If the tables are very large, you should do it on the DB server - i.e. in the SELECT statement.
HIC
Hi,
My oppinion is different to Henric (Don't get mad Henric!)
Im agree that the QV model is very good. For example, if you have a transaction table, keep the customers table as separate table, and lets the "Cust_ID" field do the job without join.
But, if you have a simply field (for example, invoice type and description), based on my experience, is better to join that to Invoices table. The reason is that the applications works better with less tables, even if the memory used is longer.
All cases are different. Some times, is better to keep the transactions tables (for example, head and body of the invoices) separately, but some times it works faster while operating if you join those tables in only one.
You have to consider the following things:
1- A field with a few values with too much repeats isn't too bad for the dataset in QV.
2- A key field that is not optimized (for example, with text/number format) takes a lot of resources.
3- A less number of tables is a good thing in QV
4- Ever will be a GOOD choice to test between two schemas!! With time, you will can intuit the results, but don't discard a surprise!
In QV the things are a little different to other BI systems. You could get a hole datawarehouse in only one table (with more difficulty to make expressions, sure) if you want, or you can create a "beauty DW"... Every time, test is a good idea.
For example, in a customer i had two big transactional tables with Head and Body parts of each invoice. In each sale, the product could have different prices. The best solution (for memory cost and performance) was join Head and Body tables, and make a separate table with the version of prices of each product.
I hope this helps.
Hi
I agree with Sebastian. And when testing the model performance, if you have slow performing charts, check that the expressions do not span too many tables. If the data is not joined in the model, QV has to perform the join in memory when evaluating the expression. If the expression includes fields that are 2 or 3 steps removed from each other, this can become very compute intensive.
So the degree to which you denormalise the data depends on the expressions you use.
Just my 2c worth
Jonathan
BTW - these things are only an issue when the data starts getting larger, and the expressions more complex and the charts take more than a second or two to render.
There is more than one way to skin a cat. This is especially true for QlikView data modelling.
Both of you raise valid points and everything you say is in principle true. So, when it comes to choosing data model, you have to weigh pro:s and con:s. Which isn't always easy. But it helps if you are informed. (which both of you seem to be .)
Some arguments for normalization:
So my view is basically that you shouldn't look at performance only. Also manageability and robustness (how easy is it for a user to enter a faulty expression) matters. Do I use denormalized models? Of course I do. But I never start with a denormalized model as the goal.
Bottom line: Both ways work, and both ways have pro:s and con:s. And I do not think that we are that far apart in our opinions...
HIC
PS
If you want to join Invoicetype and Description onto the Invoice table, you should use Applymap and not Join. Only this way can you assure that you don't get extra records produced through the join.
Hi Henric, i didnt understood about the "extra records produced through the join"...
If I use a table, which contents in field A, only values 1, 2 or 3, and use Left Join with other table that has 1, 2, 3 and 4, the "Left" clause will load only records with 1, 2 or 3... why you said that Apply map is the "only way to assure..."?
Let's take your example wtih InvoiceType above.
We assume that
The goal is to get the InvoiceType from table B into table A and then use the resulting table in QlikView. (Which, by the way, is a "join" that I recommend that you do.)
Now, if table B contains two records for a specific invoice number N (which it shouldn't, but I have seen also this type of error in real life) then both records from table B will be joined to the single record in table A and corrupt table A so that the QlikView calculations will be incorrect. Invoice N will be counted twice. It doesn't help that you use "Left join". The record with invoice N is there and will be duplicated.
Databases shouldn't contain this type of errors, but - believe me - they often do. Check the number of records before and after the join, and you will sometimes be surprised that it is not the same.
To be sure to not change the number of records, I always use applymap() as a lookup function in these situations. It is just a translation from InvoiceID to InvoiceType. Then I know that I have the same number of records before and after.
HIC
Hi,
Im my situation i have a main qvd(over 5 gb) and some description tables(7-8). in description tables i have some null values for some fileds. Since i dont want to see these null fields, i have decided to connect these tables to main qvd by left join. after joining this tables by left join, the qvd file size decrease about 1,8 gb. i think it would increase the performance since size of qvd file is falling. I have also checked the Sum() and Count() . fortunately, they dont change.based on your comments, would i say making left join is better for my situation?
Additionaly, since i want to remove the null fields can i use "keep" for that. as i know difference between keep and join is that keep dont merge tables and after keep operation tables are still separate.
Thanks to all.
If the description table has the same primary key as the fact table, then you should join.
But if the key to the description table is a foreign key in the fact table and the number of records in the description table is several magnitudes smaller than in the fact table, then you should not join. Use Left Keep instead.
HIC
i have seven description tables and main qvd table. the size of description tables are too much smaller than main qvd table and primary key of description tables are foreign key to main table. if i use "left keep" instead of "left join" then the only difference i see is separation of tables, not merged table. i wonder what are the differences between keep and join. why i sould use keep for this situation?
Thanks.