BI xPress Available Best Practices

The following Best Practices are available in the BI xPress Best Practices Analyzer.

SSIS Rules

SSIS Error

  • An OLE DB Destination does not use the Fast load feature

SSIS Performance

  • Replace Aggregate transform with a GROUP BY clause in the source query
  • An OLE DB Command transformation exists
  • Replace Sort transform with an ORDER BY clause in the source query
  • Enable Fast Parse on applicable output columns for Flat File Source components
  • Lookup component is not using Full Cache mode
  • OLE DB Destination using Fast Load should disable Table Lock and Check Constraints options
  • Script component exists in a data flow
  • "SELECT *" or Table Names should not be used for retrieving data

SSIS Warning

  • Error logging is not enabled for the package

SQL Server Rules

SQL Server Warning

  • Using SELECT * can harm both performance and application stability
  • Using @@IDENTITY can lead to incorrect results if a trigger is introduced on a target table
  • Using legacy style defaults is a deprecated functionality and will be removed in a future version of SQL Server
  • Using legacy style rules is a deprecated functionality and will be removed in a future version of SQL Server
  • Do not use spaces in object names
  • Using NOLOCK hint can lead to incorrect query results if the query is operating over changing data
  • All agent jobs should be owned by SA
  • All databases should be owned by SA
  • All tables should have a clustered index in order to allow efficient storage of data
  • All tables should have a primary key to make it clear to what values the other fields in the table are related
  • The database has never been backed up
  • The database is in FULL recovery model and the transaction logs have never been backed up
  • MaxServerMemory should be set to reserve some memory for the operating system
  • MinServerMemory should be set to prevent SQL Server yielding all its memory in a memory pressure situation
  • The database has no foreign key constraints

SSAS Rules

SSAS Performance

  • Parent Child Dimensions can impact performance as aggregates can't be precalculated for intermediate levels
  • Avoid unary operators where possible. Unary operators can disable block computation in some scenarios. They also impact performance as the size of the dimension grows.
  • Measure groups with significant amounts of data should be partitioned
  • Integer key columns are more efficient during cube processing
  • Partitions should use Query Binding as the data source for partitions
  • Do not use the .Net SqlClient Data Provider to connect to a SQL Server data source

SSAS Warning

  • Avoid the use of named queries in the Data Source View. Named queries limit the flexibility for tuning dimension queries.

SSRS Rules

SSRS Warning

  • Use shared data sources for reports

SSRS Information

  • The data set has the default, automatically generated name
  • The data source has the default, automatically generated name
  • No row counts were found in the Data Flow
  • Do not leave descriptions empty
  • Naming Convention - Ensure ActiveX Script meets naming convention standards
  • Naming Convention - Ensure Aggregate meetings naming convention standards
  • Naming Convention - Ensure Analysis Services Executes DDL Task meetings naming convention
  • Naming Convention - Ensure Analysis Services Processing Task meetings naming convention standards
  • Naming Convention - Ensure Audit meetings naming convention standards
  • Naming Convention - Ensure Bulk Insert Task meetings naming convention standards
  • Naming Convention - Ensure Character Map meets naming convention standards
  • Naming Convention - Ensure Conditional Split meets naming convention standards
  • Naming Convention - Ensure Copy Column meets naming convention standards
  • Naming Convention - Ensure Data Conversion meets naming convention standards
  • Naming Convention - Ensure Data Flow Task meets naming convention standards
  • Naming Convention - Ensure Data Mining Model Training meets naming convention standards
  • Naming Convention - Ensure DataReader Destination meets naming convention standards
  • Naming Convention - Ensure DataReader Source meets naming convention standards
  • Naming Convention - Ensure Derived Column meets naming convention standards
  • Naming Convention - Ensure Excel Destination meets naming convention standards
  • Naming Convention - Ensure Excel Source meets naming convention standards
  • Naming Convention - Ensure Execute DTS 2000 Package meets naming convention standards
  • Naming Convention - Ensure Execute Package Task meets naming convention standards
  • Naming Convention - Ensure Execute Process Task meets naming convention standards
  • Naming Convention - Ensure Execute SQL Task meets naming convention standards
  • Naming Convention - Ensure Export Column meets naming convention standards
  • Naming Convention - Ensure File System Task meets naming convention standards
  • Naming Convention - Ensure Flat File Destination meets naming convention standards
  • Naming Convention - Ensure Flat File Source meets naming convention standards
  • Naming Convention - Ensure ForEach Loops meets naming convention standards
  • Naming Convention - Ensure FTP Task meets naming convention standards
  • Naming Convention - Ensure Fuzzy Grouping meets naming convention standards
  • Naming Convention - Ensure Fuzzy Lookup meets naming convention standards
  • Naming Convention - Ensure Import Column meets naming convention standards
  • Naming Convention - Ensure Lookup meets naming convention standards
  • Naming Convention - Ensure Merge Join meets naming convention standards
  • Naming Convention - Ensure Merge meets naming convention standards
  • Naming Convention - Ensure Message Queue Task meets naming convention standards
  • Naming Convention - Ensure Multicast meets naming convention standards
  • Naming Convention - Ensure OLE DB Command meets naming convention standards
  • Naming Convention - Ensure OLE DB Destination meets naming convention standards
  • Naming Convention - Ensure OLE DB Source meets naming convention standards
  • Naming Convention - Ensure Partition Processing meets naming convention standards
  • Naming Convention - Ensure Percentage Sampling meets naming convention standards
  • Naming Convention - Ensure Pivot meets naming convention standards
  • Naming Convention - Ensure Raw File Destination meets naming convention standards
  • Naming Convention - Ensure Raw File Source meets naming convention standards
  • Naming Convention - Ensure Recordset Destination meets naming convention standards
  • Naming Convention - Ensure Row Count meets naming convention standards
  • Naming Convention - Ensure Script Task or Component meets naming convention standards
  • Naming Convention - Ensure Send Mail meets naming convention standards
  • Naming Convention - Ensure Sequence Containers meets naming convention standards
  • Naming Convention - Ensure Slowly Changing Dimension meets naming convention standards
  • Naming Convention - Ensure Sort meets naming convention standards
  • Naming Convention - Ensure SQL Server Compact Destination meets naming convention standards
  • Naming Convention - Ensure Term Extraction meets naming convention standards
  • Naming Convention - Ensure Term Lookup meets naming convention standards
  • Naming Convention - Ensure Transfer Database Task meets naming convention standards
  • Naming Convention - Ensure Transfer Error Messages Task meets naming convention standards
  • Naming Convention - Ensure Transfer Jobs Task meets naming convention standards
  • Naming Convention - Ensure Transfer Logins Task meets naming convention standards
  • Naming Convention - Ensure Transfer Logins Task meets naming convention standards
  • Naming Convention - Ensure Transfer SQL Server Objects Task meets naming convention standards
  • Naming Convention - Ensure Transfer Master Stored Procedures Task meets naming convention standards
  • Naming Convention - Ensure Union All meets naming convention standards
  • Naming Convention - Ensure Web Service Task meets naming convention standards
  • Naming Convention - Ensure WMI Data Reader Task meets naming convention standards
  • Naming Convention - Ensure WMI Event Watcher Task meets naming convention standards
  • Naming Convention - Ensure XML Source meets naming convention standards
  • Naming Convention - Ensure Task meets naming convention standards
  • Naming Convention - Ensure For Loops meets naming convention standards