Analiza koszykowa z wykorzystaniem algorytmów data mining w Microsoft SQL Server 2008 oraz Microsoft Excel 2007.
Microsoft SQL Server 2008 w ramach usługi Analysis Services dostarcza szereg algorytmów eksploracji danych (data mining). Algorytmy te możemy skutecznie wykorzystywać w analizie bardzo dużych zbiorów informacji, gdzie pewne zależności między danymi nie są tak łatwo dostrzegalne, jak w przypadku mniejszych zbiorów informacji.
Spis Treści
- Wstęp
- Analiza Koszykowa
- Konfiguracja Dodatku
- Przygotowanie Danych
- Analiza Wyników
- Dodatkowe Materiały
Wstęp
Microsoft SQL Server 2008 w ramach usługi Analysis Services dostarcza szereg algorytmów eksploracji danych (data mining). Algorytmy te możemy skutecznie wykorzystywać w analizie bardzo dużych zbiorów informacji, gdzie pewne zależności między danymi nie są tak łatwo dostrzegalne, jak w przypadku mniejszych zbiorów informacji. Możliwości zaawansowanych algorytmów eksploracji danych zawartych w SQL Server 2008 mogą wykorzystywać również użytkownicy, którzy nie posiadają zaawansowanej wiedzy statystycznej ani programistycznej.
Dzięki zastosowaniu prostego interfejsu w Microsoft Excel 2007 w połączeniu z SQL Server 2008 możemy w bardzo prosty i szybki sposób wykorzystywać możliwości data miningu do rozwiązywania typowych problemów biznesowych. Użytkownicy biznesowi i analitycy dzięki gotowym zestawom kreatorów mogą natychmiast wykonać analizę koszykową, wykonać segmentację klientów, czy też stworzyć prognozę sprzedaży na kilka przyszłych okresów.
W niniejszym artykule zostanie przedstawiony przykład wykorzystania możliwości Microsoft Excel 2007 oraz algorytmów data miningowych z Microsoft SQL Server 2008 do wykonania analizy koszykowej sprzedaży.
Analiza Koszykowa
Analiza koszykowa (Market Basket Analysis) pozwala na wyszukiwania powiązań w danych (asocjacji). W analizie tej wykorzystywany jest algorytm odkrywania reguł asocjacyjnych (wersja Apriori), który pozwala na znalezienie informacji o tym, które produkty są najczęściej kupowane w tym samym czasie. Dzięki analizie dużych zbiorów danych o transakcjach kupna można przewidzieć zachowanie klientów. W ten sposób dostosowując odpowiednio promocje i oferty, lub też rozmieszczając obok siebie wybrane towary, można zwiększyć prawdopodobieństwo ich nabycia, a co za tym idzie zwiększyć zyski ze sprzedaży.
Analiza koszykowa w dodatku do programu Excel dostępna jest na zakładce Analysis – Shopping Basket Analysis (zobacz rysunek 1).
Rysunek 1. Zakładka Analiz – wykorzystanie algorytmów data mining na danych tabelarycznych.
Konfiguracja Dodatku
Aby móc skorzystać w Excelu z algorytmów data mining SQL Server-a należy pobrać i zainstalować na komputerze użytkownika bezpłatny dodatek do Excela - Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
Po instalacji komponentu w menu Excela pojawią się dwie zakładki związane z eksploracją danych (zobacz rys. 1 i 2).
Rysunek 2. Zakładka Data Mining.
Pierwszą z nich jest zakładka Analyze (Table Analysis Tools for Excel). Dostarcza ona szereg opcji i kreatorów pozwalających przeanalizować dane w tabelach arkusza. Dzięki automatycznej analizie danych zgromadzonych w tabeli system sam dobiera najlepszy algorytm data mining oraz jego parametryzację do konkretnego problemu. W ten sposób użytkownik nie posiadający szerokiej wiedzy na temat zasad stosowania poszczególnych algorytmów w poszczególnych problemach biznesowych jest w stanie wykorzystać możliwości data miningu.
Drugą z dostępnych dla użytkownika zakładek w menu Excela jest zakładka Data Mining (Data Mining Client for Excel) przedstawiona na rysunku 2. Tutaj użytkownik ma możliwość tworzenia modeli i struktur data miningowych, wykorzystania dowolnych algorytmów dostępnych na serwerze Analysis Services oraz porównania ich efektywności (korzystając z zestawu funkcji Accuracy and Validation). Dodatkowo ma możliwość przeglądania gotowych modeli data miningowych dostępnych na serwerze.
Aby móc skorzystać z zakładki Analyze należy w pierwszym kroku po instalacji dodatku skonfigurować połączenie do usługi SQL Server 2008 Analysis Services. Połączenie to pozwala na wykorzystanie algorytmów data mining dostępnych w SQL Server do analizy danych zgromadzonych w arkuszu Excel. Podczas konfiguracji połączenia wskazujemy nazwę instancji, bazę danych oraz podajemy nazwę połączenia. Musimy mieć również skonfigurowane uprawnienia do zapisu i odczytu z bazy danych. Usługa Analysis Services do której się łączymy powinna mieć ustawiony parametr AllowSessionMiningModels na True. Dzięki temu możliwe jest tworzenie tzw. sesyjnych modeli data mining-owych, które są usuwane z serwera w momencie, gdy ostatni użytkownik zakończy połączenie do modelu lub czas trwania sesji przekroczy wyznaczony próg time out.
Aby móc wykorzystać dowolną z opcji analiz z zakładki Analyze dodatkowo musimy przekonwertować zakres komórek z danymi w arkuszu Excel do postaci tabeli. Tabela jest nową funkcjonalnością Excela 2007 pozwalającą na sortowanie, filtrowanie i przeszukiwanie danych w taki sposób, jakby znajdowały się w bazie danych. Tylko w takim przypadku zakładka Analyze jest widoczna dla użytkownika i pozwala na wybraniej jednej z dostępnych funkcji analizy danych.
Przygotowanie Danych
Do wykonania analizy koszykowej potrzebujemy informacji o transakcjach sprzedaży. W naszym przykładzie wykorzystamy zbiór przykładowych danych testowych dostępnych po zainstalowaniu dodatku Microsoft SQL Server 2008 Data Mining Add-in for Microsoft Office 2007.
Aby otworzyć dane przykładowe można zaznaczyć opcję Open an example test table upon closing this wizard w kreatorze konfiguracji dodatku uruchamianym automatycznie po zainstalowaniu produktu. Można również otworzyć plik DMAddins_SampleData.xlsx zlokalizowany w katalogu, w którym zainstalowano dodatek (domyślnie jest to katalog C:Program FilesMicrosoft SQL Server 2008 DM Add-Ins).
W otwartym arkuszu należy przejść na zakładkę Associate, na której przygotowane są przykładowe dane dotyczące transakcji sprzedaży towarów, dla których będziemy wyszukiwać powiązania.
Zawartość arkusza przedstawiono na rys. 3. Tabela zawiera kolumny opisujące numer transakcji (Order Number), kategorię (Category), nazwę produktu (Product) oraz jego cenę (Product Price).
Kolumny Category oraz Product możemy wykorzystywać zamiennie w analizie koszykowej - w zależności od tego, czy potrzebujemy informacji z dokładnością do produktu, czy kategorii produktu.
Aby wykonać analizę koszykową na innych danych musimy przygotować je tak, aby zawierały następujące kolumny:
- kolumnę zawierającą unikalny identyfikator reprezentujący numer transakcji (w ramach jednej transakcji może być sprzedanych wiele produktów)
- kolumnę z opisem/nazwą produktu, który jest analizowany
- opcjonalnie można dodać kolumnę z zawierającą cenę produktu (wartość ta jest wykorzystywana do wyliczenia całkowitej wartości transakcji, w której produkt wystąpił)
Rysunek 3. Dane źródłowe do przeprowadzenia analizy koszykowej.
Aby wykonać analizę koszykową ustawiamy kursor na tabeli z danymi (na rysunku 3). Następnie wybieramy z zakładki Table Tools opcję Shopping Basket Analysis. W wyniku tego zostanie uruchomiony kreator, który pozwoli nam określić role poszczególnych kolumn w przygotowaniu analizy. Na rysununku 4 przedstawiono przykładowe przypisanie kolumn do odpowiednich ról.
Kolumnę Order Number przypisujemy do roli Transaction ID, kolumnę Category do roli Item, natomiast kolumnę Product Price przypisujemy do roli Item Value.
Rysunek 4. Mapowanie kolumn z danymi na role w analizie koszykowej.
Dodatkową opcją jest możliwość zmiany domyślnych parametrów algorytmu reguł asocjacyjnych. Po wybraniu opcji Advanced mamy możliwość ustawienia dwóch parametrów modelu: minimalne wsparcie (Minimum support) oraz wiarygodność (Minimum rule probability).
Minimalne wsparcie (reprezentujące próg częstotliwości występowania zbioru produktów dla danej reguły w kolekcji wszystkich zbiorów) określa próg od którego dany zbiór produktów jest brany pod uwagę w analizie koszykowej. Wartość minimalnego wsparcia możemy wprowadzić bezpośrednio w postaci minimalnej liczby transakcji (opcja Items), jak również w postaci procentowej (opcja Percent). Wartość procentowa minimalnego wsparcia oznacza, że dany zbiór produktów musi stanowić co najmniej N % wszystkich zbiorów produktów. W przypadku analizy bardzo dużych zbiorów danych wartość procentowa minimalnego wsparcia pozwala nam na skoncentrowanie się tylko na kluczowych grupach produktów.
Parametr wiarygodności reguły pozwala nam na wybranie tylko tych reguł, które są najbardziej użyteczne i sprawdzają się w największej liczbie rozpatrywanych przypadków (zbiorów produktów).
Rysunek 5. Konfiguracja zaawansowanych parametrów modelu reguł asocjacyjnych.
Analiza Wyników
Po wprowadzeniu parametrów analizy i wybraniu przycisku Run, system tworzy raport z analizy reguł w postaci dwóch nowych zakładek w arkuszu Excel: Shopping Basket Bundled Item oraz Shopping Basket Recommendations.
W zakładce Shopping Basket Bundled Item (zobacz rysunek 6) umieszczone zostają informacji o wszystkich zbiorach produktów, które zostały odkryte w transakcjach źródłowych. W kolumnie Bundle of Items umieszczone są produkty znajdujące się w zbiorze. W kolumnie Bundle size umieszczona jest liczebność zbioru. Kolumna Number of sales reprezentuje liczbę transakcji, w których wystąpił dany zbiór produktów (wsparcie). W kolumnach Average Value Per Sale oraz Overall Value of Bundle umieszczone są wartości średnia i całkowita produktów ze zbioru (wyliczona na podstawie kolumny Item Value ze zbioru wejściowego).
Rysunek 6. Raport z listą odnalezionych grup produktów (Shopping Basket Bundled Item).
W zakładce Shopping Basket Recommendation (zobacz rysunek 7) umieszczone są informacje o rekomendacjach opartych na odkrytych regułach. Raport ten pozwala określić jakie produkty warto sprzedawać klientowi oraz jaki wpływ mają te rekomendacje na sumę sprzedaży.
W kolumnie Selected Item umieszczone są produkty znajdujące się po lewej stronie reguły (czyli warunkujące). W kolumnie Recommendation umieszczone są produkty, które bardzo często nabywane są razem z produktami z kolumny Selected Item. W kolumnach Sales of Selected umieszczone są informacje o liczbie transakcji, w których wystąpiły produkty z kolumny Selected Item. W kolumnie Linked Sales mamy informację o liczbie transakcji, w których dodatkowo wystąpił produkt z kolumny Recommendation. W kolumnie % of linked sales mamy stosunek tych dwóch wartości.
Produkty z kolumny Recommendation wraz z produktami z kolumny Selected Item warto zatem umieszczać obok siebie w sklepie lub też łączyć je razem w promocjach, aby w ten sposób zwiększyć szansę ich sprzedaży.
Dodatkowo w ostatnich dwóch kolumnach mamy informację o wartości produktów w rekomendacji. Dzięki temu możemy wybrać tylko te rekomendacje, które mają największą wartość w produktach, a niekoniecznie występują w dużej liczbie transakcji. W kolumnie Overall value of linked sales mamy całkowitą wartości rekomendacji, natomiast w kolumnie Average value of recommendation wartość średnią na jedną transakcję.
Rysunek 7. Raport z listą rekomendacji utworzonych na podstawie odkrytych reguł asocjacyjnych (Shopping Basket Recommendations).
Dodatkowe Materiały
- Data Mining Add-Ins for Office 2007
- Microsoft Association Algorithm Technical Reference
- SQLServerDataMining.com
- Algorytmy Data Mining w Microsoft SQL Server
- Virtual Lab SQL Server 2008 Data Mining Add-ins for Excel 2007
