tag:blogger.com,1999:blog-2268159193748171866.post2577472061793333895..comments2023-08-16T08:53:49.845+01:00Comments on Qlik Tips: Update to Link Table rulesStephen Redmondhttp://www.blogger.com/profile/10815476951939159307noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-2268159193748171866.post-40703082302174910432015-08-08T20:59:07.786+01:002015-08-08T20:59:07.786+01:00I would say that a more flexible and expandable da...I would say that a more flexible and expandable data model would be the following:<br />Assumption: Deliveries are associated with orders. The key table in the center becomes a factKey table, which is a concatenation of orders and deliveries, but without any measures, or textual attributes (thus a very lean concatenated fact). Measures and textual attributes are left in the surrounding order and delivery facts, which allows for ultimate reportability. The factKey center table would contain the same joins to customer and calendar as described. Most importantly It would also contain primary keys to both orders and deliveries. Delivery records in this central table would also thus contain an orderID. Thus with this design you could show freight by shipper, which you cannot do in the proposed design. All related data mart fact tables could be concatenated in this design, and none need to have identical dimensionality. Once again, unlimited flexibility and reportability is achieved. Will this use more memory? Yes, but I have found that fast, and inflexible data models are much worse than flexible, expandable but slower data models. I have used this approach at several clients, always with success. When the client has another related fact to add to the model its simple to just concatenate it into the center factKey table, and with and old and new dimensions to join. When you look at this design it will always look like a star schema. I have had many tens of millions of rows, but not hundreds of millions. I would submit that a single Qlikview with hundreds of millions of rows is not a good design fro mthe start, and can be refactored to really what is needed.Unknownhttps://www.blogger.com/profile/09288886624085366777noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-55861173813135857072014-09-20T02:02:44.242+01:002014-09-20T02:02:44.242+01:00Memory wise synthetic key, in your case, would pro...Memory wise synthetic key, in your case, would probably consume less memory. <br />So, creating link table provide clear, owned, expandable and easy to maintain data model which could be a technical advantage over synthetic key version.Anonymoushttps://www.blogger.com/profile/12866793106240578003noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-38388489873335924522014-09-10T19:06:15.089+01:002014-09-10T19:06:15.089+01:00One related link worth highlighting:
http://commun...One related link worth highlighting:<br />http://community.qlik.com/message/432613#432613<br />Mumbaicha Rajahttps://www.blogger.com/profile/14301722726996980420noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-52146474781344785682014-09-10T18:53:10.066+01:002014-09-10T18:53:10.066+01:00Few reasons that come to my mind:
1. Key generated...Few reasons that come to my mind:<br />1. Key generated using AutoNumber will save some ram.<br />2. AutoNumber key search will be faster with compare to link table that has string based key<br />3. Last but not the least from technical maintenance point of view - Link table is auto generated by QlikView. I don't wish to rely on something that is auto generated like this. I remember reading that Loosely Coupled Tables behavior is changed multiple times over different versions of QlikView. I prefer to avoid landing in that situation…so prefer to use link table over synthetic key – that just makes technical maintenance easy even with QlikView upgrade.<br />Mumbaicha Rajahttps://www.blogger.com/profile/14301722726996980420noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-8363855317867428492014-08-27T14:37:13.952+01:002014-08-27T14:37:13.952+01:00Ups. I've read that Barry's article previo...Ups. I've read that Barry's article previously. But that behaviour is so counterintuitive for me that it immediately escape from my mind.<br />Thank you fo clarification Vadim Tsushkohttps://www.blogger.com/profile/00135078823892044116noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-4060752464070530042014-08-27T13:35:12.445+01:002014-08-27T13:35:12.445+01:00Hi Vadim,
If you load a table with Dustinct, it s...Hi Vadim,<br /><br />If you load a table with Dustinct, it stays Distinct.<br /><br />Have a search for Barry Harmsen's blog on the subject.<br /><br />Regards,<br /><br />Stephen<br />Stephen Redmondhttps://www.blogger.com/profile/10815476951939159307noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-91616724858547674342014-08-27T06:32:33.573+01:002014-08-27T06:32:33.573+01:00Hi Stephen. Thanks for great post.
I have one rese...Hi Stephen. Thanks for great post.<br />I have one reservation though. <br />Given the fact we try to avoid duplicate key values in Key table it looks as code at step 2 of your sample would not achieve that goal.<br />Key table would have duplicate rows for all CustomerID, DateID pairs which have both Orders and Deliveries in underliyng fact tables.<br /><br />So another DISTINCT load from that Key table is necessary to get rid of that sort of duplicates. Vadim Tsushkohttps://www.blogger.com/profile/00135078823892044116noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-23958619466670741672014-08-26T15:34:22.852+01:002014-08-26T15:34:22.852+01:00I can't think of a single "technical"...I can't think of a single "technical" reason the synthetic key solution is less desirable. <br />If the data model is correct, and QV generates a synthetic key, then it's is created the correct synthetic key, it's no different to making your own concatenated key. Behind the scenes QV will store the synthetic key is the same manner as it'd store the user created concatenated key. So technically the 2 models are tautologous. <br />Now, there may be some other benefits of using the the LinkID user created concatenated key. By using autonumber you may save a minuscule amount of RAM. You could reference the link table within the app if you'd ever want/need to for some more obscure reason. <br />But, in my mind, at the technical base level, there is no reason to have to replace the synthetic key in this case. Synthetic keys are no "bad" per se, they're often indications there are underlying problems with the data model though. However in this case it all looks good, so why re-invent the wheel when QV's done all the legwork for you already. twikihttps://www.blogger.com/profile/17321852106033612638noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-14159637937896588432014-08-26T14:05:52.259+01:002014-08-26T14:05:52.259+01:00A Table Box is a far superior way to "browse&...A Table Box is a far superior way to "browse" data than the preview option. It is also excellent at revealing issues with data - although I would prefer to have those resolved a long time before I start building a key table!Stephen Redmondhttps://www.blogger.com/profile/10815476951939159307noreply@blogger.comtag:blogger.com,1999:blog-2268159193748171866.post-5627267121382637762014-08-26T13:14:14.201+01:002014-08-26T13:14:14.201+01:00Hi Stephen,
a couple of reasons why I prefer link...Hi Stephen,<br /><br />a couple of reasons why I prefer link tables to synthetic keys.<br /><br />Firstly when previewing data in the table viewer I find it easier to look through the data if I don't have a synthetic key, as browsing the synthetic key table removes the context around the data.<br /><br />Secondly if there are issues in the data load, for example an issue with date interpretation, if I am looking at data using a link table then it can be easier to spot which table is the source of the issue.<br /><br />Marcus MalinowAnonymoushttps://www.blogger.com/profile/09637872153107753966noreply@blogger.com