topic Re: it my data model perfect ? in QlikView

it my data model perfect ?

paulyeo11 — Thu, 08 Sep 2016 23:27:06 GMT

Hi All

After few people input on my data model , today i have notice in order to remove $syn key , as i need to create :-

cust_id_ap

cust_id_aging

cust_id_sales

This make me headache on maintenance., So am i right that my data model is perfect now ?

Re: it my data model perfect ?

Fri, 09 Sep 2016 01:47:16 GMT

Perfect, no - Ideally you should create specific keys rather than let them be auto-generated, so then you know what the significance and relationship of the keys is.

However, Qlik is generally pretty good at creating synthetic keys accurately, so as long as you validate the outputs and are getting the values you expect, then there is no issue with the keys generated. However, each time you alter the model, the synthesis increases the chance of a hard to trace error slipping in.

Best practice, manually create the keys.

Re: it my data model perfect ?

paulyeo11 — Fri, 09 Sep 2016 03:49:23 GMT

Hi Lee

I see am I right to sale that for APKey which I created in AP Table and Sales Table , and for these 2 table I only create one cust_id right ? So the sync key will not appear ? It is correct ?

Paul Yeo

Director

www.tdstech.com<http://www.tdstech.com>

HP+6285883521036 Indo

Re: it my data model perfect ?

Fri, 09 Sep 2016 04:05:31 GMT

If you are happy with the overall schema, you could create the Key Table: i.e.,

LOAD

SOURCE & '_' &aging AS AP_TABLE_KEY,

SOURCE & '_'&AR_KEY&'_'&custid AS Q_DOD_KEY,

...

RESIDENT

[Fact Tables]

And then create the composite key in each of the tables that sources off it.

You'd also need to remove any fields that create the synthetic keys from the ancillary tables, and make sure that no UID's are lost. How you go about that would depend on how you are bringing in the data for those tables.

Re: it my data model perfect ?

paulyeo11 — Fri, 09 Sep 2016 05:27:17 GMT

Hi Lee

I think you are try to teach me how to create link table , so that data model will be look nice , right ?

! ! ! !

I try to load the script :-

SOURCE & '_' &aging AS AP_TABLE_KEY

it create a field for AP_TABLE_KEY , which i think is correct.

When i add script :-

SOURCE & '_'&ARKey& '_' &cust_id AS Q_DOD_KEY

I Get error msg on field not found on ARKey

My overall script as below :-

LOAD

SOURCE & '_' &aging AS AP_TABLE_KEY,

SOURCE & '_'&ARKey& '_' &cust_id AS Q_DOD_KEY // Get error msg on field not found on ARKey

RESIDENT

AP_TABLE;

Re: it my data model perfect ?

Anil_Babu_Samineni — Fri, 09 Sep 2016 05:38:54 GMT

Why not remove the Synthetic Keys? Synthetic keys are not bad for dashboards. But, See this as well

Should We Stop Worrying and Love the Synthetic Key?

Re: it my data model perfect ?

paulyeo11 — Fri, 09 Sep 2016 05:52:23 GMT

Hi Anil

Thank you for your sharing.

Yes , I am aware of benefit of the $sync key .

I also saw the link talk about sync key by the name of john . He no more active nowadays

For me now I just want to learn about create a link table. Just to make my data model look nice.

Paul Yeo

Director

www.tdstech.com<http://www.tdstech.com>

HP+6285883521036 Indo

Re: it my data model perfect ?

Anil_Babu_Samineni — Fri, 09 Sep 2016 06:12:46 GMT

Hope this help you. This is very simple and data model might be good. Try this way

Re: it my data model perfect ?

paulyeo11 — Fri, 09 Sep 2016 07:00:47 GMT

Hi Anil

Very informative , thank a lot.

Paul Yeo

Director

www.tdstech.com<http://www.tdstech.com>

HP+6285883521036 Indo