none
KPI Scorecard acting strange RRS feed

  • Question

  • We have a situation where I am trying to do drill down KPI's withing dashboard designer.

    I have Average Attendance, then a school.  Both of these are objectives.  I put down my 5th grade KPI as a child to a school, the numbers are correct.  Same with 6th and 7th grade.  Everything is fine.  Then I put on 8th grade and I see #Error in all of the Actual column cells.  When I delete ANY of the other grades, I get correct numbers.

    This is really strange.  Now I remember reading something about SSAS and PPS and Kerberos and that Kerberos would interfere with large MDX queries. 

    Any suggestion would be most appreciated.
    Wednesday, May 13, 2009 8:02 PM

Answers

All replies

  • That issue would only apply if you were using Windows Server 2008 and the front-end server communicating to SSAS is running Windows Server 2008. 

    Windows Server 2008 Kerberos Bug – Transport Connection Issues with SSAS data and Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems.

    I have been told that this issue has been resolved in the latest releases of Windows 7 and Windows Server 2008 R2 release candidates.  Otherwise the hotfix will most likely not be available until July.  The knowledge bases number assigned to this is 968700, but nothing has been published on it yet.

    To troubleshoot your error I would review the Application eventlog on the PPS or SharePoint server where you are testing this to get the details of the error message being generated.  I would also run a Profiler trace against your SSAS instance to see what is being generated.

    The other thing would be to verify that you are on the latest version of PPS which would be SP2 3.0.4209.00.  There have been some hotfixes since then (PerformancePoint Server 2007 Hotfixes Available and Build List), but your scorecard should be working fine.  It is possible that you might be running into a Kerberos issue if you are using the PerUser connection setting and are using Windows Server 2008.  If you are not then provide some more details on how the KPIs are setup along with the scorecard.

    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Thursday, May 14, 2009 12:41 PM
  • I am using XP with sp3, IE 7.0.5730.11.  The PPS machine is a Win 2008 server.  The SQL Server 2008 machine that has the DB's is also a Win 2008 server.  PPS version is SP2 only.  We have not applied any of the hotfixes.  We have not run into any of the issues that these hotfixes address.  I do not know what you mean by "PerUser connection setting".  How do I check it?  What should I be using?
    From the event viewer on the PPS machine:

    Cannot connect to the "KCubes" database on the "MPLSHV03\STAGING" server.  Either the "" user does not have access to the database, or the database does not exist.

    AND right after that:

    An error occurred querying the DataSource named 'KCubes'.
    Server: MPLSHV03\STAGING
    Database: KCubes
    Cube: KDW DEV
    Exception:
    Microsoft.PerformancePoint.Scorecards.BpmException: You do not have permission to see this data.
       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQueryInternal(String query, Boolean isCellSet, Object accessMetadataCallback, Int32 sessionTimeout)
       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQuery(String query, Object accessMetadataCallback, Int32 sessionTimeout)
       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQuery(String query, Object accessMetadataCallback)
       at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQuery(KpiMeasureCollection kpiMeasures, MemberCollection pageFilters, TupleCollection memberSlices, IGlobalCache localCache)
       at Microsoft.PerformancePoint.Scorecards.DataSource.RunQuery(KpiMeasureCollection kpiMeasures, MemberCollection pageFilters, TupleCollection memberSlices, IGlobalCache localCache)
       at Microsoft.PerformancePoint.Scorecards.Server.DistributedQueryManager.DistributedQueryManager.QueryThreadProc(Object stateInfo)



    Now that does not make any sense to me.  Why would adding a forth KPI cause this error?

    The profiler shows nothing when I have 4 KPI's.  When I have 3 KPI's, this is the MDX query that is generated:

    WITH
     MEMBER [Measures].[f926ff66-c1ef-41db-98aa-59e2aa9f9e1f_baced82d-8150-4a3c-b68e-313667693270] as '(([Date].[Year].&[2009],[School Student].[Campus Name].&[KIPP AUSTIN COLLEGE PREP],[School Student].[Grade Level].&[Grade 6],[Measures].[AverageAttendance]))'
     MEMBER [Measures].[3da7643f-2eb3-4c0d-bf9b-9ad3aaa12750_ee4936d0-9055-43b0-8bbf-3386b766383e] as '(([Date].[Year].&[2009],[School Student].[Campus Name].&[KIPP AUSTIN COLLEGE PREP],[School Student].[Grade Level].&[Grade 8],[Measures].[AverageAttendance]))'
     MEMBER [Measures].[586906d8-b839-4448-99a1-c7c9868d231c_8eab1274-6814-4098-8b2f-cab1838d353a] as '(([Date].[Year].&[2009],[School Student].[Campus Name].&[KIPP AUSTIN COLLEGE PREP],[School Student].[Grade Level].&[Grade 5],[Measures].[AverageAttendance]))'
    SELECT { [Measures].[f926ff66-c1ef-41db-98aa-59e2aa9f9e1f_baced82d-8150-4a3c-b68e-313667693270],
    [Measures].[3da7643f-2eb3-4c0d-bf9b-9ad3aaa12750_ee4936d0-9055-43b0-8bbf-3386b766383e],
    [Measures].[586906d8-b839-4448-99a1-c7c9868d231c_8eab1274-6814-4098-8b2f-cab1838d353a]} ON AXIS(0)
    FROM [KAPSDW DEV]

    However, I do see a login event in the profiler for the service user does log in with a new connection id then when I run with 3 kpi's.  I do not see any error message in the profiler.  The duration of the connection is 22.

    I logged onto the SQL server machine as the service user and ran the MDX query above with no problems, then I added my forth element with no problems and the numbers returned are correct.  I did not see any errors in SQL profiler.

    Thank you for your assistance, Dan.  I have a lot of respect for you and your work.

    Bryan Shaw
    bryan.shaw@perficient.com



    Thursday, May 14, 2009 1:54 PM
  • I am still having difficulty with this.  I would appreciate some assistance, please.
    Monday, May 18, 2009 6:42 PM
  • Since you are using Windows Server 2008 on both servers and most likely are using Kerberos to enable the PerUser connection setup to pass the current user context to the SSAS 2008 Server then you are potentially running into the Kerberos bug with Windows Server 2008.

    Refer to the previous links that I posted in regards to this issue - Windows Server 2008 Kerberos Bug – Transport Connection Issues with SSAS data and Errors may occur after configuring Analysis Services to use Kerberos authentication on Advanced Encryption Standard Aware Operating Systems.


    You will need to look at switching one of your servers over to use Windows Server 2003 for the time being until the issue is resolved which might be in July.  Supposedly this has already been resolved in Windows 7 and Windows Server 2008 R2 which are both in release candidate now.


    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by BShaw Tuesday, June 9, 2009 7:52 PM
    Monday, May 18, 2009 6:49 PM
  • Has anyone seen the Hotfix for this issue?
    Tuesday, June 9, 2009 7:52 PM
  • FYI - Windows Server 2008 Kerberos Bug Patch – resolves SSAS connection issues
    Dan English's BI Blog
    _____________________________________________________
    Please mark posts as answer or helpful when they are.
    Thursday, July 30, 2009 5:02 PM
  • The same problem!

    >>Not working (install) on w2k8.

    Also.

    Why should I use Kerberos in my solution when there is Configure the Secure Store Service ?

    Why SSO doesn't work?

     


    Voyager_lan
    Friday, November 19, 2010 9:38 AM
  • It would be best if you did not add on to an existing thread that has been answered.  Appears that you are working with SharePoint 2010.  You can use the unattended or custom data setup to access your source, but if you want to use the PerUser setup then this will require Kerberos to pass the credentials to Analysis Services in a distributed environment (it will work without Kerberos if everything is running in a sandox setup though - single server).

    Plan for PerformancePoint Services security (SharePoint Server 2010) - http://technet.microsoft.com/en-us/library/ee748637.aspx

    Dan English's BI Blog
    Friday, November 19, 2010 12:50 PM
  • Hi, Dan.
    But I have distributed environment (DB Server + MOSS2010 Server) and  configured Secure Store Service & Unattended Service Accounts only.
    Сonfigure Kerberos in this case does not necessarily? If not, what else can I do for this issue in PerformancePoint Services?


    Voyager_lan
    Monday, November 22, 2010 1:44 PM
  • You really need to setup a new thread for this issue so it can be tracked accordingly.
    Dan English's BI Blog
    Wednesday, November 24, 2010 11:27 AM