1. OLTP database
- Use physical table instead of physical view to build your dimension
- Set index on these tables the following articles is very useful to chose which column indexing (http://sqlblogcasts.com/blogs/ssqanet/archive/2008/02/19/sql-server-2005-index-optimization-best-practices.aspx)
2. OLAP database
- for query performance, use user defined hierarchy as much as possible except if you use custom rollup. In this case, Parent-Child hierarchies are more performant.
- Case When mdx has been more performant than IIF in some case. When I used the Case When in a calculation of my cubes the time query was faster. Personaly, I don't hesitate to compare the response time of my query with an IIF statement and with a case when in the cube calculations part.
- Decrease the number of cube calculation. Cause these calculations are evaluated on the fly.
In plus of my best practices, you can find more on the microsoft website (http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas2005.mspx)
No comments:
Post a Comment