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;Addresses; Countries and Contact Categories. The Contact Addresses and Addresses dimensions have got Regulars Relationships(from 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(from 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_number

FROM dbo.contacts AS c INNER JOIN

dbo.contact_addresses AS ca ON c.contact_number = ca.contact_number

AND c.address_number = ca.address_number

INNER JOIN

dbo.addresses AS a ON c.address_number = a.address_number

INNER JOIN

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

INNER JOIN

dbo.contact_categories AS cc ON cc.contact_number = c.contact_number



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_number, cc.activity

FROM dbo.contacts AS c INNER JOIN

dbo.contact_addresses AS ca ON c.contact_number = ca.contact_number

AND c.address_number = ca.address_number

INNER JOIN

dbo.addresses AS a ON c.address_number = a.address_number

INNER JOIN

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

INNER JOIN

dbo.contact_categories AS cc ON cc.contact_number = c.contact_number

WHERE (cc.activity = 'BCRS') and

(co.country_desc = '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_number, cc.activity...WHERE c.contact_number = '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.