creating a query item as constant and not aggregatable

[ratnalein]

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

[adik]

place a determinant on district level

[ratnalein]

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

[adik]

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

[ratnalein]

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

[adik]

as far as i know that's how it should behave. test and let me know :)

[ratnalein]

okay, I will.

[ratnalein]

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

[adik]

ZIP-Code-ID should be key, the others will be attribues

[ratnalein]

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