BIML i Data Profiling Task – automatyczne profilowanie wszystkich tabel w bazie danych

Data Profiling Task to według mnie jeden z najciekawszych komponentów SQL Server Integration Services, który bardzo często jest pomijany, lub o którym często się nie pamięta. Jest on przydatny na wielu płaszczyznach pracy z danymi od wyszukiwania problemów z danymi podczas tworzenia aplikacji lub hurtowni danych, aż do podstawowej weryfikacji oraz badania danych na potrzeby ich późniejszego wykorzystania w odkrywaniu wiedzy. Wydaje mi się również, że jest idealnym przykładem do przedstawienia języka BIML, czyli Business Intelligence Modeling Language. Dzięki kombinacji tych dwóch technologii jesteśmy w stanie w bardzo krótkim czasie przygotować uniwersalne narzędzie do gromadzenia podstawowych informacji o danych oraz do ich profilowania.

Data Profiling Task

Jak wspomniałem wcześniej Data Profiling Task w dużym skrócie służy do badania podstawowych cech danego zbioru danych (tabel lub widoków) i jest to komponent dostępny w Integration Services. Pozwala przeprowadzić badanie dla jednej wybranej tabeli lub widoku. Wynikiem działania tego komponentu jest plik płaski w formacie XML, który można wyświetlać i analizować z wykorzystaniem narzędzia SQL Server Data Profile Viewer.

Konfiguracja komponentu jest bardzo prosta i sprowadza się do kilku prostych kroków. Jak już wspomniałem wcześniej wynikiem działania tego komponentu jest plik płaski z rezultatem analizy. W pierwszym kroku musimy zatem skonfigurować plik wynikowy poprzez wskazanie lub utworzenie połączenia do pliku.

Następnie możemy wskazać jakie informacje o tabeli chcielibyśmy uzyskać. Dostępnych jest kilka opcji (żądań):

Column Null Ratio Profile Request – ilość oraz procent wartości NULL względem pozostałych
Column Statistics Profile Request – podstawowe statystyki (minimum, maksimum, średnia, odchylenie standardowe) dla każdej kolumny liczbowej oraz typu data
Column Length Distribution Request – podstawowe statystyki o długości pól tekstowych (minimum, maksimum) oraz procentowy podział długości dla każdej kolumny tekstowej
Column Value Distribution Request – analiza wartości kolumn tekstowych i BIT. Informuje, które wartości pojawiają się najczęściej oraz jaki to procent
Column Pattern Profile Request – próba odnalezienia wzorców w danych tekstowych. Wynikiem będą konkretne wartości lub wyrażenia regularne oraz ich procentowy udział względem wszystkich wartości w kolumnie
Functional Dependency Profile Request – wyszukiwanie zależności pomiędzy atrybutami – na przykład nazwa państwa i kod ISO państwa zostanie zwrócona jako para, która jest zależna i powinna być zgodna w stu procentach.
Value Inclusion Profile Request – sprawdzenie dwóch kolumn (z możliwością wskazania różnych kolumn z różnych tabel) pod względem ich przydatności do klucza obcego

Konfiguracja niemal wszystkich z tych opcji (żądań) jest również bardzo prosta i sprowadza się do wskazania tabeli/widoku oraz ustawienia kilku podstawowych ustawień.

W wyniku utworzony zostanie plik, który można przeglądać za pomocą SQL Server Data Profile Viewer i który pokazuje wyżej wymienione statystyki.

Jak widać za pomocą zaledwie kilku kliknięć można uzyskać naprawdę dużo informacji na temat naszych danych. W przypadku jednak, gdy dysponujemy dużą bazą danych z setkami tabel taka konfiguracja mimo wszystko będzie czasochłonna i monotonna.

Ze względu jednak na tę powtarzalność wydaje mi się, że jest to jeden z idealnych przykładów na wykorzystanie języka BIML, który pozwoli nam zaoszczędzić mnóstwo pracy, a dodatkowo pozwoli nam na stworzenie uniwersalnej metody do tego typu analizy danych.

BIML

BIML jest to język dostępny z poziomu SQL Server Integration Services i który pozwala przygotować szablon pakietu/pakietów z wykorzystaniem języka C#. Za pomocą struktury pliku XML pakietu oraz kodu C# właśnie jesteśmy w stanie przygotować skrypt, który pozwoli nam wygenerować końcowy pakiet. Jeżeli pomyślimy o tym, że jesteśmy w stanie wykorzystać wszystkie możliwości języka C# – w tym pętli – oraz skorzystać z dowolnej dodatkowej biblioteki jesteśmy w stanie przygotować naprawdę uniwersalne szablony pakietów oraz zautomatyzować pisanie podobnych zadań.

Nowy skrypt możemy dodać klikając prawym przyciskiem myszy na folderze “SSIS Packages” w projekcie Integration Services w Visual Studio oraz wybierając “Add new BIML script”.

Poniżej przedstawiam skrypt, który pozwoli nam wygenerować pakiet z zadaniami wywołania komponentu Data Profiling Task dla każdej tabeli wymiaru z bazy danych Adventure Works.

       
<Biml
    xmlns="http://schemas.varigence.com/biml.xsd">

                    <# 
                      

                      
                      
                      string ConnectionString =@"Data Source=.\db14;Initial Catalog=AdventureWorksDW2014;Integrated Security=True;";
                      List<String>
                      tables = new List<String>
                        ();

                      using(SqlConnection connection = new SqlConnection(ConnectionString))
                       {
                          string query = @"

                                            SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) AS SchemaName,
                                            sOBJ.name AS [TableName],
                                            SUM(sPTN.rows) AS [RowCount]
                                            FROM sys.objects AS sOBJ
                                            INNER JOIN sys.partitions AS sPTN
                                            ON sOBJ.object_id = sPTN.object_id
                                            WHERE sOBJ.type = 'U'
                                            AND sOBJ.is_ms_shipped = 0x0
                                            AND index_id < 2 -- 0:Heap, 1:Clustered
                                            AND sOBJ.name like 'Dim%'
                                            GROUP BY sOBJ.schema_id,
                                            sOBJ.name
                                            ORDER BY 3 DESC;
                      
                      
                          ";
                      
                          using(SqlCommand command = new SqlCommand(query, connection))
                          {
                              connection.Open();
                              using (SqlDataReader reader = command.ExecuteReader())
                              {
                                  while (reader.Read())
                                  {
                                      tables.Add(reader.GetString(1));
                                  }         
                              }
                          }
                      }  
                   
                   
                 #>
    
  
  <Connections>
    <AdoNetConnection Name="Source database connection" Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" ConnectionString="<#=ConnectionString#>" />
        
      <#  foreach (string table in tables)  {  #>         
    
              <FileConnection Name="OutputFile<#=table#>" FileUsageType="CreateFile" FilePath="C:\test\ProfilerResults\<#=table#>.xml" />

        <#  }  #>   
          
  </Connections>
  <Packages>
    <Package Name="CheckDataTypes_DIMS" ConstraintMode="Linear">
      <Tasks>
        <Container Name="DIMS">
          <Tasks>
            
                <#  foreach (string table in tables)  {  #>         
           
                        <DataProfiling Name="<#=table#>" OverwriteDestination="true">
                          <FileOutput ConnectionName="OutputFile<#=table#>" />
                          <ProfileRequests>
                            <ColumnNullRatioProfileRequest Name="NullRatioReq" ConnectionName="Source database connection" SchemaId="dbo" TableId="<#=table#>" />
                            <ColumnStatisticsProfileRequest Name="StatisticsReq" ConnectionName="Source database connection" SchemaId="dbo" TableId="<#=table#>" />
                            <ColumnLengthDistributionProfileRequest Name="LengthDistReq" ConnectionName="Source database connection" SchemaId="dbo" TableId="<#=table#>" />
                            <ColumnValueDistributionProfileRequest Name="ValueDistReq" ConnectionName="Source database connection" SchemaId="dbo" TableId="<#=table#>" />
                            <ColumnPatternProfileRequest Name="PatternReq" ConnectionName="Source database connection" SchemaId="dbo" TableId="<#=table#>" />
                          </ProfileRequests>
                        </DataProfiling>

                  <#  } #>   
                   
          </Tasks>
        </Container>
      </Tasks>
    </Package>
  </Packages>
</Biml>

<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>

Opisując krótko kod chciałbym zwrócić na kilka ciekawych miejsc. Przede wszystkim kod ma postać pliku XML z elementami odpowiadającymi standardowemu plikowi pakietu. Znajdziemy tam więc miejsce “Connections” dla listy połączeń, “Packages” dla pakietów, które chcemy wygenerować, ale także “Container” dla zdefiniowania kontenerów czy “Task” dla pojedynczych zadań.

W przypadku naszego skryptu, przed zdefiniowaniem czegokolwiek wykorzystujemy jednak C#. Za pomocą prostego połączenia do bazy danych pobieramy wszystkie tabele, które rozpoczynają się przedrostkiem “Dim” i zapisujemy do listy “tables”. Następnie w połączeniach definiujemy jedno połączenie do bazy danych w których znajdują się interesujące nas tabele (w tym przypadku skorzystaliśmy również z możliwości użycia wartości wcześniej zdeklarowanej zmiennej) oraz definiujemy za pomocą pętli “foreach” jedno połączenie do pliku płaskiego dla każdej tabeli wraz z odpowiednią nazwą połączenia i pliku wynikowego. W kolejnych liniach pliku definiujemy nazwę pakietu, nazwę konteneru oraz ponownie, za pomocą pętli “foreach”, dla każdej tabeli z listy definiujemy zadanie typu “DataPfofiling” oraz konfigurujemy podstawowe ustawienia. To wszystko.

Po wybraniu skryptu oraz kliknięciu “Generate SSIS Packages” nasz pakiet zostanie wygenerowany.Jak widać zgodnie z tym co znajduje się w skrypcie widzimy wiele zadań typu Data Profiling Task dla każdego wymiaru w bazie danych Adventure Works. Widzimy również różne połączenia dla plików z wynikami dla każdego z zadań.

W podanym przez nas folderze znajdziemy znowu pliki z rezultatami analizy dla każdej tabeli. Świetne, prawda?

Podsumowanie

Celem tego postu było zwrócenie uwagi zarówno na Data Profiling Task w Integration Services jak i języka BIML. Jak widać na powyższym przykładzie ten pierwszy może być bardzo przydatny podczas poznawania, wstępnej analizy lub sprawdzania poprawności danych, natomiast ten drugi może okazać się bardzo wydajnym mechanizmem do automatyzacji powtarzalnych zadań/pakietów w SQL Server Integration Services.

Uwagi

Podczas kopiowania skryptu do Visual Studio mogą pojawić się błędy. Wynika to z faktu, iż Visual Studio będzie starał się sformatować kod podczas wklejania ze schowka i może się okazać, że doda niepotrzebne znaki, które będą powodowały błąd walidacji. Jeżeli to wystąpi należy w ustawieniach Visual Studio (Tools -> Options -> Text Editor -> XML -> Formatting) odznaczyć opcję “Auto Reformat” dla “On paste from clipboard”
Podczas próby generowania pakietu może wystąpić błąd dotyczący poziomów zabezpieczenia danych wrażliwych w SSIS. W powyższym kodzie ustawienie to nie jest sprecyzowane, więc może się różnić od ustawień projektu i innych pakietów. W tym celu trzeba zadbać o to, aby projekt i pozostałe projekty ustawić, aby nie przechowywały wrażliwych danych połączenia lub dodać odpowiedni kod do skryptu, który zmieni to ustawienie w generowanym pakiecie.
Aby nie zaciemniać kodu i zbytnio go nie rozbudowywać w powyższym przykładzie nie jest ustawiany również schemat tabel. W tym przypadku jest on “ręcznie” ustawiony na “dbo” i skrypt oczekuje, że każda tabela będzie należeć do tego schematu.
Jeżeli post będzie się cieszył popularnością wrzucę pełną wersję skrpytu, który obsługuje te wyjątki