Efektywne zarządzanie metadanymi. Import danych słowników z xls

Jednym z najczęstszych wyzwań dla administratora biznesowego Metastudio DRM, jest tworzenie słownika na podstawie skoroszytu Excel, w którym tenże słownik był dotychczas uprawiany.

Realizacja takiego zadania wymaga wykonania co najmniej 3ch kroków:

1. Założenie struktury tabeli słownikowej w bazie danych.
2. Rejestracja słownika w Metastudio.
3. Import danych ze skoroszytu, w którym dane były dotychczas utrzymywane.

W przypadku prostych słowników jest to zadanie dość trywialne. No dobrze, ale co rozumiem przez „prosty” słownik? Jest to słownik charakteryzujący się:

• niewielką liczbą kolumn (kilka),
• niewielką liczbą rekordów (kilkaset),
• uporządkowanymi danymi słownikowymi (kody o stałej długości/strukturze, opisy, etc.)

Gorzej wygląda sytuacja ze słownikami, które utrzymywane są w postaci skoroszytów Excel przez dłuższy czas, ich zawartość odbiega w mniejszym lub większym stopniu od pierwotnych założeń, zawierają dodatkowe uwagi, wartości niezgodne z pierwotnie przewidzianym „typem” kolumny, etc. W takim wypadku każdy z powyższych kroków będzie wymagał odpowiedniej uwagi.

Założenie struktury tabeli słownikowej w bazie danych

Założenie struktury tabeli słownikowej w bazie danych będzie wymagało przygotowania skryptu DDL, o składni odpowiedniej dla silnika bazy danych, w której to założona ma być tabela słownikowa. Zacznijmy od nazw kolumn. Najbardziej oczywistym podejściem do realizacji zadania jest wykonanie go w następujących etapach:

1. Otwarcie skoroszytu Excel zawierającego dane słownikowe
2. Skopiowanie wiersza z nagłówkami do odrębnego arkusza i przetransponowanie przy wklejaniu. Otrzymamy w ten sposób listę kolumn docelowego słownika.
3. Jeżeli nazwy kolumn mają charakter opisowy, konieczne będzie utworzenie nazw technicznych dla poszczególnych kolumn. Należy wziąć pod uwagę, iż często w projekcie, w ramach którego realizowane jest zadanie, istnieje pisany bądź umownie przyjęty schemat zapisu nazw kolumn, np.: podkreślenia zamiast spacji, konkretne prefiksy bądź sufiksy, charakterystyczny sposób skracania nazw.

Mamy już nazwy kolumn dla naszego słownika. Teraz pora na określenie typu oraz długości / precyzji dla każdej z kolumn. Tutaj najczęściej zaczynają się „schody”, gdyż na tym etapie musimy wziąć pod uwagę jeden z kolejnych kroków, które będą realizowane – tj. import danych. Dlaczego? Otóż właściwe zdefiniowanie struktury słownika będzie wymagało zastosowania takiego podejścia, które będzie odpowiednie dla celów jego późniejszego wykorzystania. Co przez to rozumiem?

1. Jeżeli słownik będzie zasilany tylko 1-krotnie (tzw. zasilanie „zerowe”), a następnie utrzymywany z poziomu interfejsu Metastudio DRM, to stosowniejszym będzie precyzyjne zdefiniowanie poszczególnych kolumn. Czyli jeżeli przykładowo w danej kolumnie ma być 2-znakowy kod ISO kraju – definiujemy go np. w Oracle jako varchar2(2). 
   W przypadku wystąpienia jakiejś niezgodności pomiędzy importowanymi danymi a przewidzianą strukturą, będziemy musieli poprawić ręcznie dane w skoroszycie – ale robimy to tylko raz.
2. Jeżeli słownik będzie wielokrotnie aktualizowany w trakcie jego „życia” poprzez import ze skoroszytu Excel, to sensowniej będzie przewidzieć – analizując dane w każdej z kolumn – wszystkie potencjalne przypadki, dołożyć jeszcze jakiś „zapas” dla długości kolumny a walidację danych wprowadzanych „ręcznie” poprzez interfejs Metastudio DRM zdefiniować poprzez dodanie odpowiednich walidatorów dla każdej kolumny
3. Na tym etapie należy również rozważyć dodanie kolumn technicznych:
   1. Co najmniej kolumny ze sztucznym kluczem pierwotnym: w przypadku popularnych baz danych (Oracle, Ms SQL Server) najwygodniej jest dodać kolumnę typu identity, z automatycznym generowaniem wartości
   1. Jeżeli słownik ma być audytowany, wówczas należy przewidzieć kolumny przechowujące informacje audytowe, takie jak Zmodyfikowany przez, Data modyfikacji oraz ewentualnie dodatkowo Utworzony przez oraz Data utworzenia
   1. Jeżeli wartości w słowniku mają podlegać historyzacji, należy dodać stosowne kolumny, np. wykorzystując model wolnozmieniających się wymiarów typu 2 (SCD Type 2) i dodając kolumny Data początku okresu obowiązywania oraz Data końca okresu obowiązywania rekordu
   1. Jeżeli dane w słowniku mają być walidowane a wynik tej walidacji ma być przechowywany w słowniku, należy dodać kolumnę ze statusem walidacji

Jeżeli słownik będzie modyfikowany wyłącznie poprzez interfejs Metastudio DRM (ręczna edycja, import z Excel’a), wówczas nie zaleca się stosowania dodatkowych restrykcji bazodanowych (typu CHECK, czy też dedykowanych wyzwalaczy). Właściwszym jest wykorzystanie funkcji aplikacji – unikniemy wówczas potencjalnych „kolizji” pomiędzy definicjami na poziomie bazy danych i Metastudio DRM.

Rejestracja słownika w Metastudio

Przy rejestracji słownika w Metastudio należy pamiętać o weryfikacji typów kolumn, które ustawione zostały automatycznie podczas odczytu metadanych tabeli, m.in.: weryfikacja poprawności klucza pierwotnego, weryfikacja ustawionych automatycznie typów kolumn (przykładowo typ DATE w Oracle rejestrowany jest jako DATETIME co niekoniecznie jest pożądane), poprawne zdefiniowanie kolumn audytowych, etc. W kolejnym kroku należy zadbać o zdefiniowanie właściwości takich jak okresy obowiązywania czy wartości domyślne. Jeżeli słownik ma być zasilany wyłącznie jednorazowo, w pierwszej kolejności należy wykonać kolejny krok – tj. import danych z Excela, a dopiero w następnym etapie wykonać czynności związane z doparametryzowaniem słownika, tj:

• ustawieniem walidatorów kolumnowych,
• dodaniem dziedzin,
• ustawieniem domyślnego formatowania.

Import danych ze skoroszytu, w którym dane były dotychczas utrzymywane

Najczęściej występującymi problemami podczas importu danych ze skoroszytów Excel są:

• Formuły wyliczeniowe w kolumnach, które chcemy importować: same dane, które będą importowane, zlokalizowane są w jednym z plików XML, które składają się na „paczkę” w postaci pliku widzianego przez nas w systemie operacyjnym jako .xlsx (aby się o tym przekonać, wystarczy zmienić rozszerzenie skoroszytu z .xlsx na .zip i sprawdzić efekt). Jeżeli w kolumnie skoroszytu zamiast wartości jest formuła wyliczeniowa, to mechanizm pobierający dane ze skoroszytu „widzi” zawartość jako ciąg znaków i import zapewne zakończy się niepomyślnie
• Błędy typów danych, polegające najczęściej na zapisaniu dat lub liczb jako tekst. Jak się o tym przekonać? Po otwarciu takiego skoroszytu będziemy widzieć przy problematycznej komórce stosowną informację w postaci ostrzeżenia (zielony trójkąt w rogu komórki). Przykładowo, jeżeli w takiej komórce jest wartość 23,45 ale przechowywana jako tekst, to przy imporcie separator dziesiętny nie zostanie prawidłowo rozpoznany, gdyż w pliku XML separatorem dziesiętnym jest zawsze „kropka”, a to, że widzimy po otwarciu Excela „przecinek” to wyłącznie jest narzucony format wyświetlania
• Kolumny ukryte z widoku użytkownika, które mogą spowodować problem przy dopasowaniu importowanych danych do struktury słownika
• Różnego rodzaju dopiski, komentarze wpisane bezpośrednio w wartość komórki

Reasumując: przed przystąpieniem do importu warto przejrzeć dokładnie importowany skoroszyt (a raczej konkretny arkusz skoroszytu zawierający dane, które będziemy importować do słownika) pod kątem wychwycenia oraz usunięcia powyższych problemów. Zaoszczędzi nam to sporo czasu a być może in nerwów podczas próby zaimportowania danych do słownika.