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