What is the best data model

[#1]

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