BI xPress Best Practices Analyzer

Introduction

IconDescription
BI xPress Best Practice Analyzer IconThe Best Practices Analyzer inspects SSIS packages and displays recommendations based on best practices.

Feature Highlights

  • Investigate packages for adherence to best practices ad hoc and in batch mode.
  • Store the results of the best practices analysis within the BI xPress database for later analysis.
  • Store the results of the best practices analysis within an XML file using the command line.
  • Determine package performance issues and potential bottlenecks when using predefined best practices.

Note:  You have to use the SentryOne Workbench to analyze SSIS packages on a machine that only contains SSDT for Visual Studio 2015.

Note:  If your selected technology type doesn't display when using the BI xPress Best Practices Analyzer, your machine might not meet the system requirements for the specific provider. Open the System Requirements to make sure your system meets all of the requirements for the provider.

Analyze Best Practices

BI xPress Best Practice Analyzer

Note:  For SQL Server Data Tools 2015 and above, you can analyze your packages directly from SSDT by right clicking your package or highlighting a number of packages from the solution explorer, and then selecting Analyze for Best Practices. This option opens each package within SSDT and analyzes them.

Select Best Practices in the SentryOne Workbench to open the Best Practices Dashboard. From the dashboard you can Create / Modify Best Practices, or Analyze Best Practices. Select Analyze Best Practices to open the Connection page.

Select the Technology type to Analyze

BI xPress Best Practice Analyzer Configure Source

Select the Technology type you want to analyze from the available technology providers on the left side of the screen. Enter your selected technology credentials, and then select Next to open the Rules page.

Note:  If the source type you want to use doesn't display, you may not meet the system requirements for that provider. For more information about system requirements, see the System Requirements article.

Select Rules

BI xPress Best Practice Analyzer select Best Practices

Set the Use BI xPress database to load and save Best Practices rules and analysis results toggle to On to store the results of your analysis on your BI xPress database for review at a later time or to compare changes over time.

Note:  You can connect to any BI xPress database using either Windows Integrated security or SQL Server Authentication.

Select the rules that you want to run against your package from the rules list. Select Analyze to begin analyzing your package(s).

Note:  If you have previously saved rules, select Load and then select the desired load option to load your rules. Select Save and then select the desired save option to save your current rule set.

Best Practices Analysis Results

BI xPress Best Practice Analyzer Results

The package is analyzed using the rule set you created or selected on the rules page. The results of your analysis are displayed in the results interface. Open the Best Practice Analysis Results at any time through the Monitoring Dashboards add-in or by analyzing a new package.  

Note:  If you did not select to have your results saved in the database, the analysis will not be available after closing BI xPress.

BI xPress Best Practices Analyzer Command Line

Use the BI xPress Best Practices Analyzer command line tool to analyze an SSIS Package against a personal rule set, or the shared rule set specified within the BI xPress Database. The BI xPress Best Practice Analyzer command line tool uses the PWBestPractices.exe located within the SentryOne Workbench directory.

Note:  The SentryOne Workbench directory is set to the following location by default:

  • C:\Program Files (x86)\Pragmatic Works\Pragmatic Workbench\PWBestPractices.exe

Any framework (such as Scheduled Tasks, TFS Build Server, Microsoft Systems Center) that is capable of starting an application and passing parameters using command line arguments is capable of using the Best Practice Analyzer command line tool to analyze personal or shared rule sets against SSIS Package(s).

PWBestPractices.exe {<Help> | <Items>} [<Rule Set Type>] [<Connection Information>] [<Options>]

Switch Arguments

Use -- followed immediately by the parameter switch or followed by the short form version of the parameter switch to introduce a parameter argument to the PWBestPractices.exe. If the switch requires a value, use an (equal) to separate the parameter argument. Use "" (double-quotes) around the parameter value if the value contains a space or special character(s). The following is a list of parameter arguements and their purpose in BI xPress command line:

Argument NameDescriptionSwitchShort Form
HelpDisplays the help message that gives you further clarification on using the Best Practice Analyzer's Command Line tool.--HELP-H or -?
Item Value

The fully qualified location(s) of the package(s). This must include the full package name including the file extension.

If specifying multiple targets, all items must be separated using spaces in between values, for example:

PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\ChildPackages\ETLPackage.dtsx"

"values" [...n]"values" [...n]
Item VersionSpecifies the SSIS Version of all packages you are attempting to analyze.  You can specify the following values:--VERSION="value"-N="value"
Shared Rule SetSpecifies that you want to use the shared rule set when analyzing the targeted SSIS Package. If you are using the Shared rule set switch, connection information switches are required.--SHARED-S
Personal Rule SetSpecifies that you want to use a personal rule set stored on the file system. If you are using the Personal rule set, the Shared rule set option cannot be specified.--PERSONAL-P
Connection Information Connection StringUsed along side the Shared rule set option to specify a valid SQL Server connection string that connects to the BI xPress database with the shared rule set.--CONNECTIONSTRING="value"-C="value"
Connection Information OutputUsed along side the Shared rule set option to specify that you want to output the results to the BI xPress database specified within the Connection String option. Either the XMLOutput or DBOutput option must be specified. If using the DBOutput option, the XMLOutput option cannot be specified and the Connection String option is required.--DBOUTPUT-D
Options PasswordThe package password to apply to all SSIS packages specified.--PASSWORD="value"-W="value"
Options VerboseThe Verbose switch provides you with additional information within the specified output option.--VERBOSE-V
Options XML outputSpecies the file path location where the Best Practices Command Line tool outputs the results. The XMLOutput or DBOutput option must be specified. If using the XMLOutput option, the DBOutput option cannot be specified.--XMLOUTPUT="value"-X="value"

Command Line Examples

This example analyzes the MasterPackage.dtsx file against the Shared rules set within the BI xPress database on localhost and outputs the results to the same database.

PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" --VERSION="2012" --SHARED --CONNECTIONSTRING="Data Source=LOCALHOST;Initial Catalog=BIxPress;Integrated Security=True;" --DBOUTPUT

This example analyzes the MasterPackage.dtsx file against the user's personal rule set and outputs the results to the MasterPackageResults.xml file with verbose output.

PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" --VERSION="2005" -PERSONAL -XMLOUTPUT="C:\SSIS\Analysis\MasterPackageResults.xml" --VERBOSE

This example applies the password PackagePassword to both MasterPackage.dtsx and ETLPackage.dts and then analyzes them against the user's personal rule set and outputs the results to the ETLResults.xml file with verbose output.

PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\Child\ETLPackage.dtsx" -N="2014" -P -W="PackagePassword" -N="2012" -X="C:\SSIS\Analysis\ETLResults.xml"

Create / Modify Best Practices

BI xPress Best Practices Dashboard

Select Best Practices in the SentryOne Workbench to open the Best Practices Dashboard. From the dashboard you can Create / Modify Best Practices, or Analyze Best Practices. Select Create / Modify Best Practices to open the User Defined Best Practices page.

User Defined Best Practices

BI xPress User Defined Best Practices Add a new Rule
BI xPress User Defined Best Practices New Rule Form

Use the User Defined Best Practices page to manage existing rules, and to create new rules. Select the button to add a new rule, and complete the following fields: 

Rule OptionDescription
Rule NameEnter a name for your rule.

Note:  When you save your rule for the first time, the rule name will be used as a suggestion for the file name. If the rule name has invalid characters for the file name, you will need to use a different file name.
AuthorEnter the name of the user or team that created the rule.
Reference URLEnter a link to an external or internal website that includes detailed instructions for resolving the best practice violation.
Date Created

The date the rule was created.

Note:  This field populates automatically.

SeveritySelect rule severity from the following options:
  • Error
  • Warning
  • Informational
  • Performance

Note:  The rule severity dictates the category where the rule displays in the Best Practices Analyzer.

MessageEnter a message for the rule. The message that displays in the Best Practice Analyzer's rule selection page.
RecommendationEnter a description for correcting the violation. The Recommendation displays as a tool tip on the Best Practice Analyzer's Rule Selection page, and as a column on the Results page.
Object Type FiltersEnter the desired filters. The filters that you select display after building a path evaluator.
Path EvaluatorSelect a path evaluator.

Note:  Select the Path Builder button to build a path.

Once you have completed the rule form, select the Save toolbar button to save your rule. 

Note:  Manage the rules you have created from the User Defined Best Practices page. Deleting a rule from this page removes it from the rules list, but does not delete it from the file system. 

If you saved a rule to the Shared Rule set, the rule is saved to your BI xPress database.

Using the Path Builder

The Path Builder creates the actual definition for your best practice. The Path Builder uses the SentryOne Workbench Path language to create a true or false path. The path can be analyzed in any SSIS package and verify that the created rule applies. In the following example, we create a rule that checks to make sure the user did not enter the value "Description" for a connection's Description property:

1. Open the User-Defined Best Practices page (Select Best Practices > Create/Modify Best Practices), and then select Path Builder to open the Path Builder page. BI xPress User Defined Best Practices Path Builder

Note:  The text box at the top contains the field for your path. You have a list of operators, selectors, and other commands to choose from. To reset your selections, select Reset.

2. Select a technology type from the provider list to open that technology's configuration options. Enter the applicable information to specify your Solution item(s). Select Next to continue.BI xPress User Defined Best Practices Path Builder select Metabase

3. Select the desired Object Type Filter(s) and then select the type of rule you want to create in the Object BrowserBI xPress User Defined Best Practices Path Builder select filter

Note:  Making a selection in the Object Browser section populates the middle field with that object's properties and path. In this example, we have selected the package itself from the Object Type Filters and a Flat File Connection from the Object Browser. This populates the middle pane with the path and properties of the connection manager.

4. Select the property that you want to check to generate the path. Drag the path into the Path Text Box and remove the name. BI xPress User Defined Best Practices Path Builder select property

Note:  In this example, we selected the Description property.

5. Select Preview to make sure that your rule validates against the current package.BI xPress User Defined Best Practices Path Builder Preview

Note:  Your rule should validate true for it to throw a violation of the best practice. For our purposes, our rule looks for connections where the description has a value of Description.

6. Select the back arrow to review your Object Type Filter and Rule Path in the User-Defined Best Practice creator.BI xPress User Defined Best Practices Path Builder Rule