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
