24. února 2016

Temporal tables

Co jsou temporal tables? Jedná se o novinku, která bude dostupná v SQL Serveru 2016 a je také k dispozici v Azure SQL Database jako Preview funkce. O co u temporal tables jde? Jednoduše o verzování dat. Umožňují vytáhnout, jak data vypadaly ve specifickém okamžiku v čase.
Možné aplikace
Verzování veškerých datových změn v tabulce
Správa SCD (Slowly changing dimension) pro podporu rozhodování
Možnost vrátit se k předchozí verzi záznamu/ů při nechtěné změně
Počítání trendů
Z pohledu BI bych vypíchnul první dva body, audit veškerých změn a SCD vestavěným mechanismem bez nutnosti programovat vlastní logiku při vkládání. Tzn pokud chcete SCD 2 na tabulce, kde je temporal table zapnutá. Děláte jen update stávajících záznamů. O SCD se starají skřítci SeTo a Samo :)
Jak to funguje
Kažá temporal table obsahuje dva explicitně definované sloupce typu datetime2, které vy ale neplníte. Při tvorbě tabulky tvoříte referencovanou tabulku pro udržování historie pokud je záznam v temporal table upraven, nebo smazán. Tato tabulka je tvořena buď uživatelem, nebo si systém vytvoří tabulku defaultní.
Příklad
Tvorba temporal table
CREATE TABLE dbo.MY_CUSTOMERS
(
       [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
       [NameStyle] [dbo].[NameStyle] NOT NULL,
       [Title] [nvarchar](8) NULL,
       [FirstName] [dbo].[Name] NOT NULL,
       [MiddleName] [dbo].[Name] NULL,
       [LastName] [dbo].[Name] NOT NULL,
       [Suffix] [nvarchar](10) NULL,
       [CompanyName] [nvarchar](128) NULL,
       [SalesPerson] [nvarchar](256) NULL,
       [EmailAddress] [nvarchar](50) NULL,
       [Phone] [dbo].[Phone] NULL,
       [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START,
       [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END,
       PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 ) 
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MY_CUSTOMERS_HISTORY));

Všimněte si elementů u sloupců validfrom a valito, které se budou starat o udržování historie. System versioning on a název history table se postará o zbytek. Dále jsem naplnil data do tabulky z DB AdventurWorksLT, která je k dispozici v Azure SQL Database jako demo. Po nějaké době jsem změnil emailové adresy vybraných zákazníků. Pokud chci vytáhnout veškeré verze záznamů pro zákazníka 21, mohu to udělat následujícím dotazem
DECLARE @DATEFROM datetime = getdate()-1
DECLARE @DATETO datetime = getdate()
SELECT * FROM dbo.MY_CUSTOMERS
    FOR SYSTEM_TIME 
       FROM @DATEFROM TO @DATETO
WHERE CustomerID = 21
ORDER BY ValidFrom DESC;
For system time from to definuje časové okno ze kterého mě zajímají verze. Pokud mě zajímá poslední verze záznamu k datumu a času
DECLARE @DATETO datetime = getdate()
SELECT * FROM dbo.MY_CUSTOMERS
    FOR SYSTEM_TIME AS OF  @DATETO
WHERE CustomerID = 21
ORDER BY ValidFrom DESC;

Závěr

Temporal tables jsou zajímavá funkce, která zjednodušuje práci s historizací záznamů. Pokud by Vás zajímalo více informací, můžete kouknout na oficiální dokumentaci zde https://msdn.microsoft.com/en-IN/library/dn935015.aspx

12. února 2016

Azure SQL Data Warehouse

Dnešní článek bude trochu z jiného soudku. Už od SQL Server 2008 R2 Microsoft nabízí svoji appliance pro masivní paralelní zpracování dat (MPP Masive Parallel Processing). Řešení pro ty největší datové sklady se jmenuje Parallel Data Warehouse (PDW). Pár let zpátky začal Microsoft říkat řešení Analalytics Platform System, protože k PDW přidal i možnost dotazovat se na nestrukturovaná data z Hadoop regionu díky Polybase... Pokud jsem Vás nezabil tímto úvdoníkem, tak už nevím čím. PDW  není dvakrát cenově dostupná hračka. V České republice vím o jediné společnosti, která PDW má k dispozici (a to hned dvakrát). Touto společností je můj zaměstnavatel Dixons Carphone plc. (v Česku zastoupena Dixons Retails SSC s.r.o.). PDW se budu věnovat v samostatném článku někdy v budoucnu. Jako lektor jsem se bavil se spoustou firem a poslouchal, jak řeší jestli koupit BI edici, nebo spíš skončí na Standardu protože se licencuje na jádra. Enterprise edice by byla krásná, ale že je moc drahá a budou muset vystačit se Standardem i když funkce budou chybět.
No a teď si představte rack (nebo třeba 6ti rack), do kterého narvete několik Enterprise SQL Serverů s redundancí, control nodem a že to spolu všechno mluví a parallelně zpracovává dotazy. Megaželezo + Megalicence = Megapálka :) 1,340 milionu dolarů za full rack jednorázová pořizovací cena. A kdo z Vás tohle má... :) V porovnání s konkurencí ale stále vychází PDW od Microsoftu levně viz tabulka na straně 4 (bez Hadoop regionu) http://www.valueprism.com/resources/whitepaper/APS%20TCO%20Whitepaper%20-%20FINAL.pdf
A pak zjednodušeně řečeno inženýři z Microsoftu PDW zvirtualizovali a vznikl Azure SQL Data Warehouse. Tímto se dostupnost MPP platformy výrazně posouvá blíž běžným firmám. Nastalo mi ideální období 2 dny do expirace kreditu z MSDN předplatného, tak se jde testovat.
Tvorba DW
V novém Azure Portálu – New – Data + Storage – SQL Data Warehouse
Povinné údaje
Název databáze. Posuvník týkající se výkonnosti podobný toho co je dostupný u Azure SQL Database. Výběr serveru, pokud zatím žádný vytvořený nemáte, vytvoří se nový. Resource group a subscription, ze kterého budete platit. Mezi databázemi je možno vybrat ukázkovou a koho to tu máme? Ejhle Adventure Works. To už jsem někde viděl. Jen doufám, že největší faktová tabulka bude mít víc jak 60 000 řádků. Tvorba databáze trvá několik málo minut.

Po tvorbě databáze se zobrazí následující obrazovka.
Monitor aktivity co se děje uprostřed. Důležitá nastavení včetně connection stringu. Nahoře ovládací lišta a po pravici podpora. Chcete-li se připojit z klientských nástrojů, můžete použít buď Visual Studio, nebo PowerBI. Management studio se připojí, ale neumí číst správně metadata.
Připojuji se přes visual Studio a koukám na velikost faktové tabulky FactResellerSales. Nezklamala a má svých klasických 60855 záznamů. Tady výkon neotestuji, takže si data trochu namnožím. Faktová tabulka obsahuje data po dnech, tak si je rozpočítám na minuty. Nechce se mi s tím moc trápit, tak to čísla rozpočítám na minuty rovnoměrně.
Pokud chci psát dotaz proti Azure SQL Data Warehouse musím zvolit správnou šablonu a sice .dsql stejně jako v případě PDW. Jedná se o zkratku Distributed SQL. V Azure SQL Data Warehouse máme 2 typy tabulek, replikované a distribuované. Replikované tabulky obsahují stejná data na každém uzlu. Hodí se například pro dimenze. Distribuované tabulky jsou naopak rozbity na několik serverů podle hashovacího klíče. Hodí se pro velké faktové tabulky. Na rozdíl od klasického partitioningu se hodí distribuovat tabulku podle klíče, který umožní rozprostření na výpočetní nody rovnoměrně. Nejde nám o to, abychom eliminovaly nehodící se partitions při čtení, ale naopak chceme rovnoměrně číst na několika uzlech zaráz.
Z výkonového pohledu je výhodnější než vytvořit tabulku a do ní provést insert udělat to příkazem v principu podobným SELECT * INTO tabulka FROM DATA až na to, že SELECT INTO zde nefunguje. Používá se zde syntaxe tzv. CTAS (CREATE TABLE AS SELECT). Dokonce je v mnoha případech rychlejší tabulku dropnout a znovu vytvořit příkazem CTAS, než do ní dělat insert/update.
Nová faktová tabulka z původní Fact Reseller Sales by se dala vytvořit příkazem
create table fact_reseller_sales
with (Distribution = hash(productKey))
as
with dim_hour as (
select top 24 hour_key = row_number() over(order by name)-1 from sys.columns
),
dim_minute as (
select top 60 minute_key = row_number() over(order by name)-1 from sys.columns
)
SELECT
f.*
,time = cast(cast(h.hour_key as varchar(2)) + ':' + cast(m.minute_key as varchar(2)) as time)
,Sales = salesamount /24
FROM factresellersales f
CROSS JOIN dim_hour h
CROSS JOIN dim_minute m
Dalo by se to napsat i krásněji? Možná ano, ale je půl desáté večer a já jsem si řekl, že prostě ne :)
Všimněte si několika specifik. Zaprvé Create Table As Select.
Zadruhé distribution = hash(Hashovací klíč). Pro replikovanou tabulku byste použili
create table fact_reseller_sales
with (Distribution = round_robin )
as
Za třetí na klasickém SQL Serveru bych použil nejspíš rekurzivní CTE (Common Table Expression), zde nemohu, protože mi to král bramborových lidí nedovolí (DSQL).
Protože jsem škrt a šetřil jsem na posuvníku (článek dopisuji po zahájení nového účtovacího měsíce) s výkonem (SCALE 100 DWU), tvorba tabulky trvala 7:21  a obsahuje 87 631 200 řádků. To bude na pokusy s výkonem trochu lepší.
To byl zápis, co čtení? První co zkusím je počet řádků a celková suma.
SELECT
Rows_count = count(*)
,Sales = sum(f.sales)
FROM Fact_reseller_sales f
Výstup získávám za 16 vteřin. Zkouším dotaz, kde využiji hashovací klíč.
SELECT
c.englishproductcategoryname
,s.englishproductsubcategoryname
,p.modelname
,p.englishproductname
,Rows_count = count(*)
,Sales = sum(f.sales)
FROM Fact_reseller_sales f
JOIN dimproduct p
ON p.productkey = f.productkey
JOIN dimproductsubcategory s
ON p.productsubcategorykey = s.productsubcategorykey
JOIN dimproductcategory c
ON c.productcategorykey = s.productcategorykey
group by
c.englishproductcategoryname
,s.englishproductsubcategoryname
,p.modelname
,p.englishproductname
Dotaz pouštím opakovaně a měním DTU, abych porovnal cenu a výkon :) DTU je zkratka Database Transaction Unit, která nám vypovídá o poměru, kolik transakcí za vteřinu by měl server zvládnout. Přesná definice DTU v angličtině https://azure.microsoft.com/en-gb/documentation/articles/sql-database-service-tiers/#understanding-dtus  Škálování trvá v řádu jednotek minut. Výkonové srovnání můžete shlédnout v přehledné tabulce. Zkušenost je taková, že po změně škálování, případně stopu/startu databáze je Azure SQL Data Warehouse nějaký líný. Když běží nějakou dobu, je to diametrálně lepší. Proto dva časy v tabulce
DWU
Eur/h
Čas 1.
Čas 2.
100
0.66

1:15
200
1.32
4:28
0:28
500
3.31
5:15
0:02
1000
6.61
2:40
0:02

Závěr:
Azure SQL Data Warehouse přináší možnosti MPP masám díky Azure. Za zásadní výhodu považuji možnost Warehouse pausnout a tím pádem neplatit za výkon. Posuvník se škálováním je také super, potřebujete masivní výkon pro zpracování dat ve špičce? Ohulíte DTU posuvník, až systém není využíván, stáhnete na DTU 100 nebo úplně zastavíte. Tohle s normálním SQL Serverem a koneckonců ani PDW dost dobře nejde. Tam většinou musíte škálovat hardware podle špičkového výkonu, kterého chcete dosáhnout.

3. února 2016

Power BI – Veřejné sdílení reportů

Máte nějaká data, která byste chtěli veřejně sdílet s obchodními partnery, nebo potenciálními zákazníky? Chtěli byste umístit interaktivní report na webovou stránku? Je to tak, Power BI reporty jdou sdílet veřejně na webu. Nedávno na různých sociálních sítích koloval dotazník, jak bychom si sdílení Power BI reportů na venek představovali a dnes jsme se dočkali první Preview verze.
Jak to funguje
V Power BI portálu u reportu v rozbalovacím menu vyberete menu File – Publish to web
Zvolíte create Embed code, poté publish
Vygeneruje se kód, který můžete vložit do mailu a kód pro umístění v rámci webové stránky včetně volby velikosti.
Link do mailu se nezobrazuje jako příloha, ale po kliknutí se bez nutnosti přihlásit do Power BI portálu otevře v prohlížeči. Zde se můžete podívat na můj 30 minutový výtvor nad kurzovním lístkem ČNB. Škoda, že zdroj Web přímo neumožňuje pravidelné aktualizace :(
Iframe pro zobrazení na webové stránce se zobrazí jako součást webové stránky, takže pokud mi to blogger dovolí, tady to je :)
Kód
<iframe width="800" height="600" src="https://app.powerbi.com/view?r=eyJrIjoiMzM3ODFhMWYtMjRkYi00ZDk3LWJlMDktYTBkNWI0YjYzMzBlIiwidCI6ImIyNWQxMTNlLTM3MTctNGEwZi1iMjAxLTFjYjlhMTMxOWEyNyIsImMiOjh9" frameborder="0"></iframe>

Report

Závěr

Ať pracujete pro komerční organizaci, státní správu, jste nezisková organizace, nebo jen píšete blog jako já. Vystavit report ke shlédnutí masám se může hodit. Já osobně s touto funkcí mám velké plány.