Hallo everyone,
I have following fact table:
Zip-Code #persons with own house
---------------------------------------------------------
00000 3429
00001 98
00002 111
00003 29
... ...
... ...
99999 434
And I have following dimenstion table "district":
ZIP-Code-Id District
---------------------------
00001 District_01
00002 District_01
00003 District_02
... ...
99998 District_231
99999 District_231
I want to be able to produce a report like this:
District #persons_with_own_house #population_of_the_District difference
-----------------------------------------------------------------------------------------------------------------------------
District_01 209 50000 49891
I do have the population information to each district. In order to produce the report above, I must put the information regarding population as query item in the district dimension/query subject, so that district looks like this:
ZIP-Code-Id District Population
----------------------------------------------
00001 District_01 50000
00002 District_01 50000
00003 District_02 48760
... ...
99998 District_231 7600
99999 District_231 7600
My question:
In order to produce the above report, I should model "Population" as NOT AGGREGATABLE(some kind of constant), right? How can I achive this?
For example, for District 1, it should be aggregated as twice of 50000.
Thank you.
Regards,
Ratna
place a determinant on district level
Hallo adik,
thank you for the answer. The idea of adding a determinant in the dimension table district is not clear for me. Because, determinants are needed if the granularity in the fact table is "bigger" than the granularity in a dimensional table used. In my case, the granularity looks "normal":
In fact table --> the smalles granularity = ZIP-Code:
ZIP-Code Measure
------------------------------
xxx xxx
xxx xxx
xxx xxx
In dimension table district --> the corresponding attributes are:
ZIP-Code-ID District Population_of_District
------------------------------------------------------------
00001 District_01 50000
00002 District_01 50000
00003 District_02 48760
xxx xxx xxx
xxx xxx xxx
xxx xxx xxx
I need to modell the query item "Population_of_District" of the dimension District as not aggregatable if I author a report. In the report the population should be taken like it is stated in the dimension table(e.g. report regarding District_01 should show 50000 as population instead of 100000 -not aggregatable-):
District #persons_with_own_house #population_of_the_District difference
-----------------------------------------------------------------------------------------------------------------------------
District_01 209 50000 49891
I am a newbie in Framework Manager, perhaps you meant, there is an "equivalent" solution for this problem using determinant?
Thank you.
Regards,
Ratna
you have #population_of_the_District on the district level, but within a district you have multiple zip codes, so the #population_of_the_District fact is not at the lowes level of granularity which in your case is the zip code. at least that's what i understood from what you described. on the dimension table where you have districts and zips, create the determinants with zip being unique (if that indeed is the case) and district with group by
Hallo adik,
thanks for the quick reply. Yes, you understood it correctly.
By setting the query item ZIP-Code as "uniquely identified" and District as "Group By", will it produce definitely this report(querying the report from district level as well as from population level)?
District #persons_with_own_house #population_of_the_District
---------------------------------------------------------------------------------------------------
District_01 209 50000
I am sorry for annoying without trying first, I am getting my cognos license starting next month, so right now is just to understand the things theoretically.
Thank you.
Regards,
Ratna
as far as i know that's how it should behave. test and let me know :)
okay, I will.
Hallo adik,
one question:
for my dimension table:
ZIP-Code-ID District Population_of_District
------------------------------------------------------------
00001 District_01 50000
00002 District_01 50000
00003 District_02 48760
xxx xxx xxx
xxx xxx xxx
xxx xxx xxx
I guess, I have to choose ZIP-Code-ID as a determinant and the "Uniquely Identified" should be ticked. Below, there are 2 fields:
1. Key
2. Attributes
For ZIP-Code-ID, which query items should I put into the key field and attributes field?
Thank you.
Regards,
Ratna
ZIP-Code-ID should be key, the others will be attribues
Hallo adik,
quick reply, thanks... I assume, on the other hand:
Group by should be ticked for population:
1. population should be key
2. others should be attributes
Regards,
Ratna