10IT014: Senior Database Engineer

Looks like Deltek is looking for a Senior Database Engineer.

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

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

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

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