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

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.

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.

Behind the scenes with SSRS and Vision, Part 1

In my previous post, I documented how Deltek Vision integrated with Actuate through Actuate's web service API. As many of you are happy to know, Deltek Vision 6.1 now uses SQL Server Reporting Services as it's reporting platform. My hope with this article is to give you a brief insight into the technical workings between Vision and SSRS.

The first thing I always do when investigating a new feature available from the Vision menu, is I look at the menu in design mode to see what's being called from behind the scenes.

Click on the image to take a closer look. BTW, The A/R reporting in Vision is my personal favorite.



You'll notice within the actions box that the open action Deltek.Vision.Report.Client.VisionOpenReport is called. The Deltek.Vision.Report.Client.VisionOpenReport Object and it's methods are defined within the Deltek.Vision.Report.Client.dll. The dll is probably downloaded when you log into Vision for the first time as you see something similar to the images below.



Or...



Because Deltek has create a reporting hierarchy that closely mimics the reporting menu structure, it's easy to guess where the Accounts Receivable files are. While searching for those files, you'll be happy to realize that the reporting folders contain actual Visual Studio .Net solutions. No more actuate report designer. Yeah!

If you have trouble locating the solution, do a search for the file Deltek.Vision.AccountsReceivable.Reports.sln on your Deltek Vision server or on your local machine.

Because the A/R reporting in Vision is my favorite, naturally the AR Ledger report is my favorite report. So I wanted to take a closer look at the rdl file for the AR Ledger report, and I looked through the XML definition.

You'll notice two more calls to long object names.
Deltek.Vision.ReportingAccountsReceivable.Client.dlgRepOpt_ARLedger and
Deltek.Vision.ReportingAccountsReceivable.Server.ARLedgerBO.

In my next post, I'll discuss how the report file interacts with these objects within their respective dll's.

Top questions every DBA should ask when asked for data.

As DBA's, we are responsible for the full life of the data at our hands. We need to make sure data is backed up, readily available in an acceptable format, in a timely fashion. Even when data is deleted, it's our responsibility to make sure we do not need it and we have back ups of the data just in case.

Another big job is handling requests from leaders who like to see their numbers in different ways.

A typical question a DBA might encounter is, Can I get the revenue for last fiscal year?

Below are a list of questions I like to ask when handling requests for data.

1. Is the data going to be used internally, or externally?
This is very important as to how the data might be used. Is this for a Survey that will ultimately decide an industry ranking?

2. How is the data going to be used?
Is this for an audit? Is this for projections? Is this for performance evaluations? This can help you determine how accurate the data needs to be.

3. Who is asking you for the data?
Sometimes it's a good idea to get everyone in a room to talk about what exact data is needed. I've seen so many times how an original request evolves after you have talked to everyone involved.

4. Is someone asking the person asking you?
Again, keep asking until there isn't anyone else involved with your particular data question at hand. Plus, it's always nice for your boss’s boss to see who's actually doing the work.

5. Is this a request we have fulfilled before?
No reason to re-invent the wheel. Consistency is a good thing.

6. If so, who gave you the data last time?
Go talk to that person and find out what issues they had. Ask if they were asked to re-run the numbers for any given reason. Ask him or her for the SQL they used.

7. Can I have a copy of the data provided to you last time for comparison?
This will give you the ability to provide the data in the same format and you can use this to help ballpark your accuracy.

8. Please understand, even though the bottom line numbers should be the same, some project attributes may have changed since last time the data was given to you.
Depending on how the data is aggregated, subtotals can change over time. Project Attributes such as service, sector, industry or studio can change.

9. What kind of revenue do you want? Gross? Fee?
Going back to question 1, if the request is external, maybe we want to use the larger number.

10. Are there any projects we should exclude? Highlight?
Make sure that you aren't including system level projects, or projects that may have write off's.

11. Are there any privacy issues with providing this data?
Does the data expose a confidential project or relationship with a client?

12. When is the data needed by you? When is the data needed for your deadline?
Hitting the first deadline allows some back and forth between the others involved so that you meet the end deadline.

Deltek Vision Actuate Reporting

I think we were all excited when Deltek announced that Vision 6.1 would use SSRS as it's reporting technology. But I was also frustrated because I had worked so hard to understand the Actuate reporting framework and had even dug deep into the way Vision integrated with the Actuate Reporting Server. So with this post, I wanted to focus on how Vision works with Actuate at the code level before following up with a post celebrating how Vision works with SSRS.

From a high level, the Vision application goes through the following steps when calling a report.
1) Construct a Vision State Object
2) Instantiate a Vision Authenticated Vision Object
3) Consume the Actuate Web Service
4) Create an XML String that defines the report to be called along with column and grouping definition
5) Define the output of the Actuate report.
6) Pass the object returned from actuate to the Vision Actuate Report Viewer.

1) and 2) Deltek.Vision.Ancestors
The vision state object is the object Deltek carries around in memory after the authentication process. The authentication process is handled by through a call to the GetNewSessionID() method on the Vision State object.

3) Actuate Web Service
The next part of the process is that the Vision application consumes the Actuate Web Service. You can find the definition for the actuate web service by browsing the actuate web server URL, http://servername:portnumber/wsdl

Below is a screen shot if I were trying to add the Actuate web service as a web reference to a web project within Visual Studio.




So a new Actuate Web Service API class is constructed. Vision defines a couple of properties such as Time out settings, header definition, Actuate report server URL, Locale and the targeted Actuate Report Volume.

Next Vision creates an Actuate loginRequest and loginResponse and populates various properties required for authentication to the actuate report server such as the Actuate ID, the Actuate Admin password, the Actuate Report Volume..

Next Vision encrypts the active Portal Session information and the report Session Portal Information.

4) Create XML string defining report, column and grouping information.

Vision internally builds an xml string that either contains xml stored in the database as your favorite or information from your setup within the dialogue box that contains information such as which period to run it for and columns to include and grouping.

You can find the xml string used by reports run in your system under utilities > report administrator > report logging

An Report Request and Response object is created properties about how Actuate should handle the request and ultimately how the response should be handed back. Things like output type, file archival rules, progressive viewing and most importantly, the report options for the report being requested at hand. It's important to note that the report options will be defined and ultimately part of the xml string that is passed between Vision and Actuate.

5) Define the output of the Actuate report.
Will the report be an excel file, a pdf file, or an actuate report file?

6) Pass the object returned from actuate to the Vision Actuate Report Viewer.

The response object is internally passed to a viewing page within the Vision code structure and that is where the actuate report is rendered.

Now that we understand the connection between Vision and Actuate, my next post will discuss the integration between vision and ssrs. You can't understand where you are headed if you don't know where you came from.

Enjoy...

No Clustered Index for LD?

If you have ever looked into the performance of vision, more than likely you have at some point in time taken a look at the LD table.

I was looking at the indexes for the LD table the other day, and noticed that there was no clustered index defined for LD. At first I found this troubling, but then thought about how clustered indexes work.

To get you started, the query below will give you a list of indexes that exist on LD. Unless you have added a clustered index yourself, you will see that there is no clustered index defined.

select * from sys.indexes where object_id IN(select object_id from sys.objects where name = 'ld')

A clustered index actually contains the data in order at the leaf level. Imagine if a clustered index existed, and that the clustered index was based on the columns wbs1, wbs2 and wbs3. That's seems reasonable on a labor table. Now imagine that you have the following ranges of projects in your databases starting a project number 1 (0.000.001) through project number 10000. (0.010.000).

So let's say your Deltek Vision administrator posts some time keeper data and several of your employees worked on project 0.000.500. Because a clustered index stores data in order at the leaf level, SQL Server would have the task of page splitting and moving data to accommodate the insertion of data after project 0.000.499. This would cause alot of overhead for the SQL Server. And due to the fact that every single bucket on a timekeeper timesheet becomes an individual labor detail record, this would cause the posting of labor to take much longer. In addition, labor transfers and adjustments would also take much longer during interactive billing.

After talking with another fellow dba, he reminded that many times Deltek's service packs or upgrade database scripts contain the creation of a clustered index and then the dropping of the index immediately following. Consider the following from a recent upgrade SQL file from a Deltek vision upgrade.

Print ' LD'
create clustered index junk on ld(wbs1, wbs2, wbs3)
go
drop index ld.junk
go

The above script is taking the LD table, which is a heap, and is applying a clustered index to it which forces an ordered storage of the data. The creation process removes empty space filled by forward pointers. The clustered index is then dropped.

Deltek probably at one point had a clustered index on the LD table, removed it due to performance issues. Then they probably added the above script to upgrades to help keep the LD table in working order.


Enjoy....

Time to defrag those Vision Indexes

Below is a script that can help you determine if you need to defrag your vision indexes. Enjoy...

//As usual, I provide no guarantees and cannot be liable for the code listed below.
//FYI, DO NOT RUN THIS SCRIPT DURING PEAK HOURS

DECLARE @VisionDatabaseID int
SET @VisionDatabaseID = YOUR_VISION_DATABASE_ID_HERE

SELECT
obj.Name as ObjectName,
idx.name as IndexName,
idx.type_desc as IndexType,
idxStats.Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (@VisionDatabaseID, NULL,NULL, NULL, NULL) AS idxStats
INNER JOIN sys.indexes idx WITH(NOLOCK) ON idxStats.Object_id = idx.Object_id
INNER JOIN sys.objects obj on idx.object_id = obj.object_ID AND idxStats.Index_id = idx.Index_id
WHERE Avg_Fragmentation_In_Percent >= 30
AND idx.[name] IS NOT NULL
order by idxStats.Avg_Fragmentation_In_Percent desc

Vision's Impact on TempDB

Below is a script that you can run throughout the day to understand your Deltek Vision environment's impact on the tempdb database.

//As usual, I provide no guarantees and cannot be liable for the code listed below.

select
tskUse.session_id,
tskUse.request_id,
ses.host_name,
ses.program_name,
req.cpu_time,
req.reads,
req.writes,
req.granted_query_memory*8/104 as granted_query_memoryMB,
tskUse.task_allocation_count*8/1024 as task_allocation_countMB,
tskUse.task_deallocation_count*8/1024 as task_deallocation_countMB,
(SELECT text FROM sys.dm_exec_sql_text(req.sql_handle)) AS TheQuery
from ( Select session_id, request_id,
sum( internal_objects_alloc_page_count +
user_objects_alloc_page_count) as task_allocation_count,
sum (internal_objects_dealloc_page_count +
user_objects_dealloc_page_count) as task_deallocation_count
from sys.dm_db_task_space_usage
where session_id > 50
group by session_id, request_id) as tskUse
inner join sys.dm_exec_requests as req on tskUse.session_id = req.session_id and tskUse.request_id = req.request_id
inner join sys.dm_exec_sessions as ses on req.session_id = ses.session_id
where req.database_id IN(select database_id from sys.databases where name = 'Vision')
order by tskUse.task_allocation_count DESC

10IT014: Senior Database Engineer

Looks like Deltek is looking for a Senior Database Engineer.

Returning values from mulitple records as a single column

There are times where you want to rollup single column data from mulitple records and present as a single column as part of a result set. Sure, Pivot does essentially the same thing, but you must know the values of the data and the pivot returns multiple columns, not mutliple values as one column.

The solution below comes in very handy for reporting purposes where you really want 1 line of data with associated with mulitple columns.

Enjoy..

DECLARE @StateList VARCHAR(MAX), @ClientID varchar(32)
SET @ClientID = 'YOUR_CLIENT_ID_HERE'
SELECT  @StateList = COALESCE(@StateList + ' , ','') + ClientStateList.state 
from
 (select distinct state 
  from claddress 
     where clientid = @ClientID) as ClientStateList

select name, @StateList as USLocations from cl where ClientID = @ClientID

Sizing up your large vision tables.

Below is a script that will sniff out how large your vision tables are. For simplicity sake, I've assumed that the largest tables in your vision database probably are the LD, TKDetail, Audittrail, ReportLog and ledger tables. You can compare the output produced by the sql below to the results of sp_spaceused to understand the magnitude of your largest tables. Enjoy...

SELECT
o.name,
o.Type,
s.name as SchemaName,
SUM(reserved_page_count) * 8/1024 as ReservedPagesMB,
SUM(used_page_count) * 8/1024 as UsedPagesMB,
SUM( CASE
     WHEN (index_id < 2) THEN (in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
     ELSE lob_used_page_count + row_overflow_used_page_count
     END)*8/1024 as TotalPagesMB,
SUM ( CASE
      WHEN (index_id < 2) THEN row_count
      ELSE 0
      END) as recordCount,
SUM( CASE 
     WHEN used_page_count > (CASE
                             WHEN (index_id < 2)
                             THEN ( in_row_data_page_count +
                                    lob_used_page_count +
                                    row_overflow_used_page_count)
                             ELSE lob_used_page_count + 
                             row_overflow_used_page_count END)
THEN used_page_count - (CASE
                        WHEN (index_id < 2)
                        THEN ( in_row_data_page_count +
                               lob_used_page_count +
                               row_overflow_used_page_count)
                        ELSE lob_used_page_count + 
                        row_overflow_used_page_count
                        END) ELSE 0 END)*8/1024 as IndexSizeMB,
SUM(CASE 
    WHEN reserved_page_count > used_page_count
    THEN reserved_page_count - used_page_count
    ELSE 0 END) *8/1024 as UnusedMB
FROM sys.dm_db_partition_stats dps
inner join sys.objects o on dps.object_ID = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE o.name IN('LD','TKDetail','LedgerAR','LedgerMISC','LedgerEX','LedgerAP','ReportLog','AuditTrail')
group by o.name,o.Type, s.name

Deltek Consulting Companies

Below is a list of Deltek Consulting Companies that I have come across while looking for external resources.

Incortech
http://deltekvisionsoftware.com/

Central Consulting Group
http://www.centralconsultinggroup.com/

Innovative Solutions Group
http://www.isgltd.com/

KPI Insight Consulting
http://www.kpi-insight.com/

ITB Consulting
http://www.itbconsultinginc.com/

Avanced Enterprise Systems
http://www.aesystems.net/

Strategies Group
http://www.strategiesgroup.com/

Loren Saunders
http://saundersconsultinggroup.com/deltek.html

Hirsch Consulting
http://www.hirschtech.com/DeltekVision.asp

Silver Edge
http://www.silveredge.com/

Ideal Solutions Group
http://www.idealsolutionsgroup.com/deltek.html

Computer Analytics
http://www.cacorp.com/IT%20Consulting_deltek.html

Keystone Business Solutions
http://keystonebusinessconsultants.com/service01a.html

Part 1. Adding your custom fields to Deltek's cubes

This is Part 1 of a 2 part series.

In this installment, I will simply list out, at a high level, the steps required to to adding custom project columns (projectcustomtabfields) to deltek's out of the box data cubes in Analysis services.

This article assumes you have installed the analysis services from the deltek vision utilities. You can contact me if you have any questions...

In addition, I always recommend copying exisiting environments that actually work so that you have a working copy to refer back to when trouble shooting during this exercise.

$COMPANYNAME$ = Your company name

1. Copy analysis folder from installdrive:\program files\deltek\analysis to
installdrive:\program files\deltek\$COMPANYNAME$analysis\

2. Rename solution and project within ETL folder within the $COMPANYNAME$analysis folder from Vision ETL.* to $COMPANYNAME$Vision ETL.*

3. Rename file dim_Projects.dtsx to dim_$COMPANYNAME$Projects.dtsx.

4. Adjust the sql statement used by dim_$COMPANYNAME$Projects.dtsx.

5. Alter the project table and the PRInternal table in your dw database and add the fields you want.


6. Open dim_$COMPANYNAME$Projects.dtsx.

7. Right click the "DFT - LoadPRInternal" within the Load Internal Tables group, and then click edit. Then click the "GET PR Data" icon and click edit.

8. You will edit the sql statement loaded from step 7 and add the appropriate custom project fields from a table, such as ProjectCustomTabFields.

9. In the Load Project Dimension group, right click the "DFT - Load Projects" icon and click edit. Then Right Click the "Get PR Internal Data" icon and click edit.

10. You will edit the sql statement loaded from step 9 and add the appropriate custom project fields from a table, such as ProjectCustomTabFields.

11. Build solution.

12. Backup current datacube database.

13. Restore it with another name, maybe using the $COMPANYNAME$ naming convention

14. Open up the Analysis services project in visual studio 2005.

15. Changed datasources to point at your databases.

Sorry for the cliffhanger folks, but we will start backup again with Part 2 in a few days.

Enjoy....

Deltek Invisible CRM Partnership

It will be interesting to see how this works.

Deltek and InvisibleCRM Partner to Enhance Microsoft Outlook Integration with Deltek Vision® and GovWin®

http://www.businesswire.com/news/home/20100120005975/en/Deltek-InvisibleCRM-Partner-Enhance-Microsoft-Outlook-Integration

Deltek Vision tricks for programmers: Deltek Vision Workflow Dependencies

Deltek Vision tricks for programmers: Deltek Vision Workflow Dependencies

Extracting Information from the Newforma Database (Part One)

Extracting Information from the Newforma Database (Part One)

Posted using ShareThis

Extracting Information from the Newforma Database (Part Two)

Extracting Information from the Newforma Database (Part Two)

Posted using ShareThis

Custom Database Objects or Out of the Box Deltek Objects?

I was running through a list of stored procedures and tables in our Vision database this morning, and I was trying to find some of our custom objects. Like many Deltek clients, we have built custom applications that run within Vision and or use the Vision dataset in a new/better way. While looking around, I got lost very quickly.

If you have many custom tables/stored procedures or are planning on creating custom apps within your Vision database, I would suggest that you consider the idea of creating a seperate schema for those objects to live in.

This is greatly helpful in many ways. A seperate scheman allows you to quickly see which database objects are out of the box and provided by Detlek and which ones have been created specifically for your instance/company. Furthermore, depending on how granular you wish to get, you can create schemas for different applications that run within you Vision database, or you can come up with a naming convention for you tables and stored procedures that furthere deliniate there purpose.

Enjoy......

Where did those Labor records come from?

The other day I was chasing down an issue on a Labor Detail report. There was a transaction on the report that I could not find in the LD table. Futhermore, there was no correlating transaction in the TKDetail table either. Lastly, I checked to make sure the record was not part of a timesheet data entry file.

I was working with one of our accountants while discussing the issue, and found how the entry made it's way onto the labor detail report. In Interactive Billing, there's a screen where you can "create" labor records. These records are stored in the BILD table in Vision. The labor detail report queries the BILD table along with the LD table.

If you ever run into this issue after checking, LD, TKDetail, TSDetail, try looking through the BILD table.

Enjoy.....

Vision Database Size

A few years ago while planning for a new database server, our team realized that a large chunk of our vision database consisted the text data type.

So we began investigating which tables accounted for the largest part of the database size, and narrowed down the folling tables: LD, TKDETAIL, the 4 Ledger Tables, AuditTrail and the ReportLog table. The reportlog was the table containing the most "text" datatype and by far was the larges.

Obviously, LD, TKDetail and Ledger Tables took up a large portion of our database, but we new we couldn't prune records from those tables. So, we decided to keep a certain amount of audit information and a certain amount of reporting history in the reportlog table and removed the rest. Removing the old audit information and reporting history drastically removed the amount of space the database was taking up.


Enjoy....

Profiling Your Vision Database

Before we start the article, I just want to point out that profiling or tracing your database server can impact performance.....


If you have ever let your SQL Profiler run on your Vision Database Server, you've seen thousands of Events recorded in just a few seconds. Depending on what problem you are trying to solve, you may want to weed out some of the events recorded.

One simple way is to filter your profile by ApplicationName. Depending on your configuration, Deltek appears to have four types of ApplicationNames using the database.

1) Deltek Vision (ProcessServer)
2) Deltek Vision (default)
3) .Net SqlClient Data Provider
4) Deltek Vision (Reporting)

When starting up a new profiler session, simply filter on the Column ApplicationName and list one of the above entries. This will help get you started in narrowing what kind of database activity you want to see.

Enjoy.....

Combining those Ledger Tables

A way to make your general ledger investigating a little easier, try combining your ledger tables into a single view. The below code (no guarantees with no liabilities) gives you the ability to inspect your entire ledger with a single select statement, instead of writing the same queries against each table.

//I provide no guarantees and cannot be liable for the code listed below.
create view LedgerTables
AS

select 'LedgerAR' as TableType, * from LedgerAR
UNION ALL
select 'LedgerMISC' as TableType, * from LedgerMISC
UNION ALL
select 'LedgerEX' as TableType, * from LedgerEX
UNION ALL
select 'LedgerAP' as TableType, * from LedgerAP

GO

select * from LedgerTables where //your clauses here.

Enjoy....

Vision XML Stored in the database

As many of us know, The vision application stores xml strings in the database as way to record parameters and settings. Many times, these strings are tens of thousands of characters long and are hard to read from a sql client tool interface.In addition, the strings sometimes exceed the character count limitation that many SQL client tools impose and you cannot fully view the contents of the xml.

To help parse out the xml in a usable format, I've written the query (no guarantees with no liabilities) below as an example of which parses the report name and the where clause out of the report log table and displays it.

select ReportLog.Options.query( N'/options/reportName/text()') as ReportName,
ReportLog.Options.query( N'/options/where/text()') as WhereClause
from
(
select Convert(XML,Options) as Options from reportlog
) as ReportLog


For those of you who are working within a Multicurrency environment, you can also query (no guarantees with no liabilities) the ledger tables to parse out information about rates and dates used on Given transactions



select LedgerAR.wbs1,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate/text()') as ExchgRate,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate/text()') as ExchgDate,
LedgerAR.ExchangeInfo.query( N'/parms/OvrDateUsed/text()') as OvrDateUsed,
LedgerAR.ExchangeInfo.query( N'/parms/OvrAmtUsed/text()') as OvrAmtUsed,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed/text()') as InvUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriUsed/text()') as TriUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriCurCode/text()') as TriCurCode,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate2/text()') as ExchgRate2,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate2/text()') as ExchgDate2,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed2/text()') as InvUsed2

FROM
( select wbs1,
Convert(xml,ExchangeInfo) as ExchangeInfo
from LedgerAR
where ExchangeInfo is not null
) as LedgerAR

The above queries (no guarantees with no liabilities) should help you get started in dealing with long xml strings stored in the vision database.

Enjoy......