PragmaticWorks Software

BI xPress Value Proposition

www.Pragmaticworks.com

Manage SSIS migration right from the start and standardize without the coding. BI xPress applies rules and best practices across

all of your SSIS migration packages allowing for easy, deployments, auditing, monitoring, and reporting on SSIS migration.

• Productive SSIS developers

• Operational Efficiency ($$)

• Consistent SSIS Packages

• Customizability

SSIS Developer Tool: Value Proposition

• Produce better SSIS packages in much less time:

▫ Apply Best Practice Auditing/Monitoring/Notification framework and reports:

Zero coding. Just push a button

Consistent, global framework

Flexibility for each team and application

▫ Reduce need to write custom script:

Prebuilt Script snippets (just drop them in)

Prebuilt Expressions (just drop them in)

Variable/expression edits take 1 step instead of 4 steps

▫ Reduce package maintenance time and mistakes:

“Highlighter” for expressions and config files brings hidden logic to the surface so maintainer sees the whole picture as soon as (s)he opens the package in BIDS

Productivity: Package Development

• The charts and table on the next 3 slides compare the SSIS developer time needed to create and maintain an 8 step SSIS package that performs the following functions:

▫ Check for existence of 10 flat files

▫ Loop through the 10 flat files

Decompress flat file to temp file

Load flat file into a target SQL table set

Delete decompressed temp file

Archive flat file with date appended to file name

▫ Log the appropriate package task and dataflow execution metrics for monitoring purposes

▫ Notify appropriate support personnel via email if package fails

Productivity: Package Creation Example

Time to create SSIS package from a Template

0

1

1

2

2

3

3 2.7

0.3

Ho

ur

sWithoutBI xPress

WithBI xPress

FTE hours x Hourly rate

= Cost Savings per

package developed

Time to create SSIS package from Scratch

0

2

4

6

8

10

12

14 13.3

1.1

Ho

ur

sWithoutBI xPress

WithBI xPressFTE hours x Hourly rate =

Cost Savings per package

developed

(example assumes a developer with mid-level SSIS experience)

Productivity: Package Creation Example (cont)Step Without BI

xPress from scratch

With BI xPress using Package Wizard

Without BI xPress, using saved package

With BI xPress, using Package Wizard Template

Comment

Create variables with expressions

10 minutes 1 minute 0 minutes 0 minutes Use BI xPress variable editor to drop in expressions. With Xpress template it’s automatic.

Write script code to check for file existence

1 hours 1 minute 0 minutes 0 minutes Just drop in script snippets that come with BI xPRess. With Xpress template it’s automatic.

Write script code to decompress file

1 hours 1 minute 0 minutes 0 minutes Just drop in script snippets that come with BI xPRess. With Xpress template it’s automatic.

Create data flow to move data from flat file to SQL table(s)

1 hour 30 minutes 0 minutes 0 minutes With Xpress template it’s automatic.

Add row counts to data flow 10 minutes 0 minutes 0 minutes 0 minutes Included in BI xPress Notification framework. With Xpress template it’s automatic.

Write expressions to append date to file name

1 hour 1 minute 0 minutes 0 minutes Just drop in expressions that come with BI xPress. With Xpress template it’s automatic.

Write script code to delete temp file and archive flat file

1 hour 1 minute 0 minutes 0 minutes Just drop in script snippets that come with BI xPRess. With Xpress template it’s automatic.

Write stored procs/reports and create multiple event handlers for auditing/monitoring package execution

3 hours (for event handlers)

1 minute 0 minutes 0 minutes Stored Procs & Reports are built into BI xPress. (w/o BI xPRess: Time varies based on task counts. 40 hours would be needed to write Reports and Stored Procs). With Xpress template it’s automatic.

Code the notification logic and events for handling package execution anomalies

3 hours 1 minute 0 minutes 0 minutes Built into BI xPress Notification framework. With Xpress template it’s automatic.

Set Template or saved package parameter values

n/a n/a 30 minutes 8 minutes Xpress template prompts for all parameters. When using a saved package you must find and edit all parameters

Develop complete understanding of package logic (expressions and config files) during maintenance phase

1 hour 1 minute 60 minutes 1 minute BI xPress provides top level visibility of all expressions and config files.

Validate then Deploy package & config files to other servers

30 minutes 1 minute 30 minutes 1 minute No need to edit config file path inside package during deployment with BI xPress

Testing 1 hour 30 minutes 40 minutes 10 minutes BI xPress snippets and frameworks are all pre-tested. Assumes saved package and template are well tested.

Totals 13.3 hours 1.1 hours 2 hours 40 mins 20 minutes

• The Auditing/Monitoring/Notification framework logs the results every time an SSIS package runs right down to the task level. Reports graph the package (and task) execution history for metrics such as :

▫ execution duration,

▫ number of records processed

▫ warnings, errors, etc..

Allowing DBA’s and developers to easily spot execution anomalies and debug problems sooner and faster.

• Notification framework sends email alerts to a list of people when packages fail to execute correctly.

"With this framework, we can resolve SSIS execution issues in less than half the time it used to take us." DBA, University of Illinois Foundation

Operational Efficiency when packages run in production

Example enterprise with 100 SSIS Packages:

▫ 5 run hourly (24x7) => 3600 executions per month +

▫ 70 run nightly => 2100 executions per month +

▫ 15 run weekly => 65 execution per month +

▫ 10 run monthly => 10 executions per month

▫ = 5775 Total package executions per month

▫ 1% Failure rate => 57 package failures per month to be researched

▫ 30 minute research time saved by using BI xPress Auditing/Monitoring reports

▫ 57 x 0.5 hrs x $70 labor rate= $2,021 Labor savings per month

▫ x 12 months = $24,255 labor savings per year

▫ ROI – Less than 1 month for the cost of 1 Developer @ $795

Operational Efficiency: Example Scenario

• Download trial software at www.Pragmaticworks.com

• Contact: Thomas Duclos

▫ Direct Dial: 617-326-3851

▫ thomasd@Pragmaticworks.com

Next Steps