none
Creating a KPI based on the value of Children KPI's RRS feed

  • Question

  • Hi I am utilizing Tabular datasources and I would like to provide the user with a KPI for a Revenue per square foot. I am able to achieve this utilizing a ODBC Source and return the product of Blah / Blah. However I am unable to utilizer time intelligence on this type of KPI. Is it possible to create two KPI's: 'Total Revenue' and 'Square footage', then create a Parent 'KPI Revenue per sq foot' and divide the two child KPI's to get the value for this KPI?

    Thanks,
    Tuesday, December 23, 2008 3:04 PM

Answers

  • I have gone through some testing going against a non OLAP source and I don't see anyway to be able to perform a calculation based off two two different columns.  It doesn't appear that you are able to use the MDX tuple forumla at all against a non OLAP source to be able to conduct any types of calculations.  So as far as the statement only being able to return a single value that does appear to be valid meaning that you are only able to reference a single column and are unable to perform a calculation based off two columns.

    So for the time being it appears that you are out of luck.  For some alternatives you could possibly just create the scorecard in Excel or SSRS and pass the time filter to one of these types of report items.
    Dan English -- http://denglishbi.spaces.live.com
    • Marked as answer by AseemN Tuesday, January 13, 2009 6:58 PM
    Friday, December 26, 2008 1:02 PM

All replies

  • Slightly confused when you start off stating that you are utilizing a tabular data source and then utilizing a ODBC source.  What type of source is the data coming from?  If you are referencing a SQL Server table (or view) then simply use the Tabular data source.  You can configure Time Intelligence with tabular sources and use them as filters.  I would just create the Revenue per Sq Foot calculation in the view to reference.  Might need some more information or a few rows of data typed out to review to see how you are trying to use this exactly or how you have already tried to get this to work.
    Dan English -- http://denglishbi.spaces.live.com
    Wednesday, December 24, 2008 1:25 PM
  • Hi Dan,

    In order to achieve the desired KPI value I am having to utilize an ODBC Datasource - It is my understanding that the only thing that an ODBC Datasource can return is a Scalar Value, eg the return value  of:

    Select  100/1000.

    What I would like to do is create two KPI's based on tabular datasources in order to get the values used in the Calculation, then create a KPI and derive it's value from by dividing the Two KPI's based on the tabular datasources.

    Not sure if this is any clearer

    Wednesday, December 24, 2008 2:18 PM
  • I have used ODBC data source against Oracle database and it can return more than just a scalar values.

    If you are going against a tabular data source like a SQL Server table then I would just utilize a view in the source and perform your calculations or portions of them within the view.  You should be able to use the data source that is referencing the view for your KPIs and possibly your additional role up value referencing the derived column in the view.
    Dan English -- http://denglishbi.spaces.live.com
    Wednesday, December 24, 2008 8:28 PM
  • Hi Mark,

    Is this quote from Microsoft regarding ODBC Data sources incorrect then.

    'A connection to an ODBC data source can return only a single value. Typically, dashboard designers use an ODBC data source to acquire single values from an Excel 2007 spreadsheet.'

    source:
    http://office.microsoft.com/en-us/help/HA102408421033.aspx#3.


    In order to make time intelligence work the way I would like it to I need to aggregate the 'revenues' and the 'expenses' by time period and divide them to get the percentage relative to the time period selected by the users. I am not sure how I would accomplish this with a single view.

    again thanks for your help and have a Merry Christmas.

    Richard
    Thursday, December 25, 2008 3:35 PM
  • Haven't used ODBC source against an Excel source, so cannot comment on that.  As far as going against Oracle though I know the statement is not valid because I have returned more than a single value and used the ODBC connection to return a complete dropdown filter using the custom SQL View (ODBC) filter with the SDK.

    If you have the three columns in your source with the time (date), revenue, and sq footage you should be able to get what you are looking for.  I will take a look at setting up some data and testing this out later today or tomorrow and get back to you.  Setting up the Time Intelligence with the data source and creating a filter on the dashboard page should provide you the aggreations that you would want to analyze for different time periods.


    Dan English -- http://denglishbi.spaces.live.com
    Thursday, December 25, 2008 4:52 PM
  • I have gone through some testing going against a non OLAP source and I don't see anyway to be able to perform a calculation based off two two different columns.  It doesn't appear that you are able to use the MDX tuple forumla at all against a non OLAP source to be able to conduct any types of calculations.  So as far as the statement only being able to return a single value that does appear to be valid meaning that you are only able to reference a single column and are unable to perform a calculation based off two columns.

    So for the time being it appears that you are out of luck.  For some alternatives you could possibly just create the scorecard in Excel or SSRS and pass the time filter to one of these types of report items.
    Dan English -- http://denglishbi.spaces.live.com
    • Marked as answer by AseemN Tuesday, January 13, 2009 6:58 PM
    Friday, December 26, 2008 1:02 PM