Here's the situation:
I have a large table consisting of transactional data (credit card transaction records), and I am creating a dashboard application to visualize the data.
For example, I want to display the count of transactions for each customer, or the breakdown of transaction types for a customer, etc.
Obviously, I can't query the table on the fly because it takes too long, and also I don't want to create lots of different aggregated tables manually (like customers_table, terminals_table, etc.) because then I have to implement the update method too.
I did some research and I think what I need is a cube, which has all the necessary columns as its dimensions (like customer_id, transaction_type, city, time, etc.), with pre-computed aggregations, so then I can query the cube and get whatever aggregated data that I need instantly (NOT ON THE FLY).
So I have two questions:
- First of all, am I right about how cubes work, or did I misunderstand something?
- Secondly, can I create this cube directly using PL/SQL, or should I use ODI or other tools?