Can QlikView automatically *append* a batch of rows to a fact table?
SUMMARY "Traditional" OLAP only involves aggregating data from the fact table (using SQL 'SELECT' statements) - never appending data (using SQL 'INSERT' statements).
ABC (activity-based costing http://en.wikipedia.org/wiki/Activity-based_costing) adds a kind of "twist", where the programmer may need to define (and QlikView may need to automatically populate) an additional, *more-granular* fact table... which can then be aggregated / rolled up as usual.
QUESTION I'm curious if QlikView can be used to *populate* a fact table (like using SQL 'INSERT' statements), automatically appending a batch of records (which are the the result of a query).
This would be similar to the functionality of "MAKE TABLE" or "APPEND" type queries in Microsoft Access.
DETAILS For example, in ABC you might start with a fact table - say: [Costs].
This fact table might have 3 dimensions - say: [Departments], [Vendors], [AccountingCategory].
The goal in ABC is: to allocate Costs from certain Departments (where [DeptType] = "Consumer") to other Departments (of [DeptType] "Producer").
To do this, the programmer would define a totally new dimension (say, [Activities]) and a totally new, *more granular* fact table (same as the original one - but now with an additional column for [Activity], and a quantity for this activity).
Then you use a couple of auxiliary dimension tables to associate: [Departments] where [DeptType] = "Consumer" to [Activities] and [Departments] where [DeptType] = "Provider" to [Activities]
Now that you have all this, you do some joins - and you don't just look at (read) the recordset produced by the join - you also append it (write) to the new, more granular fact table involving Activities.
Finally, you can use the associations from ("Consumer") [Departments] to [Activities] and from [Activities] to ("Producer") [Departments] to roll up the new, more-granular fact table to solve the ABC problem: allocating each row in [Costs] from a (consumer) Department to a (producer) Department.
In order for this to work, the programmer needs to define - the new auxiliary dimension table(s) and - the new, more-granular fact table,
and QlikView would need to be able to automatically execute some joins and "SQL INSERT" statements to populate this new more-granular fact table.
I've already written a program that does ABC using MS-Access. I've just started evaluating QlikView, so I'm curious if anyone out there knows whether it could do this: automatically batch-appending to a fact table.
The short answer is "Yes". You can create new (Qlikview) tables in the script from existing tables by using LOAD ..RESIDENT statements. You can JOIN or CONCATENATE data to existing tables. Your scenario given above is quite feasible with QV.