circular references / synthetic keys problem

[#1]

Hi all,

I have created a qlikview application where I pull 5 sets of data for some KPI information. In Qlikview I have created each KPI as its own table, with a key field linking back to a location from all 5 tables. This is to allow me to select a location and view the KPI data only for that location. Pretty standard stuff and it works fine.

However, within each table there is a month and a year field. Now I also want to be able to choose a month and for the KPI's to filter to a month.

So in summary I want to make selections for both Location (from a master locations table) and Month (from a master calendar), and have this filter through to all the 5 sets of KPI data. Now when I try to do this in Qlikview I get circular references or synthetic keys depending on how I structure the tables in the script.

Here is what I want in a diagram format:

There are other tables that hang off these KPI's, as the user can drill down from the KPI's into more detail. Here is the full qlikview table structure as it currently stands with the synthetic keys:

Thanks
Dan

[vgutkovsky]

Dan,

One possible solution is to create an "island" (a table not connected to anything). Then qualify each of your tables (e.g. KPI1.MONTHYEAR). In the front end, you would present the user with the island fields for selections. Then, in chart objects, you would need to manually check each MONTHYEAR against the island value. This can get somewhat heavy, depending on how many rows you have.

Another solution would be to just join all the KPI tables into 1 table. The other field names would remain the same (e.g. Security.INCIDENTDATE) while all the MONTHYEAR and MASTERLOCATIONNIDs would be joined. This would definitely be less memory-heavy than the island solution.

Regards,

[johnw]

Well, circular references are bad, and maybe I'm not understanding what you need, but the QlikView table structure you posted looks reasonable to me. You basically have a master table (the synthetic key) of all possible locations and months. For each entry in this master table, you connect to all of your KPIs for that location and month. So select a location and month, and you should automatically pull in all of the relevant KPIs. Then you have a table with information about each location. Sounds good. And you have a master calendar, where the month matches up to the month in the master table. So if you choose a date within a month, you should pull up the KPIs for that month. If you choose a location name, you should pull up the KPIs for that location. It all LOOKS like it would be working OK.

What problem are you seeing? Or are you just figuring that since there are synthetic keys, the data model must be bad? Granted, probably most of the time when they appear, the data model IS bad, but if it is here, I'm not seeing it. Synthetic keys are not proof that the data model is bad, merely a hint that you should take a close look at what you're doing.

If you just want the synthetic keys to go away, you can create the master table yourself so that it's a real key instead of a synthetic key. I don't recommend bothering, as to the best of my knowledge the synthetic key will perform the same function, be just as quick in charts, and be quicker to load. But you could.

But like I said, maybe I'm just misunderstanding what you're after.

[#4]

I agree with John, I don't foresee any problems with synthetic keys in this case as they are acting just like a composite key, if you really wanted to get rid of them, you could de normalize the table structure in the load script stage by left joining your 5 kpi tables on to the master location table creating a flat table with all kpi's in it but i would invisage a much increased loading time.

Having said that, i belive Qlikview prefers dealing with denormalized data than normalized and is very efficient with the data once in memory.

[johnw]

SQL-Cowboy wrote:Having said that, i belive Qlikview prefers dealing with denormalized data than normalized and is very efficient with the data once in memory.

When I first started, I took QlikView as an oportunity to normalize a whole lot of our denormalized data, since the product was very clever about how it could manage all the relationships properly. I was impressed, and wanted to put it through its paces, I guess.

With a lot more experience, I consider this to have been a minor mistake. Yes, QlikView deals just great with highly-normalized data, it's very impressive in action, blah blah blah. But it appears to deal even better with denormalized data. So these days, my data models are more of a compromise, with a lot of denormalization and usually only a handful of tables, separated only as truly necessary, or sometimes just to keep a clear distinction between clearly-distinct objects.

So yes, it wouldn't surprise me if slamming all the KPI tables together took extra loading time, but ended up taking very little additional memory, and actually improved the performance of your charts. Might be worth a try if you have some time to spare on experimentation.

[#6]

All,

Thanks for your replies.

I did think that, because there were synthetic keys, that there was something wrong with the data. I didn't realise that synthetic keys work ok in these situations.

Many thanks