Creating Dynamic KPIs in SSAS 2008R2 When there are different targets for different years and months RRS feed

  • Question

  • Hi All,

    Here I am working with SSAS 2008R2.

    I am trying to Create the KPIs For Citation Measure Group.

    For this I have - FactCitation(CitationCount....Etc), DimDate(Date, Time,Year,Month....,Heirarchy...Etc), Parameter(ParamID,DefaultValue.....Etc), DimPF(PDID,PFName....Etc)Tables and columns respectively.

    In Parameter Table I have Inserted 1 Record like ParamID=5709 and DefaultValue is 2.(Actually I am going to use this deafaultValue as my Target(Goal) in my KPI.

    And parameter which Means, when I try to see from front end KPI results it has to consider the Goal as 2 If we did not set any goal Mannually. Otherwise It has to take the goal as customer entered value. this is different scenario. you guys forget about it.. )

    And Now My requirement is: I have to create the KPI for below Scenarios.

    1. Annually Total Citations Vs Annually Total Target .(Year wise what is the Target Vs how many achieved).

    2. Total Citations Monthly Target.  (Monthwise Actual Count Vs Target Count)

    2. Total Citations for an Officer Monthly Target. (Officer - Month wise Actual count & Target Count)

    Any is there to guide me on this using MDX Query.

    Thanks in advance.



    Wednesday, September 9, 2015 11:07 AM



    According to your description, you want to create dynamic KPIs which has different target for each year. Right?

    In this scenario, since you have yearly and monthly target, I suggest you create new fact tables mapping Goals(Target) for different years/months. Then you can build the relationship with Date dimension table and create the KPI based on those fact target data. For more detail steps, please refer to a blog below:

    SSAS Dynamic KPI from a table


    Simon Hou
    TechNet Community Support

    Friday, September 11, 2015 8:32 AM

All replies