Целесообразно ли хранить длинные уникальные текстовые строки в кубах OLAP для сквозного поиска (особенно в SSAS)?
Я заинтересован в том, чтобы хранить несколько длинных текстовых строк в кубе OLAP длиной порядка 1000 или 10000 символов - но мне интересно, не приведу ли это меня в заблуждение. (Мне также любопытно узнать немного больше о том, как механизмы OLAP обрабатывают строки.) Я имею в виду конкретный вариант использования, который заключается в том, что у меня есть уникальное, уже существующее "описание записи" для каждого из моих фактов OLAP, и я хочу поместить эти описания в куб, чтобы у меня была возможность вернуть их, когда я выполню операцию DRILLTHROUGH. Напротив, мне не нужно, чтобы описания записей появлялись при выполнении обычных операций сводной таблицы / агрегатного типа. (Описания слишком длинные, чтобы разумно отображать их в сводной таблице, плюс каждый факт имеет уникальное описание, что означает, что не имеет смысла агрегировать по описаниям.) Мой текущий набор данных содержит около 700 000 фактов, хотя мне также любопытно, если ответ изменится для больших наборов данных.
Я надеялся, что сервер OLAP мог бы сделать что-то разумное, если бы я поместил эти длинные строки в куб. В частности, в случае Sql Server / SSAS я подумал, что, возможно, я бы поместил их в измерение, помеченное как ROLAP, для экономии использования памяти, и использовал бы вырожденное измерение (так называемое "измерение фактов" в терминологии SSAS), чтобы избежать ненужные сложности ETL. Но мне любопытно, будет ли это рассматриваться как ужасная практика по какой-то причине или есть какие-то скрытые ошибки.
Обновление: мой пример использования, где у вас есть строка, связанная с каждым фактом OLAP. Но также может быть полезно рассмотреть случай, когда строки вместо этого связаны с каждым конкретным значением определенного измерения. (Например, предположим, что у вас было измерение Company, а в каждой компании была несколько длинная строка описания компании.)
4 ответа
Вот что я смог раскрыть о последствиях хранения таких строк в SSAS, особенно в SSAS 2008. Когда я рассматриваю структуры данных, он сосредоточен исключительно на хранилище MOLAP, с чем я экспериментировал.
Во-первых, стандартные инструменты MS ETL (извлечение / преобразование / загрузка, то есть импорт данных), такие как Business Intelligence Development Studio, могут попытаться запретить импорт больших текстовых полей, особенно полей varchar(max), но есть обходной путь, и он доказал свою эффективность для мне. (Для BIDS это включает ручную настройку элемента DataSize в файле XML, потенциально до магического размера 163315555 байт. Попросите Матию Ла для выяснения этого.)
Во-вторых, насколько я могу судить, хранение множества длинных уникальных строк не должно наносить ущерб структурам данных на диске, используемым SSAS. Кроме того, размер строковых данных на диске должен быть того же порядка, что и строковые данные в вашем источнике данных. Вот некоторая грубая информация о строках дескрипторов SSAS:
- Основные структуры данных OLAP (например, для атрибутов измерения или для фактов групп мер) напрямую не содержат строк; вместо этого содержат смещения в файлах "хранилища строк" (расширения.ksstore, .asstore, .bsstore или.string.data), которые содержат фактические строковые данные.
- В данном хранилище строк каждая строка представлена только один раз. Если несколько строк в ваших исходных таблицах данных содержат повторяющиеся строки, то на уровне SSAS/MOLAP это будет переводиться в дублированные смещения файлов, а не в дублированные строковые значения
- Если исходная строка имеет длину n, то соответствующая структура данных в хранилище строк имеет 8-байтовые байты служебной информации плюс 2*n байтов на символ. (Строки по сути хранятся в 2-байтовом формате Unicode в SSAS.)
- Для некоторых фантастических подробностей об этом материале я предлагаю книгу Microsoft SQL Server 2008 Analysis Services Unleashed, в частности главу 20 "Физическая модель данных".
- По крайней мере, в моих экспериментах файлы хранилища строк не выглядят сжатыми - по крайней мере, они не намного меньше, чем хранилище несжатой строки.
Я экспериментально проверил, что текстовые данные занимают одинаковый порядок байтов, хранятся ли они в SSAS MOLAP или в таблице sql. В частности, я сделал "выбрать сумму (len(myfield)) из mytable" из одной из моих таблиц измерений, а затем сравнил с размером файлов соответствующего атрибута в моем каталоге данных SSAS. Размер был 172 МБ в SQL и 304 МБ в SQL-сервере. (Размер Sql составлял 147 МБ, если я суммировал все уникальные строки, а не все строки.) В моем случае разница в размере в основном объяснялась кодировкой символов; Мои исходные данные sql хранятся с одним байтом на символ, тогда как SSAS хранит все строки с двумя байтами на символ. Я обнаружил, что файл.kssstore полностью доминировал над всеми остальными файлами, связанными с этим атрибутом по размеру, независимо от того, оптимизировал ли я атрибут с помощью AttributeHierarchyOptimizedState=FullyOptimized.
В-третьих, размер файлов хранилища строк ограничен 4 ГБ, что ограничивает объем уникального текста, который может быть связан, скажем, с определенным измерением / атрибутом. В моем случае я до 10% пути до предела, но это может повлиять на некоторых людей. (Быстрый порядок порядка для исходного сообщения: 1M фактов * 10000 байт / на каждый факт = 10 ГБ текста). Если вы достигнете этого предела, вы, очевидно, достигнете его во время "обработки" куба. Очевидно, это относится даже к размерам ROLAP. Там могут быть некоторые хаки обойти это. Смотрите здесь. Обратите внимание, что Sql Server 2012 может снять это ограничение в 4 ГБ.
В-четвертых, кажется, что если длинные уникальные строки создают проблему в SSAS, они делают это на уровне представления в памяти. Одна потенциальная проблема (которую я не рассматривал подробно) заключается в том, что кэширование этих дополнительных строк в памяти не позволит SSAS хранить другие важные структуры данных в памяти и, таким образом, ухудшит производительность. Другая проблема, предложенная в книге The Microsoft Data Warehouse Toolkit (хотя я еще не нашел это утверждение в другом месте), заключается в том, что SSAS выполняет некоторые обширные дополнения строк в своих структурах данных в памяти:
"Реляционная база данных хранит строковые столбцы переменной длины... Однако другие части набора инструментов SQL Server будут заполнять эти столбцы до их полной ширины. Примечательно, что столбцы строк дополнения служб Integration Services и Analysis Services с пробелами по мере их загрузки в память. И Integration Services, и Analysis Services любят физическую память, поэтому стоит объявить строковые столбцы, которые намного шире, чем они должны быть ".
В заключение, до сих пор хранение моих длинных строковых данных в кубе кажется удобным, и я не обнаружил никаких причин ожидать катастрофы, поэтому я попробую. Я постараюсь предоставить обновление, если что-то не получится.
Вы можете сохранить значения в таблице реляционно, а затем создать целочисленный суррогатный ключ.
добавьте целочисленный суррогат к вашему UDM и создайте действие SSRS Drillthrough
http://msdn.microsoft.com/en-US/library/ms174526(v=SQL.90).aspx
который ищет текстовое поле по значению ключа.
Я бы использовал вырожденное измерение, но скрывал его через SSAS, пока не запросил через Drillthrough Action.
Я не могу рассказать вам о внутреннем хранилище строк для механизма AS, но что касается хранения их в SQL, я бы позаботился о том, чтобы ваш столбец varchar(MAX) находился в конце ваших столбцов, чтобы ускорить сканирование этих механизмов SQL. строк.
При 700 000 строк, при достаточном объеме памяти и дисковом вводе / выводе, вы не сильно облагаете налогом SQL.
До сих пор не проработал все описанные возможности и ссылки на него, но эта ветка с 2007 года посвящена той же теме и выглядит довольно актуальной:
Здесь появилась новая возможность: вместо того, чтобы рассматривать текст, хранящийся в таблице фактов, как вырожденное измерение, вы могли бы потенциально трактовать его как меру с текстовым значением (против числового). Первоначальный поиск в Google предполагает, что SSAS может поддерживать это, но есть некоторые приемы, чтобы сделать это правильно, например, вы, вероятно, хотите отключить агрегирование для этого показателя, вам может потребоваться сделать что-то нестандартное, чтобы поле появилось в детализации, и это может потребоваться SSAS Enterprise Edition.