locked
Year To Date Time Intelligence for Tabular Data Sources RRS feed

  • Question

  • Is it possible to apply the year to date Time Intelligence formula.
    {Year.FirstMonth : Month}
    to tabular data sources.

    I have a SQLTable Data source and when I apply the filter to a the utilizes the datasource I get and error in the KPI Value?

    Thanks,
    Wednesday, December 17, 2008 2:18 PM

Answers

  • I went ahead and created a quick sample table with just three columns to reference - DateKey, SalesActual, SalesBudget.  I setup the source against the table and redifined the data source View and Time appropriately.  I setup a filter list against a KPI that I created and verified what you are seeing.  You are absolutely correct, this does not work against the tabular.  This appears to be a bug and I would submit this on the Connect site for the Product Team to investigate and resolve or to provide solution/workaround.  Here is the link to the Connect site - https://connect.microsoft.com/default.aspx, but I am having issues with finding the area to report a bug for PerformancePoint (maybe someone from Microsoft could post the official link or provide instructions for doing this).

    I tried running a trace against the database with Profiler to see what T-SQL was actually being generated, but I am not having any luck with that.  I tried restarting IIS and SQL Server, but it seems to me that the data is still being cached and not sure right now what I need to do to get this cleared out.  I know in ProClarity there was an option to flush the cache, but I haven't had to do this with PPS yet...always a first for everything.

    I was able to setup a web page to provide a preview of what was being generated by the filter with this formula.  Once I got this setup to work using Nick Barclay's postings, Debugging Filter Links with Web Page Reports and Creating Dynamic Web Page Reports, I noticed that was you see in the filter preview is not what is being generated once deployed.  What you see with the filter preview is not what is being generated once deployed and here is the output of each:

    filter preview: [DateKey.Month].[2008-07],[DateKey.Month].[2008-08],[DateKey.Month].[2008-09],[DateKey.Month].[2008-10],[DateKey.Month].[2008-11],[DateKey.Month].[2008-12]

    dashboard filter: [DateKey.Month].[2008-07] ; [DateKey.Month].[2008-08] ; [DateKey.Month].[2008-09] ; [DateKey.Month].[2008-10] ; [DateKey.Month].[2008-11] ; [DateKey.Month].[2008-12]

    You can see that the main difference is the comma and the semicolon.  The filter does work for the basics like Month, Year, etc., but not with Year.FirstMonth:Month.  Now I am not 100% certain that this is for sure the issue with the semicolons, but it is a strong possibility.  I did set this back up with an SSAS data source and the dashboard filter information is basically the same, except that it works against an SSAS source...  This Time Intelligence filter formula appears to work differently with Tabular and SSAS data sources.

    SSAS dashboard filter:
     [Date].[Fiscal].[Month].&[2003]&[7] ; [Date].[Fiscal].[Month].&[2003]&[8] ; [Date].[Fiscal].[Month].&[2003]&[9] ; [Date].[Fiscal].[Month].&[2003]&[10] ; [Date].[Fiscal].[Month].&[2003]&[11] ; [Date].[Fiscal].[Month].&[2003]&[12]

    Dan English -- http://denglishbi.spaces.live.com
    • Marked as answer by AseemN Tuesday, January 13, 2009 8:08 PM
    Saturday, December 20, 2008 3:30 PM

All replies

  • Are you applying the filter to either the rows or the columns of the scorecard or analytic view?  Do you have time intelligence configured on your data source? 
    Wednesday, December 17, 2008 3:06 PM
  • The Rows
    Wednesday, December 17, 2008 9:44 PM
  • I am trying to apply them to the rows
    Wednesday, December 17, 2008 9:45 PM
  •   Just want to make sure that you have for sure configured the time for the data source you are working with - Configure time and aggregation settings.  Once you have this configured then you should be able to use the time intelligence forumlas for your filters - Create a filter by using a Time Intelligence template in Dashboard Designer.  Alyson has a great posting on the PerformancePoint Team Blog here that you can review for using filters with tabluar data sources for you to reference - Filters for Tabular Data Sources and Tabular Filters Explained.

    To make the time intelligence filters work though, you have to configure the time within the data source, otherwise you are going to have to use another filter type like the tabular values - Creating filters in Dashboard Designer.
    Thursday, December 18, 2008 1:31 PM
  • Hi Dan,

    Time and Aggregation are set up on all datasources. simple TI formulas like: Year; Year +1; Quarter; Month; etc.. work fine,

    when I add Year.FirstMonth : Month to the Filter and select it I get nothing returned, just empty cells.

    Thanks,
    Thursday, December 18, 2008 7:35 PM
  • can't test this right now on my own and not sure if this is the issue, but it appears that you have a space on each side of the colon.  i would remove that so it is just Year.FirstMonth:Month and try that.  if the other filters are working for you there is no reason this one shouldn't since it is clearly listed in the documentation - About syntax for Time Intelligence expressions.
    • Edited by Dan English Thursday, December 18, 2008 9:53 PM
    Thursday, December 18, 2008 9:29 PM
  • Hi Dan,

    I have Tried that and still there is no data returned. this is driving me slightly mental.

    Thanks,
    Thursday, December 18, 2008 9:33 PM
  • I had a chance to test this out with the MS BI VPC and I setup the Time with the AdventureWorks DW source and created a Time Intelligence filter.  Because of the data I had to make a slight adjustment to the formula to back date the information, but it does work and having spaces in the formula does not make a difference.

    I used the following formula: (Year-5).FirstMonth:Month-60

    and it produced this with the preview: [Date].[Fiscal].[Month].&[2003]&[7],[Date].[Fiscal].[Month].&[2003]&[8],[Date].[Fiscal].[Month].&[2003]&[9],[Date].[Fiscal].[Month].&[2003]&[10],[Date].[Fiscal].[Month].&[2003]&[11],[Date].[Fiscal].[Month].&[2003]&[12]

    I mapped the Time to the Date.Date.Fiscal and then followed the rest of the mappings assocaiting a date and then setting the Year, Semester, Quater, Month, and Day appropriately.

    I uploaded a screenshot of the configuration of the filter if you want to take a look at it in my SkyDrive account - PPS Time Intelligence Filter screenshot.

    I hope this helps you out.
    Friday, December 19, 2008 3:06 AM
  • I have no trouble using the YTD formula for Analysis Services Datasources,  I am trying to get this to work for a 'Tabular' Datasource, I have defined the Time for the datasource, yet it still does not work.

    Thanks,
    Friday, December 19, 2008 2:06 PM
  • Ok, I forgot that you were going against tabular.  I fired up my BI VPC again and created a new data source to the DimTime table in the AdventureWorksDW database.  I setup the Time and the View associating the FullDateAlternateKey as my TimeDimension and then created a filter based of this with the same formula as before without any issues.

    Formula I used for filter - (Year-5).FirstMonth:Month-60

    and it produced this with the preview: [FullDateAlternateKey.Month].[2003-07],[FullDateAlternateKey.Month].[2003-08],[FullDateAlternateKey.Month].[2003-09],[FullDateAlternateKey.Month].[2003-10],[FullDateAlternateKey.Month].[2003-11],[FullDateAlternateKey.Month].[2003-12]

    I uploaded a couple of screenshots if you want to view the Time configuration setup I did and the filter to my SkyDrive account - PPS Tabular Data Source Time Setup, PPS Tabular Data Source Time Setup2 (view), and PPS Time Intelligence Filter Tabular.

    Not sure why yours is not working if the other forumlas are working for you.  Might need to see or review some information on how your source is setup.


    Dan English -- http://denglishbi.spaces.live.com
    • Edited by Dan English Saturday, December 20, 2008 2:32 AM added additional screenshot link to view
    Saturday, December 20, 2008 2:29 AM
  • Hi Dan,

    In my environment the filter preivews just fine in the Filter Wizard, I get the same results you do, however when i use the filter in a dashboard, nothing is returned, just an empty Cell in the scorecard.

    As a clean test i created a simple table with a Datetime column and an amount column. I populated the table with a value for each month of the current year, then created a datasource setting all of the time values as you did, then created the filter, previewed perfectly. Linked the filter to the scorecard and previewed, just and empty cell when I try to use the Year.FirstMonth:Month TI formula. When I select simple TI Formulas like Year or Month, or Month-1, data is returned to the KPI as expected.

    Thanks for all of your help.
    Saturday, December 20, 2008 2:39 AM
  • I went ahead and created a quick sample table with just three columns to reference - DateKey, SalesActual, SalesBudget.  I setup the source against the table and redifined the data source View and Time appropriately.  I setup a filter list against a KPI that I created and verified what you are seeing.  You are absolutely correct, this does not work against the tabular.  This appears to be a bug and I would submit this on the Connect site for the Product Team to investigate and resolve or to provide solution/workaround.  Here is the link to the Connect site - https://connect.microsoft.com/default.aspx, but I am having issues with finding the area to report a bug for PerformancePoint (maybe someone from Microsoft could post the official link or provide instructions for doing this).

    I tried running a trace against the database with Profiler to see what T-SQL was actually being generated, but I am not having any luck with that.  I tried restarting IIS and SQL Server, but it seems to me that the data is still being cached and not sure right now what I need to do to get this cleared out.  I know in ProClarity there was an option to flush the cache, but I haven't had to do this with PPS yet...always a first for everything.

    I was able to setup a web page to provide a preview of what was being generated by the filter with this formula.  Once I got this setup to work using Nick Barclay's postings, Debugging Filter Links with Web Page Reports and Creating Dynamic Web Page Reports, I noticed that was you see in the filter preview is not what is being generated once deployed.  What you see with the filter preview is not what is being generated once deployed and here is the output of each:

    filter preview: [DateKey.Month].[2008-07],[DateKey.Month].[2008-08],[DateKey.Month].[2008-09],[DateKey.Month].[2008-10],[DateKey.Month].[2008-11],[DateKey.Month].[2008-12]

    dashboard filter: [DateKey.Month].[2008-07] ; [DateKey.Month].[2008-08] ; [DateKey.Month].[2008-09] ; [DateKey.Month].[2008-10] ; [DateKey.Month].[2008-11] ; [DateKey.Month].[2008-12]

    You can see that the main difference is the comma and the semicolon.  The filter does work for the basics like Month, Year, etc., but not with Year.FirstMonth:Month.  Now I am not 100% certain that this is for sure the issue with the semicolons, but it is a strong possibility.  I did set this back up with an SSAS data source and the dashboard filter information is basically the same, except that it works against an SSAS source...  This Time Intelligence filter formula appears to work differently with Tabular and SSAS data sources.

    SSAS dashboard filter:
     [Date].[Fiscal].[Month].&[2003]&[7] ; [Date].[Fiscal].[Month].&[2003]&[8] ; [Date].[Fiscal].[Month].&[2003]&[9] ; [Date].[Fiscal].[Month].&[2003]&[10] ; [Date].[Fiscal].[Month].&[2003]&[11] ; [Date].[Fiscal].[Month].&[2003]&[12]

    Dan English -- http://denglishbi.spaces.live.com
    • Marked as answer by AseemN Tuesday, January 13, 2009 8:08 PM
    Saturday, December 20, 2008 3:30 PM
  • By the way, I did install SP2 for PPS 2007 on the MS BI VPC that I have and this did not fix the issue either.  Just wanted to post that as a follow-up.
    Dan English -- http://denglishbi.spaces.live.com
    Saturday, December 20, 2008 3:41 PM