This project has moved. For the latest updates, please go here.

Setting dimension filter took much time

Jul 31, 2014 at 2:00 PM
Edited Jul 31, 2014 at 2:01 PM
Is it correct that setting filter values on "Filter list" tab took much time when there are huge number of items in dimension? As I saw in profile, there are MDSCHEMA_MEMBERS events exposed for each filter item. Is there any way to skip this checking?
Coordinator
Jul 31, 2014 at 8:07 PM
Can you provide some more info? How many milliseconds does each MDSCHEMA_MEMBERS call take in Profiler? And how many members are there in that hierarchy?

What version of SSAS?

I do have some nice enhancements coming in the next release in a few weeks. The enhancements are to the Search feature to let you search for multiple search terms. So it may be worth trying the Search feature in the next release with Exact Match checked and see if that performs better. It is heavily optimized to perform well with multiple search terms and large dimensions. If you want to beta test it, click furmangg and shoot me an email.
Aug 1, 2014 at 5:26 AM
We use SSAS 2012 in Multidimensional mode version 11.0.3393.0.

Dimension has about 10 million elements and MDSCHEMA_MEMBERS call took about 4-4.5 seconds each.

Well, I think I can beta test the new version. Do I still need email you?
Coordinator
Aug 1, 2014 at 12:31 PM
I sent you an email with the beta test instructions. Post back with how it goes.
Aug 1, 2014 at 1:17 PM
Thank you.
I tried the beta. As I see, it works the same manner, i.e. it searches for each item in the dimension.
Is it possible to skip this MDSCHEMA_MEMBERS checking? Maybe you implement some checkbox?
Coordinator
Aug 1, 2014 at 1:20 PM
Try Search not Filter List please. See instructions in my email.
Aug 1, 2014 at 1:27 PM
I tried searching multiple items.
What I did - I inserted 97 items in the listbox and I saw dimension items slowly appear. Every ~4 second new item.
Coordinator
Aug 1, 2014 at 1:37 PM
Can you watch it in Profiler and post the queries it runs after you click Find? Please post the durations on the slowest.
Aug 1, 2014 at 1:49 PM
For each item I get this four event.

Discover Begin 26 - DISCOVER_PROPERTIES
Discover End 26 - DISCOVER_PROPERTIES
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
  <PropertyName>Catalog</PropertyName>
</RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Catalog>NONPROD_1</Catalog>
  <DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
  <SafetyOptions>2</SafetyOptions>
  <MdxMissingMemberMode>Error</MdxMissingMemberMode>
  <DbpropMsmdSubqueries>2</DbpropMsmdSubqueries>
  <LocaleIdentifier>1049</LocaleIdentifier>
  <SspropInitAppName>OLAP PivotTable Extensions v0.8.3.0 (32-bit)</SspropInitAppName>
  <ClientProcessID>7272</ClientProcessID>
  <Content>SchemaData</Content>
  <Format>Tabular</Format>
  <DbpropMsmdActivityID>76bf8b78-0562-438e-ae25-f91a3366ee49</DbpropMsmdActivityID>
  <DbpropMsmdRequestID>9222099f-a6a8-4cbe-a9c2-7f39607152d7</DbpropMsmdRequestID>
</PropertyList>
Discover Begin 10 - MDSCHEMA_MEMBERS
Discover End 10 - MDSCHEMA_MEMBERS
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
  <CATALOG_NAME>NONPROD_1</CATALOG_NAME>
  <CUBE_NAME>F101</CUBE_NAME>
  <HIERARCHY_UNIQUE_NAME>[CLIENT].[ID CLIENT]</HIERARCHY_UNIQUE_NAME>
  <MEMBER_CAPTION>1466823</MEMBER_CAPTION>
</RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Catalog>NONPROD_1</Catalog>
  <DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
  <SafetyOptions>2</SafetyOptions>
  <MdxMissingMemberMode>Error</MdxMissingMemberMode>
  <DbpropMsmdSubqueries>2</DbpropMsmdSubqueries>
  <LocaleIdentifier>1049</LocaleIdentifier>
  <SspropInitAppName>OLAP PivotTable Extensions v0.8.3.0 (32-bit)</SspropInitAppName>
  <ClientProcessID>7272</ClientProcessID>
  <Content>SchemaData</Content>
  <Format>Tabular</Format>
  <DbpropMsmdActivityID>76bf8b78-0562-438e-ae25-f91a3366ee49</DbpropMsmdActivityID>
  <DbpropMsmdRequestID>336e62aa-a294-4ff0-86ac-6f0fb545f08d</DbpropMsmdRequestID>
</PropertyList>
This one took 4266 ms.

Unfortunately I cannot check if I has latest OlapPivotTableExtensions.dll file. It's size is 216576 bytes.
Coordinator
Aug 1, 2014 at 1:54 PM
Edited Aug 1, 2014 at 1:55 PM
I have an idea of the problem. I think the optimizations don't kick in if you have Exact Match checked. Try unchecking it and searching again. Should be very different I hope. I am not sure if it will be faster overall, but I am anxious to hear the results over Ethernet.
Aug 1, 2014 at 2:26 PM
Great, this way is much faster. But why it is necessary to check existence of the items?
Coordinator
Aug 1, 2014 at 9:23 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Aug 1, 2014 at 9:24 PM
I'm glad it's faster when you search for a lot of search terms and have Exact Match unchecked. I've created a work item to track enhancing search to perform better for "exact match" searches. I'm not sure if I'll get to that before the next release, but I will keep that work item updated.

Thanks for testing!