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.

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

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.