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.
Thanks for any help!
