hesten wrote:- What do I do when I have more than one Fact table? Do I concatenate them?
Not necessarily. In my experience, star or snowflake schema is the generally preferred approach. That said, probably only half of my applications could actually be described as using star or snowflake schema. Different approaches work better for different things. I can't tell you how best to design your data for QlikView in general any better than I can tell you how best to design your data for a relational database in general. I will say that data normalization isn't really a concern due to the read-only nature of the data and the way that the data is compressed.
hesten wrote:- Do I always concatenate codes and descriptions?
Are you asking if you should concatenate codes and descriptions in a field's value like this, "John Smith (12345)"? If so, my answer is sometimes. In the case of names, for instance, they cannot be counted on to be unique. I then think it's important to concatenate the code to the name to enforce uniqueness and allow the user to distinguish between two different John Smiths. But in many cases, the descriptions are just as unique as the codes, and may be perfectly adequate without concatenating the code.
Or are you asking a data modeling question? I suppose that's much more likely given the subject. So, say, you have an invoice table with customer IDs but not names, and then a customers table with the customer ID and the name of the customer. Are you asking if you should denormalize the name of the customer onto the invoice table? If so, again, my answer is sometimes. If all I'm pulling from the customer table is the name, it seems wasteful to me to clutter the layout with an additional table for one extra field. So I would probably just put the customer name on the invoice table. As mentioned above, denormalization like this simply isn't an issue in QlikView. On the other hand, if I wanted several fields from the customer table, or if other tables references the customer ID as well, I would likely build a separate customer table.
hesten wrote:- What should I be weary of?
Wary of? Synthetic keys and loops. QlikView will not let you create any kind of loop in your data structure. And if two tables share two fields instead of just one field, QlikView will create a synthetic key that combines the two. Essentially, it's creating a new table for you. Sometimes this is exactly what you want. Synthetic keys aren't always bad. But if you see them appear when you didn't intend them, chances are that you're doing something wrong in your data model. Have a look at the fields involved, and see where you need to trim some things.
hesten wrote:- What will cause performance issues, not just in my script, but also stuff like charts?
Performance is a very big subject. Avoid FOR loops if you can. Avoid loading data you don't need. Pretty much never do a LOAD *, just like you should almost never do a SELECT * in SQL. If the same raw data is being used by multiple applications, stage it for the applications in a QVD. When loading from a QVD, the load will go MUCH faster if you restrict yourself to a single WHERE EXISTS(field) as your condition, and only load the fields provided without any manipulation. If you require additional conditions and field manipulation, it can often be faster to apply them with an INNER JOIN after the fact, though that takes a lot more memory than not loading the data in the first place, so there are trade offs. You can often move complicated expressions or parts of complicated expressions from your charts to your script. That way, they only happen once per load instead of every time someone clicks on anything and looks at the chart.
hesten wrote:- What is the best way to go about Security, especially multi-tiered?
No idea. I'm at best peripherally involved in security, such as defining to which users I want to distribute a report. So I'll leave this to someone else.
hesten wrote:- Why do I get 'object exceeded allocated memory'?
Too much data? Expressions too complicated? Satisfying a poor expression may require joining two huge tables together on the fly, which just isn't going to work. Probably lots of possible reasons.
hesten wrote:I need answers and tips on how to develop, script and deploy the optimum QlikView solution.
Tip: Always load your data at the lowest level of possible interest, and accumulate up from there. You might occasionally be driven to create some pre-summarized data for performance reasons, but it's really opening up a can of worms. I'd try almost anything else first.
hesten wrote:Please do not refer me to the Reference Manual, because I have looked in there for Data Modelling tips, but there aren't any.
I imagine that QlikView's official position is a chipper, "We can handle ANY data model! Do ANYTHING you want and it'll work because our product is so awesome!!!" So yeah, I wouldn't expect a lot of data modeling advice from them, though perhaps I'm wrong, and it's just not in the Reference Manual. So I guess my general advice would be to refresh your star and snowflake schema knowledge, and then to post to the forum for opinions on how best to model specific things and why.
