First, the fact is that all calculations inside the pivot table are done by SSAS server, not by Analyzer (except level-omittable aggregation functions are involved). It is the SSAS that does the most of the heavy lifting when it comes to pivot table calculation, the Analyzer simply generates the MDX query for SSAS to process then waits for the result to return.
Second, the strength of SSAS (BI) is that it can pre-aggregate multi-dimension data based on the dimensions and hierarchies that are defined in the cube. Ideally the user should be using pre-aggregated data to do analysis, therefore the goal is to have as much data pre-aggregated as possible when designing the cube.
With the above in mind, it is best to avoid the following when designing a report:
1) Dynamically cross-join attributes/dimensions to create hierarchy.
2) Using calculated measures from either the back-end (cube) or front-end (defined in Analyzer report).
Now lets take a look why these should be avoid:
The differences between a pre-defined hierarchy in the cube and a dynamically cross-joined hierarchy is that there are very few or no pre-aggregated data for dynamically cross-joined dimensions. So in essence you are not using the strength of the SSAS but instead asking it to calculate the results on the fly.
For example by cross-join attributes A and B that each has 100 members, if only with a single measure is involved, then SSAS come up with the result dynamically for 100 x 100 x 1 = 10,000 data points. Even if each point takes 1/10,000 of a second to process, that still 1 full second. To make the matter worse, if you cross join two dimensions on the column, for example - Years and Cities, lets assume there are 10 years and 100 cities then SSAS has do to cross join these two dimensions on the fly for 10x100 calculations, and now if there is also a row dimension of Products (1000) then it becomes 10x100x1000. Now let's add 10 measures to the pivot table, then SSAS has to do 10x100x1000x10 = 10,000,000 calculations on the fly in real time, that's 1000 seconds which is over 16 minutes.
The story is the same while using calculated measures. All calculated measures are calculated on-the-fly (for the initial run), so regardless you are using pre-defined hierarchy or post-define hierarchy SSAS will have to calculated every single cell on the fly.
Best Practice Recommendations:
Instead of listing all member in a single attribute, create hierarchy levels to limit the items to be displayed, so instead of listing all the product like this
Create hierarchy so only part of the data is displayed thus limit the calculations
Also please consider pre-create often used hierarchies since we always want to avoid cross-join attributes/dimensions on the fly. For example if you always need to create an hierarchy using Category and Country then instead of cross join them on the fly in the report it is best to create a Category > Country hierarchy at the backend first since by doing so we can take advantage of pre-aggregated data.
Use regular measures (green) as much as possible, avoid using calculated measures (red).
and avoid front-end calculated measures, too.
Naturally it is not possible to always avoid using calculated measures, however we can try to limit the cells to be calculated at once by selectively expand sections of data so we are not asking SSAS to calculate all the data at once.
1) Tune the cube. Consider to have cube tuned so SSAS can do its job better. SSAS is just like SQL database that needs tuning, for example - reduce roll-up sum, define more hierarchies that you know user will use, have more hierarchy levels, or consider pre-aggregate more data.
2) Consider upgrade your SSAS server hardware. Upscale your SSAS server with more memory or CPU power.
3) Consider using IP Address to specify the Data Source object connection instead of using server name or domain name. Depending on how the network is setup, DNS name might take considerably longer time to get resolved.