New Partnership for Deltek Vision and Deltek Professional Services consulting

I am excited to announce the official launch of Version X Solutions!

Version X Solutions is a new partnership that brings together a combined 30 years of Deltek Vision knowledge and experience under one umbrella.

We offer a wide and practical range of products and services including custom modules, custom reports, and even a cloud hosting service.

We’ve put together the beginnings of an inventory, and will continue to add to it as we come up with new ideas and do more interesting work for clients. If you have an extension we need, we’d love to hear about it!

Of course we still offer hourly consulting for nebulous or “figure it out as we go” projects.

You can read more about us and our current offering here.

What does this mean for existing customers?

Our new partnership will not adversely affect the existing relationships I have built with you over the years.

You will continue to get the same great friendly, responsive and most importantly, knowledgeable consulting services you have come to rely on from me.

Thank you for your continued business and I look forward to working with you in the near future.

From now on, please refer to the Version X Solutions site for information about products and services offered.

 

 

 

CREDIT CARD TRANSACTION IMPORT AUTOMATION – PART 3

Ok I’ve made a lot of progress over the past two weeks. This is quite a complex project actually… but it’s about finished.

The final results look basically like this:

1. use SSIS (Sql Server Integration Services) to grab the csv files from designated directory and import them into a raw data table.

2. move any transactions from raw import data to the processing table… exclude any transactions that have already been imported.

3. using some data transformations in the form of user defined functions (UDF), make the data look like expense detail transactions

4. insert the expense detail transactions into the expense reports for the corresponding employees

5. allow employees to edit their expense transaction details in their pre-populated expense report

6. show the entirety of credit card expense transactions in a report… that shows the imported data, next to the matching expense detail records in Vision, allowing employees to accurately fill out details on their expenses, and make sure that everything has been accounted for.

7. for the payment to the credit card company, assign the cash disbursement to the different project, phase and tasks reflected in the expense detail transactions so that projects show the cash out for those expenses.

So I’ve completed everything except step 7, which I will complete over the next day or two.

Some really cool things this does:
– puts the CSV files in an archive folder and assigns a unique, logical name to them so you don’t have to worry about losing raw data history

– allows you to compare imported data from the CSV file to the actual data in expense reports by employee and expense report

– allows you to trace any imported transaction back to the actual CSV file it came from… it shows you the file name on the report

– reconciles the amounts imported vs. the amounts in the employee’s expense report so you can make sure you didn’t miss anything

– shows data that was imported into the processing section, but didn’t actually make it into an employee expense report

In a couple weeks I will provide more details on how it actually works with some screen shots of the results.

In the mean time, here is a snapshot of the entire process looked up wrapped up in an elegant diagram in SSIS. Of course, this doesn’t show any details or code… just the big picture logic of it. It’s interesting all the same.

Have an excellent day!

I’ll be at Deltek Insight in a couple weeks, and will be teaching a break out session:
PS-80 Putting Data in the Right Place

If you happen to be at Insight feel free to come say “hello” and give me some feedback on my blog!

Cheers!

Do you have a client using ADP open invoice? – CSV Exporting available for Deltek Vision 6.x and beyond

I have a client firm who’s client recently switched from Oildex to ADP Open Invoice.

In order to submit invoices to ADP Open Invoice, you have to use 1 of the following methods:
1. log in to your client’s ADP interface and us the “one at a time” “create a new invoice” method. With this method you have to enter the header information (or a customer number) and each line item on your invoice one at a time. If you have lots of labor entries, this would be cumbersome at best.

2. log in to your client’s ADP Open Invoice and upload a properly formatted CSV file for each invoice. The csv file contains all the information in the invoice. This means all header information, line items (labor, fees, expenses, consultants, addons, taxes, units) are included in that csv file. Of course, to do this you would need some sort of application or Deltek Vision add on that generates the csv invoice files based on your invoices. This is what I just created for a client, so if you need it, please get in touch with me. NO, it’s not free.

3. implement the PIDX LIDO b2b server to server post methodology. This would be a web app written in .net, which uploads xml based files to your client’s server and saves them in ADP Open Invoice for review and attaching backup. I do not have a solution for this one yet.

4. implement the PIDX RINF RosettaNet application which does everything in #3 for you, but which requires the purchase, install, configuration and maintenance of n add-on application to Vision.

I just completed developing a sql application which exports the invoice data correctly into csv files for import into the ADP Open Invoice application.

This particular client had over 300 invoices for July for this one client. Keep in mind that each invoice requires a separate file for uploading to the Open Invoice site. It took my script 42 seconds to extract the data and create the files (one for each invoice).

The best part is… I can attach this script to a workflow button. Now, when Deltek Vision 7.x comes out, with custom infocenters, I can create an infocenter called “administration”, and put the workflow button there. My client will be able to put in a start date, end date, client number, and click the button… and voila! Magical invoice files! Hundreds of them!

As a reward for my excellent work… I get some chocolate pudding from Whole Foods for dessert!

Cheers!

Postering peacock

Vision 7.0 – an update – Some questions and answers from Deltek

Hello and happy 4th of July!

Recently, Brian Lamee (Director, Product Marketing for Deltek Vision) contacted me regarding my recent post on Vision 7.0 – Rumors (click here to read), asking if I had any specific questions and offering to answer them.

I asked if he could answer them, and if I could re-post his answers on my blog… so here you are.  In some cases I’ve added an editorial comment or two to clarify things a bit.

LOREN: I heard that on premise installs of Vision are only available for Series 250 or more, and that they are more expensive. Is this true? If so, what about all the companies that have less than series 250 and are on premise installed already? What is the upgrade path to Vision 7 for them?

BRIAN LAMEE: For firms purchasing Vision new (not current Vision Clients) that are fewer than 250 employees, we are recommending Vision Essentials (our Cloud offering) but we have not done away with our on-premise options and we have NOT increased pricing for our Vision on-premise offering. We have just introduced a new Cloud offering for Vision.

[editorial: What this means for you is… if you are a small firm and don’t want to incur the expense of buying and maintaining hardware, IT staff etc. to run Deltek Vision, then there is a cloud (read: hosted) offering from Deltek now to make this possible.  This is actually a good move by Deltek, making it possible for more firms to use the platform without the huge costs of ownership.]

LOREN: In a recent email you sent to me you said that cloud based installs will be able to have custom reports through Report Builder. This is significantly different than SSRS. Is SSRS supported on cloud systems?

BRIAN LAMEE: Just to make sure we are talking about the same thing when you say SSRS, I assume you mean SQL Server Reporting Services and that is still the reporting engine for Vision 6.x both Cloud and On-premise. Clients in the Cloud can still build custom reports in SSRS on Vision data. We have a way for Consultants and Clients to load those reports into the Clients installation. When I said Report Builder, I think I went back to the older name that Microsoft referred to the tools to build SSRS reports (I can’t keep up with the name changes.)

[editorial: OK my bad… I must have been tired when I wrote that question out… yes, Report Builder reports still run on top of SSRS. What I really meant was – ‘can we still develop custom reports using BIDS, and install them in Vision. The answer from Brian is ‘yes’.  This is good news.]

LOREN: What about integrating other apps into Vision on the cloud based install? Is only the API available, or do we still have access to the entire SQL database?

BRIAN LAMEE: As long as the application can see Vision’s APIs and they do not have to be installed on the Vision Server or in our Cloud Cluster, then yes, those still work. We currently do not support 3rd party applications that have to be installed and maintained on our Cloud servers. We are looking at this in the future.

[editorial: OK this is to be expected.  You can’t ask them to maintain a cloud server for you, then install your own (or someone else’s) apps on that server and expect the cloud provider to service those apps too… nor can you expect to have unfettered access to the server since most likely it’s going to be hosting more than just your Vision install. However, my guess is that there will eventually be a way for apps to connect to cloud installs of Vision through some API or other method by just connecting directly to the SQL database, which is all you really need, since everything in Vision depends on that database.]

LOREN: If we don’t have access to the entire SQL database, what about custom stored procedures fired by workflows?

BRIAN LAMEE: Stored Procedures are supported in our Cloud version. Similar to the custom reports, we have a way to allow Consultants and Clients to load stored procs into the database.

[editorial: OK this is good… especially since I’m a consultant and do lots and lots of stored procedures, functions, data stuff and custom reports!]

CONCLUSION:
If you hear about something that Deltek is going to do to Vision, and you think it’s going to obliterate thousands of Vision implementations (like not allowing custom reports from BIDS, or not allowing access to the database) then it is probably not going to happen.

On a side note… here in Austin, TX we have a park with some pretty fancy turkeys… here’s one for you.  I took this picture a week or two ago.

Postering peacock

Case Study – Vision Implementation Restructure

Ronald Lu & Partners, HK had implemented Deltek Vision 5.1 in 2008 and had been live for 2 years.

I received an email from the founder’s son, Bryant Lu saying that although they had been live for 2 years, their accounting department was still doing a lot of accounting and reporting on spread sheets. After I spent a week in their offices I found the following major issues that needed correcting:

0. They needed to convert from Vision 5 to 6, which we accomplished in 2 weeks.

1. Their work break down structure for every project was the same, displaying the 8 standardized phases of a project that they took each project through, but not reflecting the actual milestones and deliverables of the project.  Thus, they were unable to produce meaningful reports from Vision that could show budget vs. actuals, expected revenues or EAC reports tied to project milestones.  Instead, they were maintaining a completely separate set of financials outside of Vision on an organized but extensive set of Excel spreadsheets so they could track consultant expenses, milestones, invoices, and receivables.

2. Their entire set of P+L statements and Project Earnings reports were being produced from spreadsheets in order to show P+L across companies or divisions. The reason for this was because after going live they had adopted a new organizational structure which consisted of studios (which is common in architectural firms).  Because the studios were not represented in the data in Vision, it was not possible to produce any reports based upon studio from the application.  This affected all of their performance reporting and financial reporting.

Just tackling these two issues implied that I would have to do the following:

1. Restructure the work break down structure (WBS) for every project in the database to reflect true milestones and deliverables (or start over from scratch with a new database and a new go live date, which would mean re-importing historical data… essentially doing their implementation completely over).

2. Either convert their current organization scheme to one that considered the studios, or find some way to generate their entire financial report set using a custom field in the project infocenter which would reflect the studio a project was assigned to.
While the problems and solutions described above are only numbered 1 and 2, there are many implied requirements that would come into play as I executed this project over the next 4 months.  Some of the issues I encountered and resolved were:

– In Deltek Vision, when you key convert a WBS item like a phase or task to move it to another project, phase or task (not merely changing the number), it completely ignores the invoice history data during the key conversion process.  Since we decided we would key convert every project into a new, meaningful work breakdown structure, this meant that I had to write sql scripts to convert the invoice history as well.

– The projects that RLP undertakes can last many years, and often have over 100 phases and tasks. The interface for Deltek Vision does not yet allow a user to update fee or budget amounts in a grid like format that shows the entire project structure.  During the execution of this project I built a Microsoft Access based application they could use for both data entry for the conversion (to enter the WBS for projects into Vision) and to use after the conversion for adjusting fees and budgets for projects, as well as enter fees and budgets for new projects.

– Since it was decided not to restructure their organizational scheme in Vision (this was a management decision) it was necessary to develop custom reports to satisfy their entire financial reporting set.  This included P+L reports, balance sheet reports and forecasting reports.

– Because of the language barriers (Vision is not yet localized in Cantonese, the Hong Kong version of Chinese), it was decided we would not implement Resource Planning.  Also, some projects could actually commence and continue in execution for months before actually becoming a signed project.  Thus, it was necessary to in a sense “bill time to opportunities”.  So instead we came up with a solution for revenue and opportunity forecasting using the project info center only.

There were of course many more issues that we worked through and solved throughout the course of the project.  Bottom line is that we completed and went live with the new project structure and report set by December of that year.  Working on site, together with their executive and accounting departments we were able to complete 6 to 8 months of work in 5 months.  

Here are some example images and reports from after their implementation:

Click for full size.

This shows the mission critical business path, order of priority for phased restructure, area of effort and timeline for the phased restructuring of their Vision implementation.
(I will be adding to this case study with more images and samples… later this week)

What exactly is a stored procedure? (for the laymen)

Ah… the magical stored procedures!

I have heard people talk about stored procedures for Vision, heard people ask about them, heard people say they can write them.  But what exactly IS a stored procedure?

A stored procedure is a batch of programming code written in T-SQL (short for transact SQL) that can be called upon from within SQL Server.  It is a set of instructions with a beginning, an end, a name, and some parameters (variables) that can be passed to it.

Stored procedures are constructed like this:

create procedure z_custom_my_fabulous_magical_code(@variable1 varchar(50), @variable2 int)
as
BEGIN

…. code goes here…

END

That’s the basic idea. What happens where “…code goes here…” is entirely up to the creator of that stored procedure… the programmer.

Because of the nature of the SQL programming language, and because of the power of it, it is important to have a test database upon which someone can test their programming talents or essays so that critical data is not deleted, altered or otherwise destroyed.

Because SQL is a programming language, then really only programmers (aka coders) have any business mucking around in there.

Thus, if someone tells you they can write/craft or otherwise produce those magical “stored procedures” to move data around or otherwise manipulate Deltek Vision for you. Be cautious.  Make sure that person can actually do what they claim.. that they have lots of programming experience, that they know the Deltek Vision database schema well, that in effect they know what the hell they are doing!

So how many stored procedures have I written?  Hundreds.  As well as hundreds of inline functions, scalar functions, views and crafted entire databases and applications from merely a clean slate and my brain.  That’s because I’m a programmer!

Don’t have one of those?  Go get one… or call me.

Cheers!

p.s. here’s an interesting one I wrote for analyzing Vision data… just for kicks.

ALTER procedure [dbo].[z_pr_pgc_dependent_table_assessor](@databasename varchar(255), @findstring varchar(255), @andclause varchar(8000), @optionPrint varchar(1)=’Y’)

as
BEGIN

— SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘tbTest’) AND indid < 2

select ‘TABLE COLUMNS ANALYSIS’ AS DESCRIPTION
, @DATABASENAME AS DATABASENAME
, @FINDSTRING AS FINDSTRING
, @ANDCLAUSE AS ANDCLAUSE

create table #mytables(objectid varchar(255), tablename varchar(255), columnname varchar(255), relevantrowcount bigint)

declare @runsql varchar(8000)

— get relevant table list
set @runsql = ‘
insert #mytables(objectid, tablename, columnname, relevantrowcount)
select t.object_id, t.name as ”table_name”, c.name as ”column_name”, -1 as ”rowcount”
from [‘+@databasename+’].sys.tables t
inner join [‘+@databasename+’].sys.all_columns c
on c.object_id = t.object_id

where c.name like ”%’+@findstring+’%” ‘
+ isnull(‘ and ‘+@andclause,”) + ‘
group by t.object_id, t.name, c.name
order by t.name, c.name ‘

if @optionprint = ‘y’ print @runsql
exec(@runsql)

declare @objectid varchar(255), @tablename varchar(255), @thiscolumn varchar(255), @rowcount bigint

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1

— loop through relevant tables getting relevant row counts
while isnull(@objectid,”) <> ”
begin

set @runsql =
‘update #mytables
set relevantrowcount = (select count(*) from [‘+@databasename+’].dbo.[‘ + @tablename + ‘] where isnull(‘ + @thiscolumn + ‘,””)<> ”” )
where tablename = ”’ + @tablename + ”’ and columnname = ”’ + @thiscolumn + ”’ and relevantrowcount = -1 ‘
if @optionprint = ‘y’ print @runsql
exec(@runsql)

set @objectid = ”

select top 1 @objectid = objectid, @tablename = tablename, @thiscolumn = columnname
from #mytables where relevantrowcount = -1
end

— spit out table stats
select *
from #mytables
order by tablename, columnname

declare @oldtable varchar(255)=”
— now sample data from those tables where it looks relevant
set @tablename = ”

select top 1 @tablename = tablename
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘+@databasename+’].dbo.’ + tablename
else ‘select top 3 * from [‘+@databasename+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

while @tablename <> ‘**all done **’ and @tablename != @oldtable
begin
select @tablename as tablename, @thiscolumn as thiscolumn, @rowcount as [rowcount]

if @optionPrint=’Y’
begin
print @runsql
end
ELSE
begin
EXEC(@runsql)
end

set @oldtable = @tablename

select top 1
@tablename = isnull(tablename,’**all done **’)
, @thiscolumn = columnname
, @rowcount = relevantrowcount
, @runsql = case when relevantrowcount > 0 then
(case when relevantrowcount < 10
then ‘select * from [‘+@databasename+’].dbo.’ + tablename
else ‘select top 3 * from [‘+@databasename+’].dbo.’ + tablename
end )
else ‘print ””’
end
from #mytables
where tablename != @oldtable
and tablename > @tablename

end

drop table #mytables

END

Keeping the big picture in mind

In order to effectively address a Deltek Vision implementation and make it fit better to a particular firm’s way of business, I most often walk through a process down through the 3 layers of the enterprise app, then back up again.

This is a very simplified explanation but it is merely to illustrate a framework of ideas, not give you instructions on how to do exactly what I do.

1. What is happening at the user experience and business process layer?
This is usually where the “pain” is felt. This is usually where the conversation starts. Things like this come up a lot:

We can’t make a report that shows … xyz
We have to export our invoices to Excel or Word so we can … xyz
We have to re-enter our payroll data over here…
We  can’t see the current billed amount in the project infocenter
We want to see everything on the project review screen in a report
We want to see consultant summaries and transaction details all on the same report
Setting up a project in Vision is too cumbersome if we have 3 levels of work break down structure
etc.

This is normally the origins of the wish list of items to address or problems to solve. To address these issues I look at the next two layers

 

2. The application layer
The first thing I do is ask:

What can Deltek Vision do out of the box without creating custom reports or writing SQL scripts?  How is it supposed to be done? Which items from the first layer can be solved just by using the tools at hand like workflows, custom fields, grids, report configuration or system configuration?  Which can be solved by merely changing a business process or the way something is done?

During this part of the process I often do lots of research, try different thing out on a test database, ask lots of questions of the client at hand to see what they’ve already tried.

Depending upon priorities and what can be done quickly with big returns on user comfort and business process improvement, I’ll often do the things from the first layer that can be accomplished by staying in this second layer.

As a general rule, I try to work with the tools presented as much as possible.  But, often it just can’t be done without going deeper and getting really technical, which leads us to the last layer.

 

3. The database layer
One of the great things about Deltek Vision is the open database on top of SQL Server. This means that it is possible to accomplish a great deal of automation using a combination of the tools in the second layer (workflows, custom fields and grids etc.) and SQL scripts.

Of course, everything done at this layer needs to take into consideration everything happening above it, because everything done down here in the database affects everything else on top.  The entire application is built on top of the database. It is in a real sense the foundation of the entire structure.

 

As an example, here is one issue and solution to ponder:

Problem in layer 1: Client was not able to store all data necessary in the experience grid in the employees infocenter.

Solution in layer 2: Create a custom grid to replace the standard grid, hide the standard grid

Solution in layers 2 and 3: Create a workflow (layer 2) to fire a stored procedure (layer 3) which synchronizes the data in the custom grid with the data in the standard grid so that the data in the standard grid is still available for other parts of the application.  Make sure the workflow fires whenever there is an update on the custom grid so the user experience is seamless (layer 1)

There you have it… layer 1,2,3 then back up again.

 

If you want me to address any particular topic or provide feedback feel free to leave a comment below.

Cheers!