question

yagrus2-7807 avatar image
0 Votes"
yagrus2-7807 asked yagrus2-7807 commented

Reading partitions in ms analysis services 2012

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered yagrus2-7807 commented

Hi @yagrus2-7807,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

0 Votes 0 ·

In my case, the problem is with reading the data and all the measures are physical.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered yagrus2-7807 commented

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.
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Excel sends one MDX request. After that, SSAS starts reading data from the desired partition. But he does it cyclically.
In the log profiler, I see that reading the section lasts very little. Then it starts again. And so many times.
I assume that we have problems with memory settings.

24 GB of RAM is allocated on the server running ssas.

0 Votes 0 ·