SSAS Best Practice

My team has been presented with the question:

Is it better to use another layer between an Analysis Services data cube and the presentation of it or not

It would seem to me that if the pure goal is performance, then the least amount of moving parts the better, and leave the multi-tier structures for other parts of the application.

What are TechNets thoughts on this and/or best practices as such

Thanks.

Lonnie

It depends on the situation, but what type of layer were you thinking of

Well just in general practice, we don't just serve up a data without something in between the data and the consumer of the data. This allows us to control business rules (i.e. what can be seen, operated on, and validated). But, having the UI consume the cube directly seems ok, since most of these operations can be controlled inside the cube. And since that is the case, connecting directly to it would be optimal, and sense performance is the overall purpose for using it, wouldn't it be ideal to take out as many moving parts as possible

I guess the question is, what is the best practice, how do most people connect to a SSAS cube from the client application. Is this usually done directly or is it common to build another layer that serves up the cube, or queries the cube and serves up the data Again keeping optimal performance in mind.

I know this question is hard to answer without more information regarding the system we are building, but I am just looking for a "rule of thumb" type of answer. It seems that connecting to the cube directly would be the best answer, but why would someone not want to Calculations, security, formatting, what can be viewed etc ... can all be handled directly in the cube itself.

Lonnie

Two solutions come to mind:

• Excel - everyone knows it and has it install it. The Pivot Tables can be saved for later use/reference.
• Proclarity - better/optimized MDX query (therefore best performance), but less friendly (in my opinion).

>Proclarity - better/optimized MDX query (therefore best performance), but less friendly (in my opinion).

Proclarity had better MDX if comparing with Excel 2003 but not with 2007.

and Proclarity had in no way the best performance. there are much more better tools.

From what I have seen the unoptimized MDX created by Excel2003 is the same created by Excel2007.

Can you tell us why you say the Excel2007's MDX is better

Can you give us an example, e.g., in Adventure Works to be easier simulated

Thanks.

Well, at my company we always create a .NET web application to query the cube. This protects the data from being manipulated and can be double checked by anyone without training. We have a web service on the SSAS box, which strickly takes an MDX statement input, connects to the cube, runs the MDX, and then returns the dataset. Connecting from a client directly to the cube does slow it down. There's something about connecting to SSAS from an external location which slows down the queries (especially in 2000, NEVER do it in 2000).

Even though we always have a .NET UI, we do for some cubes allow direct access for certain power users.

Hope this helps...

This does help thank you. It is an interesting thought to consider how much faster it would be running the cube queries directly on the Analysis server. However, what type of flexibility does this offer on your front end if all you get back is a dataset Clearly you lose the pivot table and charting functionality Or do you If so, maybe the answer would be to ensure the communication between the web server and the analysis server is fast, maybe even the same box(s) or network legs ...

Another thought, isn't that all the controls that connect directly to the cube do Send MDX queries Regardless of the client control, when you break it down, is that not all that is happening anyhow

Correct, all the applications are sending MDX queries and receiving datasets/etc. So I wouldnt't think any functionality should be lost.

1.) Well, that functionality is still psosible. Personally, we always do charting in our UI so thats not an issue. Even if you wanted to do it in SSAS ( ) then you could still return that object from the web service. In reality, we never show raw data from cubes in the UI. We always format it, sum it, etc. So even our graph package takes our reformatted dataset. If youre speaking about 3rd party graph components which go directly against SSAS using its own MDX, etc...we would allow direct connection to the cube, if needed.

2.) Yes, thats correct. Both ways still receive the same results. But like before, we noticed performance boosts and additional security abilties by putting a "dumb" web service on the SSAS server.