BISM – Model Semantyczny BI

Od swojej pierwszej wersji, wydanej pod nazwą OLAP Services w dalekim 1998, kluczowym pojęciem w Analysis Services (SSAS) była wielowymiarowa kostka OLAP. Zbudowanie kostki od zawsze było zadaniem dosyć kosztownym – należało bowiem zbudować hurtownię danych implementującą określony model logiczny, a następnie opracować proces ETL ładujący dane do hurtowni oraz zdefiniować strukturę kostki OLAP (wymiary, hierarchie, kalkulacje).

Raz stworzona struktura kostki była raczej mało podatna na zmiany – każda poważniejsza modyfikacja lub dodanie nowego wymiaru wymagała bowiem ponownego przeprocesowania kostki. Jeśli pomyślimy o produkcyjnych kostkach zawierających dziesiątki lub setki gigabajtów danych i agregacji, z łatwością uświadomimy sobie skalę wyzwań stojących przed zespołami utrzymującymi klasyczne rozwiązania OLAP.

Opisany powyżej model sprawdza się bardzo dobrze w dużych wdrożeniach o skali korporacyjnej, gdzie oczekujemy że raz zdefiniowana kostka OLAP nie będzie podlegała większym zmianom strukturalnym w trakcie życia systemu. Istnieje jednak wiele obszarów w których analizy wielowymiarowe mogą być bardzo użyteczne, jednak zastosowanie klasycznego podejścia byłoby niepraktyczne ze względu na czas i koszty.

Przykładem są wszelkiego rodzaju analizy ad-hoc, tworzone przez analityków lub użytkowników biznesowych. Bardzo często na swoje potrzeby łączą oni dane z kilku arkuszy Excela, baz Access czy płaskich plików, przetwarzają wyniki za pomocą formuł – a wynik prezentują np. w formie tabeli przestawnej czy wykresu. Tworzonym w ten sposób analizom brakuje jednak warstwy pośredniej, modelu semantycznego, który porządkowałby i organizował pozyskane z wielu źródeł dane.

Odpowiedzią na te potrzeby jest PowerPivot – z jednej strony intuicyjna nakładka na Excela pomagająca użytkownikowi w zapanowaniu nad chaosem danych, z drugiej – potężny silnik analityczny pozwalający na tworzenie skomplikowanych zapytań w języku DAX.

Mamy więc dwa krańcowo różne podejścia – klasyczny, „ciężki” proces wymagający przejścia całej ścieżki z procesem ETL, relacyjną hurtownią i kostką OLAP. Jego przeciwieństwem jest realizowana przez PowerPivot koncepcja self-service BI, gdy to na użytkownika biznesowego delegujemy odpowiedzialność za określenie co jest wymiarem, a co miarą. Dzięki temu oczekujemy że będzie on w stanie szybko i samodzielnie tworzyć raporty i analizy, odciążając tym samym zasoby firmowego IT.

Czy istnieje jednak coś, co jest w stanie połączyć te dwa, wydawało by się odległe,  światy? SQL Server 2012, wprowadzając nowy model semantyczny dla BI (BI Semantic Model), daje na to pytanie twierdzącą odpowiedź.

 

 

 

 

 

 

 

 

 

 

 

 

Nowy model zastępuje znany z SQL 2005 i 2008 UDM (Unified Dimensional Model) – będący w praktyce inną nazwą dla klasycznych kostek OLAP. Na całość BISM składają się 3 warstwy:

  • Model danych – wielowymiarowy (klasyczny OLAP) lub tabularyczny (nowy tryb w Analysis Services 2012)
  • Logika biznesowa– języki MDX i DAX
  • Dostęp do danych – oprócz klasycznych MOLAP/ROLAP/HOLAP mamy do dyspozycji nowości pochodzące z PowerPivot – VertiPaq (xVelocity) i DirectQuery

W ten sposób wszystkie technologie BI istniejące na platformie Microsoft zyskują wspólny mianownik oraz docelowo mogą być stosowane wymiennie. W przyszłości nie powinno nas bowiem interesować czy łączymy się z kostką OLAP czy z modelem przechowywanym przez silnik xVelocity. Również sama możliwość użycia w danej sytuacji języka DAX czy MDX nie powinna być ograniczona typem modelu z jakim się łączymy.

Można więc sobie wyobrazić odpytywanie kostki OLAP poprzez zapytania DAX, lub przeciwnie – wyciąganie danych z modelu tabularycznego za pomocą „prawdziwego” MDX. Zyskujemy w ten sposób dosyć szeroki wachlarz dostępnych opcji, z których możemy wybrać kombinację najbardziej odpowiednią dla danego projektu, w konkretnej fazie jego rozwoju.

Przykładowo, na początku życia projektu możemy użyć PowerPivot do zbudowania szybkiego prototypu rozwiązania. Po jego akceptacji przez użytkowników, możemy wdrożyć powstały model na serwerze SSAS działającym w trybie tabularycznym. W ten sposób model staje się zarządzalny – analogicznie jak w przypadku kostek OLAP, możemy nadać do niego odpowiednie uprawnienia, stworzyć role itd. Wreszcie po pewnym czasie może się okazać, że jest on na tyle dojrzały i użyteczny, że warto przekonwertować go na model wielowymiarowy, np. MOLAP, i udostępnić w ramach całej organizacji.

Pierwszym produktem implementującym BISM jest Analysis Services w SQL Server 2012 – który możemy zainstalować zarówno w wersji wielowymiarowej jak i tabularycznej. Również przy tworzeniu rozwiązań w SQL Server Data Tools mamy do dyspozycji dwa osobne typy projektów.

Jednak jak każda pierwsza wersja, również i ta jest dopiero początkiem długiej drogi. W Analysis Services 2012 brakuje bowiem kilku kluczowych funkcjonalności związanych z BISM – przede wszystkim nie mamy możliwości migracji modeli z trybu wielowymiarowego na tabularyczny i na odwrót. Kierunek przyszłych zmian został jednak wyraźnie nakreślony.

Warsztaty–szkolenie PowerPivot, DAX, SSAS Tabular, BISM…

Wraz z wersją SQL Server 2008R2 pojawił się nowy produkt analityczny dobrze znany czytelnikom mojego blogu – PowerPivot. Był to pierwszy reprezentant nowego podejścia do BI – Self-BI firmy Microsoft w dodatku pierwszy wykorzystujący technologię VertiPaq (xVelocity). W tym roku pojawiła się najnowsza wersja Platformy Bazodanowej i  Business Intelligence firmy Microsoft – SQL Server 2012. SQL Server 2012 daje nam wiele nowości, ale najwięcej z nich dotyczy rozwiązań BI:

  • Liczne nowości dotyczące SQL Server Integration Services (w tym baza danych, a także między innymi wsparcie do Change Data Capture)
  • Columnstore Index – specjalne indeksy do hurtowni o których pisałem na blogu, a także będę miał przyjemność mówić na największej polskiej konferencji poświęconej SQL Server i rozwiązaniom Microsoftu w zakresie platformy BI – SQL Day 2012
  • Nowy model danych BI – Business Semantic Model (BISM)
  • Rozszerzenia PowerPivot (zmiany designera, KPI, hierarchie)
  • Nowe narzędzie wizualizacji danych – PowerView
  • Kompletnie nowy tryb pracy SQL Server Analysis Services – Tabular – będący rozszerzeniem możliwości PowerPivot i czyniącego go rozwiązaniem Enterprise.
  • Nowy Reporting Services dla SharePoint 2010
  • Alerty w SQL Server Reporting Services
  • Data Quality Services
  • Add-in dla Excela do zarządzania Master Data Services
  • Data Mining Add-in dla Excela 2010
  • Nowości w języku DAX
  • Liczne drobiazgi..

Właśnie o tematyce związanej z PowerPivot czyli (SelfBI) i o języku DAX, obecnym w rozwiązaniach budowanych w produktach:

  • PowerPivot for Excel
  • PowerPivot for SharePoint
  • SQL Server Analysis Services Tabular

będę miał przyjemność mówić przez cały pierwszy dzień konferencji SQL Day w ramach tzw. Workshopów.

W ramach tego szkolenia- warsztatu uczestnicy będą mogli poznać zagadnienia:

  • Co to jest BISM (Business Semantic Model)
  • Jak tworzyć raproty i dashboardy przy użyciu PowerPivot (instalacja, źródła danych, miary, formatowanie, relacje, zaawansowane tworzenie raportów…)
  • Bardziej i mniej zaawansowane użycie języka DAX
  • Jak publikować i zarządzać PowerPivot dla SharePoint 2010
  • Jak przejść z danych w PowerPivot do SQL Server Analysis Services Tabular
  • Dobre praktyki projektowania rozwiązań w SelfBI

Szkolenie będzie ciekawe zarówno dla tych który zamierzają budować rozwiązanie BI w swojej firmie i nie wiedzą na jakie rozwiązanie się zdecydować, dla tych co korzystają z BI na platformie SharePoint 2010, ale także dla tych co muszą wykonywać raporty w programie Excel i chcą poprawić efektywność swoich raportów bez nakładów finansowych.

Zapraszam serdecznie. Rejestracja jest dostępna. Niestety nie wiele miejsc jest dostępnych, a i koszt nie jest zbyt duży jak na tego typu szkolenie. – SQL Day 2012

Columnstore index–nowość SQL Server 2012, czyli trochę o VertiPaq

Blog o PowerPivot, a post o Columnstore… Tak dokładnie. Takie miałem zamierzenie. PowerPivot był nowością wprowadzającą w rozwiązaniu SQL Server jako pierwsze mechanizm VertiPaq. Przy nowej edycji SQL Server 2012 – o nazwie kodowej Denali powstaje cała ścieżka rozwiązań BI bazująca lub wykorzystująca mechanizm VertiPaq:

  • Model BISM (Business Intelligence Semantic Model) w którym możemy projektować rozwiązania BI
  • PowerPivot w wersji 2 (w tym Analysis Services Integreted dla SharePoint 2010 czyli PowerPivot dla SharePoint))
  • PowerView (znany pod nazwą kodową Projekt Crescent) – nowoczesne narzędzie do wizualizacji danych (w tym danych z PowerPivot i BISM)
  • SQL Server Analysis Services Tabular Mode
  • Nowe indeksy w SQL Server 2012Columnstore Index (projekt Apollo)

Za całą pewnością właśnie te tematy będą gościć na tym blogu. Na pierwszy ogień na moim blogu biorę właśnie nowe indeksy w SQL Server 2012. Jestem pod wrażeniem tego mechanizmu i chciałem się tym podzielić z moimi czytelnikami.

Wprowadzenie do indeksów Columnstore

Dotychczasowo wszystkie dane SQL Server przechowywał na stronach wierszami, ostatnich wersjach SQL Server  2008/2008R2 umożliwiał już kompresję danych, co dawało bardzo często dobre efekty. W rozwiązaniu VertiPaq wymyślono, iż dane będą przechowywane nie wierszami, ale kolumnami Co to daje? Otóż jeżeli posiadamy miliony rekordów dotyczących np.: ceny sprzedaży, albo ilości sprzedaży można sobie wyobrazić jak często się te wartości powtarzają, ponadto należą do tego samego typu przez co można wykonać o wiele bardziej efektywną kompresję tych danych. Kolejną zaletą jest fakt, iż w danych analitycznych rzadziej pobieramy pojedyncze wiersze, a częściej wykonujemy różnego rodzaju agregaty właśnie na poszczególnych kolumnach.

Wszystkie dane silnik SQL Server umieszcza na stronach wierszami
image

w przypadku COLUMNSTORE (czyli wykorzystując mechanizm VertiPaq) dane umieszczone są kolumnami:
image

Co jeszcze daje nam taka zmiana? Po pierwsze zmniejsza nam ilość operacji I/O zarówno ze względu na wielkość danych, po drugie pobiera jedynie te kolumny, które akurat potrzebuję do danego zapytania, ale także ze względu na fakt, iż SQL Server odczytuje dane pewnym obszarami (w przypadku COLUMNSTORE mówi się segmentami) i ze względu na sposób położenia informacji SQL Server ma lepszą ilość trafień w zaczytanych danych z pewnym wyprzedzeniem, niż ma to miejsce przy odczycie zwykłych stron bazy danych.

Odczyt niezbędnych kolumn, a nie całych wierszy
image

Odczyt segmentów danych
image

Jednak to nie wszystkie zalety wykorzystywania indeksów typu COLUMNSTORE. Otóż w przypadku bardzo dużych zbiorów danych , dane przechowywane w indeks COLUMNSTORE mogą być przetwarzane w sposób wsadowy (BATCH MODE), daje to pełne wykorzystanie możliwości zrównoleglenie, gdyż każdy wsad jest niezależny od pozostałych operacji.

Ograniczenia

Struktura indeksów COLUMNSTORE ma pewne ograniczenia lub restrykcje, głównie wynikające z architektury tych indeksów:

  • Dane w indeksie COLUMNSTORE nie mogą być modyfikowane!!!
  • Nie może być w tym indeksie więcej niż 1024 kolumn
  • Indeks ten jest jedynie nie klustrowanym indeksem
  • Nie może być unikalnym
  • Nie może być zakładany na widokach ani na widokach indeksowanych
  • Nie można używać kolumn typu SPARSE
  • Nie można zmieniać definicji indeksu pryz użyciu składni ALTER INDEX (można tym poleceniem jedynie przebudować i wyłączyć indeks)
  • Nie ma takich opcji jak INCLUDE, ASC, DESC
  • Nie wspiera mechanizmu SEEK (inne zastosowanie jest tego indeksu)
  • Nie mechanizmów (kompresji – posiada własną; replikacji, CHANGE DATA CAPTURE, CHANGE TRACKING – jest tylko do odczytu; FILESTREAM)

Jak widać stosując taki indeks do hurtowni danych ograniczenia te nie są straszne. Część z nich jest wręcz oczywista ze względu na omówioną wcześniej architekturę.

Podsumowanie

Jak widać mechanizm ten jest bardzo ciekawy i daje naprawdę niesamowite rezultaty. Wykonałem już pierwsze testy na różnej ilości danych.  W kolejnych postach już niebawem na tym blogu przedstawię jak tworzyć COLUMNSTORE index, oraz omówię i przedstawię rezultaty testów jakie wykonałem.

Czekajac na Crescent…

W listopadzie w ramach konferencji PASS w USA zostało przedstawionych wiele nowych informacji dotyczących następnych produktów SQL Server i Business Intelligence (Denali, Apollo, Atlanta, Crescent, Juneau..). Na tym blogu najciekawszy dla nas jest PowerPivot. I tak parę słów podsumowania na co można liczyć w najbliższych wersjach (ponoć już CTP2 Denali nam to zaoferuje).

Po pierwsze rozszerzone będzie DAX o funkcje rankingowe. Z punktu widzenia Business Intelligence PowerPivot otrzyma wiele mechanizmów dostępnych dotychczas tylko w rozwiązaniach opartych na SQL Server Analysis Services między innymi:

  • KPI
  • Hierarchie (w tym Parent-Child hierarchies)
  • Perspektywy
  • Drillthrough

Zmienią się możliwości w zarządzaniu relacjami, będzie obsługa do relacji Wiele-do-wielu, oraz możliwość robienia wiele relacji (przypominam, iż obecnie może być tylko jedna relacja pomiędzy dwiema tabelami). W zakresie składowania danych ma dojść obsługa partycjonowania i stronicowania, natomiast bezpieczeństwa wsparcie do ról Active Directory. Bezpieczeństwa danych ma być również poziomie wiersza i kolumny.

Dużą zmianą będzie możliwość budowania tzw BISM (Business Intelligence Semantic Model). BISM będzie nowością Analysis Services w wersji Denali (SQL Server 2011). Będziemy mogli tworzyć bazę danych SSAS na serwerze w trybie in-memory, w ten sposób zainstalowane SSAS będzie mogło pracować w trybie zwykłym (jak dotychczas),a także w trybie in-memory. Podobnie jak to w tej chwili jest możliwe w przypadku integracji PowerPivot z SharePoint. Model semantyczny będzie składał się z 3 warstw:

  • warstwy danych (Data Layer)
  • warstwa logiki biznesowej (Business Logic)
  • warstwa dostępu do danych (Data Access)

Przechowywany model będzie miał w zasadzie postać relacyjną, ale będzie można wykorzystywać klientów (aplikacji), które będą mogły przy użyciu języka MDX pytać model. Drugą metodą korzystania z modelu będą aplikacje korzystające z języka wyrażeń DAX takie jak nowy produkt powstający w ramach projektu Crescent. W warstwie dostępu do danych będziemy mogli skorzystać z dwóch wariantów dostępu. Pierwszy to tzw dostęp czasu rzeczywistego, czyli dostęp bezpośredni do źródeł danych (data sources), drugi to utworzenie w pamięci tzw VertiPaq, przyspieszający znacznie działanie, ale potrzebujący czas na utworzenie takiej struktury. Wybór będzie należał dla projektanta modelu.

bism

Pozostaje nam tylko czekać na CTP2 do Denali, który może zawierać w sobie pierwsze wersje BISM i Crescent