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!!

Vision 6.1 SP4 architecture notes

Just a quick entry to let you know some high level details about the new Vision 6.1 application.

6.1 SP4 will now install and run on a 64 bit server, but still runs within the 32 bit application space. So it is not taking advantage of 64 bit.

6.1 SP4 also supports SQL 2008 R2.

6.1 SP4 works with Sharepoint Foundation 2010 for document management.

Please reply to add anything important you think I missed.

Deltek Integration Services - Aptaria

I stumbled across this site, while looking for integration services for Deltek Vision. Looks like they have performed Vision integrations with Salesforce, Siebel, PeopleSoft, Oracle and SAP. After reading their site, it looks like they focus entirely on the cloud with no need for infrastructure. Based on their focus around Google Apps, Salesforce.com and running financials through the cloud, they look like an exciting company who has Deltek Vision integration experience.

Andrew Lawlor also has a blog located at http://theondemandenterprise.blogspot.com

Looks pretty interesting.

Enjoy....

Joining Organization and CFGOrgCodes dynamically

If you have ever worked with the organization and cfgorgcodes table, you've probably had not-so-straight forward experiences in joining these two tables together. If you firm is running multi-company, then you have definitely experienced the fun in marrying to the two tables together!

In Short, the organization table lists out the full name of the given lowest level in your organization along with the full org code, project number mappings, ranges and posting rules.

The CFGOrgcodes table provides the hierarchy and the name of each level by each individual code.

For example, your organization table might have something like this in the name and org fields

Name=Enterprise:Midwest:SpringField:Engineering Dept.
Org=1:MW:SPF:ENG

Your CFGOrgCode table might have something like this based on the entry from the above example.

OrgLevel, Code, Label
1, 1, Enterprise
2, MW, Midwest
3, SPF, SpringField
4, ENG, Engineering Dept.

I put the query below together to dynamically parse out the each Org Level based on the Org Code.

SELECT o.name, o.org, ISNULL(c1.label,'') as OrgLevel1,
ISNULL(OrgLabels.org1Label,'') as OrgLevel1Label,
ISNULL(c1.label,'') as OrgLevel2,
ISNULL(OrgLabels.org2Label,'') as OrgLevel2Label,
ISNULL(c1.label,'') as OrgLevel3,
ISNULL(OrgLabels.org3Label,'') as OrgLevel3Label,
ISNULL(c1.label,'') as OrgLevel4,
ISNULL(OrgLabels.org4Label,'') as OrgLevel4Label,
ISNULL(c1.label,'') as OrgLevel5,
ISNULL(OrgLabels.org5Label,'') as OrgLevel5Label

from Organization o
inner join (Select OrgLevels, OrgDelimiter,
Org1Start, Org1Length, Org2Start, Org2Length,
Org3Start, Org3Length, Org4Start, Org4Length,
Org5Start, Org5Length from CFGFormat) as OrgFormat on 1=1

inner join
(Select orgLabel, org1Label, org2Label, org3Label, org4Label, org5Label
from CFGLabels) as OrgLabels on 1=1

left join cfgOrgCodes c1 on SUBSTRING(o.org,OrgFormat.Org1Start,OrgFormat.Org1Length) = c1.code and c1.orglevel = 1
left join cfgOrgCodes c2 on SUBSTRING(o.org,OrgFormat.Org2Start,OrgFormat.Org2Length) = c2.code and c2.orglevel = 2
left join cfgOrgCodes c3 on SUBSTRING(o.org,OrgFormat.Org3Start,OrgFormat.Org3Length) = c3.code and c3.orglevel = 3
left join cfgOrgCodes c4 on SUBSTRING(o.org,OrgFormat.Org4Start,OrgFormat.Org4Length) = c4.code and c4.orglevel = 4
left join cfgOrgCodes c5 on SUBSTRING(o.org,OrgFormat.Org5Start,OrgFormat.Org5Length) = c5.code and c5.orglevel = 5

You'll notice that I've joined on a few tables that I haven't yet mentioned.

CFGFormat
The CFGFormat table allows us to understand the start position and the length of each level in the Org Code so that we can dynamically parse it out. If you change the lengths or rules of your particular levels in your org structure, you will not need to change the above code.

CFGLabels
This CFGLabels table allows us to appropriate define what the generic term is for each level in your Organization. These levels show up on the reports.

If the above query does not work for your environment, please feel free to drop me an email...
Enjoy...

Modifying Deltek Vision’s Standard Reports

I found this great article on modify existing Vision SSRS reports. Michael Dobler dives into the issues encountered and how to work around them. He covers from start to finish, how to copy an existing and report and modify it as your own custom report. Hope you enjoy this as much as I did.