Incorrect Counting Total -SSAS

[josur]

Hello,





Im a new user of SSAS and at the moment im trying to browse a cube to output the total of contacts within a criteria.

I have one measure(fact table) named Contacts Count and 4 Dimensions Contact Addresses;Addre sses; Countries and Contact Categories. The Contact Addresses and Addresses dimensions have got Regulars Relationships(f rom PK to FK) with the fact table, the Countries Dimension is using a Referenced relationship to the measure from the Addresses dimension and the Contact Categories use a regular relationship(fr om PK to FK).



When the cube is processed(after choosing "use custom error configuration" in the dimension key errors) the total of contacts is "8727" on the Drop Totals field is the same(Total of rows) as if we queried the following way:



"SELECT DISTINCT c.contact_numbe r

FROM dbo.contacts AS c INNER JOIN

dbo.contact_add resses AS ca ON c.contact_numbe r = ca.contact_numb er

AND c.address_numbe r = ca.address_numb er

INNER JOIN

dbo.addresses AS a ON c.address_numbe r = a.address_numbe r

INNER JOIN

dbo.countries AS co ON a.country = co.country

INNER JOIN

dbo.contact_cat egories AS cc ON cc.contact_numb er = c.contact_numbe r



In my case i need to dimension the data by Countries and filter by "Activity" that is an atriibutte of the Contact Categories dimension.



If for example we use the query:

"SELECT DISTINCT c.contact_numbe r, cc.activity

FROM dbo.contacts AS c INNER JOIN

dbo.contact_add resses AS ca ON c.contact_numbe r = ca.contact_numb er

AND c.address_numbe r = ca.address_numb er

INNER JOIN

dbo.addresses AS a ON c.address_numbe r = a.address_numbe r

INNER JOIN

dbo.countries AS co ON a.country = co.country

INNER JOIN

dbo.contact_cat egories AS cc ON cc.contact_numb er = c.contact_numbe r

WHERE (cc.activity = 'BCRS') and

(co.country_des c = 'United Kingdom')"



The total of rows is 4282 but on the browse cube when we filter by activity = "BCRS" the total for United Kingdom is only 219. And also if i browse with a new dimension( fact relationship) "contacts" and select for example the attribute contact_number = "9998" the total counts is 1 (instead of 4) if we query(SELECT c.contact_numbe r, cc.activity...W HERE c.contact_numbe r = '9998'" the output is 4 rows because this contact has got 4 different type of activities(BCRS , BCTYPE...).





Im not sure if i can use cubes to pull out this kind of information and i would like some help with the above case.



Thanks in advance.



Joao.