SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

70-463 Implementing a Data Warehouse with SQL Server 2012 – ostatni krok do MCSA

Ostatni egzamin ze ścieżki MCSA SQL Server 2012 za mną – dziś zdałem ten odwlekany już chyba z półtora roku test. Okres wakacyjny + solidnie przepracowane ostatnie miesiące okazały się na szczęście wystarczającym bodźcem aby w końcu podejść do tematu i zamknąć ścieżkę MCSA SQL Server.
MCSA
Poniżej garść informacji na temat samego egzaminu, jak się do niego przygotować i czego spodziewać się na teście.

Przygotowanie do egzaminu 70-463

Najlepszym sposobem i okazją do nauki jest oczywiście komercyjne wdrożenie. Tak więc podczas ostatniego projektu hurtowni danych który prowadziłem, postanowiłem upiec dwie pieczenie na jednym ogniu i ogarnąć „po drodze”, pełen zakres materiału tego egzaminu.

Sam egzamin, odwlekałem strasznie nie tylko z powodu „braku czasu” ale także dlatego, że nie jestem do końca entuzjastą części technologii których dotyczy. Wręcz filozofii wyklikiwania pewnych rozwiązań vs pisanie własnego kodu. Data cleansing / profiling, Data Quality Services lub niektóre z zastosowań SSIS – pewnie to kwestia upodobań. W wielu scenariuszach rozwiązania oparte o T-SQL/skrypty czy CLR są mi zdecydowanie bliższe, działają lepiej i są bardziej wydajne. Choć przyznać trzeba, że pewnie czasem faktycznie prościej jest wyklikać pewne rozwiązania i skorzystać z gotowych komponentów niż pisać samemu własne procedurki. Granica jest dość rozmyta. Nie mniej jednak, aby tworzyć dobre rozwiązania z pewnością trzeba dobrze poznać różne narzędzia i nie ograniczać się tylko do SSIS lub czystego T-SQL.

Nie chciałbym się tutaj rozwodzić nad wyższością własnych, szytych na miarę rozwiązań :) od tych ustandaryzowanych, gotowych i z definicji łatwych w implementacji, które z pewnością sprawdzają się w wielu typowych scenariuszach. Nie mniej jednak, przygotowując się do zdania tego egzaminu, zdecydowałem, aby w projekcie hurtowni i procesów ETL wykorzystać (przetestować) jak największą liczbę funkcjonalności, których dotyka ten egzamin (oczywiście nie wszystkie z nich wdrożyłem komercyjnie).

Utworzyłem dwa równoległe rozwiązania – pierwsze bazujące głównie na logice skryptowej, własnych procedurach składowanych. Drugie, alternatywne, realizujące te same zadania – przeładowane fajerwerkami projekty SSIS :), Data Quality Services i inne funkcjonalności, które uznałem że warto przetestować w praktyce pod kątem egzaminu. Przygotowując się do tego testu, z pewnością najwięcej czasu warto poświęcić na budowaniu procesów ETL, bazujących na SQL Server Integration Services czyli paczek i projektów SSIS. Podejście to w moim przypadku pozwoliło mi na osiągnięcie całkiem przyzwoitego wyniku 874/1000 i tym samym realizacji ścieżki MCSA.

Materiały i pomoce

Moje nieco old-schoolowe podejście do tradycyjnych pomocy naukowych, pchnęło mnie do kupna oficjalnej pozycji edukacyjnej, rekomendowanej przez MS do przygotowań do tego testu czyli – Trainning Kit 70-463. Z tymi Trainning Kitami to różnie bywa, jednak tym razem dodatkową zachętą do kupna tej książki- była magia nazwisk autorów.

70463_TrainningKit

Dejan Sarka to firma podobnie jak Itzik Ben-Gan, której nie trzeba szczególnie reklamować. Jest autorem wielu świetnych książek i publikacji. Tym razem również się nie zawiodłem – uważam, że Trainning Kit 70-463 to lektura, którą można polecić z czystym sumieniem wszystkim przygotowującym się do tego egzaminu a także początkującym developerom rozwiązań DW/SSIS.

Książka zawiera szereg scenariuszy, które po przerobieniu dają solidną podstawę do zdania tego egzaminu. Jak to na tego typu testach bywa, pytania są dość tendencyjne i warto wcześniej odpowiednio ukierunkować się, korzystając z tego typu pomocy. Nawet jeśli czujesz, że masz spore doświadczenie w tworzeniu hurtowni czy paczek SSIS, pytania testowe mogą czasem zaskoczyć. Szkoda że nie można przejrzeć po egzaminie błędnych odpowiedzi bo pomimo, że zdałem na ponad 87% nie wiem w których pytaniach popełniłem błędy.

Drugim źródłem wiedzy które można z pewnością polecić jest oficjalny kurs MS-10777. Szczególnie dla osoby początkującej w zagadnieniach DW/SSIS to szkolenie jest nieocenionym punktem wyjścia i drogą na skróty o ile tylko cena nie stanowi przeszkody (niestety są one dość drogie).

Dlaczego warto zdać te trzy egzaminy i zdobyć MCSA SQL Server ? Chociażby dlatego że jest to wspólna dla wersji 2012 i 2014 ścieżka certyfikująca, potwierdzająca szeroką wiedzę z zakresu administracji, pisania zapytań i tworzenia rozwiązań DW / ETL dla tych platform. Poza tym jest to zawsze świetna, dodatkowa motywacja do nauki nowych funkcjonalności.

Wyszukiwanie ciągłych zakresów, dziur, problemy wysp w SQL

Weryfikacja ciągłości zakresu, identyfikacja dziur oraz scalanie przedziałów, przydaje się do rozwiązywania różnych zadań. W systemach transakcyjnych, szczególnie mocno obciążonych przez wielu użytkowników, mogą wystąpić sytuacje gdy funkcja IDENTITY nie zapewnia bezwzględnej ciągłości identyfikatorów. Dotyczy to również obiektów wprowadzonych w SQL Server 2012, znanych już od dawna w Oracle – sekwencji.

Artykuł ten, nie będzie jednak o bezpiecznym nadawaniu kolejnych, ciągłych identyfikatorów (polecam triggery INSTEAD OF INSERT). Zaprezentuję tu kilka wskazówek i praktycznych przykładów jak wykrywać nieciągłości, identyfikować dziury oraz scalać zakresy.

Niezwykle pomocne okazują się tutaj funkcje szeregujące bazujące na funkcji okna OVER() oraz wspólne wyrażenia tablicowe.


Wykrywanie nieciągłości w zbiorze

Na początek coś bardzo prostego. Trochę podstaw, bez których trudno jest rozwiązywać bardziej skomplikowane przykłady.

Załóżmy, że mamy zbiór elementów ponumerowanych w zakresie od 1-1000, inkrementowanych co 1. Każdy ciągły, jednostajnie rosnący zbiór wartości, powinien zwrócić ten sam wynik dla takiego wyrażenia:

Wartość_elementu  – ( Inkrementacja * Nr_elementu_w_szeregu ) = Const.


Wykorzystując funkcję ROW_NUMBER() i powyższą zasadę, sprawdźmy ciągłość dla poniższego zbioru:

Use tempdb
GO
 
set nocount on 
go
 
IF ( OBJECT_ID('dbo.zakres') is not null)  
drop table dbo.zakres;
 
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1
 
WHILE (@zmienna<=1000)
BEGIN
	insert into  dbo.zakres values (@zmienna)
	set @zmienna = @zmienna + 1
END;
 
-- kwerenda sprawdzająca
with CheckCont as (
	-- dla inkrementowanego o 1
	select distinct T1.VLAN  - (1*(ROW_NUMBER() OVER (ORDER BY  VLAN))) as Diff
	from dbo.zakres t1
)
Select CASE WHEN Count(Diff)> 1 then 'Discontinuous' else 'Continuous' end as Result
from CheckCont

Zakresy_ciagle_01

Jak widać bez niespodzianek – jest ok. Skasujemy teraz kilka losowych rekordów i ponowie zweryfikujemy ciągłość elementów, tą samą kwerendą sprawdzającą.

DELETE FROM dbo.zakres
WHERE VLAN IN (
SELECT TOP 3 VLAN from dbo.zakres ORDER BY NEWID() 
)

Zakresy_ciagle_02


Scalanie zakresów

Scenariusz, z którym ostatnio się spotkałem to scalanie zakresów VLAN w konfiguracji interfejsów urządzeń sieciowych (routerów). Informacje o VLANach są przechowywane w tabeli, w której każdy rekord, odpowiada pojedynczemu VLANowi, przypisanemu do danego portu, określonego urządzenia. To co trzeba wykonać, to agregacja (sklejenie zakresów) do skrótowej postaci przyjaznej dla użytkownika.

Wygenerujmy sobie prostą tabelę, z nieciągłą numeracją w kolumnie VLAN :

IF ( OBJECT_ID('dbo.zakres') is not null)  drop table dbo.zakres;
create table dbo.zakres
( 
	Vlan int
);
 
DECLARE @zmienna int = 1, @los int =0, @cnt int =1
 
WHILE (@cnt<=10)
BEGIN
 
	SET @los = case when   CONVERT(INT, (11) * RAND()) > 8  then 1 else 0 end
 
	insert into dbo.zakres values (@zmienna + @los)
	SELECT @zmienna = @zmienna +1+ @los, @cnt +=1
END;
 
select * from dbo.zakres

Zakresy_ciagle_03

Zadaniem jest wykrycie i scalenie zakresów rekordów o ciągłej numeracji. Stosując się do reguły ciągłości, którą pokazałem w poprzednim przykładzie, sprawdźmy rezultat tej formułki dla kolejnych wierszy. Widać, że wynik może posłużyć jako bezwzględny identyfikator określonego, ciągłego zakresu. W naszym przypadku mamy 4 zakresy, w tym dwa wieloelementowe.

Zakresy_ciagle_04

Kluczowym znów będzie numerowanie rekordów i późniejsze ich grupowanie względem wyliczonego w ten sposób identyfikatora zakresu. Dwa podejścia – stare, sprzed SQL Server 2005 z zapytaniem skorelowanym :

with PoNumerowane as
(
	select VLAN ,  
           (select count(*) from dbo.zakres t2 where t2.VLAN  <= t1.VLAN )  as IdRek
	from dbo.zakres t1
)
select VLAN - IdRek as ZakresID, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from PoNumerowane
group by VLAN - IdRek

Oraz lepsze, zdecydowanie bardziej wydajne, rozwiązanie z użyciem funkcji ROW_NUMBER() :

with Ponumerowane as (
	select VLAN , ROW_NUMBER() OVER(order by VLAN)  as IdRek,
	 VLAN - ROW_NUMBER() OVER(order by VLAN) as IdZakres
	from dbo.zakres t1 
)
select IdZakres, MIN(VLAN) as VLAN_START, MAX(VLAN) as VLAN_END
from Ponumerowane
group by IdZakres

Zakresy_ciagle_05
Takie rozwiązanie można ubrać w funkcję użytkownika i prezentować scalone zakresy liczb w przyjaznej formie, np. dodatkowo agregując je do postaci skalarnej (FOR XML PATH).


Wyszukiwanie brakujących elementów w zbiorze

Na koniec sytuacja, gdy interesuje nas to, czego nie ma w tabeli. Mogą to być braki numerów zamówień, albo dziury w ciągłym zakresie adresacji urządzeń IP, nadające się do ponownego przydzielenia. Identyfikację brakujących elementów, na przykładzie zbioru inkrementowanego o 1, możemy wykonać na przykład tak :

with cte as (
 
	SELECT (select MIN(VLAN) from dbo.zakres) as VLAN, 
(select MAX(VLAN) from dbo.zakres) as MaxVal
	UNION ALL
 
	-- zakładamy inkrementację + 1 
	SELECT VLAN + 1 , MaxVal
	FROM CTE  c 
	where VLAN < MaxVal
)
select c.VLAN as MissingVlan from cte c left join dbo.zakres t on c.VLAN = t.VLAN
where t.VLAN is null
OPTION (MAXRECURSION 0)

Zakresy_ciagle_06

Grafy, drzewa i hierarchie w SQL

Z grafami można spotkać się nad wyraz często. Takie najprostsze to struktura katalogów na dysku czy hierarchia przełożony-podwładny w firmie.

Bardziej skomplikowane znajdziesz w serwisach społecznościowych – np. drzewa znajomości. W sieciach teleinformatycznych będą to struktury połączeń pomiędzy węzłami. W nawigacji GPS – najkrótsze ścieżki od punktu A-Z itd.

Z punktu widzenia baz danych i tworzenia zapytań do tego typu struktur – zadania z grafami nie są trywialne. Szczególnie, gdy chcemy je rozwiązać za pomocą pojedynczej kwerendy.
W artykule tym, zaprezentuję kilka praktycznych scenariuszy wraz z rozwiązaniami bazującymi na SQL Server 2012. Mam nadzieję, że jeśli szukasz gotowych rozwiązań – uda Ci się zaadoptować prezentowane tutaj przykłady do swoich zastosowań.

W każdym z nich, wyzwaniem było napisanie rozwiązania w postaci pojedynczej kwerendy. Podejście proceduralne jest prostsze w zrozumieniu i może prowadzić do bardziej efektywnych rozwiązań (materializowanie pośrednie, indeksy na tabelach tymczasowych).

Trochę teorii

Patrząc na matematyczną teorię grafów, możemy mieć do czynienia z różnymi typami tych struktur. Na początek zajmiemy się grafami prostymi, wprowadzając przy okazji kilka podstawowych pojęć. Zbudujemy sobie tło do bardziej zaawansowanych scenariuszy.


Graf prosty (acykliczny, nieskierowany)

Graf prosty – to drzewo bez cykli, krawędzie nie są skierowane (nie ma ograniczeń kierunkowych) a pomiędzy każdą parą dowolnych wierzchołków, jest tylko jedna ścieżka. Dodanie jakiejkolwiek nowej krawędzi, spowoduje powstanie cyklu a usunięcie istniejącej doprowadzi do niespójności (podział grafu).

Grafy_Drzewa_SQL_01

W przypadku, gdy jeden z wierzchołków, jest wyszczególniony (korzeń), mówimy o drzewie ukorzenionym. Grafy proste, ukorzenione są łatwe w implementacji w relacyjnych bazach. Tworzenie zapytań do takich struktur nie stwarza większych problemów. Tego typu graf, to na przykład organizacja systemu plikowego lub hierarchia pracownicza – przełożony/podwładny.

W typowej organizacji plików i katalogów zakładamy, że dany obiekt (plik lub katalog) nie może być w dwóch różnych miejscach jednocześnie (pomijając skróty, które mogą tworzyć cykle – o tym później). Do każdego węzła, jest tylko jedna ścieżka, prowadząca ,od korzenia grafu. Patrząc na tą strukturę, całość stanowi jedno spójne drzewo – graf prosty, ukorzeniony.

Grafy_Drzewa_SQL_02

Podobnie w strukturze zatrudnienia. Dany pracownik zazwyczaj nie może mieć na tym samym poziomie dwóch bezpośrednich szefów. Droga eskalacji od podwładnego do przełożonego najwyższego szczebla, jest jedna.

Implementacja takiej struktury w bazie danych może być bardzo prosta. Realizuje się ją zazwyczaj, jako SELF JOIN, czyli złączenie tabeli samej ze sobą.

USE tempdb
GO
 
CREATE TABLE dbo.Folders (
    ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Folder VARCHAR(100) NOT NULL,
    Parent INT NULL REFERENCES dbo.Folders( ID ) 
);
 
INSERT INTO dbo.Folders ( Folder ) VALUES ('C:');
 
INSERT INTO dbo.Folders ( Folder, Parent )
VALUES  ('Inetpub',1),('Program Files',1),('Windows',1),('wwwroot',2),
('Adobe',3),('MS SQL',3),('Data',7),('system32',4),('Data',9);
 
select * from dbo.Folders

Grafy_Drzewa_SQL_03

Zapytania SQL do takiego grafu, również nie są szczególnie skomplikowane. Wystarczy wykorzystać rekurencję wspólnych wyrażeń tablicowych (CTE Common Table Expressions) i przeszukiwać drzewo w głąb od korzenia do liści.

WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
           CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_04

Takie podejście, załatwia również sprawę, w sytuacji, gdy mamy do czynienia ze zbiorem grafów prostych (las drzew). Zakotwiczenie CTE, identyfikuje wszystkie grafy i rekurencyjnie rozpina ja analogicznie jak poprzednio. Drzewa możemy ponumerować, do późniejszej analizy.

-- dorzućmy nowe drzewo - dysk D:
Insert into dbo.Folders(Folder) Values('D:');
 
Insert into dbo.Folders ( [Folder], [Parent] )
VALUES('Music',11),('Docs',11),('Mike Oldfield',12),('Tres Lunas',14);
 
WITH Paths AS 
(
    SELECT Id, Folder, CAST(null AS VARCHAR(8000)) AS Parent,
CAST(Folder + '\' AS VARCHAR(8000)) FullFolderName, 0 as Poziom, 
ROW_NUMBER() OVER(ORDER BY FOLDER) as TreeNo
    FROM dbo.Folders
    WHERE Parent IS NULL   
 
    UNION ALL
 
    SELECT f.Id, f.Folder, p.FullFolderName, 
           CAST(ISNULL(p.Parent, '')  + p.Folder + '\' 
		+  f.Folder + '\'  AS VARCHAR(8000)) , p.Poziom +1, TreeNo
    FROM dbo.Folders f INNER JOIN Paths p
                                ON f.Parent= p.ID
)
SELECT * FROM Paths
Order by FullFolderName

Grafy_Drzewa_SQL_05

W SQL Server od wersji 2008, mamy dostępny specjalny typ danych HierarchyId. Pozwala on na tworzenie bardziej zaawansowanych struktur w porównaniu do SELF JOINów. Za jego pomocą, można bardzo łatwo zamodelować scenariusz w którym istotna jest kolejność węzłów na danym poziomie hierarchii.


Graf nieskierowany z cyklami

Sytuacja staje się bardziej złożona, gdy mamy do czynienia z grafami, które posiadają cykle. Jest to typowy przykład architektury sieci teleinformatycznej, w której istnieją redundantne ścieżki w celu zapewnienia protekcji. Innym przykładem z którego korzystasz na co dzień to po prostu siatka połączeń drogowych pomiędzy miastami.

Grafy_Drzewa_SQL_06

Wyszukiwanie ścieżek pomiędzy dowolnymi węzłami takiego grafu, musi uwzględniać możliwość zapętlenia. Dla każdej pary węzłów, może pojawić się wiele ścieżek. Zależnie od stopnia złożoności grafu (liczby węzłów i krawędzi) analiza możliwych dróg, może stanowić nie lada wyzwanie.

Grafy_Drzewa_SQL_07

Skrypt z danymi do przeprowadzenia dalszych ćwiczeń od pobrania tutaj.

Implementacja takiego grafu w relacyjnej bazie danych, może być oparta o dwie tabele – węzłów i krawędzi między nimi. Na początek znajdźmy wszystkie ścieżki z punktu A-Z.

WITH Edges AS (
	-- graf nieskierowany więc bierzemy pod uwagę krawędzi dwukierunkowo
        -- A-Z i Z-A
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
	UNION
	SELECT Node2, Node1 , Metric from EDGE	
),
Paths as 
(
	-- rekurencyjne CTE
        SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist
	FROM Edges
 
	UNION ALL
 
	SELECT f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1
	FROM Paths as F join Edges T on 
	--  wykluczenie cykli, czyli nie przechodzimy 
        -- przez żaden wierzchołek dwukrotnie
		case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
)
SELECT *  
FROM  Paths
WHERE Start = 'A' and Koniec = 'Z'
ORDER BY Dist

Grafy_Drzewa_SQL_08

Powyższe podejście, pomimo że w jednej kwerendzie, jest słabe wydajnościowo i przy dużej liczbie (tysiące) węzłów / krawędzi działa po prostu bardzo wolno. Wystarczy spojrzeć na plan wykonania – przypomina zapytanie prawdziwie skorelowane. Alternatywnym rozwiązaniem jest podejście proceduralne, w którym tworzymy drzewa rozpinające.


Graf skierowany z cyklami i metrykami krawędzi

Wyszukiwanie najkrótszej drogi z punktu A-Z, zazwyczaj wymaga analizy jakości poszczególnych odcinków. Sytuacja taka ma zastosowanie zarówno w sieciach drogowych (autostrady, drogi lokalne), jak również informatycznych. W tych drugich, brane są pod uwagę zazwyczaj parametry jakościowe, czyli pasmo, opóźnienia, jitter. O ile w sieciach teleinformatycznych kierunkowość zazwyczaj jest tematem pomijalnym, o tyle drogi jednokierunkowe, to norma.

Bazując na danych z poprzedniego przykładu, wyznaczmy raz jeszcze najlepszą ścieżkę z A-Z, biorąc pod uwagę kierunkowość krawędzi oraz metryki. Nasz graf będzie wyglądał teraz tak :

Grafy_Drzewa_SQL_09

W tej sytuacji widać, że pomiędzy węzłami A i Z powinny zostać znalezione tylko dwie ścieżki. Ponadto nie istnieje żadna ścieżka powrotna (Z > A). Zmodyfikujemy odrobinę zapytanie z poprzedniego przykładu :

WITH Edges AS (
	-- graf skierowany więc bierzemy drogi takie jakie są
	SELECT Node1 as Start, Node2 as Koniec, Metric from EDGE
),
Paths as 
(
	SELECT start , koniec ,  
        CAST('.' + start + '.' + koniec + '.' as varchar(max)) as paths, 1 as Dist, Metric
	FROM Edges 
 
	UNION ALL
 
	select f.start, t.koniec , CAST(f.paths + t.koniec + '.' as varchar(max)), 
	F.dist+1, F.Metric + T.Metric
	from Paths as F join Edges T on 
                 case when F.paths like '%.' + T.koniec  + '.%' then 1 else 0 end = 0
		 and f.koniec = T.start
),
PathRanking as (
select   *, 
	DENSE_RANK() OVER(Partition BY Start, Koniec Order by Metric, Dist) as RNK
from Paths)
Select * from PathRanking
where start = 'A' and koniec = 'Z';

Grafy_Drzewa_SQL_10

Obydwie kosztują tyle samo (suma metryk jest identyczna), ale droga bezpośrednia A>Z ma mniej skoków, stąd zostaje wybrana jako najlepsza.


Las drzew (grafów) nieskierowanych, nieukorzenionych

Bazując na powyższej metodzie, możemy pokusić się o rozwiązanie jeszcze jednego problemu. Zadaniem nietrywialnym, jest analiza zbioru grafów nieskierowanych bez korzenia. Tego typu struktury, spotkać możesz na przykład w serwisach społecznościowych.

Grafy_Drzewa_SQL_11

Wyobraźmy sobie prostą tabelę powiązań (znajomości) pomiędzy użytkownikami. Pytanie w jaki sposób, określić czy dana osoba należy do jakiegoś kręgu (grupy znajomości mogą się tworzyć samoczynnie, bez dodatkowych relacji). Daną grupę, wyznaczają wszystkie osoby, które są w jakikolwiek sposób ze sobą powiązane (nawet pośrednio). Czyli tak naprawdę, musimy zidentyfikować przypisanie każdej krawędzi do określonego drzewa.

Rozwiązaniem w postaci jednej kwerendy, może być modyfikacja znanego już algorytmu wyznaczania ścieżek.

WITH Znajomi as 
(
	select * from (
	VALUES  ('a','b'),('a','i'),('b','i'),('c','j'),('d','j'),
		('e','k'),('f','k'),('g','l'),('h','l'),('e','l')--,('a','e')
 
	) as Tab(a,b)
 
), 
Edge as (
 
	select l.a as Start , cast( l.b  as varchar) as Koniec, 1 as dist 
	from Znajomi l
	union all 
		select cast( l.b  as varchar) , l.a, 1
	from Znajomi l
),
Paths as 
(
	select start , koniec ,  CAST('.' + cast(start as varchar(10))+ '.' 
		+ cast(koniec  as varchar(10)) + '.' as varchar(max)) as path, dist
	from Edge
 
	UNION ALL
 
	select f.start, t.koniec , 
		CAST(f.path + cast(t.koniec as varchar(10)) + '.' as varchar(max)), F.dist+1
	from Paths as F join Edge T on 
	case when F.path like '%.' + cast(T.koniec as varchar(10)) + '.%' then 1 else 0 end = 0
		and f.koniec = T.start
)
, 
Grupy as (
 
   select c.a, c.b , SUBSTRING(max(path),2,CHARINDEX('.',max(path),2)-2) as GRID
   from  Znajomi c left join Paths    r  on r.Path like '%' + c.a + '.' +  
   cast( c.b  as varchar) + '%' or r.Path like '%' + cast( c.b  as varchar) + '.' + c.a  + '%' 
   group by c.a, c.b 
)
select a, b, DENSE_RANK() OVER(order by GRID) as Grupa from Grupy

Grafy_Drzewa_SQL_12

Zauważ, że wykorzystałem tu pewne fundamentalne i oczywiste założenie. W danym drzewie, mamy możliwość dojścia do każdego węzła. W przeciwnym razie, dany graf byłby niespójny i stanowiłby tak naprawdę dwa lub więcej drzew.

W związku z tym, w ostatnim CTE (Grupy), grupuję po maksymalnej (równie dobrze mogłaby to by być minimalna) ścieżce, która zawiera identyfikatory węzłów. Na podstawie tego największego identyfikatora węzła, określam przynależność do grupy.


Podsumowanie

Zaprezentowane w tym artykule rozwiązania to tylko jedne z możliwych podejść. Proceduralnie można by je było rozwiązać znacznie prościej i szybciej. Jeśli masz propozycję rozwiązania tych zadań w bardziej efektywny sposób za pomocą jeden kwerendy – zapraszam do dyskusji !

Tematyka grafów jest znacznie szerzej (świetnie) opisana w książce Itzika Ben Gana „Inside Microsoft SQL Server 2008: T-SQL Querying” – polecam wszystkim.

Generowanie zbioru – wektor dyskretnych i ciągłych wartości

W praktyce pisania zapytań SQL, nieraz spotkałem się z potrzebą wygenerowania automatycznej tabeli. Na przykład osi czasu z uwzględnieniem każdego dnia albo wektora kolejnych liczb z zadanego przedziału.

W artykule tym, zademonstruję jak wygenerować za pomocą CTE, zbiór zawierający inkrementowane elementy z określonego przedziału.

Generowanie wektora danych za pomocą CTE

Zastosowań takiego ciągłego, dyskretnego zbioru jest wiele. Rozważmy taki scenariusz.

Chcę utworzyć raport, zagregowanej sumy wszystkich złożonych zamówień w zadanym przedziale dat. Istotnym dla mnie jest zachowanie skali czasu – tak aby pokazane były na niej wszystkie dni z zadanego okresu. Również te, w których nie spłynęło ani nie zostało zrealizowane żadne zamówienie. Chcę także na tym samym wykresie pokazać informacje o realizacji zamówień.


To co potrzebuję na początek, to tabeli w postaci wektora ze wszystkimi kolejnymi dniami np. od ‚2014-02-01’ do ‚2014-02-14’. Do takiego zbioru (osi czasu) będę odnosił się analizując już szczegółowo daty zamówień.

Można stworzyć taką tabelę za pomocą wielu operacji UNION (niewygodne). Można też ładować w pętli wartości do tabeli tymczasowej. Nie są to jednak „ładne” i uniwersalne sposoby.

Do utworzenia takiego ciągłego zbioru, idealnie nadaje się rekurencyjne CTE. W SQL Server 2012, można za ich pomocą tworzyć nieograniczone (rekursywnie) zbiory. Opisuję w detalach strukturę rekurencyjnych CTE w ramach kursu SQL.

Kwerenda, która wygeneruje interesujący mnie zbiór, będzie wyglądała tak :

WITH Daty AS
(
	-- Rekurencyjne Common Table Expression 
          -- domyślnie max 100 przebiegów rekurencyjnych, czyli będzie działało 
          -- do 2014-05-12 :) potem trzeba użyć MAXRECURSION
 
	SELECT cast('2014-02-01' as SmallDatetime) as Data , 1 as Liczba
 
	UNION ALL
 
	SELECT Data+1, Liczba+1
	FROM Daty a
	WHERE a.Data < getdate()-1
 
)
SELECT * FROM Daty

CTE_wektor_wartosci

Szczególnie fajną rzeczą w CTE, jest możliwość korzystania z nich w różnych strukturach – np. widokach. Tworzenia tabel tymczasowych i technik programistycznych nie wpleciemy w zwykły widok.

Rekurencyjne CTE są domyślnie ograniczone do 100 przebiegów. Możemy jednak sterować ich maksymalną liczbą, stosując opcję MAXRECURSION.

Parametr ten przyjmuje wartości od 0 (nieograniczona ilość przebiegów) do 32767. Tworzenie np. zbioru (wektoru) z 1000 kolejnych liczb, wymaga zastosowania opcji MAXRECURSION. W przeciwnym razie, po wykonaniu setnego (domyślnie) przebiegu rekurencyjnego, otrzymamy błąd :

Msg 530, Level 16, State 1, Line 3
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Zatem jeśli potrzebujemy większy wektor, na przykład wspomniany 1000-elementowy, to utworzymy go w ten sposób :

with Liczby as 
(
	select  1 as Liczba
	UNION ALL
	select Liczba+1
	from Liczby a where a.Liczba < 1000
 
)
Select * from Liczby
OPTION (MAXRECURSION 0)
Liczba
-----------
1
2
3
. . .
998
999
1000

(1000 row(s) affected)

Wróćmy jednak do naszego scenariusza. Skrypt generujące przykładowe dane, z których korzystam w dalszej części znajdziesz tutaj.

Kwerenda odnosiła się będzie bezpośrednio do utworzonego wektora czasu (pierwsze CTE – Daty). Jest on główną osią na której pokazywać będę zagregowane i narastające sumy zleceń.

with Daty as 
(
	SELECT cast('2014-02-01' as date) as Data 
 
	UNION ALL
 
	SELECT DateAdd(dd,1,a.Data)
	from Daty a
	where a.Data < getdate()-1
 
),
Orders_raport as (
 
	SELECT a.Data,  count(distinct o.Order_id) as Orders_QTY, 
	     SUM(count(distinct o.Order_id)) OVER(order by a.Data)  as Agg_Orders_Qty,
	     count(distinct o2.Order_id) as Comp_Orders_QTY, 
	     SUM(count(distinct o2.Order_id)) OVER(order by a.Data)  as Agg_Comp_Order_qty
	FROM Daty a 
	     left join dbo.Orders o on a.Data =  cast(o.create_date as date) 
                         and cast(o.create_date as date) > '2014-01-31'
	      left join dbo.Orders o2 on a.Data = cast(o2.completion_date as date) 
                         and cast(o2.create_date as date) > '2014-01-31'
	GROUP BY a.Data
 
) 
SELECT *
FROM Orders_raport
ORDER BY  DATA

CTE_wektor_wartosci2

Teraz na podstawie tak przetworzonych danych, łatwo np. w Excelu wygenerować wykres – co należy podkreślić – w ciągłej dziedzinie czasu.

CTE_wektor_wartosci3

MCSA, egzamin 70-462 Administering Microsoft SQL Server 2012 Databases

Kolejny krok na ścieżce MCSA za mną. Dziś zdałem drugi z 3 egzaminów wchodzących w skład ścieżki Microsoft Certified Solutions Associate: SQL Server 2012. Chcę się tym razem podzielić z Wami wrażeniami „na gorąco”, jak wygląda egzamin 70-462 Administering Microsoft SQL Server 2012 Databases.

Egzamin był dla mnie bardziej wymagający od 70-461, przede wszystkim dlatego, że moja działalność związana z bazami danych jest obecnie przesunięta w kierunku development/BI.

Przygotowanie do egzaminu 70-462

Zacznijmy od punktu wyjścia. Patrząc obiektywnie, wszelkie egzaminy wymagają z pewnością praktycznej znajomości tematu. Szczerze mówiąc, trudno mi sobie wyobrazić możliwość przerobienia tylko w teorii zakresu materiału wymaganego na egzamin 70-462 i zdania go bez ćwiczeń praktycznych. Praktyka jest obowiązkowa, choćby na maszynach wirtualnych.

Moje przygotowanie merytoryczne związane z administracją serwerów opartych o tę technologię, bazuje na doświadczeniach z pracy z rzeczywistymi, komercyjnymi serwerami a także szkoleń, scenariuszy, które prezentowałem i przerabiałem w trakcie kursów. Jednak jak to w życiu bywa, z wymaganego zakresu znalazło się kilka obszarów gorzej poznanych (i wciąż kilka jest ;)), na niesatysfakcjonującym dla mnie poziomie. Stąd musiałem się trochę podciągnąć – szczególnie w High Availability.

Odpowiedzmy sobie szczerze, ilu z DBA, ma kontakt z poważnymi, komercyjnymi implementacjami środowisk w których zastosowano AlwaysOn Availability Groups czy instancji w ramach Failover Cluster opartych o SQL Server 2012. Tym bardziej, że w środowiskach produkcyjnych, panują niepisane zasady ewolucji. Nikt natychmiast po premierze nowej platformy, nie podejmuje decyzji o migracji. Z tego powodu, w wielu płaszczyznach, bazować trzeba na wiedzy praktycznej, przerobionej na serwerach w wersjach 10 i wcześniejszych oraz mniej lub bardziej złożonych scenariuszach testowanych na maszynach wirtualnych.

Na szczęście większość zakresu tematycznego egzaminu 70-462, to typowe zadania administratorskie, które pokrywają się np. z SQL Server 2008 R2. Są to zarządzanie uprawnieniami, polityki, optymalizacja, rozwiązywanie problemów z serwerem, tracing, strategie backupowe… etc. niby znane i lubiane. Przyznam się jednak bez bicia, że w praniu wyszły pewne braki i to w obszarach które wydawały mi się … dobrze znane. Taki już urok pytań testowych Microsoft.

Środowisko testowe

Do przetestowania większości z tematów poruszanych na egzaminie wystarczy zainstalowana wersja trial SQL Server 2012 Enterprise, a najlepiej dostęp do komercyjnej lub chociaż developerskiej instancji SQL Server. Nawet najlepszy poligon nie zastąpi skromnej, ale rzeczywistej bitwy.

Odpowiednie środowisko testowe to podstawa. Do symulacji wielu serwerów, tematów związanych z High Availability, replikacji zastosowałem maszyny wirtualne postawione na Hyper-V (Vmware również się do tego celu świetnie nadaje).

Wszystko co potrzebne można pobrać w wersjach testowych (trial) z oficjalnych zasobów Microsoft i budować praktycznie dowolne scenariusze. Podstawą tutaj będzie Windows Server 2008 R2 lub 2012 + SQL Server 2012 Enterprise najlepiej wszystko w architekturze x64. Przyda się też dobry sprzęt. W moim przypadku użyłem trochę już leciwego, ale wciąż dającego radę Core2 Duo E8400 na płycie Gigabyte EP45 lekko podkręcony z 8 GB RAM PC8500. Na tak przygotowanej kanwie, postawiłem kilka maszyn wirtualnych + kontroler domeny i w ten sposób mogłem zacząć zabawę w Master of Disaster.

Literatura, kursy przygotowujące do 70-462

Po całkiem udanej pozycji Trainning Kit związanej z egzaminem 70-461, postanowiłem iść za ciosem i zakupić analogiczną, dedykowaną lekturę, pomocną w przygotowaniach do tego testu.

Moje zamiłowanie do tradycyjnej formy pomocy naukowych, pchnęło mnie więc do zakupu książki Trainning Kit (Exam 70-462) Administering MS SQL Server 2012 Databases. Co więcej, jest już dostępna również w języku polskim i na domiar złego wybrałem właśnie tą wersję.
70-462_trainning_kit

Moja opinia na temat tej książki, jakkolwiek subiektywna, może być tylko jedna – tym razem porażka na całej linii. Autorzy starają się poruszyć wszystkie zagadnienia, związane z egzaminem – jednak to co istotne jest albo „wyjaśniane” jednym zdaniem, lub wcale. O ironio, każda lekcja (do tego zdążyłem się już przyzwyczaić studiując oficjalne materiały MSFT) rozpoczyna się bardzo obiecująco : „Po przerobieniu tego rozdziału będziesz umiał….”. Niestety, z pewnością nie nauczysz się zbyt wiele studiując tą pozycję.

Polskie tłumaczenie, to niestety często spotykany problem w literaturze IT. Czytając ją, kilka razy zastanawiałem się czy jesteśmy w tym samym teatrze, bo próby tłumaczenia pewnych nie tłumaczalnych w nomenklaturze IT słów wprawiały mnie w dłuższą zadumę, przechodzącą w poczucie wyrzuconych w błoto pieniędzy.
Książka jest słaba, a jedynym plusem są ćwiczenia i kilka scenariuszy, które faktycznie okazały się pomocne w zrozumieniu paru tematów. Jednak cena (ponad 150 PLN) jest kompletnie oderwana od merytorycznej wartości tej lektury. Tym razem – nie polecam.

Poza ogólnodostępnymi materiałami, które pomogły mi w przygotowaniu się do tego egzaminu, był z pewnością autoryzowany kurs Microsoft 10775. Wiem, że dla wielu ta wskazówka może okazać się mało pomocna, bo cena tego szkolenia jest wysoka (5 dni ~ 4000 PLN), jednak wiele firm edukacyjnych prowadzi aktualnie projekty dofinansowane z EFS i można załapać się na darmowy kurs (w 100% dofinansowany).

Szkolenie to (podobnie jak poprzednia jego wersja MS 6231), materiały oraz ćwiczenia pomagają w zrozumieniu najważniejszych zagadnień związanych z administracją SQL Server. Oczywiście nie ma się co łudzić, że po takim kursie będziesz od razu dobrym administratorem – nic nie zastąpi praktyki. Jednak z czystym sumieniem można go polecić bo z pewnością jest to pomocny i solidny punkt w przygotowaniu do egzaminu 70-462.

Ponadto jeśli masz doświadczenia z zarządzaniem SQL Server w wersjach 2005-2008 R2 – to na wiele pytań będziesz mógł odpowiedzieć praktycznie z marszu. Osobiście najwięcej czasu w trakcie przygotowań, poświęciłem na High Availability w SQL Server 2012. To dla mnie w pewnym sensie pięta achillesowa (jak się okazuje można mieć więcej niż dwie ;)). Na szczęście pytania z tej serii są raczej bardzo ogólne, poza tym stanowią najmniejszą (12%) sekcję tematyczną 70-462.

O samych pytaniach zbyt wiele jak zwykle pisać nie można – klauzula tajności, ale garść ogólnych wskazówek poniżej :

  • pytania są prostsze i trudniejsze, ale do przejścia :)
  • czas – 150 minut w zupełności wystarcza aby przebrnąć na spokojnie przez wszystkie nawet dwukrotnie
  • wymagany poziom poprawnych odpowiedzi – standardowe 70%
  • cena egzaminu 100 USD – polecam bezpośrednio rezerwować na Prometric.com jest znacznie taniej niż u „Partnerów”
  • wciąż obowiązuje promocja na darmową poprawkę w przypadku niepowodzenia – Second Shot (do końca maja 2013) lub 15% discount na egzaminy
  • egzamin (podobno jak wszystkie pozostałe ze ścieżek MCSA/MCSE) jest dostępny tylko w języku angielskim

Więcej o samym egzaminie znajdziesz na oficjalnych stronach Microsoft.