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