Hierarchy and snowflake scheme

[alexandernatale]

Good morning everyone,

I cannot understand how to correctly model a situation represented in the following figure.

The need is to create a hierarchy that allows me to calculate the "facts" at an organizational level (in this case geographic but it could also be at an organizational level such as department, sub-department, sub-sub-department, product line, sub- product line).
Furthermore, for each "level", I need to define a series of indicators (benchmark type).

I am evaluating 2 hypotheses:

HP 1:

Within each "level" table, I create columns that identify the single indicator with a "flag" type field: 1 if the indicator must be calculated, 0 if it must not be calculated. So I will have n columns (one for each indicator) and the flag will tell me if for a given city, the indicator must be calculated or not.
My idea is to insert the indicator flag (indicator1 ... indicatorn) directly inside the modifiers of the set analysis.

For instance:

= sum ({<indicator1 = {1}>} Sales) / count ({<indicator1 = {1}>} quantity)

HP 2:

I create separate tables, one for each "level" to which the indicator must be associated. In this case, to avoid circular references, I will have to create an additional StateCode1 field (equal to StateCode) within the tables indicating the geographical hierarchy that will bind with StateCode1 of the Indicators_State table.

Which model would you choose in this case?

Furthermore:
researching on the web I found the "hierarchy" / "hierarchyBelongTo" function but I don't know if it's actually right for me and I don't know how to solve the indicator problem...

Thanks for your help!

[vinieme12]

Consolidate all geography dimensions into one table

Location: 

Load * from locationSource;

Left join(Location)

Load * from CitySource;

Left join(Location)

Load * from StateSource;

Left join(Location)

Load * from RegionSource;

[alexandernatale]

thanks for reply @vinieme12 

for the indicators, do you advise me to create a separate table or integrate the information as in hypothesis 1?

[vinieme12]

what exactly are those indicators ? how are they related to location?

can you post some sample data ?

[alexandernatale]

Gli indicatori sono solo valori di riferimento calcolabili per tutti o solo per alcune località.

La mia idea era quella di creare una "bandiera" a livello di indicatore per definire con 1 quando l'indicatore dovrebbe essere calcolato per una determinata posizione o 0 se non dovrebbe essere calcolato.

All'interno dell'analisi impostata poi andrò ad inserire il flag.

Esempio:

StateCode	IndicatoreStato1	IndicatoreStato2
1A1	1	1
1A2	1	0
2A1	1	1

In questo modo con un'analisi impostata di questo tipo:

= somma ({<StatoIndicatore2 = {1}>} Valore)/somma ({<StatoIndicatore2 = {1}>} qta)

Posso calcolare l'indicatore 1 solo per determinati stati.