🎎 🏡 🦋 टाइप 1 और 2 की विशेषताओं के साथ एक आयाम बनाने की विधि 🥢 🤦🏾 ⛽️

हम दूरसंचार में DWH पर काम कर रहे हैं, इसलिए मैं जिस उदाहरण पर विचार कर रहा हूं उसे "सब्सक्राइबर" कहा जाता है। सिद्धांत सार्वभौमिक है और यह "ग्राहक" या "रोगी" हो सकता है - उद्योग पर निर्भर करता है। मुझे उम्मीद है कि विभिन्न उद्योगों से DWH डेवलपर्स द्वारा कार्यप्रणाली उपयोगी पाई जाएगी।

यदि आपको समझ में नहीं आता है कि डीडब्ल्यूएच, माप और तथ्य क्या हैं, तो मैं राल्फ किमबॉल की पुस्तक डाइमेंशनल मॉडलिंग को पढ़ने की सलाह देता हूं। हम एक एंटरप्राइज़ की एनालिटिक्स और समेकित रिपोर्टिंग के लिए डेटाबेस के बारे में बात कर रहे हैं, विशेष रूप से भविष्य के प्रश्नों में चयन (WHERE) के लिए विशेषताओं (फ़ील्ड्स) को स्टोर करने वाले मापों (फ़ील्ड्स) के गठन और अद्यतन के बारे में।

हमारी तकनीक Microsoft SQL सर्वर के लिए है।

परिवर्तन परिभाषा सिद्धांत

प्रकार 1 (पुन: लिखने योग्य) और 2 (माप रिकॉर्ड में इतिहास के भंडारण के साथ) की विशेषताओं का परिवर्तन खेतों के चेकसम की तुलना के आधार पर निर्धारित किया जाता है।

चेकसमों की गणना करने के लिए, T-SQL CHECKSUM फ़ंक्शन का उपयोग किया जाता है, जो टाइप टेक्स्ट, नेक्स्ट, इमेज का समर्थन नहीं करता है, जिसे आयामों में फिट नहीं होना चाहिए। व्यवहार में BINARY_CHECKSUM का उपयोग करने से पता चला है कि NULL वाले क्षेत्रों में परिवर्तनों का गलत तरीके से पता लगाना संभव है। इस तकनीक के साथ, .NET पर विकसित कस्टम चेकसम फ़ंक्शन का उपयोग करना संभव है।

माप की घोषणा

एक आयाम को एक प्राथमिक कुंजी के साथ घोषित किया जाना चाहिए जिसमें एक गुच्छेदार सूचकांक होता है।

उदाहरण के लिए एसक्यूएल स्क्रिप्ट "सब्सक्राइबर" आयाम घोषणा:

USE [DWH] GO /*     Dim */ IF SCHEMA_ID('Dim') IS NULL EXECUTE('CREATE SCHEMA [Dim] AUTHORIZATION [dbo]') GO /*   */ IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Dim].[]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) DROP TABLE [Dim].[] GO /*   */ CREATE TABLE [Dim].[] ( [AccountKey] INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, [ ] INT NULL, [ ] NVARCHAR(13) NOT NULL CHECK ([ ] IN (N'.', N'.', N' ')) DEFAULT N' ', [] NVARCHAR(16) NOT NULL DEFAULT N' ', [ ] NVARCHAR(50) NOT NULL DEFAULT '   ', [  ] DATE, [  ] DATE, [] NVARCHAR(100) NOT NULL DEFAULT '   ', [] NVARCHAR(100) NOT NULL DEFAULT '   ', [] NVARCHAR(100) NOT NULL DEFAULT '   ', [ ] DATE, [] AS [] + CASE WHEN [] != N'   ' THEN N' ' + [] ELSE N'' END + CASE WHEN [] != N'   ' THEN N' ' + [] ELSE N'' END, [ ] NVARCHAR(100) NOT NULL DEFAULT N'   ', ... [] NVARCHAR(200) NOT NULL DEFAULT N'   ', [  SMS] NVARCHAR(20) NOT NULL DEFAULT N'   ', [] NVARCHAR(200) NOT NULL DEFAULT N'   ', [E-mail] NVARCHAR(50) NOT NULL DEFAULT N'   ', [ ] AS N': ' + [] + N', ' + N'  SMS: ' + [  SMS] + N', ' + N': ' + [] + N', ' + N'E-mail: ' + [E-mail], [StartTime] DATETIME2 NOT NULL, [EndTime] DATETIME2 NULL, [Checksum1] INT NULL, [Checksum2] INT NULL ) /*    */ SET IDENTITY_INSERT [Dim].[] ON INSERT INTO [Dim].[] ([AccountKey],[ ],[StartTime]) VALUES (-1, NULL, GETDATE()) SET IDENTITY_INSERT [Dim].[] OFF GO

यह आवश्यक है कि माप के आधार पर स्थिति [EndTime] IS NULL के साथ व्यापार कुंजी पर एक सशर्त सूचकांक लगाया जाए। माप तालिका ([AccountKey]) की प्राथमिक कुंजी के क्लस्टर इंडेक्स के अधीन इंडेक्स में चेकसम को शामिल करना, प्रारंभिक क्वेरी को निष्पादित करते समय माप तालिका को स्वयं नहीं पढ़ने देता है। उसी समय, सूचकांक विशिष्टता नियंत्रण का कार्य करता है - एक व्यापार कुंजी के लिए एक वैध रिकॉर्ड।

"सब्सक्राइबर" माप के लिए उदाहरण सूचकांक:

 CREATE UNIQUE INDEX IX__ ON [Dim].[] ([ ], [EndTime]) INCLUDE ([Checksum1], [Checksum2]) WHERE [EndTime] IS NULL

विशिष्ट प्रारंभिक SQL क्वेरी

प्रारंभिक SQL क्वेरी में एक आंतरिक सबक्वेरी होती है [i], जो स्रोत कॉपी तालिकाओं से फ़ील्ड प्राप्त करती है, और एक बाहरी क्वेरी [o], जो चेकसम उत्पन्न करती है, और व्यापार कुंजी के साथ वर्तमान माप पंक्ति को जोड़ती है।

"सब्सक्राइबर" आयाम के लिए प्रारंभिक क्वेरी का एक उदाहरण:

 SELECT [o].*, [].[Checksum2] AS [OLD_CHECKSUM2], [].[Checksum1] AS [OLD_CHECKSUM1], [].[AccountKey] AS [OLD_AccountKey] FROM ( SELECT i.*, CHECKSUM( [ ], [], [ ], [  ], [], [], [], [ ], [], [], [], [], [ ], [ ] ) AS [CHECKSUM2], CHECKSUM( [], [  SMS], [E-mail], [ ] ) AS [CHECKSUM1] FROM ( SELECT FROM [Raw].... LEFT JOIN [Raw].... ) AS [i] --     ) AS [o] --      LEFT JOIN [Dim].[] ON [o].[ ] = [].[ ] AND [].[EndTime] IS NULL --

प्रकार रूपांतरण (यह आमतौर पर CAST (... AS NVARCHAR (..)) या IIF (ISDATE ([...]) = 1, CAST ([...] AS DATE), NULL) के साथ-साथ संपूर्ण बाइंडिंग है। स्रोत तालिका (LEFT JOIN) और सशर्त क्षेत्र गठन (CASE, IIF) आंतरिक क्वेरी में FROM (...) AS [i] के अंदर किया जाना चाहिए।

यदि स्रोत तालिकाओं को जोड़ने का तर्क बहुत जटिल है (उदाहरण के लिए, आपको पदानुक्रम से कुछ डेटा खींचने की आवश्यकता है) और इसे FROM के अंदर निष्पादित नहीं किया जा सकता है, तो SSIS में डेटा प्रवाह से पहले, आपको SQL टास्क सम्मिलित करना होगा, जो अलग-अलग तालिकाओं में मध्यवर्ती डेटा बनाता है (आपकी नामकरण योजना का अवलोकन) वस्तुओं)। अस्थायी तालिकाएँ काम नहीं करेंगी क्योंकि SSIS उनसे आउटपुट स्ट्रीम के मेटाडेटा को निर्धारित करने में सक्षम नहीं होगा।

आप इसके निष्पादन योजना को देखते हुए सूचकांक और प्रारंभिक क्वेरी के बीच बातचीत की शुद्धता को सत्यापित कर सकते हैं। निष्पादन योजना के अंत में, तालिका का कोई संदर्भ नहीं होना चाहिए:

विशिष्ट डेटा स्ट्रीम

आयाम बनाने वाले डेटा का प्रवाह ऊपर वर्णित प्रारंभिक SQL क्वेरी को छोड़ देता है और तर्क को और बदल देता है।

ऑपरेशन में "स्टार्टटाइम जोड़ना", एसएसआईएस पैकेज स्टार्ट टाइम का उपयोग करके स्ट्रीम में स्टार्टटाइम जोड़ें (वेरिएबल @ [सिस्टम :: टाइमटाइम] ले लें))

ऑपरेशन "टाइप 1 अपडेट का पता लगाएं" में, हम अभिव्यक्ति के आधार पर एक स्ट्रीम - "टाइप 1 फ़ील्ड अपडेट किए गए हैं" का चयन करते हैं! ISNULL (OLD_CHECKSUM1) && CHECKSUM1! = OLD_CHECKSUM1।

ऑपरेशन में "व्यावसायिक कुंजी द्वारा टाइप 1 के फ़ील्ड अपडेट करें", हम सभी आयाम रिकॉर्ड (टाइप 2 फ़ील्ड के ऐतिहासिक मूल्यों वाले पहले से ही बंद रिकॉर्ड सहित) को अपडेट करते हैं, जिसमें पहले प्रकार के गुण बदल गए हैं - इसके लिए हम अप्रासंगिक रिकॉर्डों को काटने की स्थिति के बिना एक व्यवसाय कुंजी का उपयोग करते हैं। ([EndTime] पर कोई शर्त नहीं)। "सब्सक्राइबर" के लिए उदाहरण:

 UPDATE [Dim].[] SET [] = ?, [  SMS] = ?, [E-mail] = ?, [ ] = ? [Checksum1] = ? WHERE [ ] = ?

ऑपरेशन में "टाइप 2 अपडेट और नए रिकॉर्ड का पता लगाएं", हम दो धाराओं को अलग करते हैं:

"नया एक ISNULL अभिव्यक्ति पर आधारित (OLD_AccountKey)
CHECKSUM2! = OLD_CHECKSUM2 पर आधारित "टाइप 2 फ़ील्ड्स अपडेटेड"

"बंद माप माप पंक्तियों" ऑपरेशन में, धारा से प्रारंभ समय मान के साथ माप रिकॉर्ड के लिए [EndTime] अद्यतन करें। "सब्सक्राइबर" के लिए उदाहरण:

 UPDATE [Dim].[] SET [EndTime] = ? WHERE [AccountKey] = ?

ऑपरेशन में "आयाम में पंक्तियाँ डालें" हम एक ही समय में नई पंक्तियाँ सम्मिलित करते हैं, धारा से StartTime को [StartTime] फ़ील्ड में डालें, माप कुंजी और [EndTime] पर ध्यान न दें ([EndTime] फ़ील्ड में उत्पन्न NULL वर्तमान रिकॉर्ड का संकेत होगा)।

अंतिम ऑपरेशन पर पेस्ट करते समय, यह फास्ट लोड मोड का उपयोग करने के लिए काम नहीं करेगा क्योंकि अपडेट के साथ एक ही थ्रेड में किया गया प्रविष्टि टेबल स्तर पर लॉक का विस्तार किए बिना लाइन पर काम करना चाहिए, अन्यथा एक साथ किए गए संचालन के बीच संघर्ष होगा। वैकल्पिक रूप से, आप संचालन को नियंत्रण प्रवाह के विभिन्न चरणों में अलग कर सकते हैं, रॉ या कैश में मध्यवर्ती परिणाम संग्रहीत कर सकते हैं और संचालन के आदेश का पालन कर सकते हैं।

लेनदेन का उपयोग

डेटा प्रवाह कंटेनर (या सामान्य कंटेनर के स्तर पर, यदि आपने नियंत्रण प्रवाह के चरणों द्वारा संचालन को विभाजित किया है), तो लेनदेन शामिल करना वांछनीय है। ऐसा करने के लिए, TransactionOption = आवश्यक (DTC की आवश्यकता है) और IsolationLevel ReadCommitted से कम नहीं सेट करें।

यदि कोई प्रवाह नहीं है, तो डेटा प्रवाह में रुकावट के दौरान, कुछ माप रिकॉर्ड प्रासंगिक वास्तविक रिकॉर्ड सम्मिलित किए बिना बंद रह सकते हैं। अगली शुरुआत में, लापता प्रविष्टियों को नए के रूप में डाला जाएगा, लेकिन शुरुआत के समय पिछली पंक्ति के समापन समय से अलग होगा। यह तथ्य को ध्यान में रखा जाना चाहिए यदि तथ्य उस समय रिकॉर्ड प्रासंगिक को संलग्न करने की विधि द्वारा माप से बंधे होते हैं - जब संचालन केवल समापन समय से जुड़ा होता है।

अन्य तरीकों से तुलना

धीरे-धीरे बदलते आयाम नामक मानक SQL सर्वर एकीकरण सेवा घटक का उपयोग करने की तुलना में, यह विधि आयाम पंक्ति में प्रत्येक क्षेत्र के साथ प्रत्येक क्षेत्र की तुलना नहीं करती है - यह इस तरह की तुलना करने के लिए तालिका का उपयोग भी नहीं करता है। यह मुख्य लाभ - गति देता है। इसके अलावा, मानक धीरे-धीरे बदलते आयाम तुरंत तत्वों की एक पूरी श्रृंखला को नियंत्रित करते हैं और इससे उन्हें अनुकूलित करना मुश्किल हो जाता है। मेरे लिए अज्ञात कारणों के लिए, मानक SSIS घटक गलत तरीके से परिवर्तनों का पता लगा सकता है (शायद यह NULL क्षेत्रों से संबंधित है)।

तृतीय-पक्ष वाणिज्यिक घटकों की तुलना में, इस पद्धति का एक मौलिक लाभ यह है कि यह एसक्यूएल रिलीज के साथ भेजे गए मानक घटकों और कार्यों पर आधारित है। इसलिए, आपको SQL Server के अगले संस्करण में अपग्रेड करने के लिए अद्यतन घटकों के लिए प्रतीक्षा करने की आवश्यकता नहीं है।

विधि संशोधन

एक संशोधित तरीका संभव है जहां शामिल होना SQL क्वेरी में नहीं बल्कि लुकअप ऑपरेशन के माध्यम से SSIS डेटा स्ट्रीम में किया जाता है। यह अधिक डेटाबेस कॉल की ओर जाता है और कम कुशल एकल अनुरोधों के साथ प्रभावी हैश मैच की जगह लेता है। दूसरी ओर, यह आपको स्रोत तालिकाओं (स्रोत प्रणालियों से लोडिंग के चरण में उत्पन्न तालिकाओं) और अलग-अलग सर्वरों पर माप तालिकाओं को अलग करने की अनुमति देता है। लेकिन इस अवसर का लाभ संदेह में है।

मर्ज ज्वाइन ऑपरेशन के माध्यम से एसएसआईएस स्ट्रीम में शामिल होना संभव है, लेकिन इसके लिए टेबल को पुनः प्राप्त करना और इसे सॉर्ट करना होगा, जो सूचकांक के फायदों को नकार देगा।

चेकसम की गणना डेटा स्ट्रीम में भी संभव है, उदाहरण के लिए एक अलग घटक के रूप में, लेकिन इस मामले में अलग-अलग घटकों की आवश्यकता के अभाव का लाभ गायब हो जाता है।

C # में स्क्रिप्ट परिवर्तन का उपयोग करके डेटा स्ट्रीम में चेकसम की गणना एक संभावित संशोधन विकल्प है, यदि यह जटिलता समझ में आती है।

संशोधित विधि के कार्यान्वयन (स्क्रीनशॉट के साथ) का एक सिंहावलोकन (इसके पिछले संस्करण के तथ्य में) कोन्सन्स से चेकसम की गणना के लिए एक मुफ्त तीसरे पक्ष के घटक का उपयोग करके और कस्टम लुकअप (इंडेक्स का उपयोग करने के लिए) यहां देखा जा सकता है ।

टाइप 1 और 2 की विशेषताओं के साथ एक आयाम बनाने की विधि