Sql-server – Drillthrough action on semi-additive measure group

sql serverssasssdt

I have created a semi-additive (Last Non-empty) measure in a cube that I am developing. For that measure group I have created a drillthrough action. When I use that drillthrough action it is returning not just one row for each item as I would expect, but one row for each item for each date, as though it is not recognizing that the measure is semi-additive.

The same issue is mentioned in a couple of Microsoft Connect articles from 2009 here and here, but there is no comment on how to resolve the issue, and I've found very little else on this problem.

I'm using SQL Server 2012 Enterprise.

Is there a solution or a work-around for this problem?

Best Answer

Assuming it is SSAS Multidimensional you can deploy ASSP and build a rowset action which returns the right rows. The rowset action expression would be something like:

ASSP.GetDefaultDrillthroughMDX(Tail(NonEmpty(Existing [Date].[Date].[Date].Members), 1).Item(0))

To explain that code, we get the existing dates (the dates in context of the current cell) then we filter down to only dates with a non empty value for the current measure (inheriting all the other filters on the current cell) then find the most recent date.

More info on this stored procedure can be found here: http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

Note a rowset action can't be the double click drillthrough action in Excel unfortunately. You will have to expose your action when users right click on the cell and choose Additional Actions.

Excel has a double click behavior. Double click performs a drillthrough returning either the default columns or returning the columns you define in the drillthrough action you define for that measure group as default. If I execute a DRILLTHROUGH SELECT statement manually without specifying the columns I want then I will get back the columns defined in the drillthrough action flagged as the default

Related Solutions

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.

Sql-server – SSAS cube fact and dimension relationship

What you are seeing is a typical example of a missing dimension relationship.

Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships.

If you add a dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation isn't present a dimension relation has to be created manually.

So in your example you need to go to the cube where the measure group is residing and select dimension relationships and define the relation between your dimension key and the fact table.

See defining dimension relationships for details on how to do exactly that.

It's actually fairly obvious why SSAS would behave like that. If there is no relation between axis and figures, there is no way a slicer can have impact on the figures.

If you are using a Tabular model, unless it lets you add relations in both directions I fear you'll be out of luck and need to design your data warehouse better using actual fact tables and dimension tables.

Best Answer

Related Solutions

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

Sql-server – SSAS cube fact and dimension relationship

Related Question