Analýza nákupního košíku

Pokud jste nedočkaví článku, můžete přeskočit moji krátkou vsuvku po odmlce na sekci článek.
Vsuvka je o tom, proč byla odmlka a co bude s blogem dál :) Rozcestník souvisejících článků o Power BI najdete zde http://www.neoral.cz/2016/10/power-bi-rozcestnik.html

Vsuvka autora mimo téma (ale koneckonců je to blog:) )

Po delším výpadku v psaní blogu jsem zpátky a budu opět pravidelně přispívat. I s psaním blogu je to podobné,
jako se vším ostatním. Na „chvíli“ vypadnete a pak se těžko naskakuje. Psaní článků pravidelně je poměrně
časově náročné a když to člověk přičte k práci, rodině a tréninku triatlonu (letošním cílem je Moraviaman na
tratích železného muže v Otrokovicích a to si žádá přípravu). Něco musí jít trochu do pozadí. A trošku jsem se
méně věnoval komunitě. Ubylo článků na blogu a také přednášek bylo o něco méně, než 2016. Promítla se i
lehká motivační krize když několik měsíců čekáte na update, který nepřichází. Novoroční předsevzetí (ano
opravdu v únoru), je opět zamakat na doručování informací ze světa Business Inteligence. Většina článků bude
věnována Power BI. Nechci moc tříštit úsilí. Při rozhvoru na jedné komunitní akci mě inspirovala kamarádka
(tímto Magdu zdravím:) ) Jestli nechci zkusit dát na blog i číslo účtu, že jí v minulosti blog pomohl při hledání
informací. Že kdybych ho tam měl, možná by mi tam i něco poslala. Dlouho jsem o tom přemýšlel a říkám si
nakonec, proč ne. Pokud byste cítili silnou potřebu podpořit mé snahy o doručování novinek a praktických
zkušeností z BI světa. Zde je: 670100-2201291528/6210

Také ho přidám do kontaktní sekce. Nic to ale nemění na tom, že tento blog je nekomereční záležitost. Píši jej
dobrovolně ve volném čase bez nároku na odměnu, jako podporu české a slovenské BI komunity.

Dost bylo vsuvek, pojďme na samotný obsah článku.

Článek

O analýze nákupního košíku jsem měl přednášku na třech konferencích. SQL Saturday v Praze, G2B Teched v 
Brně a Show IT v Bratislavě. Stejné téma, možná lehce jiné pojetí a různá publika. Třikrát a dost. V Praze
vzniknul tento záznam https://www.youtube.com/watch?v=QkMJCW7cjEs takže, pokud Vám to uteklo, můžete
se podívat. Tento článek přináší doprovodné materiály a zdrojové kódy. Co bylo obsahem přednášky pro Ty z 
Vás, co jste ji neviděli.

Proč vlastně tohle téme

Pracuji v Dixons Carphone, což je společnost zabývající se retailem. Analýza toho, co se kupuje pohromadě je
tedy v rámci firmy celkem zajímavé téma. Když se mluví o analýze nákupního košíku běžně, většinou se člověku
vybaví technologie jako Machine Learning, Data mining. Což jsou skvělé přístupy a s tím spojené technologie,
ale ne každá společnost v týmu má lidi, kteří tyto metody a principy ovládají. Hlavní důvod přednášky byl, když
to řešíme my, možná i někdo jiný. Tak proč se nepodělit o svůj přístup a řešení.

Pár bodů k zamyšlení

Můžu a chci sledovat chování zákazníků (myšleno jako konkrétní člověk se jménem, příjmením adresou atd).
První věcí k zamyšlení je o jaké formě prodeje se vlastně bavíme. Online vs. obchod. U online nákupu jsme
schopni jednotlivé zákazníky rozlišit, protože nám tak nějak o sobě musí něco sdělit. Alespoň pokud si chce
zboží vyzvednout :). U nákupu v kamenném obchodě je to složitější. Tam pokud si člověk nevyřídí kartičku na
sbírání bodů zákazníka bez kamerového systému nerozlišíme.

Další faktor je četnost nákupů zákazníka. Asi bude rozdíl, jestli prodáváte potraviny. Nebo třeba elektroniku. U
online obchodů ze dvou nákupů velkých spotřebičů za rok asi hůř budete dělat závěry, než když u jednoho
zákazníka, který pravidelně kupuje deset rohlíků a dva lahváče.

Technologická perspektiva je o tom, jaké technologie máte k dispozici a jaké technologie ovládají vaši
kolegové/zaměstnanci. Neméně důležité je: kdo a jakou formou má získávat ze systému informace. Jestli se
bude jednat o stroj (našeptávač v eshopu), nebo člověk odpovědný za nacenění produktů/marketingové akce
atd.

Chci vůbec ukládat informace o zákazníkovi jako individualitě v BI systému?  V e-shopu se tomu nevyhnu, ale v 
datovém skladu by mi možná stačily informace o transakci. Jaké položky byly pořízeny na jedné transakci můžu
sledovat jak pro eshop, tak kamenný obchod. A je mi „jedno“ jestli se zákazník jmenuje Pepa, nebo Karel, nebo
třeba Unknown (v případě kamenného obchodu). To že v BI citlivé informace nechceme, na to může mít i vliv
nařízení GDPR.

Jaké byly požadavky

Výsledek řešení prezentovaný v tomto blogu je výstupem na základě požadavků byznysu. Ty byly následujcí

• interaktivní report přístupný uživatelům pro interaktivní analýzu
• sledování základních KPI pro primární produkt a produktů co se prodali s produktem primárním
• (attached). Metriky například Částka s daní, Částka bez daně, Marže, Počet kusů a další
• security model, omezení produktové hierarchie na úrovni primárního produktu (o row level security v 
• Power BI jsem psal zde http://www.neoral.cz/2016/04/power-bi-role-zabezpeceni.html)
• možnost měnit vstupní parametry v průběhu analýzy (časové období, kanál prodeje, parametry
• produktové hierarchie a další)
• čím dříve to bude, tím lépe

Jaké jsem zvolil technologie a proč

SQL Server jako vrstva která drží surová data, struktury bylo potřeba jen trochu uzpůsobit potřebám
analytického modelu.

Power BI moje volba číslo jedna, když je požadavek na interaktivní report

SSAS (SQL Server Analysis Services) díky svým analytickým možnostem a obavám z výkonnosti řešení a objemu
dat. Dalo by se použít jen Power BI, ale mohli bychom narazit na 1GB limit. SSAS umožňují také možnost dle
potřeby postavit SSRS report, nebo ad hoc připojení přes Excel.

Logický datový model

Schéma hvězda, kde se vše točí kolem prodejů. Možnost analyzovat přes Datum, primární produkt (Product),
související produkt (attached product) a další dimenze.

Dotaz pro získání informací o prodejích

Dochází zde k namnožení záznamů aby na transakci ke každému primary produktu byla vygenerována ještě
Attached část. Ale tak, aby nedošlo k ovlivnění celkového výsledku. (Total sales musí sedět na to co se skutečně
prodalo).

;WITH DATA_CTE AS

(

SELECT

f.CHAIN_KEY

,f.CHANNEL_KEY

,f.TRANSACTION_DATE_KEY

,f.COST_CENTRE_KEY

, f.PRODUCT_KEY

,f.TRANSACTION_KEY

,SUM(f.SALES_INC_VAT) as SALES_INC_VAT

,SUM(f.SALES_EX_VAT) as SALES_EX_VAT

,SUM(f.TAB_MARGIN) as TAB_MARGIN

,SUM(f.UNITS) as UNITS

FROM dbo.UVW_FACT_SALES f

GROUP BY

f.PRODUCT_KEY

,f.TRANSACTION_KEY

, f.CHAIN_KEY

,f.CHANNEL_KEY

,f.TRANSACTION_DATE_KEY

,f.COST_CENTRE_KEY

)

SELECT

/*header*/

p.CHAIN_KEY

,p.CHANNEL_KEY

,p.TRANSACTION_DATE_KEY

,p.COST_CENTRE_KEY

/*primary*/

,p.PRODUCT_KEY

,p.TRANSACTION_KEY

,CASE WHEN p.PRODUCT_KEY = a.PRODUCT_KEY THEN p.SALES_INC_VAT ELSE NULL END as SALES_INC_VAT

,CASE WHEN p.PRODUCT_KEY = a.PRODUCT_KEY THEN p.SALES_EX_VAT ELSE NULL END as SALES_EX_VAT

,CASE WHEN p.PRODUCT_KEY = a.PRODUCT_KEY THEN p.TAB_MARGIN ELSE NULL END as TAB_MARGIN

,CASE WHEN p.PRODUCT_KEY = a.PRODUCT_KEY THEN p.UNITS ELSE NULL END as UNITS

/*attached*/

,a.PRODUCT_KEY AS ATTACHED_PRODUCT_KEY

,CASE WHEN p.PRODUCT_KEY <> a.PRODUCT_KEY THEN a.SALES_INC_VAT ELSE NULL END as ATTACHED_SALES_INC_VAT

,CASE WHEN p.PRODUCT_KEY <> a.PRODUCT_KEY THEN a.SALES_EX_VAT ELSE NULL END as ATTACHED_SALES_EX_VAT

,CASE WHEN p.PRODUCT_KEY <> a.PRODUCT_KEY THEN a.TAB_MARGIN ELSE NULL END as ATTACHED_TAB_MARGIN

,CASE WHEN p.PRODUCT_KEY <> a.PRODUCT_KEY THEN a.UNITS ELSE NULL END as ATTACHED_UNITS

FROM DATA_CTE AS p /*primary*/

LEFT JOIN DATA_CTE AS a /*attached*/

 ON p.TRANSACTION_KEY = a.TRANSACTION_KEY

Trocha DAX výrazů v datovém modelu

Se měla právě postarat, aby byla zachována správnost výsledků. Dva příklady

Primary Sales Ex Vat:= CALCULATE(SUM(‘Sales’[SALES_EX_VAT]);all('Attached Product’))

Daný vzorec říká, že metrika má ignorovat dimenzi Attached Product.

Attached Sales Ex Vat:= CALCULATE(SUM(‘Sales’[ATTACHED_SALES_EX_VAT]);ALLSELECTED('Product');EXCEPT('Attached Product';'Product'))

Attached metriky mají ve výpočtu ignorovat dimenzi primární produkt. Attached je to, co vznikne po množinové
operaci Except mezi Attached Produkty a produkty primárními v daném kontextu.

Ukázky z hotového reportu uvidíte nejlépe na videu. Stejně jako popis případných nejasností. Těžko vložit
veškeré myšlenkové pochody a několik týdnů vývoje a ladění do pár řádků v blogu. Navíc tenhle je už dlouhý.

Materiály

Nicméně, pokud byste se rádi pokusili o reverse engineering, máte možnost. Řešení prezentované veřejně na
konferencích jsem připravil na demo databázi Contoso Retail DW. Ke stažení
zde: https://www.microsoft.com/en-us/download/details.aspx?id=18279

Contoso prodává elektroniku stejně jako Dixons, takže nejvhodnější kandidát. Poté jsem musel vytvořit pohledy,
které názvy sloupců a struktury učesaly do stejné podoby, jako byly nad daty produkčními.

Pohledy, stejně jako SSAS Tabular projekt a prezentace ze všech zmiňovaných konferencí jsou ke stažení
zde https://drive.google.com/open?id=1VbliCEwxQbPdyZRwmA7Lz8Q-QUhLOqzS

Závěr

Po delší odmlce jeden praktický článek na téma požadavky, výběr technologie a řešení. Nejdu příliš do detailu.
Pokud si to chcete raději shlédnout, koukněte na záznam ze SQL Saturday
Praha https://www.youtube.com/watch?v=QkMJCW7cjEs Veškeré materiály jsou k dispozici na linku
zde https://drive.google.com/open?id=1VbliCEwxQbPdyZRwmA7Lz8Q-QUhLOqzS

Ústřední myšlenkou je, že výběr technologie je dán požadavky na řešení. Analýza nákupního košíku se dá
provádět i s pro širokou SQL komunitu s dostupnými nástroji, jako je SQL Server a Power BI.

SQL Server 2016 SP1 a SQL Server vNext

Máme tu nový rok a první článek. Mnozí z nás se ještě nestihli vzpamatovat z příchodu SQL Serveru 2016 (natož aby stihli zmigrovat) a už nám vyšel první service pack pro SQL Server 2016, který tentokrát není jenom záplatami pro stávající produkt, ale přináší i nové funkce, případně odemyká funkce pro uživatele edice Standard. Týmy v Redmondu nezahálí a už stihli vydat CTP 1.1 nové verze SQL Serveru, kde ostrá verze se očekává v polovině roku 2017. Takže pokud jste 2016ku nestihli, zvažte, jestli ji rovnou nepřeskočit.

SQL Server 2016 SP1

Začnu funkcionalitou, které funkce odemkl SP1 pro uživatele edice standard. Z BI funkcí se jedná o Columnstore, Partitioning, PolyBase, Change Data Capture. Pro podrobnější seznam přikládám screenshot z oficiálního blogu s dostupností funkcí v edicích v RTM a v SP1

Pro detailnější informace k Service Packu bych vás odkázal na oficiální blog v angličtině

https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

Největší radost lidem z BI branže udělá asi právě column store.

Co se zatím ví o ...

SQL Server vNext

Ostrá verze bude dostupná v polovině roku 2017 a bude fungovat i na Linuxu (aktuálně si můžete zkusit preview)

První CPT obsahuje následující rozšíření

Analysis Services

Nový kompatibility level pro Tabular

„Nový“ způsob získávání dat. Máme Power Query přímo v AS Tabular

Zabezpečení na úrovni celých tabulek

Podpora „ragged hierarchies!

SSIS

Scale Out, Podpora pro Microsoft Dynamics Online

R integrace

R balíčky sqlrutils pro jednodušší integraci s SQL olapR pro konektivitu proti SSAS

Pár nových T-SQL funkcí

Oficiální blog https://msdn.microsoft.com/en-US/library/mt788653(SQL.130).aspx

S ohledem na ...

Power BI integrace

Reporting Services team se nechal slyšet v oficiálním blogu zde https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/

že

První verze Power BI integrované do RS testovatelné v on-premises prostředí se dočkáme v polovině ledna 2017, čili velmi brzy :)

Integraci do SQL Server 2016 neplánují v žádné podobě (SP, kumulativní update, nic)

Čeho se dočkáme

Dalších konektorů, vlastních vizualizací, možnosti koukat na On-premises Power BI přes mobilní aplikaci, vlastních vizualizací.

Co se neplánuje

Dashboardy, Q&A, Quick Insights... no žíly potrhané nemám :)

Závěr

Bohatá nadílka se nám před Vánoci urodila a čeká nás spousta zajímavých věcí v roce 2017. Pokud jste již zmigrovali na SQL Server 2016, koukněte co nového přinesl Service Pack. Pokud jste investici zvažujete a netlačí vás čas, zvažte vNext. Již brzy si budeme moct sáhnout on-premises na integrovanou verzi RS a Power BI.

SQL Server 2016 – General Availability

SQL Server tým oznámil 2.5, že ostrá verze SQL Serveru 2016 bude veřejně dostupná 1.6.2016 (oficiální blog https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/). Bylo také oznámeno, co najdeme ve které edici SQL Serveru a některé změny byly velmi příjemné. Na konferenci SQL Bits, které jsem měl to štěstí se účastnit, Joseph Sirosh (Corporate Vice President Data Group Microsoft) oznámil, že s dostupností SQL Serveru na Linuxu můžeme předběžně počítat někdy v květnu 2017. Developer edice je dostupná zdarma v rámci Visual Studio Dev Essentials https://www.visualstudio.com/en-us/products/visual-studio-dev-essentials-vs.aspx. Pokud chcete utéct z Oracle, Microsoft Vám umožní převést licence zdarma při splnění určitých podmínek https://www.microsoft.com/en-us/server-cloud/sql-license-migration.aspx?wt.mc_id=WW_CE_DM_OO_BLOG_NONE. Co tedy čekat od nového SQL Serveru?

Staré technologie byly předělány v jádře, tudíž vyššího výkonu byste měli dosáhnout čistě upgradem. Microsoft udává různé nárůstky pro různé technologie. Jak to ale bude u Vás nezjistíte, dokud to nezkusíte :)

Edice

Počítá se s edicemi Enterprise (pochopitelně), Standard, Developer a Express. Na první pohled se jeví, že vypadla edice Business Intelligence, byť tohle jsem nikde oficiálně nečetl. Mezi edicemi není zmiňovaná. Jak se bude nový SQL Server licencovat zatím oznámeno nebylo. Zatím jen přehled co najdeme v jednotlivých edicích. Začnu novinkami a změnami od edic nejnižších směrem nahoru k Enterprise. Co je v edicích nižších, je automaticky i v edicích vyšších.

Express

JSON – podpora pro práci s JSON dokumenty (obdobná jako pro XML)

SSRS – modernizovaný reporting (ale bez reportingu mobilního)

Query store – nástroj určený pro chytání SQL Skriptů, jejich exekučních plánů, výkonových metrik, jejich analýzu a případné vynucování exekučních plánů.

Stretch database – možnost odložit velké tabulky do Azure například za účelem archivace.

Standard

Express +

Tabular – i uživatelé standardní edice se dočkali základní verze tabulárního modelu (16 GB na instanci). Tohle je stěžejní

SSAS Multidimenzionální – vylepšení

Reporty v Power BI – Přišpendlení  reportů do Power BI z SSRS

Temporal – práce s temporal tabulkami pro verzování, psal jsem zde http://www.neoral.cz/2016/02/temporal-tables.html

Row Level Security – v relační databázi jsme schopní díky RLS zpřístupnit uživatelům personalizovaná data podobně jako v případě dynamického zabezpečení u kostek http://www.neoral.cz/2016/01/dynamicke-zabezpeceni-ssas-s-pouzitim.html

Dynamic data masking – citlivé informace se nezobrazí v čistém textu, ale jsou překryty maskou (jiné znaky)

Availability groups – 2 nody, ale pouze jedna databáze (ne ke čtení)

R – jednovlkánové Rko uvnitř databáze, konektivita k R Open

Polybase – umožní kombinovat dotazy z relačního i nerelačního světa pomocí SQL dotazů (jen compute node)

Enterprise

Standard +

Operational analytics – což je merketingový název pro column store indexy, které i pro non clustered variantu neblokují zápisy do tabulky. Jdou též vytvářet nad in-memory tabulkami. Určeno pro operativní reporting nad zdrojovým systémem.

Mobilní reporting – vypadá to, že až v enterprise

Tabular – Vylepšený direct query mód

R – integrace v databázi i samostatně. Využití více vláken.

Polybyse škálovaně

SSIS integrace Azure Data Factory a Azure SQL DW http://www.neoral.cz/2016/02/azure-sql-data-warehouse.html

Transparent Data Encryption – data šifrovaná v klientské aplikaci jsou šifrovaná po celou dobu cestou do SQL Serveru, v něm i zpět.

Novinky ve vysoké dostupnosti vylepšené Availability groups a možnosti pro hybridní scénáře.

Rozšířené možnosti pro In-Memory

Závěr

V dnešním článku jsem shrnul jak bude pravděpodobně vypat nadílka v jednotlivých edicích SQL Serveru. BI edice nebude. Tabular bude ve standardu. Mobilní reporting jen v Enterprise edici. Ale i tak každá edice obsahuje něco nového. Samotný upgrade na novou verzi by měl pomoci výkonu. Pokud jste instalovali RC 2.0, fungoval mi in place upgrade na RC 3.0. Takže snad to půjde bez problémů i na RTM. Co je velmi příjemné, na své si přijdou i potenciální zákazníci, kteří si nemohou dovolit Enterprise edici.

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.

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