I want to love the Synthetic Key, but it doesn't love me?

[derekjones]

Hi All

I can see the benefit of Synthetic keys, however I'm struggling to make them work (on a performance/data volume perspective) in my application. I've figured out a workaround, but before I emabrk on restructuring my data to this model, I want to make sure I'm not missing a trick on how to correctly use Synthetic Keys.

My scenario is this..

Attached in caltest_1.qvw is a small example of my original database structure. Here I effectively used a synthetic key to link CustomerID and RepID in order to get a true representation of Customers and Reps (If I didn't use ID's and strored Customers and Reps in the Sales Table, I would lock my report down to only report Customers or Reps that have sold in the Sales table). So far, so good. However, I have the similar issue with a Targets file. In this first example I created my own TargetKEY based on RepID and Month. However, if you take a look at the report, for Month 3, it only is showing Janet in the "Sales vs Target" even though John has a target of £36 for Month = 3.

So, I decided to allow another synthetic key to allow qlikview to link RepID, MonthID itself. in caltest_2.qvw attached it shows you this format and all works perfectly, John now has his target in Sales vs Target and if you select Named = Y, since Janet is not named to Customer3 who sold in Month 3, her Sales goes to 0 which is perfect. So I then decided to apply this architecture to my real world app. It's done what has required, but since the $Syn 4 Table contains every combination of CustomerID, RepID, Month and ShippedDate, in my real data over 3 years, 20,000 customers, 1,000 reps etc, that's now a file with over 60 million records which has increased the app size from around 100MB to a massive 250MB! In the small example you can see on 3 customers, 2 reps and 6 dates this has already blown up to 450 rows!

My issue is that I don't think I need one synthetic table for all these 4 variants, creating millions of combinations. Can you split up the Synthetic Key table?

So in my final example (caltest_3.qvw), I've created my own key for Named accounts called NamedKEY. My issue here is although it has restricted Named, if you select Named = Y then the data has no reference and clears the Month 3 selection and brings back all the data which is not what I ideally want as I would like to show Month 3 and zero for both reps with their targets if Named is selected.

If anyone out there has any advice, I would be most grateful.

My thanks in advance

Derek

[derekjones]

OK, I've got a little further on with this, but I'm still stuck.

After further investigation, the data model in caltest_3.qvw does not solve all my problems. To help explain, I've now added another sales record for Janet on an account which she is named to. In the attached caltest_3a.qvw you can see if you select Named = Y, then although Janet now stays in the Sales vs Target, John has dissappeared even though he has a target of £36. Again, caltest_2.qvw does not inhertit this issue and still shows both Janet and John in the table when Named = Y is selected.

The issue with caltest_2.qvw is that $Syn 4 Table in my realworld app contains every combination of ShippedDate (1000 values) and CustomerID (20,000 values) which is causing it to become millions of rows. I've realised that ShippedDate is unnecessary for the keys I require as:

• Named Accounts is based on RepID and CustomerID
• Targets is based on RepID and Month

But, if I try and remove ShippedDate from the key (I create a MonthKEY in example caltest_2a.qvw attached) it causes a loop which I've been struggling to prevent without putting back ShippedDate into the key.

I'm really stuck now and would appreciate any advice on my data model, even if the answer is that it's impossible to do any other way, at least I will know to give up on trying to get around it.

My thanks in advance for any suggestions.

Derek