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.

The KeyConvert impact on the Vision database

I was working with a fellow business analyst the other day who was investigating the impact of running a keyconvert on our production database during business hours. She was asking me alot of questions that I could not answer. What exactly is locked, what will cause blocking and so forth. So we set up a test on our developer database server and I will discuss the results below.

While profiling the Vision database during the keyconvert, we encountered many different lock types and lock modes. I will discuss each below. It's important to note that depending on what kind of key convert you run, you many encounter different types of locking and different kinds of lock modes.

Lock Types

11 - METADATA
A lock on a part of the catalog.

5 - OBJECT
A lock on a table or index. An object lock can be acquired on any object existing in sys.all_objects.

6 - PAGE
A lock on a data page in the database. All data in the database exists on an 8kb page.

7 - KEY
A lock on a row in an index.

9 - RID
A lock on a given row in a heap (table with no clustered index).

2 - DATABASE
Any activity on a database causes a database lock. This prevents the database from being dropped, restored or taken offline.

Lock Modes
From a high level, there is a naming convention in the locking mode definition. Below are some of the relevant modes and their definitions.

• Shared (S) Used for read operations like the SELECT statement. No data is changed. It's important to note that the data cannot be changed until the SELECT statement releases it.
• Update (U) Used on resources that can be updated. Helps prevent deadlocking with sessions or trying to read and change data on the same piece of information.
• Exclusive (X) Used for data-modifications like the INSERT, UPDATE and DELETE statements. This mode prevents multiple resources trying to modify the same data at the same time.
• Intent Used establishes a locking hierarchy. The intent locks are intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
• Schema Used when an operation is dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
• Key-range protects a range of rows read by a query when the serializable transaction isolation level is used. This mode protects the range to make sure that other transactions can't insert rows that would qualify for the queries of the serializable transaction.

Below are the locking modes we encountered during our key convert process.

7=Intent Update Lock (LCK_M_IU)
4=Update Lock (LCK_M_U)
1=Schema Stability lock (LCK_M_SCH_S)
0=NULL - Compatible with all other lock modes (LCK_M_NL)
3=Shared Lock (LCK_M_S)
8=Intent Exclusive Lock (LCK_M_IX)

So I have documented all of these technical definitions and codes, but have not yet committed to discussing what the basic affects are of running a keyconvert on a production database during business hours.

In short, don't run a keyconvert on a production database during business hours. Ideally, do it over a weekend where you can eliminate any usage on the system via normal IT down time or to ensure international usage is limited as much as possible.

In a future post, I hope to document more specific examples outline the KeyConvert impact on Interactive Billing, Actuate/SSRS reporting and simple info center management.

Enjoy.....

Deltek Vision tricks for programmers: The new reporting framework for Vision (SSRS)

I just completed a series on SSRS and Vision, and found this great entry on another blog talking about the same subject. This entry covers what you need to get started, challenges of the SSRS implementation and how to talk to your management about upgrading the reporting server platform to SSRS.

Deltek Vision tricks for programmers: The new reporting framework for Vision (SSRS)