Feeds:
Posts
Comments

Archive for the ‘SQL’ Category


Lot of times i have seen users asking when should i be creating a report in Excel Services or should i be using SSRS, in my future post i will be covering around performance point reports as well but for now i would be just giving you high level objective guidance on when to use Excel Services vs when to use SSRS

Note: this is my personal experience and would be applicable generally but exceptions are everywhere

Excel Services When to consider

  • Interactive Filtering : As we all know with excel services you can still filter data even when the data has been rendered within the sheet, but for SSRS report you need to predefined filters in form of parameters, once the data is rendered you need to reapply the filter and refresh in order to render the report, hence consider excel services wherein interactive data filtering would be required  
  • Conditional / Interactive Formatting : Conditional / Interactive  formatting is something which is easily available and can be done within excel but with SSRS though conditional formatting might take some effort to develop but interactive formatting might not be available at all
  • Pivot table Layout : So one of the things excel does the best is Pivoting of data, it’s not that SSRS can’t do that, with matrix & query combination it can be achieved to a degree but considering the effort i would suggest use Excel services than to be using SSRS
  • Quick Report : Another aspect to decide how quickly you need  the report, with excel services you might be up & running within few minutes than SSRS taking more time to design & develop the report, hence  for rapid report development, often i use term RRD to as an acronym, use Excel services
  • Usability : From usability standpoint since still more than 90% of the people use Excel as their reporting tool, hence people love to see their report in Excel, so it also depends upon audience what sort of audience you have

SSRS Reports when to consider

  • Tabular layout : If you are developing reports with tabular layout, personal recommendation would be to use SSRS than Excel services specifically if there are external data ranges, though even pivot table data can be shown as tabular format in excel but in my personal experience i have had problems with tabular reports, hence i recommend SSRS for the same
  • Dynamic parametrized query : Though some of them can be achieved by using query filter webpart and by passing parameter or Using UDF in combination but needs additional effort, also excel loads all the data at once and can only be filtered wherein all data might not be required upfront, also increased load time of report, whereas SSRS renders limited set of data as per parametrize filter 
  • External Images / 3D Charts : If you are using external linked images SSRS would be the way to go, also you would notice that charts within excel services renders flat rather than three-dimensional, also have experienced issues with colors being plotted on excel services as when hosted have experienced color disorientation
  • User Specific Permissions :  This can still be done in Excel services but at times is a security overhead which needs to be managed but can easily be done with SSRS report 
  • Multiple Export Options : There are multiple report export options available within SSRS whereas with Excel services you can only export as excel
  • Subscriptions : One of the best advantages is see is automated subscriptions from SSRS which is not available out of box with excel services though the same can be achieved using customizations but as always an additional overhead

Hope this helps clarifying and narrowing down options for reports


 

Read Full Post »


Thought of sharing for people who are looking for reporting data on Portfolio strategy, as you know Portfolio Strategy data is not available out of box with Project Server  2010 installation, hence before proceeding for reporting you need to get the “Portfolio Strategy Object Publish to RDB” solution starter installed in your environment, get it from here :: http://archive.msdn.microsoft.com/P2010SolutionStarter , post installation make sure you publish your scenarios, analysis solution drivers , priorities etc… before you would find actual data in DB

Since this being a solution starter the schema is not documented and at times its hard to look where to find which data, other way would be to look for sample reports & data connection queries, that gets installed upon deploying the solution starter, or if you want to understand the complete schema look at the attached diagram, download the image below & enlarge to see

Portfolio Strategy RDB DataSchema

 

 

 

 

 

 

 

 

Let us know if this helps

Read Full Post »


To help you determine when you have to scale up or scale out your Microsoft Project Server farm, use performance counters to monitor the health of the system. Use the information in the following tables to determine which performance counters to monitor, and to which process the performance counters should be applied, For more Information Refer to :

http://technet.microsoft.com/en-us/library/gg715567.aspx

Read Full Post »


received a question recently thought would share with all :), Especially for those who haven’t planned upgradation to Project Server 2010, be aware that MS Project Server & MS Portfolio Server main stream support end date is 4 /10/2010, though extended support end date is far enough, hence Plan your upgrade accordingly 🙂

For other MS product life cycle refer to : http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=project&Filter=FilterNO

Read Full Post »


Often we need to know the projects which has been saved by PM’s but hasn’t been published, in order to check with PM’s if they forgot   to publish or intentionally they have saved the changes, all this is to make sure the correct data is reflected  🙂 , Make sure to run this against draft DB

Note: querying draft DB directly is not supported by MS, hence perform this at your own risk

So here is the query by which you can find out

select p.WPROJ_LAST_PUB, p.PROJ_LAST_SAVED, p.PROJ_NAME, p.Proj_UID from MSP_PROJECTS p where p.WPROJ_LAST_PUB < p.PROJ_LAST_SAVED

Read Full Post »


Recently i got a question where in a user has asked to get all the resources assignment info, earlier in 2003 it was available in MSP_WEB_WORK, the below query would fetch all the assignments in 2007/2010, Further you customized to accomodate your requirements

SELECT     MSP_EpmResource_UserView.ResourceName, MSP_EpmAssignment_UserView.AssignmentStartDate, MSP_EpmAssignment_UserView.AssignmentFinishDate,
                      MSP_EpmAssignment_UserView.AssignmentActualStartDate, MSP_EpmAssignment_UserView.AssignmentActualFinishDate,
                      MSP_EpmAssignment_UserView.AssignmentActualWork, MSP_EpmAssignment_UserView.AssignmentWork, MSP_EpmProject_UserView.ProjectName,
                      MSP_EpmTask_UserView.TaskName
FROM         MSP_EpmAssignment_UserView INNER JOIN
                      MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID INNER JOIN
                      MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                      MSP_EpmTask_UserView ON MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

Read Full Post »


While working with Project Server / Protfolio Server Reporting DB, I often faced problems keep on looking / searching for certain column names, where the heck is that column to reference !!! LOL, Even though you would say, SDK has all the schema 😦 , Still at times it became very difficult to find the Column References, for eg – try looking SDK for Resource Cost Rate Table Info, and i bet you would have to try hard n hard to find it,
Then i thought why not have a utility which will search specific texts for me in all tables and give me the columns name which contain that particular text, wouldn’t that make my life easier and here i wrote one such piece, thought of sharing as it would help you reduce some work load atleast 🙂

CREATE TABLE #TempTable (TABLE_NAME sysname, COLUMN_NAME sysname, MatchesFound int)
GO
SET NOCOUNT ON
DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @SearchString varchar(80), @Count int
SELECT @SearchString = ‘Cost Rate’
DECLARE SearchCursor CURSOR FOR 
 SELECT c.TABLE_NAME, c.COLUMN_NAME    FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
     ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
  WHERE c.DATA_TYPE IN (‘char’,’nchar’,’varchar’,’nvarchar’,’text’,’ntext’)
    AND t.TABLE_TYPE = ‘BASE TABLE’
OPEN SearchCursor 
FETCH NEXT FROM SearchCursor INTO @TABLE_NAME, @COLUMN_NAME
WHILE @@FETCH_STATUS = 0
 BEGIN
  SELECT @SQL = ‘INSERT INTO #TempTable (TABLE_NAME, COLUMN_NAME, MatchesFound) SELECT ‘    + ”” +  @TABLE_NAME + ”” + ‘,’    + ”” + @COLUMN_NAME + ”” + ‘,’    + ‘COUNT(*) FROM [‘ + @TABLE_NAME      + ‘] WHERE [‘ + @COLUMN_NAME + ‘] Like ‘    + ””+ ‘%’ + @SearchString + ‘%’ + ””  
  EXEC(@SQL)
  IF @@ERROR <> 0
   BEGIN
    SELECT @SQL
    SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
    GOTO Error
   END
  FETCH NEXT FROM SearchCursor INTO @TABLE_NAME, @COLUMN_NAME
 END
SELECT * FROM #TempTable WHERE MatchesFound <> 0
Error:
CLOSE SearchCursor
DEALLOCATE SearchCursor
GO
DROP TABLE #TempTable
GO
SET NOCOUNT OFF–__________________
–Sunil Kumar Singh

Read Full Post »

Older Posts »