Using SSAS, how to design a contact info retrieval based on filters?

Question

I am new to SSAS trying and need help designing this reporting requirements:

• Filter for Customers based on dimensions (regular and fact) in various tables related by many-to-many relationships
• With this list of Customers, retrieve their contact details (address, email, etc)

My plan is to use SSAS (multidimensional) with either Excel 2007/SSRS reports for the user front end. My queries are:

1. Should the customer contact details be a dimension or fact? (One customer has only one contact detail and presently resides in the same table)
2. Once the customers have been filtered, how then to generate the customer’s contact details from the filter results?
3. Will my choice of tools work for my requirements?

Many thanks in advance.

Best regards,

CT

Answer 1

1. Contact details, such as address should be dimension attributes, not facts
2. If you have properties that you do not need to slice by, but you want to report on them, you can ustilies Member Properties as described Defining Member Properties for an Attribute in SSAS 2008 . For your solution, maybe a Drill Through action Defining and Using a Drillthrough Action gooing to a SSRS report would be more suitable
3. If you are going to tie your original statement up with your comments and utilise Total Sales, Purchased Products etc then the solution starts to make sense and should give you what you want. Without the metric part, SSAS is an overkill and straight table queries would suffice

From your description, you do not have a many-to-many relationship, however if you end up with them take a look at http://www.sqlbi.com/articles/many2many#2