Let’s assume the application that saved the sale accepts a comment from the user (free-form and nothing we want to aggregate by). I would like to save this in the data warehouse so we can use Excel to show the comment for each sale.

Here is an example of how I would like to see the data in Excel. If this isn’t possible without a dimension then what about “right click” show details? What are the best options to model this?

January TestUser1 $100.00 *Drill down to comments*TestUser2 $200.00TestUser3 $300.00

If you need to access granular data that is mostly likely at the same level as your fact, there is no simple way to do it with SSAS.

I've actually had to do something similar to this and the method I used was to add a custom action that would run a SQL query against the SQL DB to return the rowset based on the dimension attributes.

If this is a must and you have the time to spend on developing it, you need to install ASStoredProcedures which will allow you to execute stored procedures.

Have the detail data stored on your fact table.

Create a stored procedure on your SQL database with the data that accepts dimension attributes (Customer.Name, Date.Month, Date.Date). Based on these attributed, the stored proc should filter out the Fact data and return the limited data set.