Hot Fix #32 for Vision 6.1 SP4

Hot Fix #32

Hot fix #32 contains two important performance improvements that were fixed in Hot fix #31. I'll cover one of the improvements below, and will post another entry later discussing the second performance fix.

The first performance improvement is related to a delete statement that is run in the background and is triggered my multiple events during a user’s activity within Vision. The delete statement below was responsible for clearing out old entries in the workflow log table.

DELETE From WorkflowLog WHERE
datediff(dd,WorkflowTime,GETUTCDATE()) >
(SELECT WorkflowLogLength from CFGSystem )

Based on the execution plan below, you can see that this is a less than ideal delete statement, and the performance degradation is multiplied if you are keeping millions of rows in your workflowlog table.




As part of Hot Fix #32, Deltek re-worked the delete statement and it looks something like what is listed below.

DELETE From WorkflowLog WHERE WorkflowTime < @datevariable

Based on the execution plan below, you can see the plan is much simpler and leads to better overall performance.



If you want to squeeze out even better performance, add a non clustered index on the workflowtime column for the workflowlog table.

Enjoy

Creating an Atom Feed from Vision reports

Vision 6.1 SP4 now supports SSRS 2008 R2. R2 has many new features that include Atom rendering extensions. This means a report can be a data feed. What this really means is that a report can be a data source that can be used by any application that can consume a feed.

For example, Excel 2010 with PowerPivot can consume an Atom Feed. For the most part, anything that can consume XML can also consume an ATOM feed.

I tested this out by going to the ReportManager URL for our SSRS 2008 R2 reporting server that is used by Vision. The default URL for most R2 installations usually looks something like this: http://YOUR_REPORT_SERVER_NAME/Reports_SQL2008R2

Find your Vision report and run the report. You should notice an orange icon in which I have highlighted with a red box around it. This is the export to data feed icon.

See the screen shot below.

When you click the orange icon, you will be prompted to save the atom feed. An improtant note about the screen shot below is if you have Excel 2010 installed from the machine you are exporting the data feed to, you will have an "Open" button to open directly into excel.

I chose to save the Atom file, which you then can open in any text editor to view, or you can view directly in your browser like the screen shot below.

Although it may be hard to detect based on my poor screen shot, you will see a URL listed in the collection XML tag. If you replace the "&" to "&" in the URL, copy and paste it into your browser, you will get an enormous XML data dump of the contents of the report. This is the XML output from the data feed.

From here, you can choose to open the XML or just the .atomsvc file directly in Excel 2010, or in any other data feed consumer and your Vision reports can be a datasource for the Web 2.0 revolution.

Enjoy!!!

History of Deltek Acquisitions

Yesterday I quickly blogged about Deltek's purchase of INPUT. But I got to thinking last night, that Deltek has acquired many companies over the years which has helped the evolution of their products. Below is a list of the companies they have acquired during their short history.

Deltek Acquisitions

INPUT (10-1-2010)

Maconomy (7-21-2010)

MySBX (12-15-2009)

WST Pacific Pty Ltd (5-30-2007)

AIM Corp (5-16-2007)

C/S Solutions (7-27-2006)

Welcom (5-20-2006)

Wind2 (10-4-2005)

Semaphore Inc (8-10-2000)

A/E Management Services (4-25-2000)

Harper & Shuman Inc (6-1-1998)

SalesKit Software Corp (5-5-1998)

Deltek Acquires INPUT

Looks like Deltek gobbled up another company. INPUT, which provides opportunity and business development intelligence, was purchased for $60 million. More details below.

http://www.deltek.com/company/pressroom/showfullstory.asp?show=555

As a side note, Deltek also purchased Maconomy back in June for upwards of $70 million. It appears that they are buying the last few ERP pieces left in the market space for AEC.

Web Gardens

Out of the box,it seems that implementing web gardens for the application server should work with Vision 6.1 SP4 and could even create better performance while providing some level of fail-over tolerance for single server implementations.

Vision 6.1 SSRS Reports

As I’ve mentioned in previous posts, when Deltek announced that they were switching to SSRS for their reporting platform, I was ecstatic. I thought how much more wonderful the world was going to be by moving off of a heavy I/O dependent reporting architecture like Actuate and moving to a memory hungry architecture like SQL Server Reporting Services.

In previous versions of Vision that use actuate, administrators would have to crawl through the report log grid in report administration to cough up SQL that was being run by reports. Or, if you were clever enough, you could piece together some breakpoints and look through the SQL variables in the actuate designer.

Now, The SSRS report .rdl file themselves contain enough of the SQL that you can gather what the reports are trying to do. Deltek mentioned a couple of years back, that this was purposeful in order to help users understand what reports were trying to do and to allow easier custom development. For those of you who have authored custom actuate reports, it was not an easy task.

It’s great that the SQL is readily available and viewable in the Vision .RDL files, but I started wondering why such complicated SQL was nested in the Reporting Layer and kept out of the database in the fashion of Stored Procedures.

I reached out to some folks in the know and they gave me a pretty reasonable answer as to why Deltek may have coded it in this fashion. In short, there’s a lot of dynamic query manipulation that occurs when various grouping, sorting and columns options are chosen. So, it’s easier to manipulate the SQL within the .RDL file. In addition, if the Vision .RDL files used stored procedures, any bug fixes to the reports themselves would require both an .RDL file change and a database change for the stored procedures if it was needed.

If you open the .RDL files in the visual studio .net toolset, you will be happy to see tons of SQL that you can copy out of the xml definition and into your SQL client tool or your own custom report to change how you wish.

Happy customizing!!

The Halloween Problem

35 years ago today, a group of database professionals ran into a problem they coined “The Halloween Problem”. The group was writing a query that was supposed to give every employee who did not earn at least $25,000 a year, a 10 percent raise. When the query was completed, every single employee had a salary of $25,000. The update did not return any errors.

After deep research the team discovered that each updated record was incorrectly visible again to the query execution engine which made the rows available for updating again and again until each salary was $25,000 for each row.

There are several versions of this story available online.

1. Many sites talk about the update occurring on data used by the index and the update physically moves the rows in which they are made available again for updating.
2. Other sites talk about the query optimizer generating an incorrect plan in which rows were incorrectly made available for update.

There is also talk that table spools help prevent the Halloween problem, but I have not researched this.

Happy Halloween!!