Analysis Services: Павышэнне прадукцыйнасці куба, выкарыстоўваючы Microsoft SQL Server 2000 Analysis Services

Зыходзячы з пералічаных пытанняў, вызначым адпавядае крыніца дадзеных у існуючай ER дыяграме (entity-relationship diagram, дыяграма "аб'екты-адносіны") вельмі вялікі БД (VLDB, Very Large DataBase). Табліцы можна вызначыць наступным чынам:

• Табліца Product змяшчае інфармацыю аб рахунках банка (напрыклад, бягучы або тэрміновы рахунак).
• Табліца Customer Segment прадастаўляе магчымасць падзяліць кліентаў банка на катэгорыі (напрыклад, кліенты-фундатары або кліенты-даўжнікі).
• Табліца Period змяшчае дадзеныя аб часовым перыядзе для разліку паказчыкаў прыбытку. Разгляданая база даных змяшчае інфармацыю за 2 гады (1996 і 1997).
• Табліца Region змяшчае інфармацыю аб геаграфічным становішчы кожнага банка.
• У табліцу Household занесены дадзеныя аб кліентах, якія могуць мець некалькі рахункаў.

Для атрымання адказаў на пытанні аб эфектыўнасці таго ці іншага прадукту была распрацавана схема "зорка" (для OLAP-куба). Табліца Account_Prof_Fact была пабудавана з табліц аб прыбытковасці па рахунках за ўсе перыяды (гл. Мал. 2). У ёй утрымліваюцца дадзеныя аб прыбытковасці той ці іншай банкаўскай паслугі, напрыклад, штомесячныя даходы і выдаткі для розных прадуктаў; іншымі словамі, табліца змяшчае значэння totals для ўсіх мер за кожны месяц. Былі вызначаныя пяць вымярэнняў: Product, Period, Region, Household і Customer Segment.

Мал.2. Схема "зорка" з фактамі і вымярэннямі

Стварэнне і запаўненне вітрыны дадзеных SQL-сервера

Для запаўнення табліц фактаў і вымярэнняў у вітрыне дадзеных былі выкарыстаныя службы трансфармацыі даных (Data Transformation Services, DTS). Табліцы, якія захоўваюць дадзеныя па кожным з перыядаў, былі аб'яднаны ў адну табліцу фактаў, названую Account_prof_fact і змяшчае інфармацыю за ўсе 24 месяца. Табліца фактаў ўтрымлівае прыблізна 13 млн. Запісаў.

Вітрына дадзеныхКолькасць шэрагаўПамерAccount_Prof_Fact

13,036,152 5188.00 МБ CustSegmentDim 7 0.03 МБ HouseholdDim 200,001 38.56 МБ ProductDim 14 0.03 МБ RegionDim 51 0.04 МБ TimeDim 24 0.03 МБ
Пабудова OLAP-кубоў

Далей была створана шматмерных база дадзеных OLAP, названая AccountProfitabilityOLAPDatabase, якая змяшчае 12 кубоў аднолькавай структуры, але з рознымі рэжымамі захоўвання і ўзроўнямі агрэгавання. На малюнку 3 паказана структура аднаго з гэтых кубоў.

Наступная табліца змяшчае кароткую характарыстыку кожнага з 12 кубоў. Як ужо было адзначана, кубы маюць аднолькавую структуру, але рэжымы захоўвання і ўзроўні агрэгавання ў іх розныя.

Назва кубаРэжым захоўвання дадзеныхУзровень агрэгавання (у працэнтах)

AccountProfitabilityCubeM0 MOLAP 0 AccountProfitabilityCubeM30 MOLAP 30 AccountProfitabilityCubeM60 MOLAP 60 AccountProfitabilityCubeM90 MOLAP 90 AccountProfitabilityCubeH0 HOLAP 0 AccountProfitabilityCubeH30 HOLAP 30 AccountProfitabilityCubeH60 HOLAP 60 AccountProfitabilityCubeH90 HOLAP 90 AccountProfitabilityCubeR0 ROLAP 0 AccountProfitabilityCubeR30 ROLAP 30 AccountProfitabilityCubeR60 ROLAP 60 AccountProfitabilityCubeR90 ROLAP 90

Мы выбралі восем мер з паказаных на мал. 3. Базавая табліца фактаў змяшчае 13 млн. Шэрагаў. У наступнай табліцы прыведзены апісання мер, уключаных у кубы.

Прыбытак, які банк атрымлівае ад індывідуальных кліентаў за перыяд часу. Эканамічны даход азначае чыстую прыбытак або страту ад аперацый за вылікам падаткаў з даходу на капітал за асобны перыяд часу. Даход ад розніцы працэнтных ставак даход, які банк атрымлівае ад індывідуальных кліентаў за асобны перыяд часу. Ён уяўляе сабой розніцу паміж узроўнем працэнтных ставак, па якіх банк дае крэдыт і прымае дэпазіты. Даход у форме камісій даход, які банк атрымлівае ў форме камісій ад індывідуальных кліентаў за асобны перыяд часу. Рэзервы на выпадак невяртання пазык Рэзерв на выпадак невяртання пазык індывідуальнымі кліентамі. Выдаткі па дадзеным прадукту выдаткі, звязаныя з канкрэтным прадуктам, за асобны перыяд часу. Выдаткі выдаткі, якія нясе банк, за асобны перыяд часу. Чысты прыбытак чысты прыбытак азначае чыстую прыбытак / страту ад аперацый за вылікам падаткаў, расходаў па выплаце працэнтаў і дывідэндаў за асобны перыяд часу. Аперацыйныя выдаткі аперацыйныя выдаткі, якія нясе банк пры ўзаемадзеянні з індывідуальнымі кліентамі, за асобны перыяд часу.

У наступнай табліцы прыведзены апісання пяці вымярэнняў, якія былі ўключаныя ў кубы - для гэтага выкарыстоўваліся табліцы вымярэнняў у вітрыне дадзеных, арганізаванай па схеме "зорка".

200001 2 19128 Kб ProductDim 14 1 3 Kб RegionDim 51 2 8 Kб TimeDim 24 3 5 Kб CustSegmentDim 7 1 2 Kб

На малюнку 4 паказаны дадзеныя куба пасля яго апрацоўкі.

Апрацоўка вынікаў для кожнага рэжыму захоўвання дадзеных

Усе наступныя графікі пабудаваныя для узроўняў агрэгавання 0%, 30%, 60% і 90%, хоць у большасці выпадкаў выкарыстоўваюцца толькі значэння ад 30% да 60% (0% і 90% былі ўключаныя для параўнання). Нагадаем, што ўзровень агрэгавання характарызуе павелічэнне прадукцыйнасці апрацоўкі запытаў у параўнанні з адсутнасцю папярэдне разлічаных агрэгатаў дадзеных.

Час апрацоўкі для кожнага рэжыму захоўвання

Наступныя вынікі былі атрыманы ў выніку апрацоўкі ідэнтычных па структуры кубоў з рознымі рэжымамі захоўвання і ўзроўнямі агрэгавання.

Мал.5. Час апрацоўкі для кожнага рэжыму захоўвання

Такім чынам, можна зрабіць наступныя высновы:

• Пры ўзроўні агрэгавання 0% ROLAP спатрэбілася найменшую колькасць часу для апрацоўкі куба. Пры гэтым дадзеныя табліцы фактаў і вымярэнняў у куб ня дадаюцца і агрэгаты не разьлічваюцца.
• Па меры павелічэння ўзроўню агрэгавання, ROLAP - у параўнанні з MOLAP або HOLAP - затрачвае ўсё больш часу на апрацоўку куба.
• Адрозненне паміж MOLAP і HOLAP ў прамежку 30 - 60% нязначна.
• Час апрацоўкі MOLAP і HOLAP павялічваецца ў прамежку 60 - 90%, але не вельмі.
• Час апрацоўкі ROLAP павялічваецца экспанентна ў прамежку 60 - 90%.

На наступным графіку паказана змена патрабаванага прасторы на дыску ў залежнасці ад узроўню агрэгавання для кожнага рэжыму захоўвання.

Мал. 6. Патрабаваны аб'ём дыскавай прасторы
Гледзячы на ​​графік, можна заключыць, што:

• Рэжым захоўвання MOLAP патрабуе больш месца, чым HOLAP або ROLAP. (Кубы MOLAP ўтрымліваюць копіі зыходных фактаў і вымярэнняў).
• Адрозненне ў колькасці спажыванага дыскавай прасторы пры рэжымах MOLAP і HOLAP нязначна ў інтэрвале 0 - 60% і павялічваецца па меры набліжэння да ўзроўню 90%.
• Рэжым захоўвання HOLAP выкарыстоўвае найменшую колькасць дыскавай прасторы. Гэта абумоўлена тым, што копіі зыходных фактаў і вымярэнняў адсутнічаюць у базе дадзеных OLAP, а агрэгаты захоўваюцца ў аптымізаваным шматмернага фармаце.
• Рэжым захоўвання ROLAP патрабуе дадатковага месца на дыску, калі ўзровень агрэгавання перавышае 30% і калі ён набліжаецца да 90%. (Графік ўлічвае аб'ём прасторы, патрэбнага для захоўвання агрэгатаў дадзеных у рэляцыйнай базе дадзеных).

Дадзеная табліца паказвае аб'ём неабходнай дыскавай прасторы на дыску для MOLAP-куба у параўнанні з зыходнай схемай "зорка" (табліца фактаў і табліцы вымярэнняў) у РСУБД.

Узровень агрэгавання (%)Дыскавая прастора для MOLAP-кубаПамер схемы "зорка" (табліцы фактаў і вымярэнняў з індэксамі)Ступень сціску дадзеных пры пабудове кубоў MOLAP

60 335.75 5188 93.53 90 353.11 5188 93.19

Відавочна, што займанае OLAP-кубам месца складае прыкладна 7% ад аб'ёму, неабходнага для схемы "зорка". Нават пры 90% -ым узроўні агрэгавання ўдаецца дасягнуць амаль такой жа ступені сціску. Дадатковае прастору, неабходнае для пабудовы MOLAP-куба, залежыць ад колькасці узроўняў ў вымярэнні, колькасці мер і тыпу дадзеных.

У гэтае табліцы паказаныя час апрацоўкі запытаў MDX і SQL ( "Чаму роўны эканамічны даход за першыя кварталы 1996 і 1997 гг. І розніца паміж імі па кожным з сегментаў?").

Тып запытуЧас апрацоўкі

MDX 4 сек. SQL 88 сек.

Відавочна, што MDX-запыт прасцей і выконваецца значна хутчэй.

Таксама можна правесці параўнанне хуткасці апрацоўкі запытаў у розных асяроддзях: SQL-запыты, якія выконваюцца на SQL-сэрвэры, і MDX-запыты на OLAP-сэрвэры, захоўвае MOLAP-куб. (Кожны запыт выконваўся пасля перазагрузкі сервера, каб гарантаваць, што ў кэшы адсутнічаюць вынікі запытаў).

Нумар запыту (гл. табл. )Час, затрачаны на апрацоўку MDX-запыту на OLAP-серверы (выкарыстоўваючы рэжым захоўвання MOLAP і ўзровень агрэгавання 60%)Час, затрачаны на апрацоўку SQL-запыту на SQL-сэрвэрыпрыбл.лік запісаў

1 4 секунды 88 секунд 13 млн. 6 10 секунд 36 секунд 13 млн. 7 4 секунды 89 секунд 13 млн.

Нягледзячы на ​​тое, што падобнае параўнанне можа здацца не зусім карэктным, яго вынікі сведчаць аб тым, што выкарыстанне MDX-запытаў і Analysis Services можа істотна павысіць прадукцыйнасць. Паколькі OLAP-кубы ажыццяўляюць захоўванне папярэдне разлічаных агрэгатаў, эфектыўнасць у OLAP-асяроддзі значна вышэй, чым пры выкарыстанні рэляцыйных баз дадзеных.

Час выканання MDX-запытаў пры розных рэжымах захоўвання дадзеных

Разгледзім час апрацоўкі для найбольш часта выкарыстоўваюцца бізнес-пытанняў, выкарыстоўваючы MOLAP, ROLAP, або HOLAP, розныя ўзроўні агрэгавання, а таксама "цёплы" або "халодны" кэш. "Халодны" кэш азначае, што перад выкананнем запыту сервер перазагружаўся і ў кэшы не ўтрымлівалася вынікаў папярэдніх запытаў; у выпадку з "цёплым" кэшом, вынікі запытаў захоўваліся ў кэш-памяці. Час фіксавалася ў секундах (не ў мілісекундах). Прадукцыйнасць пры "цяплом" кэшы ў сярэднім значна вышэй, чым пры "халодным".

Сярэдні час апрацоўкі ( "Халодны" кэш)

На малюнку 7 паказаны залежнасць сярэдняга часу апрацоўкі запытаў для MOLAP, HOLAP і ROLAP ад узроўню агрэгацыі пры "халодным" кэшы.

Мал.7. Параўнанне значэнняў часу апрацоўкі

Наступны малюнак ўяўляе сабой павялічаны варыянт папярэдняга і больш дэталёва ілюструе змена прадукцыйнасці ў інтэрвале 30 - 90%.

Мал.8. Параўнанне часу апрацоўкі для найбольш часта выкарыстоўваюцца запытаў

Інфармацыя, прыведзеная на графіцы, паказвае, што:

• MOLAP дазваляе дасягнуць найбольшай хуткасці пры апрацоўцы запытаў, прычым прадукцыйнасць істотна ўзрастае пры павелічэнні ўзроўню агрэгавання з 0 да 60% (таксама, як і для ROLAP і HOLAP).
• Пры павелічэнні ўзроўню агрэгавання з 60 да 90% рост прадукцыйнасці з'яўляецца неістотным для ўсіх рэжымаў.

Прыведзены графік паказвае залежнасць сярэдняга часу апрацоўкі для MOLAP, HOLAP і ROLAP ад узроўню агрэгацыі ў выпадку з "цёплым" кэшам.

Мал.9. Час апрацоўкі запытаў

Такім чынам:

• Калі вынік запыту маецца на кэш-памяці, яго апрацоўка адбываецца практычна імгненна (менш за 1 секунды) для ўсіх запытаў незалежна ад рэжыму захоўвання або ўзроўню агрэгавання. На графіцы значэнне роўна 1, так як на папярэдніх малюнках час таксама акругляюцца да секунд.

Сярэдні час апрацоўкі пры "халодным" кэшы ў параўнанні з "цёплым" кэшам

Мал. 10. Параўнанне часу апрацоўкі для "цёплага" і "халоднага" кэша
Гэты графік паказвае, што пры "цеплынёй" кэшы вынік выдаецца менш, чым за 1 секунду. Вы можаце выконваць найбольш частыя запыты ў якасці пакетнага задання адразу пасля апрацоўкі дадзеных куба. У гэтым выпадку затрачваецца час будзе менш.

Наступны графік ілюструе час працы працэсара пры апрацоўцы запытаў да MOLAP-, ROLAP-, and HOLAP-кубам. Чырвоным колерам адзначана час працы SQL-сервера, сінім - OLAP-сервера.

Мал.11. Час загрузкі працэсара для розных рэжымаў захоўвання

Дадзены малюнак кажа аб тым, што:

• MOLAP патрабуе працы цэнтральнага працэсара толькі на OLAP-серверы.
• ROLAP і HOLAP выкарыстоўваюць працу працэсара ў большай ступені. Нават HOLAP інтэнсіўна выкарыстоўвае RDBMS-сервер. Гэта можа быць выклікана тым, што выбраныя для дадзенага эксперыменту запыты не ўтрымліваюць шмат агрэгатаў, ужо наяўных у Analysis Services (іх неабходна разлічваць падчас выканання).

Некаторыя рэкамендацыі па аптымізацыі прадукцыйнасці

Існуе дзве катэгорыі найбольш эфектыўных спосабаў аптымізацыі OLAP-кубоў: зніжэнне часу апрацоўкі дадзеных OLAP-куба і зніжэнне часу выканання запытаў.

Рэкамендацыі па памяншэнні часу апрацоўкі дадзеных куба

Нам удалося знізіць час апрацоўкі з некалькіх гадзін да пары хвілін, выкарыстоўваючы наступныя прыёмы:

• Выкарыстанне прасторавай схемы для вітрыны дадзеных. Схемы "зорка" дабра падыходзяць для OLAP-кубоў. Акрам першаснага ключа, звязанага з скурным вымярэннем табліцы фактаў, мы апісалі сувязі па вонкавым ключу Паміж фактамі І табліцамі вымярэнняў. Мы стварылі складовай індэкс па ўсіх вонкавых ключах Ў табліцы фактаў. Акрам гэтага, мы вызначылі індэксы па Асобны знешніх ключах, Каб паскорыць аперацыі апрацоўкі.
• Выкарыстанне Cube Editor для аптымізацыі структуры схемы І мінімізацыі колькасці злучэнняў, неабходных пры апрацоўцы дадзеных куба. Мы павялічылі Памер буфера для працэсу (дыялогавае Акно Properties для OLAP-сервера) ды 1 Гб на кампутары в е 4Гб памяці. Важная таксама надаць УВАГА ВЫБАРЫ ўзроўню агрэгавання. Пасвіліся ўстаноўкі значэння ўзровень агрэгавання 90% спатрэбілася 8 - 9 гадзін, Каб скончыць апрацоўку дадзеных куба. Аптымальным з'яўляецца значэнне каля 25%. Пасля гэтага можна злёгку павышаць узровень агрэгавання і дасведчаным шляхам усталяваць, ці акажа гэта станоўчае ўздзеянне на хуткасць выканання найбольш часта выкарыстоўваюцца запытаў.

Нам удалося аптымізаваць час апрацоўкі MDX-запытаў шляхам:

• Эфектыўнага выкарыстання памяці для Analysis Services;
• Размяшчэння Analysis Services (OLAP-кубоў) і SQL-сервера (вітрыны дадзеных) на розных кампутарах;
• Выкарыстання рэжыму захоўвання MOLAP.

Таксама рэкамендуецца выкарыстоўваць майстар "Usage-Based Optimization Wizard" для разліку дадатковых агрэгатаў дадзеных, неабходных для павышэння прадукцыйнасці апрацоўкі запытаў.

заключэнне

У дадзеным артыкуле мы правялі даследаванне прадукцыйнасці SQL Server 2000 Analysis Services пры выкарыстанні розных рэжымаў захоўвання (ROLAP, MOLAP, і HOLAP) і розных узроўняў агрэгавання пры выкарыстанні вялікіх аб'ёмаў інфармацыі.

Асноўныя высновы:

• Па меры павелічэння ўзроўню агрэгавання больш часу патрабуецца для апрацоўкі ROLAP-кубоў у параўнанні з кубамі MOLAP and HOLAP.
• MOLAP патрабуе больш месца на дыску, чым HOLAP і ROLAP; HOLAP патрабуе менш за ўсё дыскавай прасторы.
• Калі OLAP-кубы выкарыстоўваюць рэжым захоўвання дадзеных МOLAP, займанае імі месца складае толькі 7% у параўнанні з памерам зыходнай схемы "зорка".
• Выкарыстанне MDX-запытаў можа істотна паскорыць працу сістэмы, так як OLAP-кубы ўтрымліваюць папярэдне разлічаныя агрэгаты дадзеных.
• Максімальная хуткасць апрацоўкі запытаў дасягаецца пры выкарыстанні рэжыму захоўвання дадзеных MOLAP.
• MOLAP выкарыстоўвае працэсар толькі на OLAP-серверы.

Адным з ключавых пераваг, якія дае выкарыстанне сховішчаў дадзеных, з'яўляецца магчымасць правядзення аналізу. А адно з асноўных вартасцяў інтэрактыўнага аналізу (інакш - FASMI, Fast Analysis of Shared Multidimensional Information, Хуткі аналіз выкарыстоўваецца сумесна шматмернай інфармацыі) - магчымасць выкарыстання Analysis Services. Дадзены артыкул дае доследная пацвярджэнне высокай прадукцыйнасці кубоў, пабудаваных з выкарыстаннем Analysis Services.