Calculated "virtual" rows
Posted by: Hauke P.
Date: July 11, 2012 04:03AM

I'm new to these forums, so please forgive me if I chose the wrong forum for this post.

I'm currently planning the implementation of some kind of BI system. While creating the DB design, I encountered a problem for which I hope to get hints to solve it on these forums.
The system I'm designing should store a set of business figures for a set of years and locations. My core problem is that some of the figures are not directly stored in the database but have to be calculated from other figures.

Please take a look the following example DB:

mysql> select * from dim_location;
+-------------+------------------+---------------------+
| location_id | location_name    | location_address    |
+-------------+------------------+---------------------+
|           1 | Office Munich    | NULL                |
|           2 | Office Stuttgart | Koenigstraße 1      |
|           3 | Office Hamburg   | Reeperbahn 3        |
|           4 | Office Berlin    | Unter den Linden 16 |
+-------------+------------------+---------------------+

mysql> select * from dim_year;
+---------+------------------------+
| year_id | year_comments          |
+---------+------------------------+
|    2009 | NULL                   |
|    2010 | the crisis hit us hard |
|    2011 | NULL                   |
|    2012 | getting better again   |
+---------+------------------------+

mysql> select * from dim_figure;
+---------------------------+----------------------------------------------------+--------------------------------------------------------+
| figure_id                 | figure_description                                 | figure_rule                                            |
+---------------------------+----------------------------------------------------+--------------------------------------------------------+
| admin_costs               | Administrative Costs                               | NULL                                                   |
| admin_costs_purchase_dist | Administrative Costs distribution key for Purchase | NULL                                                   |
| admin_costs_sales_dist    | Administrative Costs distribution key for Sales    | NULL                                                   |
| admin_costs_stock_dist    | Administrative Costs distribution key for Stock    | NULL                                                   |
| goods_costs               | Costs for buying goods                             | NULL                                                   |
| marketing_costs           | Costs for doing marketing                          | NULL                                                   |
| purchase_costs            | Total Purchase Costs                               | goods_costs + admin_costs_purchase_dist * admin_costs  |
| sales_costs               | Total Sales Costs                                  | marketing_costs + admin_costs_sales_dist * admin_costs |
| stock_costs               | Total Stock Costs                                  | warehouse_costs + admin_costs_stock_dist * admin_costs |
| total_costs               | Total Costs                                        | purchase_costs + stock_costs + sales_costs             |
| warehouse_costs           | Costs for maintaining the warehouse                | NULL                                                   |
+---------------------------+----------------------------------------------------+--------------------------------------------------------+

mysql> select * from fact_data;
+---------+-------------+-------------+-------+
| year_id | location_id | figure_id   | value |
+---------+-------------+-------------+-------+
|    2009 |           1 | admin_costs |   300 |
...

My current plan is to store all "basic" figures (i.e. figures that don't have a figure_rule) in the fact_data table. On the other hand, all calculated figures should be calculated in some kind of magic view... something like this:
SELECT year_id, location_id, figure_id, value FROM ###MAGIC### WHERE figure_id IN ("total_costs", "purchase_costs", "goods_costs") AND year_id = 2010;

My question boils down to: What do I put in ###MAGIC###? ;-)
And: If something like this is not possible (or not performant for ~200 figures and ~30 locations), what design alternatives would you suggest?

Just for clarification: I put the figure_rule in the dim_figure table only for demonstration purposes. Of course the figure_rule has to become a part in some query.
I already tried something like this:
(SELECT * FROM fact_data)
UNION
(SELECT a.year_id, a.location_id, "purchase_costs", a.value + b.value * c.value 
 FROM fact_data a
 LEFT JOIN fact_data b ON a.year_id = b.year_id AND a.location_id = b.location_id AND b.figure_id = "admin_costs_purchase_dist"
 LEFT JOIN fact_data c ON a.year_id = c.year_id AND a.location_id = c.location_id AND c.figure_id = "admin_costs"
 WHERE a.figure_id = "goods_costs");

However, this is basically the opposite of performant. ;)


Thank you for any replies in advance!

Cheers,
Hauke



Edited 1 time(s). Last edit at 07/11/2012 05:04AM by Hauke P..

Options: ReplyQuote


Subject
Written By
Posted
Calculated "virtual" rows
July 11, 2012 04:03AM
July 18, 2012 03:56AM
July 29, 2012 05:07AM
August 01, 2012 12:38PM
August 02, 2012 08:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.