Прикладна система на базі засобів Microsoft

Використання технологій Microsoft в системі "FinExpert сховище даних" компанії IDM

У 2002 році корпорація Microsoft випустила SQL Accelerator For BI – набір інструментів для побудови рішень, що використовують OLAP та інформаційні сховища. Нагадаємо, що до його складу входять сервер баз даних SQL Server, аналітичні OLAP-служби для Багаторозмірні аналізу Analysis Services, служби збору, перетворення і завантаження даних Data Transformation Services (DTS), а також засоби пошуку закономірностей в даних і графічні інтерфейси. Але для реалізації конкретної бізнес-завдання при застосуванні SQL Accelerator все одно потрібна участь програмістів, що ми і хочемо показати на прикладі системи "Сховище даних "від компанії IDM.


Технологічна основа


Як сервер бази даних сховища використовується Microsoft SQL Server 2000 SP3, хоча ніщо не заважає задіяти й інші СУБД. Як OLAP-сервера застосовуються служби Analysis Services зі складу SQL Server. На етапі завантаження даних в проміжні таблиці сховища використовується технологія DTS. Для генерації звітів застосовується генератор звітів Seagate Crystal Reports 8.5 і компоненти Microsoft Office Web Components (OWC). Загальна архітектура системи будується за модульним принципом і ділиться на кілька підсистем (див. Малюнки 1, 2).


Для побудови додатків підтримки прийняття рішень у системі застосовуються Analysis Services з пакету SQL Server. При необхідності можлива підтримка і інших продуктів класу OLAP – наприклад, ORACLE Express. Однак для цього вже потрібна додаткова розробка. Поки ж технологія інтеграції програм IDM в Microsoft Analysis Services дозволяє вибрати один з варіантів використання:



Самі OLAP-куби розробляються в стандартному інструменті Microsoft – Analysis Manager – із застосуванням мови MDX. При реалізації конкретного проекту можна вибрати, наприклад, структуру даних типу "зірка", оскільки цей підхід дозволяє мінімізувати розмір вихідної бази даних. Прийнятна швидкість роботи з кубом досягається за рахунок максимально повного агрегування даних і вибору змішаного (HOLAP) способу зберігання даних куба.


При перенесенні даних в проміжні таблиці бази даних ми використовували DTS. У процесі розробки пакетів нам довелося зіткнутися з низкою проблем, і перш за все з "прихильністю" готового DTS-пакету до імен серверів, баз даних, таблиць та шляхам файлів. Звичайно, цю проблему можна вирішити, описавши за одним джерелом даних для бази джерела і приймача, а для решти таблиць використовувати копії (Клони) цих джерел. Але в такому випадку пакет зможе обробляти тільки одну таблицю в кожен момент часу. При великих же обсягах даних представляється більш доцільним описувати кілька фізичних джерел даних (до п'яти), так щоб всі таблиці розподілялися рівномірно по цим відкритим джерелам, в залежності від середніх (прогнозованих) обсягів містяться в них даних. При цьому навантаження на кожне відкрите з'єднання з базою даних повинна бути по можливості однаковою. На практиці такий підхід дозволяє збільшити швидкість пересилання даних приблизно в 3-4,5 рази – залежно від ступеня розпаралелювання.


Використання DTS на віддалених майданчиках мало два очевидні недоліки:



Для усунення цих недоліків процедура вивантаження даних була "винесена" в самостійне консольний додаток, написане на Visual Basic 6. Таким чином, тепер для вивантаження потрібно тільки наявність Microsoft Data Access Components (MDAC). Приблизний зовнішній вигляд налаштувань вивантаження даних наведено в Лістингу 1. Як можна помітити, в реєстрі прописуються фактично фрагменти SQL-пропозиції, що дозволяє задавати список таблиць і правил вивантаження без перекомпіляції програми або без модифікації DTS-пакета.


При реалізації системи "Сховище даних" в якості основного засобу розробки ми використовували Microsoft Visual Basic 6 з комплекту Microsoft Visual Studio 6 SP5. Деякі компоненти, наприклад COM-інтерфейси, були реалізовані на VC + + 6 за допомогою ATL.


Для проектування (описи) структури баз даних і генерації SQL-сценаріїв створення баз даних застосовувалося CASE-засіб CA ERWin 4.1 у поєднанні з репозитарієм для колективної розробки ModelMart 4.1.


"НОУ-ХАУ" від компанії IDM


Однією з основних завдань системи є забезпечення отримання цілісної картини з розрізнених наборів даних. Як відомо, більшість підприємств на теренах колишнього СНД не мають єдиних корпоративних довідників, тому, навіть якщо зібрати інформацію з підрозділів в центрі, її не можна буде адекватно зіставити, і вже тим більше консолідувати. Ця проблема вирішується за допомогою підсистеми підстановки відповідностей.


Процес підстановки відповідностей передбачає заміну всіх полів посилань (relationships fields) розрізнених записів, що надійшли з різних майданчиків, а отже, заснованих на різних версіях довідників (Можливо, продукованих різними системами), на посилання на записі так званих корпоративних довідників сховища. Процедура підстановки відповідностей виконується у вигляді пакету (на всій множині записів проміжної таблиці), проте повинна відпрацювати для кожного посилання кожного запису, імпортованої в проміжні таблиці сховища.


Очевидно, що при такій постановці завдань найбільш логічним є використання так званих предметних ідентифікаторів записів, тобто пошук ID запису довідника по набору предметних полів, однозначно ідентифікують сутність, яку представляє запис довідника. Наприклад, для контрагента такими наборами можуть бути "код контрагента" або набір "код банку" + "номер рахунку в цьому банку". Для того щоб дана операція була можливою, кожна запис повинна містити надмірну інформацію, якої і є предметні поля з довідника.


З технічної точки зору процес підстановки відповідностей являє собою виконання масованих модифікацій записів проміжних таблиць. Для кожного посилання між двома таблицями описується правило підстановки відповідності, яке визначає, по відповідності яких полів проміжної таблиці і довідника в яке поле проміжної таблиці потрапляє ключ запису довідника. Крім того, враховуються так звані "ручні" правила відповідностей, які являють собою свого роду виключення із загального правила. "Ручні" правила обробляються для кожного правила в першу чергу і мають більш високий пріоритет, ніж автоматичні.








Малюнок 3. Приклад OLAP-куба, розробленого для конфігурації "Торговий дім"

Процес підстановки автоматичних відповідностей поділяється на два етапи. На першому етапі з довідника в тимчасову таблицю витягуються стовпці, задіяні в правилі. Потім, в залежності від налаштувань системи, видаляються дублікати по полях відповідностей (може бути залишена запис з числа дублікатів з мінімальним значенням PK – це визначається настройкою). Крім того, з тимчасової таблиці видаляються запису, одне або більше полів яких порожні (Малюнок 4). На другому етапі здійснюється підстановка відповідників у проміжну таблицю (Малюнок 5). Підстановка виконується для записів, для яких відпрацювали всі попередні правила. Ознака сповістити чергового правила для певного запису проміжної таблиці – збільшення на одиницю значення в службовому полі confset цього запису.








Малюнок 4. Схема видалення записів

Після того як процес підстановки для таблиці завершений, записи, для яких виконані підстановки за всіма заданими правилами, можуть бути перенесені в основні таблиці сховища. Записи, для яких з яких-небудь причин не виконувалася підстановка хоча б по одному правилу, залишаються в проміжних таблицях до того моменту, коли користувач усуне причину "відсутності" відповідних посилань.








Малюнок 5. Схема підстановки відповідностей

Налаштування правил установки відповідностей проводиться в два етапи: на першому етапі задаються автоматичні правила відповідності (фактично правила пошуку відповідностей), на другому, при необхідності, задаються "Ручні" відповідності для записів ("прямі" відповідності типу запис-запис), які не можуть з яких-небудь причин оброблятися автоматичними правилами. Як автоматичні, так і "ручні" правила відповідності призначені для забезпечення підтримання посилальної цілісності записів, що надходять з різних джерел.








Малюнок 6. Налаштування автоматичних правил відповідності

Правило відповідності встановлюється окремо для кожного посилання таблиці фактів сховища. У правилі можна вказати кілька умов (тобто відповідностей конкретних полів), при виконанні одного з яких запис вважається обробленої за даним правилом. Порядок застосування умов в рамках правила визначається його пріоритетом (чим менше пріоритет, тим раніше застосовується умова). Можна задати умову більше ніж по одному полю – для цього потрібно просто вказати однакові значення пріоритету для різних умов, в такому випадку умови об'єднуються по оператору "І" (AND). Налаштування "ручних" правил відповідності виконується на рівні окремих записів.








Малюнок 7. Призначення відповідностей вручну

Підсистема генерації статистичних звітів реалізована на базі генератора звітів Crystal Reports. Ця підсистема дозволяє користувачам отримувати статистичні звіти безпосередньо за даними сховища, в тому числі і без застосування інших підсистем, як-то: OLAP-підсистема, Web-підсистема і ін








Малюнок 8. Задані ручні відповідності






Малюнок 9. Динамічно формована форма запиту параметрів для побудови звіту

Підсистема для роботи з довідниками (див. Малюнок 10) була б стандартною, якби не списки (grids) та екранні форми для кожного довідника, які не створюються програмістом, а конструюються "на ходу ", за обраними таблиць і метаданих (найменування полів російською мовою і т. д.). Крім того, проводиться відстеження полів-посилань і заміна стандартних полів введення на випадаючі списки (визначення посилань виконується за допомогою стандартної інформації, що зберігається в системних таблицях, природно "обернутої" до подання; код подання див Лістингу 2).








Малюнок 10. Редагування корпоративного довідника контрагентів






Малюнок 11. Зовнішній вигляд вікна режиму налаштування доступу за таблицями

При цьому також відстежуються типи полів, обмеження на їхню довжину, обов'язковість для заповнення і т. д. Це дозволяє, зокрема, додавати нові довідники в систему без будь-якого програмування, шляхом налаштувань в модулі "Адміністратор системи".


Налаштування системи та безпека


Налаштування системи "Сховище даних" зберігаються в реєстрі Windows. Для редагування настройок передбачений спеціальний інструмент зі зручним інтерфейсом, щоб не доводилося користуватися програмою RegEdit (Що, як правило, веде до помилок при настроюванні і серйозних проблем, аж до псування системного реєстру). Засоби забезпечення безпеки системи інтегровані із засобами безпеки SQL Server – Використовується аутентифікація (підтримуються режими як Windows, так і Integrated Security). Підсистема поділу прав доступу використовує механізм ролей (roles) SQL Server. Ця підсистема реалізована на підставі механізмів SQL Server – користувачів і ролей (roles). Поділ доступу до даних таблиць порядково проводиться за допомогою механізму керованих уявлень (controlled views; див. Лістинг 3).


На сьогодні в системі підтримуються наступні рівні призначення дозволів доступу (див. Малюнки 11, 12):



Збереження налаштувань у власних настроювальних таблицях дозволяє:









Малюнок 12. Зовнішній вигляд вікна режиму налаштування доступу по рядках таблиць






Малюнок 13. Налаштовані з програми "Адміністратор системи" права доступу до таблиць при перегляді їх засобами SQL Server






Малюнок 14. Утиліта налаштування параметрів (вивантаження)

У системі передбачена можливість додавати нові джерела даних для сховища поступово. Ця процедура виконується на рівні настроювань системи, тобто без зміни її компонентів. Для налаштування сутностей – Джерел даних – у системі передбачена спеціальна утиліта (див. Малюнок 14). При налаштуванні сутностей можна задати параметри, необхідні для перенесення даних з таблиці-джерела в таблицю-приймач (Найменування сутностей, поля таблиці джерела і відповідні поля приймача, умови вибірки). Крім того, можна задати умову вивантаження з урахуванням наявності пов'язаної таблиці, тобто вивантажити тільки ті записи, для яких є відповідні в іншій вивантажуваний таблиці; таку умову застосовується з урахуванням періодів фіксації обох таблиць. Фактично налаштування вивантаження представляють собою вказівку таблиць і полів для вивантаження, а також деяких умов вивантаження у форматі фрагментів SQL-запитів.


Після виконання процедури підстановки відповідностей дані можуть бути перенесені з проміжних таблиць в основні таблиці сховища. У цьому процесі беруть участь записи, для яких успішно застосовані всі задані правила відповідностей. Налаштування алгоритму перенесення даних виконується для кожної проміжної таблиці індивідуально засобами системи (див. Малюнок 15). Як показано на малюнку, настройка перенесення даних фактично являє собою SQL-запити, які будуть виконуватися системою при перенесенні даних для кожної таблиці.








Малюнок 15. Налаштування перенесення даних.






Малюнок 17. Призначення файлової групи для поділюваної таблиці.

Реалізація схеми поділу в системі спричинила за собою необхідність розробки власної схеми генерації автоінкрементні первинних ключів, оскільки поля з ознакою IDENTITY для поділюваних таблиць не підтримуються. Ця схема реалізується за допомогою тригерів і декількох допоміжних таблиць. При реалізації такої схеми основна увага приділялася підвищенню швидкодії при виконанні пакетної вставки великих масивів даних. Для цього використовувалися тимчасові таблиці зі структурою, яка відповідає структурі "базової" таблиці, за винятком поля – сурогатного первинного ключа: для нього був обраний стандартний автоінкремент SQL Server з динамічно заданим діапазоном. У Лістингу 6 наведено приклад тригера для генерації автоінкрементні первинного ключа.


Син помилок важких


Як показує практика, дані найчастіше доводиться отримувати з вельми недосконалих систем і СУБД. Причина проста і прозаїчна – як правило, основним критерієм при покупці OLTP-системи є її функціональність. А найбагатша функціональність зазвичай буває у тих систем, які розробляються давно і несуть неабиякий вантаж застарілих технічних рішень. До того ж для коректної обробки даних бажано дотримуватися ряд вимог щодо даних системи-джерела:



У принципі, можлива експлуатація рішень для сховищ даних і без наявності такої функціональності у системах – джерелах даних, проте це призведе до зростання обсягу переданих даних або до неможливості коректного відстеження віддалених записів. Крім того, структури даних різних систем мають свої особливості, навіть за умови автоматизації однієї і тієї ж предметної області. Звідси рекомендації – будувати рішення для вивантаження даних на підставі засобів, які передбачають можливість максимально деталізованою налаштування, наприклад зберігання фрагментів SQL-виразів на комп'ютері, де проводиться вивантаження даних.


Далі, як відомо, максимальна кількість таблиць в одному SQL-запиті для SQL Server 2000 одно 256. Здавалося б, це доволі багато. Однак не слід забувати, що якщо в запиті беруть участь подання, то в число використовуваних в ньому таблиць входять всі таблиці, застосовувані в даному поданні. У нашому випадку при використанні розділяється подання ситуація ускладнюється тим, що при досить нормалізованої схемі даних кількість таблиць у запиті доводиться множити на кількість років, за який зберігається інформація в розділі. Якщо в запиті одна і та ж таблиця підключається декілька разів, з різними псевдонімами, то вона "прочитується" також кілька разів. У результаті для складних таблиць даних з великою кількістю довідників "впертися" в обмеження в 256 таблиць можна буквально за два-три роки.


В принципі з такою проблемою ми зіткнулися лише у звітах, які будуються безпосередньо по базі даних сховища і інтегрують дані за досить тривалий період або містять стовпці, обчислювані окремими підзапитах за періодами. Вихід з такого становища досить простий: для кожного такого звіту створюється збережена процедура, в якій покроково наповнюється якась тимчасова таблиця. Після закінчення виконання процедура повертає викликав модулю набір рядків, що містить рядки цієї тимчасової таблиці. Таким чином, один великий запит ми розбиваємо на ряд дрібних, в кожному з яких менше 256 таблиць.


У цій статті ми спробували узагальнити досвід, отриманий при вирішенні класичної завдання OLAP – сховище даних протягом кількох останніх років. Сподіваємося, що вона буде цікава як фахівцям компаній, розробляє готові рішення, так і співробітникам IT-підрозділів корпорацій. Для тих, хто хотів би більше дізнатися про вирішувані бізнес-завданнях і функціональності системи, рекомендуємо звернутися до статей, наведених у списку "Література". Для поповнення теоретичного багажу в області технологій OLAP & Warehousing можна звернутися до ресурсів сайту http://www.olap.ru.


Література



  1. Банасевіч А., Гурленко А. Сховища даних і OLAP / / Корпоративні системи, Київ, 2002, № 4, с. 44-51.
  2. Банасевіч А., Кудінов А. Від Informix – до SQL Server / / Відкриті системи, січень 2002, рубрика СУБД, с. 55-61.
  3. Муравйов В., Банасевіч А. Використання технологій INFORMIX в ERP-системі FinExpert. 8.0 / / INFORMIX Magazine / російське видання, літо 2001, рубрика "Проекти та програми", с. 23-27.
  4. Прес-реліз "Сховища даних і технології OLAP для обробки інформації" / / Office, 2002, № 5 (52), с. 77 (див. також http://www.finexpert.com/News/Presrel/ hdolappr.html).
  5. Муравйов В., Банасевіч А. Технологічні рішення в управлінні підприємством / / Корпоративні системи, 2000, № 4, рубрика "Галузеві рішення", с. 27-31.
  6. Прес-реліз "Cистема FinExpert ™ 8.0 компанії IDM переведена на платформу СУБД Microsoft SQL Server 2000", http://www.microsoft.com/rus/General/Press/ 2001/june/FinExpert.htm.
  7. Використання сучасних технологій корпорації Microsoft в системі автоматизації управління підприємством FinExpert ™ 8.0 / / Матеріали спільного семінару компаній IDM і Microsoft на виставці "Управління підприємством-2001 ", 6.12.2001.
  8. Семінар Троїстого союзу компаній / / Матеріали спільного семінару компаній IDM, Microsoft і KPMG на виставці "Управління підприємством-2000", 2.11.2000, http://idm.kiev.ua/news/index.html (див. також IDM, KPMG і Microsoft провели спільний семінар, http://itc-ua.com/article.phtml?ID=4251).
  9. Server 2000 Books Online, Microsoft.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*