SSAS member performance metrics

Question

I am wondering if there are any performance implications associated with adding a large number of calculated elements to my cube. On the one hand, it’s nice to have something specific, located centrally, tested and available for use from any client that does not support MDX. On the other hand, some of these members that I add may not be used very often, so I could just insert them into one or two reports that they might need.

In addition to the clutter associated with redundant members, should I keep the number of settlement members as low as possible? Will the cube processing time increase more? Will they slow down queries that do not use these calculated members?

Answer 1

Calculated elements have practically no effect on processing or on other queries. Add as much as you want!

The reason is that they are simply defined on the cube, but are actually evaluated at runtime. Therefore, the only queries that will be slowed down or affected by them are the queries that use them. Expect them to return a little slower than their relatives, for this reason.

Look at each opportunity to make the calculated element the actual part of your cube, if it is used very often. Also, learn and love the scope statement. Although the computed element that scope d is still computing at runtime, the scope statement provides it with a ready-made execution plan, so it tends to be faster. I often create a member in a DSV, and then scope for my computed members with a lot of volume.

Answer 2

Anytime you can return calculations to the relational model, this will increase the performance of MDX queries; but also have a negative impact on processing performance.

If you can pre-compute some measures using the built-in sql logic, expose them as measures in the data source view. A storage engine can create aggregates, and a formula engine will have less work. You basically push the heavy climb down to sql. This works very well for static calculations and transform coefficients, and things like simple arithmetic, etc.

Another thing you can do is create any intermediate counted elements that should not be used as hidden by the end user, this will not affect performance; but will de-clutter the cube from the perspective of end users.