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..