Power BI novinky a výhled do dubna 2016

Minulý týden se konal v Seattlu Microsoft Data Insights Summit, kde se sešli lidé z produktových týmů Power BI a Excelu s komunitou a zákazníky. Kromě prezentací co bylo, bylo učiněno i několik oznámení, co se chystá v nejbližších měsících. Některé oznámené novinky jsou zcela zásadní, no a tak tu máme další článek :)

Celý záznam ze summitu byl zveřejněn a je volně ke shlédnutí na Youtube https://www.youtube.com/user/mspowerbi/playlists?sort=dd&shelf_id=7&view=50 I Dixons Carphone měl své zastoupení. Mezi příběhy zákazníků prezentovala naše šéfová Lara Swinburn mimo jiné náš Power BI dashboard ze špičky na konci minulého roku. Ten dashboard je takové moje děťátko, takže pokud máte chuť se podívat koukněte na následující video a přetočte na 26:40 https://www.youtube.com/watch?v=vmeUXZ1wTqY&index=4&list=PL1N57mwBHtN26j4PGNQ91VCxHAxkl0_C5

Výběr novinek z měsíců minulých

(některé jsou venku už poměrně dlouho a připadá mi, jako by tu byly odjakživa, těžko uvěřit, že ještě tři měsíce zpátky se jednalo o novinku). Poslední můj update shrnoval novinky po listopad, takže od listopadu dál. Pokud máte přehled co se dělo v minulých měísích, klidně přetočte na část „výhled co se chystá dál“

Power BI Desktop

Vylepšené možnosti formátování

• formátování popisků po řadách
• změna počtu desetinných míst ve vizualizacích
• změna velikosti textu ve vizualizacích
• styly a štěteček na kopírování formátu

Konektivita

• podpora pro víceúrovňové hierarchie s drilováním v multidimenzionálních SSAS
• možnost zadat data ručně (hodí se pro mapovací tabulky, případně pro citliviostní what-if analýzu)
• Konektor na SAP BW

Vizualizace

• Podpora R vizualizací
• Možnost přidat referenční linky do grafů (například plán)
• KPI vizualizace

Další

• export dat použitých ve vizualizaci do CSV
• možnost definovat hierarchie

Mobilní Power BI

• Integrace SQL Serveru 2016 a mobilní aplikace pro iOS
• Čtečka QR kódů pro Android
• Kešování dat a možnost prohlížet reporty i když jste offline
• Mobilní aplikace pro Windows mobile 10
• Otáčení rozložení pro Android a Windows mobile
• Procházení reportu na mobilu nejen náhled komponent z dashboardu

Service

• tisk dashboardů a reportů
• export dat, ze kterých je vykreslena vizualizace
• možnost publikovat reporty z SQL Serveru 2016 do dashboardů
• sdílení s uživateli mimo organizaci (hodí se pro sdílení obsahu s partnerskou firmou, externisty atd)
• v administraci zakázání sdílení reportů na web a s externími uživateli
• webový obsah a video widget v dashboardech
• trendove cary v Q&A

Vlastní vizualizace

• poměrně hodně vizualizací pro práci s projektovými daty (asi si zaslouží samostatný článek)
• celkově je těch vizualizací hodně, koukněte na aktuální seznam sem https://app.powerbi.com/visuals/

Gateway

POZOR!!! Podpora SSAS kontektoru končí 31.3.2016, používejte místo něj Power BI Enterprise Gateway o které jsem psal zde http://www.neoral.cz/2016/03/power-bi-enterprise-gateway-direct.html

Výhled co se chystá dál

co bylo oznámeno na Data Insights Summitu? Některé zmiňované novinky jsou již v pohybu

V prvé řadě Excel a Power BI jsou více než nejlepší kamarádi pokud jste to nevěděli

Co to pro nás znamená? Velmi úzkou integraci Excel & Power BI

Březen 2016

Analýza datasetu z Power BI v Excelu

Už nyní najdete u datasetu možnost analyzovat v Excelu.

Po kliknutí se vám stáhne .odc soubor s připojením. Když jej otevřete, spustí se Excel, vyzve k autentizaci do Power BI a můžete svůj dataset procházet přes kontingenční tabulku. Jen to chce, aby dataset měl nadefinovaná měřítka. Číselný sloupec sám o sobě nestačí, nejde umístit do oblasti dat, pouze na řádky/sloupce/filtr. Máme tu tedy odlehčené SSAS as service :) Super skvělé

Přišpendlení komponent z Excelu

je k dispozici již nějakou dobu (psal jsem http://www.neoral.cz/2016/01/power-bi-doplnek-do-excelu.html), ale už se funkce dostala z fáze preview.

Power BI aplikace na Apple Watch

Je pro vás display mobilního telefonu příliš velký, nebo jste líní tahat mobil z kapsy? Koukejte jak se byznysu daří na hodinkách :)

Zabezpečení na úrovni rolí

Bude se dát definovat jaká data uživatel uvidí v reportu pomocí rolí včetně omezení na úrovni řádků. Doposud všichni uživatelé viděli v reportu stejná data, pokud se nejednalo o živé připojení přes Enterprise Gateway. V době psaní článku tato funkce ještě není ve webovém rozhraní dostupná.

Monitorování používání

Pro administrátory možnost sledovat využití. Nejeden BI vývojář si kladl otázku. Ten člověk co nejvíc křičel, že potřebuje rychle report, kolikrát se na něj podíval? Bude možno, konečně :)

Duben 2016

Power BI Desktop

• trendové čáry
• drilování v tabulkách
• formátování dat v tabulce včetně podmíněného formátu

dále se dočkáme možnosti klást otázky a snad dostávat správné odpovědi i při použití živého připojení do zdroje :)

Závěr

Na summitu bylo oznámeno několik zcela zásadních novinek. Jde vidět, že produktový tým poslouchá komunitu i firemní zákazníky. I díky tomu za krátkou dobu života si získala služba přes 5 milionů předplatitelů. Hlas uživatelů říká, chceme hlubší interakci s Excelem a hlas byl vyslyšen. Můžeme analyzovat datasety z Power BI v Excelu. Hlas říká, chceme lepší kontrolu nad daty, která uživatelé uvidí v reportu. A přichází zabezpečení pomocí rolí včetně filtrace na úrovni řádků. Jen tak dál Power BI jen tak dál :) Pokud by vás některé novinky zajímaly, umíte anglicky a chcete se podívat na dema „jak to asi bude vypadat“, doporučuji shlédnout keynote ze summitu https://www.youtube.com/watch?v=jH7BVpVMsEw&t=2085s

Reporting Services - Branding

Sotva jsem si nainstaloval a nakonfiguroval RC0, Microsoft vydal po cca 14 dnech RC1. Release Candidate (RC) 1 přináší další kvantum nových funkcí a to nejen v reporting services. SSRS jsou ale moje oblíbená technologie, která novou krev hodně potřebovala. V minulosti jsem hodněkrát v různých variacích slyšel otázky a poznámky:

„Mě se ten portál nelíbí. Počkejte, to se s tím vzhledem nedá nic dělat? Vypadá to škaredě. Potřebovali bychom upravit barvy do firemní šablony, upravit popisky a přidat logo, aby to vypadalo jako naše stránka... Naši uživatelé nerozumí pořádně anglicky, chtělo by to portál v češtině...“

Kromě toho, že nový portál kompletně nahradil portál starý, jsme se dočkali možnosti upravit vzhled, aby stánka vypadala jako „Naše stránka“.

V menu nového portálu se můžeme proklikat na záložku branding

Před

Je možno nasadit balíček s vaší značkou. Jak má takový balíček vypadat?

Jedná se o zip soubor skládající se ze tří souborů

Popis barev jako .JSON, logo a xml popis metadat. Názvy souborů jsou v současné verzi povinné. Nový SQL Server stáhnete zde http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016 ukázkový „brand package“ ve stylu Xbox zde https://msdnshared.blob.core.windows.net/media/2016/03/Xbox.zip a šablonu https://msdnshared.blob.core.windows.net/media/2016/03/Multicolor-example-brand.zip

Zip obsahující tyto tři soubory stačí vypublikovat a je hotovo

Po

Co je uvnitř branding souborů?

logo.png – tak tohle si ani nezaslouží komentář

metadata.xml

XML soubor, který se odkazuje na logo a na barvy.

<?xml version="1.0" encoding="utf-8"?>

<SystemResourcePackage xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/systemresourcepackagemetadata"

 type="UniversalBrand"

 version="2.0.2"

 name="Dixons"

 >

 <Contents>

   <Item key="colors" path="colors.json" />

   <Item key="logo" path="logo.png" />

 </Contents>

</SystemResourcePackage>

colors.json

JSON soubor popisující barvy. Zde se děje nejvíce práce :) Soubor s barvami by se dal rozdělit na dvě velké části. Barvy portálu a barvy vizualizací mobilních reportů. Barvy jsou v šestimístném formátu s předcházejícím symbolem #.

Závěr

Branding je zcela zásadní novinkou Reporting Services 2016. To aby stránka vypadala jako Vaše stránka už není výsadou SharePoint integrace, ale postačí nativní mód. Otazníkem ve vzduchu zůstává lokalizace do češtiny. O té jsem se zatím nic nedočetl. Přepnutí regionu a jazyka na serveru nezabralo.

A protože RC SQL nejde jednoduše upgradovat, tak nezbývá než odinstalovat a nainstalovat novou verzi. Opět píši o Reporting Services, protože tato technologie je mi velmi blízká a novou krev do žil velmi potřebovala. Ne že by veškeré zajímavé novinky v SQL Serveru 2016  byly pouze v Reporting Services. Ani zdaleka, je toho daleko víc. Je to dobré, je to funkční, je to testováno na lidech/klientech (ještěže máme Azure).

Otázka:

Kolikrát jsem to jen slyšel?

Doplňující poznámky a otázky ke starším verzím SSRS: Mě se ten portál nelíbí. Počkejte, to se s tím vzhledem nedá nic dělat? Vypadá to škaredě. Potřebovali bychom upravit barvy do firemní šablony, upravit popisky a přidat logo, aby to vypadalo jako naše stránka... Naši uživatelé nerozumí pořádně anglicky, chtělo by to portál v češtině...

Odpověď:

Hodněkrát jsem to slyšel všechno :)

Nastavení dalších očekávání:

Lokalizace do češtiny zatím nenalezena (to ale neznamená, že neexistuje, nebo nebude existovat). Nově lze portál obarvit do korporátních barev, přidat logo

Power BI Enterprise Gateway a Direct Query proti SSAS

Nějakou dobu zpátky jsem psal o Power BI konektorech, které slouží k přístupu ke zdrojům uvnitř organizace. Článek najdete zde: http://www.neoral.cz/2015/10/power-bi-konektory-na-on-premise-zdroje.html V této oblasti ale došlo ke změnám. Personal Gateway zůstává, ale od Analysis services konektoru se postupně opouští. Už dnes se dá říci, že Analysis services konektor neumí oproti Enterprise gateway nic navíc. Naopak Enterprise Gateway toho umí výrazně více, než SSAS konektor. V dnešním blogu bych rád shrnul jak na který konektor s hlavním zaměřením na novou enterprise gateway (pokud se dá říkat v Power BI světě nová nástroji, který je k dispozici více jak měsíc :)). A také se podělím o své praktické zkušenosti

Analysis services konektor

Pokud jej máte nainstalovaný ve firmě, můžete jej používat dál, ale neztrácejte s tímto konektorem čas pro nový vývoj. Enterprise Gateway nahrazuji a rozšiřuje možnosti tohoto konektoru.

Power BI Personal Gateway

První otázka která mě napadla, když jsem viděl tento název poprvé byla: Proč personal? Není to snad určeno pro podnikové použití? Po pár měsících používání asi už znám odpověď. Personal gateway můžete nainstalovat na jakýkoliv počítač, klidně i soukromý laptop a umožňuje aktualizovat vaše data formou importu. Neumožňuje živé připojení. Data jdou aktualizovat max 8 krát denně a nelze se stoprocentně spolehnout na časy kdy k aktualizaci dojde (viz článek zkušenost s praktickým nasazením http://www.neoral.cz/2015/11/power-bi-zkusenost-z-praktickeho.html) Importem dat do Power BI datového modelu přijdeme o několik věcí. Jednak možnost řídit práva na úrovni databáze. To je způsobeno tím, že se vše plní pod stejným servisním účtem. Dále při importu ztratíte interaktivitu výpočtu. Například pokud máte v kostce ze které import provádíte meziroční srovnání v procentech. Tohle srovnání se vyhodnotí na detailní řádkové úrovni vašeho datasetu. Na vyšších úrovních si jej musíte znovu spočítat. Power BI by vám sesumovalo procenta (a to není dobré). Musíte tedy replikovat stejnou logiku na úrovni reportu, i když už je logika vyřešena v podkladovém zdroji.

Power BI Enterprise Gateway

Je, jak název napovídá, určena pro enterprise nasazení. Tedy centrální nasazení a správu datových zdrojů a možnost sdílet jednu gateway pro více uživatelů. Umožňuje jak živé připojení, tak import. Živé připojení je dostupné pro následující zdroje

·        SQL Server a PDW

·        Analysis services

·        SAP Hana

·        Oracle

·        Teradata

Aktualizace formou importu

·        SQL Server

·        Analysis Services

·        SAP HANA

·        File

·        Folder

·        Oracle

·        Teradata

·        SharePoint list (on-premises)

Import přes Enterprise gateway jsem zatím nezkoušel. Z grafického uživatelského rozhraní je vidět, že 8 aktualizací/den zůstává a navíc bych přišel o dvě zásadní věci zmíněné výše. Zabezpečení na straně databáze a logiku vytvořenou v databázi.

Pro naše produkční nasazení se nabízí kombinace multidimenzionálních SSAS a přímého připojení. V kostce je vytvořen poměrně bohatý datový model s časovými kalkulacemi, výpočty variancí proti budgetu a další. Stejně tak je implementováno dynamické zabezpečení (pokud vám tento pojem nic neříká http://www.neoral.cz/2016/01/dynamicke-zabezpeceni-ssas-s-pouzitim.html), které omezuje uživatelům ke členům produktové hierarchie.

Otázka zněla, bude fungovat přímé připojení přes Power BI v cloudu do naší on-premises kostky a hlavně bude fungovat i zabezpečení členů v produktové dimenzi? Bude Enterprise gateway schopna předat identitu uživatele do on premises kostky?

Enterprise gateway běží pod servisním účtem, který musí být administrátorm SSAS instance ke které se chcete připojit. Identitu uživatele předává pomocí vlastnosti EffectiveUserName ve vlastnostech připojení. A hlavně můžu konečně s klidným srdcem říct. Funguje to :) Testováno na pc i na mobilu. Uživatelé omezení produktovou hierarchií vidí jen relevantní data.

Narazil jsem ale také na jedno nepříjemné omezení, které se Vám nebude líbit. Při konzultaci s jedním klientem, jsme se snažili připojit z Power BI desktopu přímým připojením na SSAS. Bez úspěchu. Problém byl ve standardní edici SQL Serveru :( Kdo jste na standardce, polkněte nadávku, poté slzu, smůla. Důvod je ten, že Power BI posílá proti SSAS (i multidimenzionální instanci) DAX dotazy. DAX dotazy standardní edice z licenčních důvodů neumí. A to přesto, že multidimenzionální SSAS ve standardní edici SQL Serveru jsou.

Závěr

Enterprise gateway je pro produkční nasazení nejvhodnějším kandidátem. Umožňuje jak import dat, tak živé připojení proti vybraným zdrojům. Pokud se budete dotazovat přímým dotazem proti SSAS nepřijdete ani o logiku, ani možnost zabezpečit data. Bohužel živý dotaz proti SSAS nefunguje proti standardní edici SQL Serveru.

SQL Server 2016 RC0

V pondělí 7.3.2016 Microsoft přináší dvě velké novinky z oblasti SQL Serveru. Na první novink se připoutejte a ujistěte se, že za Vámi není hluboká propast, nebo tvrdá zeď až budete padat. Můžu? Snad jsem Vám dal dost obrňovacího času. Microsoft plánuje umožnit provozovat SQL Server na Linuxu. Píšu to 8.3. není tedy Apríl. Více informací v originále najdete zde https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/07/announcing-sql-server-on-linux/ případně zde http://blogs.microsoft.com/?p=67248?wt.mc_id=WW_CE_DM_OO_BLOG_NONE a o Preview si můžete zažádat zde https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx Jedná se zatím o oznámení, ještě to není hotové a upečené. Co je ale hotové a upečené je RC0 SQL Serveru, tedy Release Candidate. Což by mohlo i znamenat, že finální verze SQL Serveru je blízko (možná a docela pravděpodobně se něco nového dozvíme na virtuální akci Data Driven https://goo.gl/8z7Bv6

RC SQL Serveru 2016 můžete stáhnout zde https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016 Co přináší nového?

Business Intelligence

Analysis Services (SSAS)

Přináší možnost uspořádat atributy v Tabulárním modelu do složek. Ke všem modelům, jimž nastavíte úroveň kompatibility 2016, můžete přistupovat přes PowerShell a SSIS. Také přibývá nový Tabular Object Model, který si klade za cíl, jednodušší programový přístup k tabulárním modelům.

Reporting Services (SSRS)

Zde se jedná spíš o staronové funkce aneb staré funkce dostupné v novém portálu. Konkrétně se jedná o

·        správu subscriptions

·        správu sdílených datasetů

·        kešování a aktualizae dat

·        tvorba linkovaných reportů s jinými výchozími hodnotami parametrů

·        možnost přišpendlit (pin) grafické objekty do Power BI skupin dashboardů, SSRS automaticky vytvoří subscription pro aktualizaci dat

·        výběr měny pro tvorbu KPI a mobilních reportů

Integration Services (SSIS)

SSIS Projekty v Data Tools (SSDT) můžou mířit na 2012,2014,2016 verze SQL Serverů přepínáním jednoduchého kombo boxu. Přináší podporu pro nastavení vlastního logování na úrovni celého serveru. SSIS HDFS konektor podporuje ORC formát.

Master Data Services (MDS)

Nová správa Derived Hierarchy.

Database engine

Konfigurace na úrovni databáze

Podpora nastavení na úrovni databáze, které mohly být dříve nastaveny pouze na serveru. Například MAXDOP (maximum degree of parallelism), vyprazdňování procedurální keše, povolení/zakázání parameter sniffing na úrovni databáze a další.

Polybase

Podpora veřejných kontejnerů v blob úložišti Azure. Podpora poslední distribuce Cloudera CDH5.5 na Linuxu.

A další

Nová funkce vracející tabulku STRING_SPLIT, která rozparsuje text podle oddělovače.

Podpora importu a exportu dat kódovaných UTF 8

Zvýšený výkon u DML operací nad tabulkami, které mají velké množství cizích klíčů.

Pokud by Vás zajímalo, jak šel čas a co přibývalo ve verzích předchozích, můžete nahlédnout sem

https://msdn.microsoft.com/en-us/library/dn876712.aspx?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

Závěr

Když jsem poprvé zavítal na stránku RC0 shrnující nové funkce, zaujala mě fráze „This month update contains...“ Čili vypadá to, že nejen Power BI team do toho jde z ostra, ale i SQL Server team se snaží o měsíční aktualizace a nové funkce. To že aktuální verze je release candidate svědčí o tom, že finální verze se blíží. Věřím, že se velkého oznámení dočkáme ještě tento týden.

Mobilní reporty v SQL 2016 (CTP 3.3)

Na přednášce pro WUG jsem mimo jiné ukazoval novinky v SSRS 2016 (CTP 3.3). Novinek v klasických stránkovacích reportech jak je známe, toho moc nového není. Vizualizace jsou vesměs stejné až na dva nové typy grafů, možnost přišpendlit report do PowerBI.com (tohle se mi líbí), pozicování parametrů, nový HTML 5renderer a funkční tisk :) Nejzajímavější novinkou je, alespoň pro mě, právě mobilní repoting. Jedná se o integraci nástrojů Datazen do jádra SQL Server Reporting Services. Tyto novinky shrnu i v dnešním článku, berte ale v potaz, že se jedná o zatím verzi Community Technology Preview. Do ostré verze se ještě můžeme dočkat změn.

Mobilní reporting

Po instalaci v průběhu konfigurace SQL Serveru definujeme adresu report manageru, ktekrá je většinou ve tvaru http://server/reports Pro nasazení reportu používáme adresu webové služby SSRS, která bývá ve tvaru http://server/reportserver pokud se jedná o pojmenovanou instanci, za tuto adresu si ještě doplňte _instance. Pokud by se tedy můj server jmenoval Neoral instance SQL2016, adresa report manageru by byla http://neoral/reports_sql2016

Adresa report manageru je ta přes kterou chodíme na reporty koukat, adresa webové služby ta, přes kterou nasazujeme. Aby to bylo ještě komplikovanější. Přibyl nám nový portál, do kterého se dostanete z portálu odkazem v levém horním rohu

Nový portál vypadá takto

Pod šipkou směrem dolů v horním menu najdete možnosti pro stažení nové verze Report Builderu, Mobile Report Publisher a Power BI for mobile. Pro tvorbu nových reportů můžeme použít právě Mobile Report Publisher, který vypadá po nasázení par vizualizací do záložky layout takhle

Vpravo nahoře pod otazníkem je rozbalovací menu, kde můžeme zvolit typ zařízení a optimalizovat stejný report na různě velké obrazovky. Škála vizualizací je poměrně široká, ale hned napoprvé mi tam chyběl například sloupcový kategorický graf se dvěmi měřítky.

Na záložce data můžete použít buď simulovaná data, nebo vložit data vlastní. Jako zdroj se dá použít lokální Excel, nebo data z report serveru (sdílený dataset). Při prvním připojení na report server je potřeba zadat správnou adresu serveru, uživatele a heslo. Tohle mě na začátku zmátlo nejvíc. Snažil jsem se zadávat adresu report serveru, ale správná adresa je reports_preview jak vidíte na obrázku

Definice dat je specifická pro různé vizualizace. Náhled reportu by mohl vypadat například následovně v zobrazení Master (pc)

SSRS portál na mobilu v mobilním prohlížeči / Report v mobilním prohlížeči

Reporty z nového portálu bohužel nejdou přišpendlit do Power BI jako reporty klasické stránkovací. Tato funkce je dostupná z portálu starého. Mobilní reporty nejdou ve starém portálu otevřít.

Závěr

Moblní reporty vypadají docela pěkně. Integrace všech funkcí Datazenu ale ještě není kompletní. V průběhu tvorby jsem narazil i na různé chybky v designere, či nedodělávky. Jedná se ale o CTP, tak nezbývá než doufat, že v ostré verzi produktu bude vše fungovat tak jak má.

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

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.