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)

Behind the scenes with SSRS and Vision, Part 3

In this last installment, I will briefly discuss the interaction between vision and the SSRS service layer. So far I have discussed the interaction between Vision and the .rdl report file in part 1 of this series, and vision/report file and the reportserver database in part 2.

Assuming you have permissions, log into your reporting server via the following url.

http://YOUR_REPORT_SERVER_NAME/reportserver. Follow the link by clicking on each link (folder) you encounter. More than likely, each link (folder) will represent the structure of your installation. For example, it will more than likely be the following, install_directory/web_link_name/list_of_all_users_who_have_run_reports/Report_folders/ Report_instance_name./

Below is an example of when you finally navigate to the report instance you ran. Click on the image to take a closer look. You'll recognize the path of the report because it existed in several calls to the database I documented in my previous post.

exec CreateObject @ItemID='61A90D67-9B14-466E-98F0-3E967F646B86',@Name=N'Accounts Receivable Ledger',@Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A

SSRS has been instructed by Vision to create and save the AR Ledger Report that was created through the Vision interface. This report is re-viewable through the native SSRS interface from outside of Vision. If you remember back in the actuate days, you could log into the Actuate Report Server/Report Volume through a java application and actuate provided the same functionality.

From a high level, I have tried to show you the interaction between Vision and the Report file, the reportserver database, and the SSRS service layer. Please feel free to respond to this post or email me directly if you have any questions or objections. Enjoy....

Behind the scenes with SSRS and Vision, Part 2

In my previous post, I covered the communication between the Vision application and and the SSRS Report File (.rdl). I discussed the AR Ledger report specifically, because that is my favorite. With this post, I hope to discuss Vision's interaction with the SSRS reporting server and focus on the reportserver database.

When the AR Ledger report is configured and executed using the Vision interface, many calls are made to the SSRS layer, and many things are happening within the reportserver database.

From a high level, the following stored procedures are called within the reportserver database. I may have these a little out of order, but understanding the big picture is the intent here. In addition, I've captured what each call looks like, but I have ommitted or simplified parameters passed to the stored procedures.

1) CreateObject
2) AddDataSource
3) ObjectExists
4) LoadForDefinition
5) GetReportParametersForExecution
6) GetAllProperties
7) GetCompiledDefinition
8) ObjectExists
9) CreateSession
10) InsertUnreferencedSnapshot
11) SetSessionData

CreateObject
The CreateObject stored procedure is used to create a specific version of the report for your session that you are unknowlingly creating behind the scenes. A record is created within the report catalog (catalog table) that includes a unique identifier, the name of the report, the path of the report and the location as well.

The path of the report contains the Vision installation directory along with the version of vision, the weblink name used and the username you are running the report as. IE, @Path = '/vision/weblink name/username/....'

exec CreateObject @ItemID='61A90D67-9B14-466E-98F0-3E967F646B86',@Name=N'Accounts Receivable Ledger',@Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A

AddDataSource
Every report can have at least 1 data source. While the report is being created via the CreateObject Stored Procedure, the AddDataSource stored procedure stores the appropriate data sources used within the report along with the unique identifier created in the CreateObject Stored Procedure.

exec AddDataSource @AuthType=1,@DSID='A5CB6F20-519F-4863-8505-3B16EE341FCB',@ItemID='61A90D67-9B14-466E-98F0-3E967F646B86',@Name=N'DefaultDataSource',

ObjectExists
Checks the catalog to make sure that the report exists along with the appropriate authorization level.

exec ObjectExists @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

LoadForDefinitionCheck
According to Microsoft, this stored procedure is used "For loading compiled definitions to check for internal republishing, this is done before calling GetCompiledDefinition or GetReportForExecution".

exec LoadForDefinitionCheck @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AcquireUpdateLocks=0,@AuthType=1

GetReportParametersForExecution
This stored procedure grabs the current parameter set or a recent parameter set from the reporting cache.

exec GetReportParametersForExecution @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

GetAllProperties
Retrieves all properties for a given report including server information, user information, creation date and xml options.

exec GetAllProperties @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=1

GetCompiledDefinition
This procedure is used for creating and calling up a snapshot of a report.

exec GetCompiledDefinition @Path=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@AuthType=N'1'

CreateSession
Creates and updates the session for a given report run.

exec CreateSession @SessionID='kehsjb55qpdklmj4xsiantjt',@ReportPath=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/

InsertUnreferencedSnapshot
This stored procedures either writes a snapshot to the snapshot table or temporarily saves the snap shot in the reportservertempdb database.

exec InsertUnreferencedSnapshot @EffectiveParams=N'declare @p20 xmlset @p20=convert(xml,N'20.....

SetSessionData
Similar to the CreateSession procedure, this creates or updates the session.

exec SetSessionData @SessionID='kehsjb55qpdklmj4xsiantjt',@ReportPath=N'/Vision/WEB_LINK_NAME/USER_NAME/AccountsReceivable/AR Ledger__BAA6DDF0E57149E09925692F96EF950A/Accounts Receivable Ledger',@Timeout=600,@AutoRefreshSeconds=0,@OwnerSid=@OwnerName=.......


So far we have covered Vision's interaction with the .RDL file, and with this post we have covered the interaction between the Vision, the .RDL file and the reportserver database. In my next post, I will complete this series tying the entire picture together.