-
Incorrect Counting Total -SSAS
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|