Hey All -
I'm Russell Christopher, a long time BI practitioner. While trapped by snow I decided to give myself a holiday treat -- Learning Qlikview! I'm having a blast and have tons of questions. I don't have internet where I'm staying right now, so I (literally!) hiked down a mountain, got in my car and drove into town so I could post. I'll be back in a couple of days to see what sort of responses I get!
Best Learning Resources?
Right now I have the QlickView Reference pdf that came with 10, a 9.0 Tutorial document, and the examples that came with 10. Are there other (free and deep) resources out there I can grab?
I'm a Microsoft guy by trade, and am therefore using the Contoso Retail DW as a sample data source. The DW is basically a snowflake schema in which some of the dimensions share lookup tables. Qlikview did not like those shared lookup tables as they created "loops". I ended up having tons of loose tables and my lists, tableboxes, and charts pretty much stopped working.
After reading up on the loose table statement I was able to selectively "target" the problem tables and keep the rest of my schema from getting too screwy.
Question 1: I understand why QV does what it does in this regard, but it is kind of a pain. Is there a best practice to work around this behavior? For example, I might have two dimensions (Customer and Sales Territory) which point to the same fact table and both have a foreign key pointing at a "lookup" table called Geography - LOOP! Is the best practice to simply denormalize things during the data load and actually APPEND the Geography field coming out of the Geography table to both Customer and Sales Territory? That way I can get rid of the (problem) Geography table altogether and simplify things for my user? If there a QV function to help me do this? If not, what is the best approach?
Question 2: As I experimented with "tuning" loose tables, I noticed an odd behavior. Was I doing something wrong, or am I just dealing with the sort of "odd behavior" that all software exhibits sometime?:
1. Load only tables which won't cause loops. (A fact table and a couple dimensions)
2. Launch Table Viewer. As expected, no "dotted" lines (representing loose relationships, I guess). I see solid "relationship lines" (what do you call these in QV-speak?) between all tables.
3. Modify script to add a table which WILL cause looping, but do NOT use LOOSE TABLE statement - just let QV figure things out on its own. Reload data.
4. QV complains about loops, as expected.
5. Launch Table Viewer - TONS of dotted lines, often between a fact table and a dimension (like TIME) which has NO relationship whatsoever to the new "problem" table which caused the loop. QV seems to have "tapped" multiple, unrelated tables as loose, instead of just the "problem" table and tables related to it. Why?
6. Edit script, tag the new "problem" table with LOOSE TABLE. Reload data.
7. In the Table Viewer, none of the "incorrectly loose" tables from step 5 have been restored to "non-loose" status - they still have dotted lines. Weird.
8. Edit script, commenting out the section where I load the "problem table". Reload data so that things like they did in steps 1-2 - No dotted lines.
9. Un-comment "problem table" script section, but make sure that I'm tagging the "problem table" as LOOSE TABLE. Reload data.
Result: Only "problem table" is tagged as loose in Table Viewer. Phew!
It seems that once QV has decided (correctly or not) a table should be loose, it doesn't change its mind, even after the developer specifies which table(s) should be loose in the script. One must "manually" do something to get them into a "non-loose" state ala step 8. Is this normal?
I noticed that QV created a synthetic field on my fact table. In this case, the field was made up of the table's primary key, and two measures in the table. How does QV determine when it should create these? It appears to have something to do with the cardinality of the values involved, but I'm not quite clear on things. Why one synthetic field which contains the PK and two measures instead of two synthetic fields, each with the PK and a single measure?
I assume the synthetic field is a performance enhancer? Why?