Reading partitions in ms analysis services 2012

yagrus2 1 Reputation point
2022-05-12T14:23:02.537+00:00

Hi guys!
There is an olap cube. In Excel, I create a pivot table for which the source is a cube.
The group of measures from which I take a measure contains partitions.
I build the report in the context of three attributes.
In the profiler I see:
The reading of the data of the "Inventory2022Q1" section has begun.
Reading the data of the "Inventory2021Q1" section is completed.
Such messages are repeated many times.
That is, for one request, the cube reads the same information many times.
Maybe there is not enough memory for this request? there is a lot of memory on the server.
Can you tell me if there is a setting that will help?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,007 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2022-05-13T06:30:41.39+00:00

    Hi @yagrus2 ,

    Thanks for posting here. Firstly, please be sure to notice the difference between reading partition data multiple times and load partition data multiple times.

    Load partition data multiple times could be a sign that you have memory pressure in your SSAS server, thus the partition data need to be pushed out to load the newly requested data.

    While reading partition data multiple times means SSAS Formula Engine and Storage Engine need to request the data multiple times from memory cache with disk IO.

    Regarding why partition, Partitioning can improve both processing performance and query performance significantly. Analysis Services can process multiple partitions in parallel and this can lead to much more efficient use of CPU and memory resources on your server while processing is taking place. Analysis Services can also fetch and aggregate data from multiple partitions in parallel when a query is run too, and again this can lead to more efficient use of CPU and memory and result in faster query performance. Lastly, Analysis Services will only scan the partitions that contain data necessary for a query and since this reduces the overall amount of IO needed this can also make queries faster.

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Olaf Helper 25,901 Reputation points
    2022-05-13T06:54:59.467+00:00

    Can you tell me if there is a setting that will help?

    A setting for what; you didn't mentioned any real issue.

    Client is Excel and you can not control from SSAS server side what a client do/query.