SSAS / MDX Query – Need row total

I've often found errors reported from SSAS indicating a duplicate key value in the dimension. When I've reviewed the source data, the duplicates aren't found. While I can't shed light on why this error is popping up in the dimension, I can offer a solution that bypasses the error and allows the cube to finish deploying and processing.

If you open the dimension object and look at the properties, you'll see a section for ErrorConfiguration. If you change this to "custom", you can tell SSAS what to do when it encounters errors in processing the object. If you want to log the error and allow the processing to continue, use "ReportAndContinue" as the value for properties like KeyDuplicate, KeyNotFound, NullKeyConvertedToUnknown, NullKeyNotAllowed - whatever is appropriate for your design. I set the KeyErrorLimit to 1 and set the KeyErrorLimitAction to StopLogging so your log files don't get overloaded with the same or similar errors.

As I said, this doesn't SOLVE the issue, but it does allow the cube to continue processing and sometimes, I've found, that's enough.

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

• You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.

• You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists. You can use a set definition to specify any date range you want. Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows. I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.