Archive for the ‘Reports’ Category

Protected: Project PPM Reporting and BI

Tuesday, March 1st, 2016

This content is password protected. To view it please enter your password below:

SQL Query to Find Active Resources for Licensing

Monday, December 14th, 2015

SQL Query to Find Active Resources for Licensing

V: 1.2, MD: 01/24/2017

Following SQL query will show the list of users that are active, that connected to Project Web App (PWA) since specified date (Oct 1st, 2015 or the date you specify) and should be counted towards the licensing.

* Contact Softvative for accurate licensing assessment of your SharePoint or Project Server PPM farms. We have done Software Asset Management (SAM) projects for customers where we worked with the software vendors to save the licensing costs. We have skills in Microsoft MAP, System Center and other custom tools for the SAM.

Click here to download the SQL query file.

Replace the syntax with your Project Server database name.

 

 

PerformancePoint Unable to Create Report QueryProducedTooManyResults

Tuesday, April 29th, 2014
Issue Summary:
In SharePoint 2010, using PerformancePoint Dashboard Designer tool, you are trying to create report and it brings the following message:Unable to create report. Query produced too many results.
Contact the administrator for more details.

Event Viewer Errors on SharePoint App Server:

Error 1:
Log Name:      Application
Source:        Microsoft-SharePoint Products-PerformancePoint Service
Date:          2/4/2014 11:37:55 AM
Event ID:      1
Task Category: PerformancePoint Services
Level:         Error
Keywords:      
User:          DomainSPFarmAct
Computer:      SrvApp02.domain.com
Description:
An exception occurred while running a report.  The following details may help you to diagnose the problem:
Error Message: Error running data source query.
<br>
<br>
Contact the administrator for more details.
Dashboard Name: 
Dashboard Item name: 
Report Location: {6ed9a727-029b-1e22-d81a-f39274d4d9e2}
Request Duration: 300,564.30 ms
User: DomainBIDeveloper
Parameters: 
Exception Message: Error running data source query.
Inner Exception Message: 
Stack Trace:    at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation 
analyticReportViewLocation, BIDataContainer biDataContainer)
   at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.ExtractReportViewData()
   at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.CreateRenderedView(StringBuilder sd)
   at Microsoft.PerformancePoint.Scorecards.ServerRendering.NavigableControl.RenderControl(HtmlTextWriter writer)

PerformancePoint Services error code 20604.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-PerformancePoint Service” Guid=”{A7CD5295-CBBA-4DCA-8B67-D5BE061B6FAE}” />
    <EventID>1</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>1</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-04-18T16:37:55.171984000Z” />
    <EventRecordID>603586</EventRecordID>
    <Correlation ActivityID=”{43305F92-CD1F-4E75-B19B-2581FE7C04CD}” />
    <Execution ProcessID=”6420″ ThreadID=”6468″ />
    <Channel>Application</Channel>
    <Computer>SrvApp02.domain.com</Computer>
    <Security UserID=”S-1-5-41-1623458274-11437692956-399368559-1726″ />
  </System>
  <EventData>
    <Data Name=”string1″>An exception occurred while running a report.  The following details may help you to diagnose the problem:
Error Message: Error running data source query.
&lt;br&gt;
&lt;br&gt;
Contact the administrator for more details.
Dashboard Name: 
Dashboard Item name: 
Report Location: {6ed9a727-029b-1e22-d81a-f39274d4d9e2}
Request Duration: 300,564.30 ms
User: DomainBIDeveloper

Parameters: 
Exception Message: Error running data source query.
Inner Exception Message: 
Stack Trace:    at Microsoft.PerformancePoint.Scorecards.Server.PmServer.ExecuteAnalyticReportWithParameters(RepositoryLocation 

analyticReportViewLocation, BIDataContainer biDataContainer)
   at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.ExtractReportViewData()
   at Microsoft.PerformancePoint.Analytics.ServerRendering.OLAPBase.OlapViewBaseControl.CreateRenderedView(StringBuilder sd)
   at Microsoft.PerformancePoint.Scorecards.ServerRendering.NavigableControl.RenderControl(HtmlTextWriter writer)
PerformancePoint Services error code 20604.</Data>
  </EventData>
</Event>

Error 2:
Log Name:      Application
Source:        Microsoft-SharePoint Products-PerformancePoint Service
Date:          2/4/2014 11:37:55 AM
Event ID:      2
Task Category: PerformancePoint Services
Level:         Warning
Keywords:      
User:          DomainSPFarmAct
Computer:      SrvApp02.domain.com
Description:
Error running data source query.
XML for Analysis parser: The XML for Analysis request timed out before it was completed.

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: XML for Analysis parser: The XML for Analysis request timed out before it was completed.
   at 
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteMultidimensional(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
   at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
   at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQueryInternal(String query, Boolean 
isCellSet, Int32 sessionTimeout)

PerformancePoint Services error code 10116.
Event Xml:
<Event xmlns=”http://schemas.microsoft.com/win/2004/08/events/event”>
  <System>
    <Provider Name=”Microsoft-SharePoint Products-PerformancePoint Service” Guid=”{A7CD5295-CBBA-4DCA-8B67-D5BE061B6FAE}” />
    <EventID>2</EventID>
    <Version>14</Version>
    <Level>3</Level>
    <Task>1</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime=”2014-04-18T16:37:55.156358900Z” />
    <EventRecordID>603585</EventRecordID>
    <Correlation ActivityID=”{43305F92-CD1F-4E75-B19B-2581FE7C04CD}” />
    <Execution ProcessID=”6420″ ThreadID=”6468″ />
    <Channel>Application</Channel>
    <Computer>SrvApp02.domain.com</Computer>
    <Security UserID=”S-1-5-21-1627688274-1190192956-3999157559-1626″ />
  </System>
  <EventData>
    <Data Name=”string1″>Error running data source query.
XML for Analysis parser: The XML for Analysis request timed out before it was completed.

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: XML for Analysis parser: The XML for Analysis request timed out before it was completed.
   at 
Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Ex

ecuteMultidimensional(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection 

parameters)
   at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteCellSet()
   at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider.RunQueryInternal(String query, Boolean 
isCellSet, Int32 sessionTimeout)

PerformancePoint Services error code 10116.</Data>
  </EventData>
</Event>

Resolution:
I went to Central Admin > Manage Service Applications > PerformancePoint Service Application Settings and found these default settings.

There wasn’t any relevant setting. I then used Power shell commands.

Run this PowerShell Get command to see current values.
Get-SPPerformancePointServiceApplication -identity “PerformancePoint Service Application”

Then run following PowerShell to set a higher value for AnalyticQueryCellMax.
Set-SPPerformancePointServiceApplication -identity “PerformancePoint Service Application” -AnalyticQueryCellMax 10000000

References:
1. http://technet.microsoft.com/en-us/library/ee906547(v=office.14).aspx
2. http://social.technet.microsoft.com/Forums/en-US/3e676663-0d5f-4cbf-9694-14e82772c8e6/unable-to-create-report-query-produced-too-many-results?forum=ppsmonitoringandanalytics

Query to List PWA Groups with Resources

Saturday, February 17th, 2007

Query to List PWA Groups with Resources

Version 3.2, Modified: 01/24/20175

Applies To: Project Server 2007, 2010, 2013


The following query gives you list of groups with users, their NT accounts, email address & some other details. I’ve used the query to get list of all users that have Project Manager / Resource Manager access or part of similar custom groups. These can be often helpful in getting the list of users that might have MS Project Professional installed on their desktops for licensing counts.

MS Project Server 2013 Version

 

Click here to download the 2013 version query.

 

MSP2013_SQLQuery_Designer_for_Resources_and_Associated_Groups

MSP2013 SQLQuery Designer for Resources and Associated Groups

 

 

MS Project Server 2010 Version

— MSPS – Query to Finds PWA Security Groups with Active Users of those groups
–use ProjectServer_Published
select sg.Wsec_GRP_Name as GroupName, –mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
where 
sg.WSec_grp_name like ‘Project Managers’ or
sg.WSec_grp_name like ‘Resource Managers’ or
sg.WSec_grp_name like ‘IT Administrators’ AND
mr.Wres_Is_Enabled = ‘1’
order by 
sg.wsec_grp_name, mr.res_name


Find users that have Permissions or Categories directly assigned

— MSP Query to Find users that have Permissions or Categories Directly Assigned
use [ProjectServer_Published]
  select
      u.res_name,
      case when c.wsec_cat_name is null
          then ‘Permission directly assigned to user’
          else ‘Category directly assigned to user’
          end SecurityIssue,
      c.wsec_cat_name,
      f.wsec_fea_act_name_id
  from
      dbo.msp_resources u
      inner join dbo.msp_web_security_sp_cat_relations r on u.res_security_guid = r.wsec_sp_guid
      left join dbo.msp_web_security_sp_cat_permissions p on r.wsec_rel_uid = p.wsec_rel_uid
      left join msp_web_security_categories c on r.wsec_cat_uid = c.wsec_cat_uid
      left join msp_web_security_features_actions f on p.wsec_fea_act_uid = f.wsec_fea_act_uid
  where
      p.wsec_allow = 1 or p.wsec_deny = 1 or c.wsec_cat_uid is not null
  order by

      u.res_name

MS Project Server 2007 Version

— This Query give gives the list of groups with associated users. (by FM)

select sg.Wsec_Grp_ID as GroupID, sg.Wsec_GRP_Name as GroupName, mr.Wres_ID,
mr.Res_name as ResourceName, mr.Wres_NT_Account as NTAccount, mr.Wres_email as Email,
mr.Wres_Use_NT_logon as UseWinAuthentication, mr.Wres_Is_Enabled as IsEnabled, mr.Wres_Can_Login as CanLogin,
mr.Wres_Last_Connect as LastConnect, mr.Wres_is_Manager as IsManager
from msp_web_security_groups sg
inner join msp_web_security_group_members sgm on sg.Wsec_grp_guid = sgm.Wsec_grp_guid
inner join msp_web_resources mr on sgm.Wres_GUID = mr.Wres_GUID
–where sg.wsec_grp_id = 4 AND sg.WSec_grp_name like ‘Project Managers’
order by sg.wsec_grp_name, mr.res_name



Contact your DBA / Project Admin to run this query. It is not recommended to run SQL queries again production Project Server / SharePoint databases.

Few external References with related Information
  1. Create a report of Users and Security Groups