actionssasmdxolapdrillthrough

Remove duplicates from OLAP Drill in SSAS

I am using Visual Studios BIDS to modify an existing OLAP cube.

In SSMS: There is an underlying fact table (FactTableMain) with a very fine grain that contains 10 different measures to track the status of an application (they act almost like a flag). The measures either have the individual's ID value or are NULL.

In SSAS Visual Studio OLAP:

  1. There are 10 measure groups. Each measure group is based on a DSV named query that selects 1 of the FactTableMain measures where MeasureName IS NOT NULL.
  2. A drill action for each measure group with only the PersonName and PersonID columns being returned.

The drills for each measure group:

  1. shows duplicates (as not all fact table columns are return columns for the drill)
  2. Do not return the expected number of rows that the measure count displays

I have tried:

  1. multiple MDX conditions using filter and distinct on the drill through action, but they either make no difference or the action disappears entirely
  2. Create a junk drill dimension that selects the distinct IDs from the FactTableMain and set that as the only return column for the drill through action (made no difference to drill through return rows)
  3. Creating New (Standard) Action as a rowset and dataset, using MDX action expressions

I think I need a New (Standard) Action with an MDX Action expression with these properties:

My current MDX query does return results, but only for the first measure's overall total and it is not formatted correctly at all. It does not work if I select a different measure in the client application, rerun the query, and drill again. I have searched and searched, but I am out of ideas and sitting in a black pit of doom. :(

My current MDX query is:

WITH 
  SET [person] AS 
    NonEmpty([person].[person].[person]) 
  MEMBER CurrentMeasure AS 
    [Measures].CurrentMember 
SELECT 
  NonEmpty
  (
    Filter
    (
      [Quarter].[Quarter].[Quarter].MEMBERS
     ,[Quarter].[Quarter].CurrentMember
    )
  ) ON COLUMNS
 ,(
    [person]
   ,NonEmpty([person].[person ID].[ID])
  ) ON ROWS
FROM [Applications];

Goal: I would ultimately like the drill action to be dynamic enough to know the current measure the user is selecting and filtered by the user's dimension selection for rows/columns.

Questions:

  1. Is there a way to filter distinct or non empty rows using a condition for the original drill through action? I know there are drill limitations, but is there something that would workaround the drill's limitations?
    1. How can I create a Standard Rowset action that is dynamically to the user's selections (my goal).
    2. Any ideas?

A URL action type is not an option for our business needs.

EDIT: I removed everything unnecessary from the DSV and am selecting only distinct rows. Each ID can have more than 1 application and an application can have more than 1 area of interest. Now the drills return 1 row per ID, application, and area of interest. We only want the drill to return the distinct IDs, no matter the number of applications or areas of interest. I am not sure where to go from here. Can I filter our the application number and/or areas of interest dimensions in the drill?

Solution

  • I believe that you are going too fast too quick. The DSV should show the data without duplication in the browser. If it's not, go back to the DSV and check what it is. Maybe create a view (an Indexed view) on top of the fact table, so you can make sure that you query only the data that you want. Also: are you sure that your dimensions are linked correctly? Sometimes duplication appears due to dimensions not being set up correctly with wrong keys for linkage.

    In MDX: If you create a Calculation in the Calculation tab you can do drill in it. Otherwise, you'll have to write the correct MDX query each and every time.

    HTH.