Hello
I 'am struggling a little bit to define the appropriate data model.
Data comes from a dedicated system and can be easily structured in different maners. To keep it simple let say that
- I have a fact table aggreageting indicators related to customers
- On this fact table 1 hierarchy is linked : Customer (customer sub familly / customer familly)
- Incators are monthly but they can be aggregated by year
The goal is to produce basic charts listed below
Chart 1
| 2011 | 2010 | VAR€ | VAr % |
|---|
| Indicator 1 |
|
|
|
|
| Indicator 2 |
|
|
|
|
| Indicator 3 |
|
|
|
|
Char t2 : Gauge : value of indic1/value of indic2
Chart 3
| Fam Cli 1 | Fam cli 2 | Fam cli 3 |
|---|
| Indicator 1 |
|
|
|
| Indicator 2 |
|
|
|
I have worked in 2 directions but still facing issues regarding the strucuture of the fact table
Option 1 fact table = customer number, date, indicator code, value
creation an indicator table (indicatir code, name)
Option 2 fact table = Customer numbre, date Indicator 1, Inidicator 2, indicator 3
Option 2 is my favorite because it is much more simple to develop, you dont need to define set analysis to select the indicator you would like to show, just pick up the field. It is fine for Chart 2 and 3 but I can't manage to calculate variation between 2 years (chart 1), it's seem also that it is faster and use less memory
I am currently heading for option 2
Does anybody has a feeling
Lionel
