General Guidance / Best Practices for Analysis Services Design?

[G.OKeefe]

When designing our Microsoft BI environment (including a SQL Server 2005 Data Warehouse, Analysis Services 2005 and Reporting Services 2005, for the most part), should we focus more on optimizing processing performance or query performance?

I’m hearing a couple of different things from some consultants we’ve been "auditioning," and, as they seem a little light (maybe it’s the ever present “language barrier …”), I was hoping to be given a little guidance in the Best Practice considerations.

Any quick and dirty advice?

TIA,

George

[rmiao]

Don't think it's one or the other situation, you need to look at processing window and query requirement.

In our case, raw data are available around 2am and users start to work at 8am. So we have to do everything to ensure whole processing complete in this window. From user side, requirement is open report in less than 10 seconds. Again, we have to do whatever to meet that requirement.

[Island1]

As my colleague states above, both are important.

My advice: Design first to optimize for query performance, with a secondary focus on processing performance (especially when there is apparent conflict between choices due to time, resources, etc., but in general, as well).

My reasoning: Processing performance issues can often be resolved / mitigated through tuning in the underlying relational layer – another opportunity to optimize processing performance lies within the AS / OLAP layer on the “induction end:” you can eliminate joins in the cube processing statement, etc.

But if you keep in mind the primary objective of an OLAP system - to serve decision-supporting (and other) answers to information consumers rapidly and accurately - you can easily see that query performance is of high importance.

Good Luck.

Bill