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