Use SSAs server-side dynamic name set with caution

Named sets in SSAs are frequently used in projects. Common functions such as topcount, filter, and exists can be set as static namespaces, some do not work. For example, if topcount is set to static set, it doesn't make much sense. It is related to the performance problem of accessing SSAs in Excel today, in the support phase, it took nearly 20 seconds for a project to access SSAs for the first time to create an external table by using Excel. It was intolerable to troubleshoot the problem. First, use profiler to capture performance bottlenecks, as shown in figure

It is found that every time you create an external table, the server will query the mdschema_sets dynamic view and then try to query $ system on the SSAS server. choose mdschema_sets to check the content. Create a DMX query window and enter select * from $ system. after mdschema_sets F5, it is not surprising that this query was executed for 20 seconds and only five records were found, which is exactly the five dynamic sets designed in the cube, one of them is the [Top 100 account by sales] ranking of the top members, while the member dimension is a large dimension with tens of millions of records.

It is concluded that when creating an external table, the server automatically calculates all the dynamic sets in the system. Because they are dynamic, even if a cache exists, a query is performed again.

The current solution is to delete all the dynamic sets of these servers. If you need these functions in the future, you can also implement them on the Excel client, for example:

Create a client nameset

Then you can find the name set under the corresponding dimension.

The usage is exactly the same as the name set on the server. The only trouble is that you need to create this MDX named set on the client of each end user.

 

Use SSAs server-side dynamic name set with caution