Just downloaded a copy of QlickView and I attempting to put a POC together.
I am using my datawarehouse (star schema) as a source.
Most of my fact tables have multiple date keys e.g. order date, requested date, ship date etc.
I am not sure how / if this can work with QlickView?
I need these date keys to be related to my date dimension, if i rename all these columns in Load statement of the script to point to the Date dimension key - the load fails. If I take it down to one date dimension in the fact load, then it succeeds.
I don't entirely follow, but I believe there are two basic options when you have multiple dates like this.
First option: You just load them all in with their separate names. You'll typically then want a calendar associated with each so that you can select "Order Year" and/or "Ship Week", that sort of thing. Each date gets its own calendar. The advantage is that it is then simple to look for something with an "Order Month" of January 2010 and a "Ship Month" of March 2010. The disadvantage is that you can't just select June 2010, for instance, and see everything ordered, requested OR shipped in June 2010. You also end up with a ton a fields. It also creates a whole lot of fields.
Second Option: Use a "crosstable load" instead of a regular load to turn your date columns into date rows. You then just have one date field, and each is distinguished by, say, a "Date Type" value of 'Order', 'Requested' and 'Ship'. The advantage is that now you can select June 2010, and by default, you'll see everything ordered, requested OR shipped in June 2010. It only requires a single date field, a single calendar. You can still select a specific "Date Type" if you want to look at only material that was shipped in June 2010, regardless of when it was ordered. The disadvantage is that you can't easily look for something with an order month of January 2010 and a ship month of March 2010. I've worked around this in one application, but it was rather ugly, and I frankly doubt the users ever use it as a result.
Imagining your datawarehouse you would have to create a date table with different column names (e.g. Order Month, Requested Month, Ship Month, etc.) for each date column that you have since your datawarehouse probably shows you the whole sales cycle for each row.
If you had a transactional database as your data source, you could combine the date column of various transaction types like orders, deliveries or invoices.
Karl Pover wrote: Imagining your datawarehouse you would have to create a date table with different column names (e.g. Order Month, Requested Month, Ship Month, etc.) for each date column that you have since your datawarehouse probably shows you the whole sales cycle for each row. If you had a transactional database as your data source, you could combine the date column of various transaction types like orders, deliveries or invoices.
I think we're thinking basically the same thing, which I figure is a good sign. The first option I mentioned is the same as keeping the whole sales cycle on a single row. The second option I mentioned involves converting your sales cycle row into a transactional "database" as you load it into QlikView, with multiple rows for each sale.
Yep, that looks like a correct implementation of Option 1 to me. The OrderDate and ShipDate tables are indeed what I meant about having separate calendars for each date field. They're tied to the correct field in your main table by the date key for each, and that appears to be the only connection from your calendars to the main table. It's fine that the date key isn't actually a date. All that matters is having an equivalent connection between the calendars and your main table. Looks right.
It does look like you're doing the same SQL twice. You could save yourself a bit of database processing if you loaded the second table directly from the first, LOAD OrderDayOfMonth as ShipDayOfMonth... RESIDENT OrderDate. To save even more database processing, you could create a calendar QVD. Have a separate application that maybe once per day loads the data using SQL to a single master calendar, then stores that as a QVD. Then when you're loading your specific calendars, load from the master calendar and rename as you go. But all that is about performance and database load tuning, not about just the basics of getting the data model right. I think you got the basics of the data model right (or at least "option 1" right - I just don't know which option is better for you).
I'll give John the last word since they're his options, but it looks good. You might look into the Resident command so you don't have to call SQL Select for each Date table.
My only additional comment is that Option 1 is easier on the script side, but it is harder to develop the interface because the user might not want to see more than 1 date filter so you have to depend on creating actions and triggers in order to select one date filter automatically when the other is selected. So, keep in mind Option 2 which will make the interface easier to develop.
How would I implement option 2? I have agreement date and settlement date in one table, and I have a company wide TimeDimension QVD. I want to select one fiscal year / fiscal qtr and see the data that has either that agreement date or that settlement date.