Implementing table-driven KPIs

• Mike Austin-398977

  Say Hey Kid

  Points: 697

  More actions

  April 15, 2014 at 4:41 pm

  #306706

  My users want to be able to change KPIs for various metrics over time. Essentially, it's a type 2 SCD KPI "dimension" with the following structure:

  UseCase (links to another table with a MetricDimID)

  BeginDate

  EndDate

  KPI_Cutoff

  In SSAS, I'm creating a named query:

  SELECT DISTINCT d.DateDimID, m.MetricDimID, mk.KPI_Cutoff

  FROM EB_UnderWriter.MetricDim m

  JOIN Common.MetricKPI mk ON m.UseCase = mk.UseCase

  JOIN Common.DateDim d on d.DateValue BETWEEN mk.BeginDate AND mk.EndDate

  JOIN EB_UnderWriter.UWScorecardCubeSourceFact f ON d.DateDimID = f.CompletionDateDimID

  WHERE m.MetricCategory = 'UW Timeliness'

  This gives me the KPI goal in effect for every metric for every metric + date combination in the fact table

  I've added this to my DSV and tried linking to both my fact table and my dimension tables.

  I added this table as a measure group. I chose "no aggregations" because that's what I need.

  My problem is that the goals are showing up in the cube as all null and the status expression is all wrong.

  As a test, I added a named calculation to my fact table and plugged in a constant value. I added it to my measures. If I choose "no aggregations" everything is null; if I select distinct count incorrect results are returned.

  The problem seems to be that selecting "no aggregations" means everything is null, while selecting any aggregation at all leads to wildly incorrect results.

  Unfortunately, google does not return useful results on how to do this. Any help appreciated.

  Mike