Przenoszenie danych między serwerami SQL za pomocą wygenerowanych skryptów.

Dziś pokażę jak wykonać przeniesienie danych pomiędzy serwerami z różnymi wersjami SQL. Jeśli chcielibyśmy przenieść ze starszego serwera na nowszy nie powinno być z tym większego problemu. Problemy pojawiają się jeśli chcemy przenieść dane (bazy) z serwera o wyższej wersji do serwera z niższą wersją. Niestety tu napotkamy na problemy (zarówno próbując wykonać Attach plików MDF i LDF z nowszej wersji, jak i próbując odzyskać backup wykonany na wyższej wersji)

Możemy skorzystać z exportu a następnie importu danych znajdujących się w bazie. Ja jednak pokażę jak wygenerować skrypty pozwalające na migrację danych.

Załóżmy że mamy dwa serwery (SQL2019 i SQL2016) i chcemy przenieść całą bazę z wyższej wersji (2019) do wersji starszej (2016).
Łączymy się do serwera źródłowego. Wybieramy bazę którą chcemy przenieść i z menu wybieramy opcję GENERATE SCRIPTS

Mamy do wybory dwie opcje. Pierwsza pozwala na wygenerowanie skryptu dla całej bazy.

Drugi dla pojedynczych obiektów bazy (tabele, widoki lub procedury składowane).

Przy wyborze Single Script File (jeden skrypt) wybieramy Ścieżkę i nazwę skryptu. Możemy również m.in. wybrać od razu wczytanie skryptu do okna SQL Management Studio.

Po naciśnięciu przycisku ADVANCED mamy sporą listę parametrów dotyczących tworzonych skryptów.

Na głównie będą interesowały poniższe czyli wybór czy w skrypcie mają być zawarte dane znajdujące się w tabelach czy tylko skrypty tworzące strukturę czy jedno i drugie:

Wersja docelowej wersji SQL-a:

Edycja docelowej wersji SQL-a:

Możemy również wybrać opcję w której wygenerowane zostaną skrypty dla poszczególnych obiektów (np. tabel). Wskazujemy w takim przypadku tylko folder gdzie mają się znaleźć skrypty a nazwy zostaną wygenerowane automatycznie

Małe podsumowanie i naciśnięcie FINISH spowoduje uruchomienie tworzenia skryptów.

Skrypty utworzone.

Teraz nie pozostaje nic innego jak skopiować je na docelowy serwer i uruchomić.
Powinniśmy zacząć od skryptu tworzącego nasza bazę (w naszym przypadku Northwind.Database.sql). Przy tworzeniu bazy nalezy zwrócić uwagę na ścieżki do plików bazy czy jest wpisana odpowiednia ścieżka.
Następnie wykonujemy skrypty dla poszczególnych obiektów (najpierw tabel, potem widoków, procedur składowanych itd).
Jak widać poniżej skrypt dla tabeli dbo.Categories.Table składa się z sekcji tworzącej tabele (CREATE TABLE) wraz z indeksami jak i jej napełnienie (INSERT dla każdego wiersza osobny)

PROCEDURA

Inną metoda jest zastosowanie procedury za pomocą której wygenerujemy skrypt to wprowadzenia danych do naszej tabeli. Link do wkryptu sp_generate_inserts.sql

Procedurę uruchamiamy poleceniem (przykładowo dla bazy Nortwind i tabeli dbo.Customers

EXEC sp_generate_inserts 'Customers'

Aby ograniczyć wielkość pliku uruchomimy to z parametrem określającym wygenerowanie skryptu dla TOP(5) wierszy tabeli

EXEC sp_generate_inserts 'Customers', @top = 5

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
PRINT 'Inserting values into [Customers]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','Méxi
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S

PRINT 'Done'

SET NOCOUNT OFF

Po zakończeniu prac usuwamy procedure poleceniem
drop proc sp_generate_inserts

Więcej informacji dotyczącej procedury sp_generate_inserts :
https://vyaskn.tripod.com/code.htm#inserts

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

FireBird (cz.3) – Bóbr dobiera się do naszej bazy :)

Jeżeli jesteśmy przyzwyczajeni do graficznych programów do obsługi baz danych to możemy przy obsłudze bazy FireBird skorzystać z darmowego programu DBeaver w wersji portable czyli bez konieczności instalacji (https://dbeaver.io/download/)

Po ściągnięciu pliku ZIP rozpakowujemy go i uruchamiamy program dbeaver.exe
Jak widać program obsługuje sporą ilość baz (MSSQL, Oracle, MySQL, PostgreSQL, DB2, itd).

Po wybraniu FireBirda wciskamy „next” i konfigurujemy połączenie do naszej bazy.
Może pojawić się komunikat o konieczności ściągnięcia sterowników (oczywiście akceptujemy)

Podłączyliśmy się do bazy.

Zaszufladkowano do kategorii FireBird | Dodaj komentarz

FireBird (cz.2) – Pierwsze kroki

Po instalacji FireBird’a (opisane w artykule https://micek1968.pl/?p=4338) wraz z silnikiem bazy danych zostało zainstalowane oprogramowanie „FireBird ISQL Tool”.

Jest to proste okno poleceń za pomocą którego możemy pracować na naszych tabelach w bazach FireBird.
Możemy uruchamiać ISQL-a z linii poleceń. Program znajduje się w folderze z binarkami bazy danych (C:\Program Files (x86)\Firebird\Firebird_4_0)
Uruchamiamy CMD (jako ADMINISTRATOR !!!) i uruchamiamy program ISQL.EXE

Prościej jest jednak uruchomić to z menu startowego.

Tu także uruchamiamy ISQL w trybie administratora. Uruchomienie w trybie zwykłego usera spowoduje, że nie będziemy mieli dostępu do baz systemowych (np. listy userów baz).

Zgodnie z informację podaną w oknie najpierw musimy podłączyć się do bazy lub ją stworzyć (Use CONNECT or CREATE DATABASE to specify a database)
Jako że nie mamy jeszcze żadnej bazy w pierwszej kolejności ją utworzymy.

UTWORZENIE BAZY DANYCH

SQL> CREATE DATABASE "C:\FireBirdDB\BazaDanych.FDB";
Server version:
WI-V4.0.1.2692 Firebird 4.0

W folderze utworzył się plik FDB.

Po utworzeniu bazy danych, wylogowujemy się za pomocą polecenia QUIT.

PODŁĄCZENIE DO BAZY DANYCH

Aby podłączyć się do bazy danych należałoby wydać polecenie:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB";
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: MICEK1968

Możemy sprawdzić listę użytkowników:

SQL> SHOW USERS;
Users in the database
  1 #MICEK1968

Przy aktualnie podłączonym użytkowniku znajduje się znak "#".

Informacje o naszej aktualnej bazie danych (do której jesteśmy podłączeni)

SQL> SHOW DATABASE;
Database: C:\FireBirdDB\BazaDanych.FDB
        Owner: MICEK1968
PAGE_SIZE 8192
Number of DB pages allocated = 240
Number of DB pages used = 224
Number of DB pages free = 16
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 6
Transaction - oldest active = 7
Transaction - oldest snapshot = 7
Transaction - Next = 12
ODS = 13.0
Database not encrypted
Embedded connection
Creation date: Feb 2, 2022 18:25:07 GMT
Default Character set: NONE

To samo uzyskamy poprzez "SHOW DB".

UTWORZENIE TABELI i WPROWADZENIE DANYCH

Jeśli już podłączyliśmy się do bazy, wyświetlimy listę tabel

SQL> SHOW TABLE;
There are no tables in this database

W bazie nie ma żadnych tabel więc ją utworzymy.

SQL> CREATE TABLE ADRESY
CON> (
CON> nazwisko varchar(40),
CON> adres varchar(50),
CON> telefon varchar(9)
CON> );
SQL> COMMIT;

Można też stworzyć plik tekstowy zawierający treść skryptu a następnie wykonać polecenie

SQL> IN c:\FireBirdDB\create_table_adresy.sql;
SQL> 

Ponownie sprawdzamy listę tabel

SQL> SHOW TABLE;
ADRESY

Wyświetlamy dane w tabeli

SQL> SELECT * FROM ADRESY;
SQL>

Nic nie zwróciło gdyż tabela jest pusta. Wprowadzimy do niej jeden wiersz.

SQL> INSERT INTO ADRESY
CON> (Nazwisko, Adres, Telefon)
CON> VALUES ('Nowak Jan','Szczecin','997997997');
SQL>

Ponowne wyświetlenie danych zwraca dane

SQL> SELECT * FROM ADRESY;

NAZWISKO                  ADRES           TELEFON
========================= =============== =========
Nowak Jan                 Szczecin        997997997

Możemy do każdej tabeli dodać komentarz w którym opiszemy co baza zawiera

SQL> COMMENT ON TABLE adresy IS 'Ta tabela zawiera adresy i telefony.';

Zapisany komentarz wyświetlamy

SQL> show comment;
COMMENT ON TABLE        ADRESY IS Ta tabela zawiera adresy i telefony.;

USUNIĘCIE TABELI

Po zakończeniu prac możemy (ale na razie tego nie róbmy) usunąć naszą tabelę Adresy

SQL> DROP TABLE adresy;
SQL>

USUNIĘCIE BAZY DANYCH

Usuwanie bazy danych jest bardzo proste (Również proszę na razie tego nie wykonywać). Należy jednak uważać, bo FireBird nie prosi o potwierdzenie tylko bezpowrotnie usuwa z dysku bazę do której aktualnie jesteśmy podłączeni.

SQL> DROP DATABASE;
SQL>

Dlatego zalecane jest logować się do bazy zwykłym użytkownikiem o odpowiednich uprawnieniach 🙂

LOGOWANIE DO BAZY JAKO SYSADMIN

Jeśli chcielibyśmy utworzyć bazę jako SYSADMIN i następnie przyznawać użytkownikom odpowiednie uprawnienia (jako administrator) należało by to zrobić poleceniem:

SQL> CREATE DATABASE "C:\FireBirdDB\BazaDanych.FDB" USER "SYSDBA" PASSWORD "masterkey";
Server version:
WI-V4.0.1.2692 Firebird 4.0

Aby zalogować się do bazy jako SYSADMIN którego hasło ustawialiśmy podczas instalacji FireBirda nalezy wykonać polecenie:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'SYSDBA' password 'masterkey';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: SYSDBA

Oczywiście jako administrator mamy dostęp do wszystkich obiektów bazy.

SQL> select * from adresy;

NAZWISKO                                 ADRES                                              TELEFON
======================================== ================================================== =========
Nowak Jan                                Szczecin                                           997997997

Natomiast logując się na konto innego usera niż MICEK1968 (właściciel bazy) i SYSADMIN otrzymamy błąd dostępu:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'user123';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: USER123

SQL> select * from adresy;
Statement failed, SQLSTATE = 28000
no permission for SELECT access to TABLE ADRESY
-Effective user is USER123

i przełączamy się na MICEK1968

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'micek1968';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: MICEK1968

SQL> select * from adresy;

NAZWISKO                                 ADRES                                              TELEFON
======================================== ================================================== =========
Nowak Jan                                Szczecin                                           997997997
Zaszufladkowano do kategorii FireBird | Dodaj komentarz

FireBird (cz.1) – Instalacja

Firebird – system zarządzania relacyjnymi bazami danych zgodny ze standardem ANSI SQL-92; obok MySQL oraz PostgreSQL jest jednym z trzech najpopularniejszych wolnodostępnych systemów zarządzania bazą danych. (według Wikipedii).

Na początku pobieramy najnowszą wersję FireBirda ze strony https://firebirdsql.org/en/server-packages/

Uruchamiamy program instalacyjny i przechodzimy kolejne kroki instalacji.
Starsze wersje posiadały na liście język polski jednak obecnie producent zrezygnował i jest tylko kilka podstawowych. Wybieramy oczywiście język angielski 😉











FireBird zostały zainstalowany.
Sinik FireBirda jest uruchomiony jako serwis (usługa). Jego stan możemy sprawdzić na liście usług.

W menu startowym pojawiło się

Po jego uruchomieniu pojawia się proste narzędzie do podłączenia się do bazy.

FireBird jest gotowy do pracy.

Zaszufladkowano do kategorii FireBird | Dodaj komentarz

Extended Events czyli szukanie zapytań w SQL

Załóżmy, że codziennie wieczorem ktoś uruchamia jakieś zapytania do naszej bazy powodując np. zwiększone obciążenie serwera. Aby znaleźć winowajcę możemy wykorzystać narzędzie Extended Events.
Sposób konfiguracji opisałem w oddzielnym artykule (http://micek1968.pl/?p=3632).
Dziś skupimy się na konfiguracji aby wyszukiwało wykonywane „duże” zapytania.

Wchodzimy we właściwości naszej sesji Extended Events.


Przechodzimy do zakładki Event. proponuję w tym miejscu wybrać z biblioteki zdarzeń tylko „SQL_STATEMENT_COMPLETE”. W zupełności to wystarczy do naszych poszukiwań.

Przechodzimy w prawo do konfiguracji zdarzeń, a następnie do zakładki filtrowania.

Interesują nas tylko „duze” zapytania więc wybieramy zapytania które zwracają więcej niż 50 wierszy. Wybieramy z listy rozwijanej „LAST_ROW_COUNT”

a następnie podajemy wartość powyżej której ma wyszukiwać.

Przy ponownym otwarciu ustawień znak „>” zmienia się automatycznie w wartość „greater_than_uint64”

Zatwierdzamy ustawienia i uruchamiamy Sesję Extended Events

Następnie wykonujemy (testowo) kilka zapytań na bazie Northwind. Nie ma tam dużo wierszy ale wszystkie zapytania zwracające więcej niż 50 wierszy zostaną zarejestrowane.

SELECT * FROM [Northwind].[DBO].[ORDER_DETAILS] 
SELECT * FROM [Northwind].[DBO].[ORDERS]
SELECT * FROM [Northwind].[DBO].[PRODUCTS]
SELECT * FROM [Northwind].[DBO].[CUSTOMERS]

Następnie zamykamy sesję

I wyświetlamy zapisane dane

Jak widać nasze zapytania zostały zarejestrowane:

Widać kto to wykonywał, z jakiej komputera, ile wierszy zwróciło, treść zapytania i wiele innych rzeczy.
Wyszukiwanie możemy za pomocą filtra oczywiście ograniczyć do jednej bazy i wielu innych różnych parametrów.

Problemem się staje kiedy to puściliśmy rejestrację zdarzeń do Extended Events np na cały dzień i zarejestrowało nam kilkaset tysiecy zapytań do bazy. Jak znaleźć wśród tego interesujące nas zapytanie.
Możemy się wspomóc odczytem zarejestrowanych danych z plików poprzez zapytanie t-sql.

W naszym przykładzie zdarzenia są zapisane w pięciu plikach, ale może być ich więcej i o większej pojemności.

Najprostszym zapytanie zwracającym zarejestrowane wyniki będzie

select * from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)

Zwróci to nam wszystkie zarejestrowane zdarzenia. Mozemy ograniczyć się do pojedynczych plików wpisując ich nazwę.

Jeśli np wiemy, że zapytanie dotyczy konkretnej bazy mozemy dodać warunek
WHERE event_data like '%Orders%'
Zapytanie zwróci tyle zdarzenia dotyczące tabeli ORDERS.

Wszelkie dane dotyczące danego zdarzenia znajdują się w kolumnie EVENT_DATA i EVENT_DATA_XML (zapisane w formacie XML).

Możemy bardziej uporządkować wyświetlane dane

SELECT
object_name,
file_name,
file_offset,
event_data,
'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
CAST(event_data AS XML) AS [event_data_XML]
-- TODO: In ssms.exe results grid, double-click this xml cell!
FROM
sys.fn_xe_file_target_read_file(
--'C:\Temp\MySession_1_0_132878711812050000.xel',
'C:\Temp\MySession_1_0_*.xel',
null, null, null
)
WHERE event_data like '%Orders%'

Poniższe zapytanie zwraca nam zapisane zdarzenia w bardziej czytelnej postaci.

select
n.value('(@name)[1]', 'varchar(50)') as event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
-- WHERE n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') like '%Orders%'

Jeśli nas interesuje tylko tabela ORDERS musimy odznaczyć ostatni wiersz z warunkiem WHERE.

A na na koniec taka mała porada, że jeśli chcielibyśmy zarejestrować tylko zapytania wykonywane w określonym przedziale czasowym (np między 2 i 3 w nocy najprościej powinniśmy utworzyli dwa joby: jeden uruchamiający a drugi zatrzymujący naszą sesję (nie ma możliwości ustawienia tego we właściwościach sesji).
W kroku joba nalezy umieścić polecenie uruchamiające/zatrzymujące naszą sesję

ALTER EVENT SESSION event_session_name ON SERVER STATE = {START | STOP}

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Power BI Dekstop (cz.4) – Automatyczne odświeżanie danych

PowerBI ogólnie działa w ten sposób że dane z bazy lub innego źródła są pobierane do PowerBI. Ich zmiana w źródle nie powoduje zmiany w raportach. Aby nasze raporty reagowały na zmiany wartości w tabelach należy wybrać źródło typu DirectQuery. Opcję ta jest dostępna przy konfiguracji źródła danych.

Na początek przygotujemy sobie prostą przykładową tabelę w naszej bazie PowerDB. Zawierać ona będzie pięć wierszy z identyfikatorem (ID) oraz wartością w przedziale 10-20.

USE [PowerDB]
GO

CREATE TABLE [dbo].[refreshDB](
[ID] [int] NULL,
[wartosc] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[refreshDB]
([ID],[Wartosc])
VALUES
(1, 12),(2,14),(3,16),(4,13),(5,19)
GO

Sprawdzamy dane w naszej tabeli
select * from [dbo].[refreshDB]

ID          wartosc
----------- -----------
1           12
2           14
3           16
4           13
5           19

(5 rows affected)

Dane mamy już gotowe. Uruchamiamy PowerBI Desktop. Po uruchomieniu wybieramy GET DATA

Wybieramy bazę danych SQL Server Database

Wpisujemy nazwę naszego serwera SQL oraz nazwę bazy danych. Poniżej zaznaczamy że nasze połączenie będzie typu DirectQuery.

Wybieramy tabelę z naszymi danymi

Umieszczamy wykres w naszym reporcie.

Analogicznie jak to było w poprzednim artykule o tworzeniu raportów, przenosimy nazwy kolumn (ID, Wartość) do odpowiednich pól właściwości naszego wykresu (Axis, Value). Na wykresie pojawiają się nasze dane.

Teraz musimy ustawić częstotliwość odświeżania danych w naszym wykresie. We właściwościach wykresu klikamy w ikonę wałka malarskiego 😉 Na samym dole jest opcja PAGE REFRESH. Włączamy opcję suwakiem, a następnie ustawiamy czas przykładowo na 15 sekund.

Podłączamy się za pomocą Management Studio do naszej bazy i zmieniamy dane dla ID=3

USE [PowerDB]
GO
UPDATE [dbo].[refreshDB]
SET [wartosc] = 40
WHERE ID = 3
GO

Po kilku sekundach nasz wykres automatycznie się zmieni.

Zaszufladkowano do kategorii PowerBI | Dodaj komentarz

PowerBI Desktop (cz.3) – Pierwszy raport

W poprzedniej części opisu PowerBI Desktop stworzyliśmy bazę z tabelami zawierającymi przykładowe dane dotyczące sprzedaży firmy informatycznej posiadającej kilka oddziałów.
Dziś omówię jak stworzyć prosty raport przy pomocy którego będziemy mogli przeanalizować te dane.

Uruchamiamy PowerBI Desktop. Pojawia się ekran startowy.


Wybieramy GET DATA

PowerBI Dekstop podłączy się teraz do naszego źródła danych. Wybieramy go z listy. Wybieramy oczywiście MSSQL Server.

Mozemy również SQL-a wybrać z listy baz. Jak widać PowerBI obsługuje różne technologie.

Wpisujemy nazwę serwera oraz bazy z której chcemy zaimportować dane

Wybieramy tabele z których chcemy pobrać dane.

Dane są wczytywane z wybranych tabel w bazie.

Tabele zostały wczytane. Jak widać PowerBI znalazł i powiązał ze sobą odpowiednie kolumny (nazwę firmy)

Wybierając na lewej krawędzi okna środkową ikonkę możemy włączyć podgląd zaimportowanych tabel. Tabelę zmieniamy wybierając ja po prawej stronie ekranu w oknie FIELDS

Aby wyświetlić dane zapisane w kolumnie LOKALIZACJA w tabeli ITLOCATION jako dane adresowe należy zmienić ich kategorię danych.

Zaznaczamy kolumnę Lokalizacja i zmieniamy jej kategorię na ADDRESS


W tabeli ITSales też musimy zmienić format danych

Zmienimy format wyświetlanej daty z pierwszej kolumny DATOWNIK tabeli ITSALES

Format daty został zmieniony

Dane mamy gotowe. Pora na wyświetlenie ich w wykresach 🙂

Klikamy w drugi typ wykresu w oknie VISUALIZATIONS. Na ekranie głównym pojawi się pole (kafelek) wykresu.

Aby wyświetlić danych w tym wykresie należy odpowiednie nazwy kolumn z okna FIELDS (po prawej stronie ekranu) przenieść do odpowiednich pól we właściwościach wykresu

Do pola VALUES przeciągamy kolumnę WARTOŚĆ.
Jako że na osi X mają być wyświetlone nazwy firm przeciągamy do pola AXIS kolumnę FIRMA
Chcemy aby na wykresie były przedstawione ilości sprzedaży poszczególnych kategorii sprzętu informatycznego więc do okna LEGEND przenosimy kolumnę SPRZET_IT.
Na wykresie pojawiają się pierwsze wartości danych.

Umieścimy teraz drugi wykres (tym razem liniowy) przedstawiający wartość sprzedanych sprzętów informatycznych.

Do okienek AXIS i VALUES przenosimy odpowiednio kolumny DATOWNIK i WARTOSC

Na wykresie pojawi się kropka. Jest to powodem że dane są wyświetlane jako suma z całego roku („Wartość by year”).

Aby ujrzeć dane z poszczególnych miesięcy lub dni, należy kilkukrotnie nacisnąć ikonkę nad wykresem liniowym z dwoma strzałkami w dół.

Wyświetlanie danych zostało zmienione na „Wartość by day”

Klikając w pola górnego wykresu, będziemy obserwować zmieniający się dolny wykres przedstawiający wartości przedaży.

Umieszczamy kolejny element naszego raportu. Tym razem będzie to mapa na której przedstawimy lokalizację naszej firmy i jej obroty w postaci okręgu, którego wielkość będzie zależna od wartości sprzedaży. Klikamy w globus w zakładce VISUALIZATION.

Analogicznie jak w poprzednich wykresach umieszczamy odpowiednie kolumny w okienkach właściwości wykresu

Pojawi się mapa Polski z kręgami o różnej wielkości.

I oczywiście znowu możemy klikając w odpowiednie punkty na wykresach lub mapie wyświetlać i analizować dane dotyczące sprzedaży.

Dołożymy jeszcze dwa pola (kafelki) naszego raportu.

I znowu przeciągamy odpowiednie kolumny do pól właściwości wykresu.

W wykresie wyświetliła się nam pierwsza nazwa sprzętu z kolumny, jednak my chcielibyśmy aby wyświetlana była ilość danego sprzętu a nie jego nazwa.

Dlatego też musimy zmienić typ pola z First na Count (licznik)

Zmienialiśmy na „Count of SprzetIT” i wyświetliła się globalna ilość sprzedanego sprzętu (tyle jest wierszy w tabeli ITSales)

testujemy czy to się zmienia klikając w różne miejsca na wykresach. Dane się zmieniają.

Poniżej umieścimy jeszcze jeden ostatni kafelek (ten sam typ co poprzedni). Przenosimy odpowiednie kolumny do pól właściwości kafelka.

Wyświetlona jest wartość sprzedanych wszystkich sprzętów. I znowu możemy sprawdzić czy wartość się zmienia podczas wybierania równych punktów na wykresach.

Zmienimy teraz sposób wyświetlania danych w tym kafelku (żeby pozbyć się tych K na końcu wartości).

Opcje tego dotyczące znajdziemy klikając w „wałek malarski” i wybierając opcję Display Units

Zmieniamy AUTO na NONE.

Dane są wyświetlane prawidłowo tzn tak jak chcieliśmy 😉

Raport mamy ukończony.

Zaszufladkowano do kategorii PowerBI | Dodaj komentarz

PowerBI Dekstop (cz.2) – Przykładowa baza dla pierwszego raportu

Dziś pokażę jak wykonać raport z bazy zawierającej dane dotyczące sprzedaży firmy informatycznej posiadającej kilka oddziałów w kilku polskich miastach. Nie mamy takiej bazy ale w kilku prostych krokach ją stworzymy i napełnimy ją przykładowymi danymi.

Najpierw tworzymy bazę danych i tabele na nasze dane (na dane dotyczące sprzedaży i lokalizacji).

CREATE DATABASE PowerDB

CREATE TABLE [dbo].[ITSales](
[Datownik] date NULL,
[Firma] varchar(10) NULL,
[Wartosc] decimal(4,0) NULL,
[Sprzet_IT] varchar(10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ITLocation](
[Firma] varchar(10) NULL,
[Lokalizacja] varchar(40) NULL
) ON [PRIMARY]
GO

Teraz napełnimy tabele danymi. Tabela będzie zawierała 500 losowych wpisów z listy sześciu imion właścicieli oraz z czterech kategorii sprzedawanego sprzętu plus dodatkowo losowe daty sprzedaży z ostatnich 9 dni.


BEGIN
DECLARE @firma INT = ROUND(rand()*5,0)+1
DECLARE @firma_TXT varchar (10)
DECLARE @sprzet INT = ROUND(rand()*3,0)+1
DECLARE @sprzet_TXT varchar (10)

if (@firma = 6) set @firma_txt = 'Adam';
if (@firma = 5) set @firma_txt = 'Piotr' ;
if (@firma = 4) set @firma_txt = 'Tomasz';
if (@firma = 3) set @firma_txt = 'Andrzej';
if (@firma = 2) set @firma_txt = 'Marian';
if (@firma = 1) set @firma_txt = 'Waldemar';

if (@sprzet = 4) set @sprzet_TXT = 'komputery';
if (@sprzet = 3) set @sprzet_TXT = 'laptopy';
if (@sprzet = 2) set @sprzet_TXT = 'drukarki';
if (@sprzet = 1) set @sprzet_TXT = 'tablety';

INSERT INTO [dbo].[ITSales](
[Datownik],[Firma],[Wartosc],[Sprzet_IT]
)
VALUES
(
GETDATE()-ROUND(rand()*9,0) , @firma_TXT , ROUND(rand()*1000,0)+1 , @sprzet_TXT
)
END
GO 500

Sprawdzamy czy tabela nie zawiera NULL-i

select COUNT(*) from [dbo].[ITSales] where [Firma]=NULL or [Wartosc]=NULL or [Sprzet_IT]=NULL

Oraz druga tabela zawierająca lokalizacje firm

INSERT INTO [dbo].[ITLocation](
[Firma],[Lokalizacja]
)
VALUES

('Adam','Szczecin, Mickiewicza 12'),
('Piotr','Poznań, Mickiewicza 14'),
('Tomasz','Warszawa, Mickiewicza 32'),
('Andrzej','Gdańsk, Mickiewicza 3'),
('Marian','Kraków, Mickiewicza 23'),
('Waldemar','Lublin, Mickiewicza 7')
GO

Sprawdzamy poprawność zapisanych danych:

SELECT top(10) * FROM [PowerDB].[dbo].[ITSales]

Datownik   Firma      Wartosc                                 Sprzet_IT
---------- ---------- --------------------------------------- ----------
2022-01-24 Marian     752                                     drukarki
2022-01-27 Adam       409                                     drukarki
2022-01-23 Marian     883                                     drukarki
2022-01-27 Andrzej    232                                     laptopy
2022-01-26 Marian     549                                     komputery
2022-01-28 Waldemar   7                                       drukarki
2022-01-21 Marian     613                                     tablety
2022-01-26 Tomasz     510                                     tablety
2022-01-20 Piotr      523                                     komputery
2022-01-25 Piotr      495                                     drukarki

(10 rows affected)

SELECT * FROM [PowerDB].[dbo].[ITLocation]

Firma      Lokalizacja
---------- ----------------------------------------
Adam       Szczecin, Mickiewicza 12
Piotr      Poznań, Mickiewicza 14
Tomasz     Warszawa, Mickiewicza 32
Andrzej    Gdańsk, Mickiewicza 3
Marian     Kraków, Mickiewicza 23
Waldemar   Lublin, Mickiewicza 7

(6 rows affected)

Baza gotowa, więc możemy przystąpić do budowania naszego raportu.

Zaszufladkowano do kategorii Bez kategorii | Dodaj komentarz

SQLMaile w formacie HTML

Standardowo maile wysyłane w usłudze SQLMail są w formacie tekstowym. Czasami jednak potrzebne jest skorzystanie z formatu HTML, aby np wysłać tabelkę lub po prostu „pokolorować” naszego maila ;). I tym się dziś zajmiemy.
Ogólnie opisując należy w treści maila wysłać kod HTML. Poniższy skrypt właśnie to robi.

Na początek coś prostego. W mailu wyślemy trzy teksty różnego koloru i pisane różną czcionką:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile,
@recipients='micek1968@gmail.com',
@subject = 'Kolorowy Mail HTML',
@body = '<font color="green" face="arial"><i> Zielony tekst ARIAL <br /> </i></font>
<hr />
<font color="red" face="times"><i> Czerwony tekst TIMES <br /> </i></font>
<hr />
<font color="blue" face="courier"><i> Niebieski tekst COURIER <br /> </i></font>
',
@body_format = 'HTML' ;

W mailu otrzymamy

Teraz wyślemy coś bardziej skomplikowanego czyli wynik zapytania w formie prostej tabeli:

DECLARE @BodyText NVARCHAR(MAX);

SET @BodyText =
N’Lista pracowników w IT w roku 2021:<BR><BR>’ +
N'<table border=”1″ style=”width:30%;”>’ +
N'<tr>
<th style=”background-color:#FF0000″>Imię</th>
<th style=”background-color:#00FF00″>Nazwisko</th>’ +
CAST ( ( SELECT top(10) FirstName as 'td’,”,LastName as 'td’
from [AdventureWorks2012].[Person].[Person]
FOR XML PATH(’tr’), TYPE) AS NVARCHAR(MAX) ) +
N'</table>’ ;

exec msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile
, @recipients = 'micek1968@gmail.com’
, @subject = 'Raport za 2021 rok’
, @Body = @BodyText
, @body_format = 'HTML’;

 

W wyniku wykonania tego skryptu otrzymamy na maila:

Na zakończenie jeszcze mail w formie HTMLa z załącznikiem graficznym.

create procedure sendMail (@attachement nvarchar(maX))
as
begin

DECLARE @body NVARCHAR(MAX)

SET @body ='<font color="green" style="font-size:30px" face="arial"><i> Kot z miasta Łodzi... <br /> </i></font>'
SET @body = @body

EXEC msdb.dbo.sp_send_dbmail
@profile_name = SQLMail_profile,
@body = @body,
@body_format ='HTML',
@recipients='micek1968@gmail.com',
@subject = 'Mail z załącznikiem' ,
@file_attachments = @attachement
End

Uruchomienie procedury wysyłającej maila z załącznikiem:

exec sendMail 'C:\TEMP\kotek.jpg'

W mailu otrzymamy:

Usunięcie procedury

drop procedure sendMail

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Zastosowanie IDENTITY w tabelach

Aby wyjaśnić pojęcie Identity czyli autoinkrementacji w tabelach utworzymy tabele z kolumną ID zdefiniowaną jako IDENTITY oraz kolumną Name


CREATE TABLE [TESTOWA].[dbo].[IdentityTable1]
(
id INT IDENTITY,
name VARCHAR(20)
)

Commands completed successfully.

Zapisujemy do niej kilka wierszy nie podając wartości ID

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ADAM'),('PIOTR'),('ANDRZEJ'),('KRZYSZTOF'),('JERZY'),('MARIUSZ');
GO

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable1]

id          name
----------- --------------------
1           ADAM
2           PIOTR
3           ANDRZEJ
4           KRZYSZTOF
5           JERZY
6           MARIUSZ

(6 rows affected)

Jak widać w kolumnie ID wartości przyjmują kolejne liczby.

Jeśli byśmy chcieli podać liczbę ID w insercie zwróci nam błąd

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(id,name) VALUES (1,'ROBERT');
GO

Msg 544, Level 16, State 1, Line 35
Cannot insert explicit value for identity column in table 'IdentityTable1' 
when IDENTITY_INSERT is set to OFF.

Jest możliwość zapisu z wartością ID z użyciem opcji IDENTITY_INSERT ale o tym napiszę później.

Tworzymy drugą tabelę z kolumną ID (identity) podając wartość od jakiej ma zacząć liczyć ID (2) oraz co ile ma się zwiększać wartość ID (5).

CREATE TABLE IdentityTable2
(
id INT IDENTITY (2,5),
name VARCHAR(20)
)

Commands completed successfully.

Przenosimy dane z tabeli 1 do 2. Jako, że kolumna ID na ustawione Identity przenosimy tylko dane z kolumny Name

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
SELECT name from [TESTOWA].[dbo].[IdentityTable1]
GO

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]

id          name
----------- --------------------
2           ADAM
7           PIOTR
12          ANDRZEJ
17          KRZYSZTOF
22          JERZY
27          MARIUSZ
 
(6 rows affected)

Jak wyżej próba zapisania wiersza ze zdefiniowaną wartością ID będzie zwracało błąd.

Wartość @@IDENTITY zwraca nam ostatnią wartość Identity jaką użyliśmy w ostatnio używanej tabeli. Obecnie pokazuje nam wartość z tabeli IdentityTable2, ale jeśli wykonamy insert do tabeli IdentityTable1, będzie pokazywało wartość odpowiednią dla tej tabeli (6)

SELECT @@IDENTITY as 'IDENTITY'

IDENTITY
---------------------------------------
27

(1 row affected)

INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ROBERT');
GO

SELECT @@IDENTITY as 'IDENTITY'

IDENTITY
---------------------------------------
7

(1 row affected)

Możemy też wydać zapytanie:

SELECT
IDENT_CURRENT('IdentityTable1') as CurrentIdentTable1,
IDENT_CURRENT('IdentityTable2') as CurrentIdentTable2

CurrentIdentTable1                      CurrentIdentTable2
--------------------------------------- ---------------------------------------
6                                       32

(1 row affected)

SELECT SCOPE_IDENTITY() as 'IDENTITY';

IDENTITY
---------------------------------------
32
 
(1 row affected)

USE TETSOWA
DBCC CHECKIDENT([IdentityTable2])

Checking identity information: current identity value '32', current column value '32'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Aby zmienić wartość identity na pozycji "startową" 3 wykonujemy polecenie:
DBCC CHECKIDENT([IdentityTable2], RESEED, 3)

Checking identity information: current identity value '32'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Checking identity information: current identity value '3' czyli zgodnie z tym co zdefiniowaliśmy dla tabeli IdentityTable2 następna wartość będzie 8.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('ANNA'),('KATARZYNA'),('ZOFIA'),('MONIKA'),('TERESA'),('KRYSTYNA');

(6 rows affected)

SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA

(13 rows affected)

Teraz spróbujemy wymusić zapis do tabeli z pominięciem Identity. Wykorzystamy do tego opcję IDENTITY_INSERT

SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] ON;
GO
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(id,name) VALUES (10,'BORYS');
GO
SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] OFF;
GO

Commands completed successfully.
(1 row affected)
Commands completed successfully.

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
10          BORYS
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA

(14 rows affected)

Po wyłączeniu (OFF) parametru IDENTITY_INSERT kolejne ID będą przyjmowały wartości zgodnie z definicją Indentity.

INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('TAMARA');

id          name
----------- --------------------
2           ADAM
7           PIOTR
8           ANNA
10          BORYS
12          ANDRZEJ
13          KATARZYNA
17          KRZYSZTOF
18          ZOFIA
22          JERZY
23          MONIKA
27          MARIUSZ
28          TERESA
32          ROBERT
33          KRYSTYNA
38          TAMARA

(15 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz