0

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

cte00
  • 33
  • 5
  • What made you put this into a cube? Usually cubes are for aggregation and analysis. This sound like you could just query a table and receive the same benefits. What kind of things would you filter/slice by? If that field will have lots of values, it can get annoying to filter in Excel with a really long list unless you are going to write some VBA and make your own dropdowns. – mmarie Dec 19 '13 at 16:18
  • The idea is to allow users an ability to filter based on various parameters (total sales by customer, age, product purchased, etc) and determine the number of customers with those parameters. Once the sub-set customers identified, generate the contact details for these customers – cte00 Dec 20 '13 at 07:23

1 Answers1

0
  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

Daryl Wenman-Bateson
  • 3,870
  • 1
  • 20
  • 37
  • Hi Daryl, thanks for your advice. I can understand drill-through in Excel whereby it produces an additional sheet. What do you mean by a drill-through action going to a SSRS report? – cte00 Dec 26 '13 at 06:39
  • Also, my research lead me to use Multidimensional instead of the Tabular version. My rational is I need the drill-through, and I have a number of many-to-many relationships to handle. Have I made the right choice with the Multidimensional model? – cte00 Dec 26 '13 at 06:55
  • You set up the drill through action as part of the multi dimensional cube (inside of SSAS). The link added should help explain how it works. Multi dimensional model does indeed handle many-to-many, however it is more complex than Tabular. As you are new to both environments, you may want to consider if you can make the tabular model work as the learning curve will be quicker. This may help you work around many-to-many in tabular http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering – Daryl Wenman-Bateson Jan 03 '14 at 12:41