Why Scheduling Mustn’t be Allowed to Become an Extinct Science – Basics of Planning

Want to know best practices for planning, read this elegant article, this article is one of the best ones i have come across  

Click Here => Why Scheduling Mustn’t be Allowed to Become an Extinct Science

Read Full Post »

Top Sharepoint 2010 BI Features

 

Publish Access 2010 databases and their reports to SharePoint 2010

Create Data Sources and Data Views to a wide variety of data stores with SharePoint Designer 2010

Create Charts with the Chart Web Part

Visualize Data with Visio Services

Use Business Connectivity Services to expose external content types and lists

Create reports using a wide array of data sources including SharePoint lists with SQL Server Reporting Services 2008 R2 Report Builder

Create dashboards with PerformancePoint Services and Dashboard Designer

Provide self-service analytics with PowerPivot add-ons for Excel and SharePoint / Excel Services

Expose just about everything to other applications with Web services, REST, and ODATA

Read Full Post »

EPM Team – Back to the Basics: Rolling Up % Complete and Elapsed Duration

Refer to this link if you want to know how the field formulas are being calculated and the logic behind it. obviously basics but you should know

“What % Complete is, And how it rolls up” , Nice article

Click Here => EPM Team – Back to the Basics: Rolling Up % Complete and Elapsed Duration

Read Full Post »

EPM Team – Excel Based Portfolio Optimizer Simulation v1 Release

Check the link below , i am sure you will be amazed to see, there is an Portfolio Optimizer in Excel which more or less works the same way
MS Project Portfolio works, Work done by Catapult Systems

Click Here => EPM Team – Excel Based Portfolio Optimizer Simulation v1 Release

Just download and watch out all the related sheets and you will get to learn many things….

Read Full Post »

Myth – SQL Server – @Table Variables Vs #Temp Tables

 

Many a times we use #temp tables in our SQL Query, the question is should we use #temp table or @table variable, and the long and short of it is
”It DEPENDS”, Wow confused, rather than making it more complex, let me simplify it,
so the points to be considered

When to choose @Table variable and when #Temp table  

• If You have less data or simple data, possibly under 10000 rows use @variable
• If you don’t want the data to be have transactional logging or locking or any kind of rollback then use @Table variable
• If you don’t need to create index in data rows then use @Table variable, though there are workarounds to it, use a constraint

Obviously @variables will be faster because  

• Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
• Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them
• And Of course you will not need to DROP #TEPM TABLE every time you create one :-), less manageability

Drawbacks of @Table Variable

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.
• You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and asp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure

Myth :: Memory Consumption

A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache)

-=================================== TEMP TABLE EXAMPLE=============================

SELECT
        MSP_EpmTask_UserView.TaskDuration, 
        CASE WHEN MSP_EpmTask_UserView.TaskDuration < 10 THEN 1
             WHEN MSP_EpmTask_UserView.TaskDuration > 10 AND MSP_EpmTask_UserView.TaskDuration <= 25 THEN 2
             WHEN MSP_EpmTask_UserView.TaskDuration > 25 THEN 3             
        END AS DurRangeCount
INTO #TEMP
FROM  MSP_EpmProject_UserView INNER JOIN MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
WHERE (MSP_EpmTask_UserView.[Replan Task] = 1) AND (MSP_EpmTask_UserView.TaskPercentCompleted = 100)AND (MSP_EpmProject_UserView.ProjectCondition = ‘Active’)

–Select COUNT(DurRangeCount),DurRangeCount FROM #TEMP GROUP BY DurRangeCount

ALTER TABLE #TEMP ADD DurRange VARCHAR(20)
UPDATE #TEMP SET DurRange = ‘0 – 10’ WHERE DurRangeCount=1
UPDATE #TEMP SET DurRange = ’11 – 25′ WHERE DurRangeCount=2
UPDATE #TEMP SET DurRange = ‘ > 25’ WHERE DurRangeCount=3

Select COUNT(DurRangeCount) AS NoOfProject,DurRange FROM #TEMP GROUP BY DurRangeCount,DurRange

DROP TABLE #TEMP

===================================================================================

-================= SAME QUERY REWRITTEN USING @TABLE VARIABLE===================================

DECLARE @TABLE1 TABLE (DurRange Varchar(20),TaskDuration int,DurRangeCount int)

INSERT INTO @TABLE1(TaskDuration,DurRangeCount)
SELECT    MSP_EpmTask_UserView.TaskDuration, 
        CASE WHEN MSP_EpmTask_UserView.TaskDuration < 10 THEN 1
             WHEN MSP_EpmTask_UserView.TaskDuration > 10 AND MSP_EpmTask_UserView.TaskDuration <= 25 THEN 2
             WHEN MSP_EpmTask_UserView.TaskDuration > 25 THEN 3             
        END AS DurRangeCount
FROM  MSP_EpmProject_UserView INNER JOIN MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
WHERE (MSP_EpmTask_UserView.[Replan Task] = 1) AND (MSP_EpmTask_UserView.TaskPercentCompleted = 100)AND (MSP_EpmProject_UserView.ProjectCondition = ‘Active’)

–ALTER TABLE @TABLE ADD DurRange VARCHAR(20)
UPDATE @TABLE1 SET DurRange = ‘0 – 10’ WHERE DurRangeCount=1
UPDATE @TABLE1 SET DurRange = ’11 – 25′ WHERE DurRangeCount=2
UPDATE @TABLE1 SET DurRange = ‘ > 25’ WHERE DurRangeCount=3

Select COUNT(DurRangeCount) AS NoOfProject,DurRange FROM @TABLE1 GROUP BY DurRangeCount,DurRange

===============================================================================

 

MSDN LINK ::  INF: Frequently Asked Questions – SQL Server 2000 – Table Variables

Read Full Post »