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 :)
seems like simple model...where is the issue exactly?
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
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 :)
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
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.
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