Modelling in Framework Manager

[ratnalein]

Hallo everyone,

I have a fact table:

Zip-Code                #persons with own house
---------------------------------------------------------
00000                      3429
00001                          98
00002                        111
00003                          29
...                                  ...
...                                  ...
99999                        434


I have only 2 dimension tables:

District                   ZIP-Code-Id
---------------------------------------
District_01             00001
District_01             00002
District_02             00003
...                                ...
District_231           99998                                 
District_231           99999


Region                 District_Id
-----------------------------------
Region_01           District_01
Region_01           District_02
Region_01           District_03
Region_02           District_04
...
Region_17           District_231


I would like to build a report "give me the amount of all of persons with own house from a certain district in relation with all the persons living in that district":

District           #persons_with_own_house             #population                difference
--------------------------------------------------------------------------------------------------------
District_01                   3527                                        6000                         2473


My question:
I have the information of population regarding each DISTRICT, but not of each ZIP-Code.  In order to be able to create the above report, I am thinking about expanding my district dimension table with the population information:
Goal --> report on DISTRICT level
Solution --> expanding the population information on REGION dimension table?

Please kindly advices for this.. Thank you..

Regards,

Ratna :)

[adik]

seems like simple model...where is the issue exactly?

[ratnalein]

Hallo adik,

I have the population information on from each DISTRICT.  I am confusing, where should I put this information attribute, on the dimension District or Region?
I have put the population information in the dimension table?

Since the population information is constant, I think I have to modell it in the Framework Manager as "not aggregatable".  My question:
how can I modell it in Framework Manager, that this attribute will be taken on the report(since I would like to have it on my report as comparison:

District           #persons_with_own_house             #population_of_the_District                difference
-----------------------------------------------------------------------------------------------------------------------------
District_01                   3527                                        6000                                                       2473

Thank you for your advice.

Regards,

Ratna

[adik]

where does #population_of_the_District  come from? a table in your database? if it is a different table that the one with #persons with own house fact, than you should use the two tables as two different fact tables, and use the district dimension table as a conformed dimension between the two fact tables (that is, after you import disctrict dimension table, create an alias of it to link into the #population_of_the_District fact table.
hope i understood what you ment and that you can understand what i ment :)

[ratnalein]

Hallo adik,

thanks for the quick reply.  The attribute "#population_of_District" is the attribute I would like to use to produce the comparison.  It is a constant(should be modelled as non aggregated in Framework Manager)   My report should kinda look like this:

District           #persons_with_own_house             #population_of_the_District                difference
----------------------------------------------------------------------------------------------------------------
District_01                   3527                                        6000                                               2473


Right now, I have this 1 fact table and 2 dimension tables:

--> Fact table(Zip table)
Zip-Code                #persons with own house
---------------------------------------------------------
00000                      3429
00001                          98
00002                        111
00003                          29
...                                ...
...                                ...
99999                        434

--> District table
ZIP-Code-Id     District
---------------------------
00001              District_01
00002              District_01
00003              District_02
...                    ...
99998              District_231                 
99999              District_231                 


--> Region table
District_Id                   Region
--------------------------------------
District_01                   Region_01
District_02                   Region_01
District_03                   Region_01
District_04                   Region_02
...                                ... 
District_231                 Region_17


So, in order to achive the wanted report, I am thinking about taking a small addition on the region dimension table, so that the tables look like this:

--> Fact table(Zip table)
Zip-Code                #persons with own house
---------------------------------------------------------
00000                      3429
00001                          98
00002                        111
00003                          29
...                                ...
...                                ...
99999                        434

--> District table
ZIP-Code-Id     District
---------------------------
00001              District_01
00002              District_01
00003              District_02
...                    ...
99998              District_231                 
99999              District_231                 


--> Region table
District_Id                   Region         #Population
------------------------------------------------------
District_01                   Region_01       30000
District_02                   Region_01       43847
District_03                   Region_01       34079
District_04                   Region_02           345
...                                ...                       ---
District_231                 Region_17         1140

My question:
Is this the correct plan?  After adjusting the region table as above, what points I should take attention by modelling in Framework Manager.  I am sure, that I have to modell the attribute #Population as "not aggregatable".   Moreover, since there is no population information on Zip table, how should I set the relationship scope?

Thank you.

Regards,

Ratna   

[adik]

hi,
that doesn't look right to me.
from your example you want to see the difference at district level so adding it as an attribute to the regions dimension won't make much sense. if you're keen on adding it as an attribute then add it to the district dimension.

[ratnalein]

Hallo adik,

I think you are right.  If I put the attribut to the DISTRICT dimensiontable, it matches to the "modell it as not aggregatable".

Thank you.

Regards,
Ratna