I am new to SSAS and I'm having some trouble with a "count of rows" measure.
I'm doing this all with named queries from a MySQL database and have created two logical tables in my DSV. The first table is called "Person" and has a primary key of "Person GUID". The second is called "Role" and has a primary key of "Role GUID" and a foreign key of "Person GUID". This is acting as a dimension table and also has an attribute of "Role Name".
What I want to do is be able to select a role from my dimension table and have this show me the number of people in that role, using a "count of records" measure from the Person table. The problem is, people can hold multiple roles, and the way it is structured in my "Role" table is that there is a separate row for each role that a person might have...in other words, "Person GUID", which is how it is mapped to the measure group, could be duplicated many times.
This is not working in SSAS - It doesn't seem to be giving me an accurate count. It appears to be only considering the role of the first instance of a particular Person GUID.
I know that I must be looking at this the wrong way...any help that anyone could offer would be much appreciated. I understand that I could just do a count of rows on the "Roles" table and then be done with it but because I have other dimensions that I want to correlate with it that are also mapped to the "Person" table, this isn't an acceptable solution for me. (These other dimensions have "Person GUID" as the primary key and thus don't have the same problem)
Sounds like you need to model a many to many dimension relationship between the person fact table and role dimension.
Your current role dimension sounds like it needs to be split out into a new bridge table mapping persons to roles. The other table is a simplified role table joining to the mapping table.