Allow Compose to add a relationship with a different column(s) other than the PK
In Compose when we add a relationship you can ONLY use the PK of the other table to do the lookup.
But there are several occasions where the lookup needs to be accomplished with another column, or columns, which are also unique. But we dont have any direct way to use that other than using a "lookup" functionality which doesnt perform well.
So adding something like an alternate unique key or something along those lines would help.
Hi @Marcoimp - this is related to building out a data warehouse using Qlik Data Integration's data warehouse automation features (Qlik Compose for Data Warehouse) - not the association engine / features in QlikSense/QlikView...
Regardless its common for data to be linked/ joined via attributes that are not primary keys - but unique keys and this scenario is often found in source relational databases.
As Tim mentioned. This has nothing to do with a BI app. As the original post mentioned, this is for Compose for Datawarehouses, which is Qliks app for datawarehouse automation.
Which currently does not support using anything other than the PK to create the join in the mapping load of a relationship between 2 business entities in the Data Vault created and maintained by Qlik Compose for Datawarehouses. And we need another way of doing this.
Running into very similar issue with POC prospect, all PK's defined in the source DDL are based on generated UID's. Additional UNIQUE constraints are defined on natural keys used in generating the UID's.
Subsequent FK DEFINITIONS ARE defined on the UNIQUE (natural key) columns which prevents from creating Relationship in C4DW. In this particular case the FK's using Natural Key only extends to a few tables and the UNIQUE constraint enables us to use that Natural Key as the primary key for this POC.
Had there been Fks defined using both UID and Natural key(unique constraint) the workaround would have been more 'detailed'.
Thank you for your suggestion. Let me see if I got this right (correct me if I'm wrong please):
You are suggesting a new relationship type which will be part of the model. Instead of using the regular PK target, it will use a different set of fields in the target which are also unique. For example, use "Customer SSN" instead of "Customer ID".
I assume the target unique key must be:
Type 1
Not updatable.
The different target key would result in different code for looking up the surrogate key in loading the DWH, but from that point on the relationship will work the same as regular relationships (for data marts).
Does the above correctly capture the suggestion? It makes sense to me, but I am wondering if we need to enforce the uniqueness and the non-updatability of this target. An interesting consideration here is also if the relationship target should be ad-hoc, or have these "alternate keys" as part of the target definition.
and now I got VF also needing this feature. Actually I keep having customers needing this, especially when the transactions they want to add to the Data Vault come from different systems. Like VF, which they have Online and Offline applications for sales.
