Медленное многомерное выражение для сводной таблицы Excel?

Мне очень трудно, когда Excel работает очень медленно при взаимодействии со сводной таблицей. Добавление / удаление поля, изменение фильтра или среза - все это занимает несколько минут, когда Excel замораживается до ответа.

Похоже, что генерируемые многомерные выражения крайне неэффективны. Я понимаю, что они должны генерировать многомерные выражения динамически и должны поддерживать многие функции сводных таблиц, но быть в 100 раз медленнее - это смешно.

Когда они генерируют многомерные выражения для поля в строке или столбце, они используют DrilldownLevel (... [Property Dimension]. [County])

Я не уверен, какова цель более сложного подхода Excel, но я надеюсь, что где-то есть некоторые варианты, которые я могу снять, чтобы Excel не нужно было использовать функцию DrilldownLevel.

Вместо этого я обычно опускаю функцию Drilldownlevel и просто использую [Property Dimension]. [County] . [County] , чтобы получить доступ к атрибуту.

Запрос того же набора результатов занимает 5 минут с многомерными выражениями Excel и менее 5 секунд с моим многомерным выражением.

Я убедился, что медлительность не является проблемой при рендеринге / форматировании результатов Excel, так как я взял многомерное выражение, используемое Excel, и запустил его непосредственно в SSMS для проверки таймингов. Я могу просматривать диспетчер задач на сервере и наблюдать, как процессор работает, пока обрабатывает результаты.

Обратите внимание: я не виню сервер, поскольку могу создавать запросы многомерных выражений, которые выполняются очень быстро и дают те же результаты.

Как заставить Excel генерировать более эффективные многомерные выражения? Я использую Excel 2010.

Я слышал, что powerpivot генерирует более эффективные многомерные выражения, однако Powerpivot нельзя использовать поверх SSAS, так как он не использует куб SSAS. Итак, короткая напыщенная речь о том, почему Powerpivot поверх SSAS не работает. Если вы импортируете данные из SSAS в powerpivot, все, что вы, по сути, делаете, - это выполнение гигантского перекрестного соединения для переноса данных из SSAS в таблицу Powerpivot. Если вы пробовали это, вы обнаружите, что он генерирует имена / метки полей, такие как "Property DimensionCountyCounty Name" ... вау, правда? Затем вы просто работаете с данными, используя локальный механизм OLAP Powerpivot, и, следовательно, зависите от клиентской машины с 64-битной ОС для работы с набором данных разумного размера. Это как если бы вы просто вырезали SSAS, бросая всю свою тяжелую работу по созданию сложной базы данных OLAP и всех метаданных, вычислений, агрегатов и т. Д. Половина причины использования SSAS заключается в том, что он может суммировать детализированные данные перед тем, как он будет возвращен клиенту, так что клиенту не нужна 64-разрядная ОС и не требуется огромное количество ресурсов на клиенте. Я очень старался сделать powerpivot против SSAS пригодным для использования, но после того, как попробовал несколько подходов и снова и снова с пользователями, он действительно был далеко от того, чтобы быть пригодным для использования. Чтобы не сбивать Powerpivot, поскольку я вижу его полезность во многих других сценариях, но если ваш куб SSAS является важной частью вашей системы (т.вычисления, агрегирование большого количества записей на стороне сервера и т. д.), то powerpivot кажется неправильным вариантом.

Вот пример моего запроса:

SELECT 
NON EMPTY CrossJoin(
{[Department Dimension].[Name].[Name]},
 {[Finance Month].[Report Year].[Report Year]}
 )
  ON COLUMNS , 
 CrossJoin(
   {[Department Finance Line Type Dimension].[Display Order].[Display Order] }, 
   {[Department Finance Line Type Dimension].[Line Number].[Line Number]},
   {[Department Finance Line Type Dimension].[Display Name].[Display Name]}
   ) 
   ON ROWS  
   FROM 
   (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [HYP Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

А ниже - то, что сгенерировал Excel. На самом деле я уже удалил несколько других аспектов запроса Excel, когда попытался упростить его, чтобы определить, в чем причина. Вот как выглядел запрос, когда он все еще выполнялся медленно, а затем я сделал следующий шаг, когда я удалил DrilldownLevel и заменил. [All] на. [ Attribute Name ], что стало очень много Быстрее.

Очень-очень медленный запрос:

SELECT 
NON EMPTY CrossJoin(
{DrilldownLevel({[Department Dimension].[Name].[All]})},
 {DrilldownLevel({[Finance Month].[Report Year].[All]})}
 )
 DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , 
 CrossJoin(
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Order].[All] })}, 
   {DrilldownLevel({[Department Finance Line Type Dimension].[Line Number].[All]})},
   {DrilldownLevel({[Department Finance Line Type Dimension].[Display Name].[All]})}
   ) 
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  
   FROM (
   SELECT ({[Department Dimension].[County].&[Seminole],[Department Dimension].[County].&[Sarasota]}) ON COLUMNS  FROM [Afr Data View]
   ) 
   WHERE ([Department Finance Line Type Dimension].[Section Name].&[Part 1 - Balance Sheet],
   [Measures].[Amount]
   ) CELL PROPERTIES VALUE

Зачем ему DrilldownLevel (... [All])? Можно ли где-нибудь перевернуть, чтобы Excel не генерировал эту часть запроса, чтобы он работал быстрее?