Многомерные кубы

← →
Jeer © (2007-04-26 16:48) [0]

Пришлось столкнуться в одной информационно-аналитической системе с многомерными выборками из еще более многомерного куба.
Пример:

SELECT * FROM STATE_YEAR
WHERE YEAR_ID IN (2001 , 2002 , 2003 , 2004 , 2005 , 2006 , 2007)
AND MSR_MODE_ID IN (101 , 100 , 301 , 300)
AND SRC_ID IN (4 , 13)
AND TERR_ID IN (29202 , 29251 , 29204 , 29206 , 29252 , 29253 , 29208 , 29254 , 29255 , 29256 , 29257 , 29210 , 29212 , 29258 , 29213 , 29259 , 29260 , 29215 , 29261 , 29216 , 29262 , 29263 , 29218 , 29223 , 29264 , 29265 , 29225 , 29227 , 29229 , 29232 , 29234 , 29236 , 29238 , 29242 , 29244 , 29266 , 29267 , 29268 , 29246 , 29269 , 29250 , 29401 , 29605 , 29405 , 29214 , 29415 , 29610 , 29410 , 29220 , 29270 , 29271 , 29272 , 29000)
AND BRANCH_ID IN (0)
AND MSR_STATE_CODE IN (1003 , 1004 , 1005 , 1006 , 1007 , 1008 , 1009 , 1010 , 1011 , 1012 , 1013 , 1014 , 1015 , 1016 , 1017 , 1018 , 1002 , 1001 , 1021 , 1022 , 1023 , 1024 , 1025 , 1026 , 1027 , 1028 , 1029 , 1030 , 1031 , 1032 , 1033 , 1034 , 1035 , 1036 , 1020 , 1019 , 1000)

Система практически падает при сближении мерности выборки и куба.
Подозреваю, что выход только один - делать оптимизированные разрезы и получать результат как серию запросов.

Или не прав, или что-то еще ?

P.S.
Про технологии хранения-выборки (MOLAP/ROLAP) не будем, т.к. эта ИА-система на сегодня данность.

← →
Правильный Вася (2007-04-26 17:03) [1]

> Система практически падает при сближении мерности выборки и куба.

по-русски

← →
clickmaker © (2007-04-26 18:36) [2]

> Система практически падает

какая хоть система? ну в смысле СУБД

← →
Johnmen © (2007-04-26 21:06) [3]

> Jeer © (26.04.07 16:48)
> Подозреваю, что выход только один - делать оптимизированные
> разрезы и получать результат как серию запросов.

Я сильно подозреваю, что это НЕ СПАСЁТ положения.
А единственный выход не проходит из-за "т.к. эта ИА-система на сегодня данность."

← →
Jeer © (2007-04-27 10:06) [4]

clickmaker © (26.04.07 18:36) [2]

СУБД MS-SQL

Johnmen © (26.04.07 21:06) [3]
>Я сильно подозреваю, что это НЕ СПАСЁТ положения.

Эксперименты показали, что острота снижается.

Предположим, что есть измерения:
- время
- территории
- виды деятельности
- источник информации
- показатели.

Т.е. имеем 5-мерный куб.

Значимые по объему выборки проходят по измерениям:
-время
-территории
-показатели

Т.е. имеем 3-мерный срез из 5-мерного куба.

Попытка получить этот срез одним select приводит к значительному времени
исполнения и съеданию ресурсов памяти.

Проведенный эксперимент показал, что зависимость времени выполнения запроса от числа запрашиваемых параметров (ось "параметры") оказалась практически квадратичной.

Таким образом, единственный запрос с числом параметров 60 занимал время 90 сек, а 6 запросов по 10 параметров в каждом заняли в сумме 24 сек.

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

P.S.
Поскольку, в данном случае, я не разработчик, а только пользователь, как бизнес- аналитик, возможности что-либо кардинально изменить не имеется:(

Поэтому инструментарий класса Analysis и OLAP Service пробегает мимо.

← →
ANB © (2007-04-27 11:17) [5]

> Jeer © (27.04.07 10:06) [4]

А можно глянуть на :
1. структуру таблицы
2. Количество записей в таблице
3. Индексы, которые к ней прикручены
?
ЗЫ. Жаль, что не оракл, мс скл не оптимизил, но пути схожие, только синтаксис разный

← →
ANB © (2007-04-27 11:18) [6]

> Подозреваю, что выход только один - делать оптимизированные
> разрезы и получать результат как серию запросов

Скорее всего, в сумме будет еще медленнее.

← →
Jeer © (2007-04-27 11:48) [7]

ANB © (27.04.07 11:17) [5]

>1. структуру таблицы

Cтруктура базы не особо сложная: http://hlab.nm.ru

размерностей порядка 7-8
по каждой размерности от нескольких до нескольких тысяч показателей
таблиц значений - три (месячный, квартальный, годовой)
в каждой из них до 1 млн записей
Индексы по минимуму и в основном по relations

Т.е. выбирать по максимуму то это будет 8-мерный куб

> корее всего, в сумме будет еще медленнее.

Нет, эксперимент показал, что в 4 раза быстрее см. [4]

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

← →
Jeer © (2007-04-27 11:50) [8]

> Проведенный эксперимент показал, что зависимость времени
> выполнения запроса от числа запрашиваемых параметров (ось
> "параметры") оказалась практически квадратичной.

Уточнение - кубическая зависимость при трех измерениях.

← →
ЮЮ © (2007-04-27 12:16) [9]

STATE_YEAR - это таблица или View?
Если таблица, то есть ли в ней индексы по полям YEAR_ID , MSR_MODE_ID, SRC_ID, TERR_ID, BRANCH_ID и MSR_STATE_CODE ?

← →
Jeer © (2007-04-27 12:23) [10]

Здесь только таблицы.
Как я уже сказал выше, по всем связках есть индексы.

← →
ЮЮ © (2007-04-27 12:37) [11]

>Таким образом, единственный запрос с числом параметров 60 занимал время 90 сек, а 6 запросов по 10 параметров в каждом заняли в сумме 24 сек.

Может дело именно в этой системе? А как по времени исполняется запрос в SQL QA? И тоже время уменьшается при уменьшения "параметров"?

← →
ЮЮ © (2007-04-27 12:46) [12]

> > Подозреваю, что выход только один - делать оптимизированные
> > разрезы и получать результат как серию запросов.

Куда уж обтимизировать запрос на выборку из одной таблицы?
А MS-SQL какой?
Просто на 6.5 я как то опасался конструкций IN (...). На 2000-м и нормальном железе эти опасения прошли. Правда и больше 2-З писать не приходмтся. Но и с твоими скобками в SQL QA выполняется за 0:00:00. Правда таблиц-миллионеров нет :(

← →
ANB © (2007-04-27 12:51) [13]

> Jeer © (27.04.07 12:23) [10]

Судя по всему, индексы игноряться или используются неоптимально. В оракле я бы повесил хинты и пересобрал статистику. Нечто подобное должно быть и в MS SQL. Планчик умирающего запроса не смотрел ?

← →
Jeer © (2007-04-27 13:14) [14]

> ЮЮ © (27.04.07 12:37) [11]

> как по времени исполняется запрос в SQL QA?

С уменьшением числа параметров в размерностях при неизменном числе размерностей время зависит кубически от числа параметров.

Стоит только включить еще одну размерность и начать увеличивать число параметров в ней - похоже зависимость становится степенной с число степеней равным числу размерностей + еще и число параметров влияет.

От In() никуда не дется, т.к. пользователь имеет право выбирать любые комбинации размерностей и числа параметров в ней.
В общем, хреновая это консерватория:(
Разработчик предполагал создание некоторого OLAP инструмента, да про "дороги" забыл.

← →
ANB © (2007-04-27 13:26) [15]

> Jeer © (27.04.07 13:14) [14]

По хорошему, добавление размерности практически не должно влиять на скорость выполнения запроса. План надо смотреть.

← →
ANB © (2007-04-27 13:27) [16]

Еще я бы посмотрел в сторону составных индексов - может помочь, если селективности обычных не хватает. Но с ними нужно аккуратно играть. И место они хорошо кушают.

← →
ANB © (2007-04-27 13:29) [17]

Кстати, программист, который это сделал и ушел - все сделал правильно. Фактически, это OLAP и есть. Теперь это уже работа админа - все это настроить и оптимизнуть.

ANB © (27.04.07 13:26) [15]

> По хорошему, добавление размерности практически не должно
> влиять

Еще как влияет, т.к. по большому счету это количество перестановок.

> Еще я бы посмотрел в сторону составных индексов

Там создан кластерный индекс по полям-связкам, не думаю, что дополнительные отдельные индексы по полям ситуацию улучшат.

> Кстати, программист, который это сделал и ушел - все сделал
> правильно. Фактически, это OLAP и есть. Теперь это уже работа
> админа - все это настроить и оптимизнуть.

Это делал не "программист", а большая контора, которая предполагала внедрение такой системы по органам власти, но..

В итоге данных набрано море и стоит задача их анализа, а инсрумент - йох.
Хоть свой садись и пиши.

Только вот незадача - в текущем статусе я не админ, не разработчик, а аналитик.:)

> Там создан кластерный индекс по полям-связкам, не думаю,
> что дополнительные отдельные индексы по полям ситуацию
> улучшат.

Кошмар. Наверное и вставить запись в таблицу-миллионер непросто?

> не думаю, что дополнительные отдельные индексы по полям
> ситуацию улучшат

Не думай, а сделай. Думает пусть сервер. Тем более, что ухудшить твою ситуацию уж точно невозможно.
Хотя сомнения гложут поможет ли действительно мертвому припарки, ибо уж очень круто - кластерный индекс по полям-связкам. Может кластерный индекс попроще сделать - по суррогатному ключу целого типа.

> Только вот незадача - в текущем статусе я не админ, не разработчик,
> а аналитик.:)

Т.е. наши советы ты применить не можешь?

> С уменьшением числа параметров в размерностях при неизменном
> числе размерностей время зависит кубически от числа параметров.

Тут, похоже, не только индексы не работает, но и сервер "отдыхает" - для каждого входящего в IN значения сканирует таблицу заново

> Там создан кластерный индекс по полям-связкам, не думаю,
> что дополнительные отдельные индексы по полям ситуацию
> улучшат.
>

В общем, создал отдельные индексы по полям-связкам.
Ситуация улучшилась на порядок:)
Уже что-то.

Хлипкая контора все это дело проектировала, ой хлипкая.
В качестве антирекламы - ИАС "Инфовизор" Ивановского универа.

Сервер MySQL, например, так и делает... Насколько я знаю, в руководстве написано, что поиск по индексам (а также по параметрам запросов) оптимизируется. Полностью на это полагаться нельзя, лучше самостоятельно выбирать изначально поиск по минимальному числу данных, чтобы сэкономить главным образом время, затем и память.

Причём здесь MySQL ?

> Причём здесь MySQL ?

Вобщем то все приличные СУБД пытаются запрос оптимизить

Многомерные кубы Найти похожие ветки