15.استفاده از using

using System;
using System.Text;

class Program
{
    static void Main()
    {
// Use using statement with class that implements Dispose.
using (SystemResource resource = new SystemResource())
{
    Console.WriteLine(1);
}
Console.WriteLine(2);
    }
}

class SystemResource : IDisposable
{
    public void Dispose()
    {
// The implementation of this method not described here.
// ... For now, just report the call.
Console.WriteLine(0);
    }
}

16.اطلاعات ارسالی توسط شبکه را به حداقل برسانید

استفاده یا عدم استفاده از یک تکنولوژی یا ابزار خاص، به پارامترهای مختلفی از جمله ابعاد پروژه، مهارت و دانش اعضای تیم، ماهیت پروژه، پلتفرم اجرا، بودجه‌ی پروژه، مهلت تکمیل پروژه و تعداد نفرات تیم بستگی دارد. بنابراین واضح است پیچیدن یک نسخه‌ی خاص، برای همه‌ی سناریو‌ها امکان پذیر نیست؛ اما شرایطی وجود دارد که استفاده یا عدم استفاده از این ابزارهای تکنولوژیک منطقی‌تر مینمایند.

Stored Procedure (که از این به بعد برای ایجاز، SP نوشته خواهد شد) هم از قاعده فوق مستثنی نیست و در صورت انتخاب صحیح میتواند به ارائه‌ی محصول نهایی با کیفیت‌تری در زمان کوتاه‌تری کمک کند و در صورت انتخاب ناآگاهانه ممکن است باعث شکست یک پروژه (بخصوص در بلند مدت) شود.

تاریخچه

SQL توسط شرکت IBM در اوایل دهه 70 میلادی ایجاد شد. با اوج گرفتن زبان‌های رویه‌ای، SQL هم چندان از این قافله عقب نماند که منجر به پذیرش SP به عنوان یک استاندارد، در دهه 90 میلادی و پیاده سازی تدریجی آن توسط غول‌های سازنده دیتابیس شد (رجوع فرمایید به ^ و ^). این فاصله 20 ساله باعث غنی‌تر شدن SQL شد و وجود SP - به معنی انتقال مدل برنامه نویسی رویه‌ای به SQL - بخشی از مشکلات قبلی کار با کوئری‌های پشت سر هم و خام را حل کرد. از سال 2000 میلادی به بعد، ORM‌های قدرتمندی از جمله  Hibernate  و پیاده سازی‌های مختلفی از Active Record  و Entity Framework متولد شدند. بنابر این تقدم و تاخّرهای زمانی، بدیهی است اغلب مزایای SP نسبت به Raw SQL Query و اغلب معایب آن نسبت به ORM‌ها باشد. 

بنظر میرسد برای پاسخ به سوال اصلی این مطلب، ناگزیر به مقایسه SP با رقبای دیرینه‌اش هستیم. با برشمردن معایب و مزایای SP میتوان به نتیجه‌ی منطقی‌تری رسید. البته باید در نظر داشت صرف استفاده از SP به معنای بهره‌مند شدن از مزایای آن و صرف استفاده نکردن از آن هم بهره‌مندی از رقبای آن نیست. چگونگی استفاده یک ابزار، مهمتر از خود ابزار است.

معایب SP

- دستورات Alter Table ، Add Column و Drop Column  به این سادگی‌ها هم نیستند؛ ممکن است به یکی از جداول دیتابیس دو ستون اضافه یا از آن حذف شوند. مجبوریم تمامی SP‌ها را بخصوص Insert و Update متناظر با جدول را تغییر دهیم که این تغییرات ممکن است بصورت زنجیره‌وار به سایر SP‌ها هم سرایت کند. حال شرایطی را در نظر بگیرید که تعداد SP‌های شما به چند ده و یا حتی به چند صد عدد و بیشتر، رسیده باشد که این به معنی زحمت بیشتر و تغییرات پر هزینه‌تر است.

- احتمال کند شدن ماشین سرویس دهنده در اثر اجرای تعداد زیادی SP ؛ چناچه بخش زیادی از منطق برنامه از طریق SP اجرا شود، سرور دیتابیس موظف به اجرای آنهاست. اما در صورتیکه منطق، در کد برنامه قرار داشته باشد، امکان توزیع آن بر روی سرور‌های مجزا و یا حتی ماشین کلاینت وجود خواهد داشت. امروزه اکثر کلاینت‌ها به دیتابیس‌های سبک و سریعی مجهز شده‌اند. بنابراین در صورت امکان چرا بار پردازشی را به عهده آنها نگذاریم؟! 

- یکپارچگی کمتر؛ تقریبا همه اپلیکیشن‌ها نیازمند ارتباط با سایر سیستم‌ها هستند. اگر بخش‌های زیادی از منطق برنامه درون SP مخفی شده باشند، این نقطه تلاقی بین سیستمی، احتمالا درون خود دیتابیس قرار میگیرد و این به معنی ایجاد SP های بیشتر، افزودن پارامتر‌های بیشتر، توسعه SPهای قبلی و بطور خلاصه اعمال تغییرات بیشتر، که منتج به قابلیت نگهداری کمترخواهد شد.

- انعطاف پذیری کمتر؛ در یک شرایط ایده آل، عملکرد اپلیکیشن، مستقل از دیتابیس است. اگر نیاز به تغییر دیتابیس، مثلا از اوراکل به Microsoft SQL Server وجود داشته باشد، نیاز به بازنویسی و انتقال فانکشن‌ها و SP ها محتمل است و از آنجائیکه که با وجود استانداردها، دیتابیس‌های مختلف، معمولا در Syntax دستورات، تفاوت‌های فاحشی دارند، هر چه کد بیشتری در SP ها باشد، نیاز به انتقال و تبدیل بیشتری وجود دارد. 

- عدم وجود بازخورد مناسب؛ بسیاری از اوقات در صورت بروز اشکالی در حین اجرای یک SP، فقط با یک متن ساده بصورت Table has no rows   و یا  error مواجه میشویم. چنین خطاهایی هنگام دیباگ اصلا خوشایند نیستند. MS SQL در این بین بازخورد‌های مناسبی را ارائه میکند. اگر تجربه کار با سایر دیتابیس‌ها را داشته باشید، اهمیت بازخورد‌های مناسب، ملموس‌تر خواهد بود.

- کد نویسی سخت‌تر؛ نوشتن کد SQL  معمولا در همان IDE  اپلیکیشن انجام نمیشود. جابجایی مداوم بین دو IDE ، دیباگ و کد نویسی از طریق دو اینترفیس مجزا، اصلا ایده‌ال نیست. 

- SP  منطق را بیش از حد پنهان میکند؛ حتی با دانستن نام صحیح یک SP، باز هم تصویری از پارامتر‌های ارسالی به آن و نتیجه برگشتی نخواهیم داشت. نمیدانیم نتیجه حاصل از اجرای SP ما مقداری را برمیگرداند یا خیر؟ در صورت وجود برگشتی، یک Cursor است یا یک مقدار؟ اگر Cursor است شامل چه ستون‌هایی است؟

- SP نمیتواند یک شیء را به عنوان آرگومان بپذیرد؛ بنابراین احتمال کثیف شدن کد به مرور افزایش پیدا میکند و بدتراز آن، در صورت ارسال اشتباه یک پارامتر، یا عدم  تطابق تعداد پارامتر‌ها، مجبور به بررسی تمام آنها بصورت دستی هستیم. برای مثال دو قطعه کد زیر را با هم مقایسه کنید:

INSERT INTO User_Table(Id,Username,Password,FirstName,SureName,PhoneNumber,x,Email)
VALUES (1,'VahidN','123456','Vahid','Nasiri','09120000000','vahid_xxx@example.com')

و معادل آن در یک ORM  فرضی:

public void Insert(User user)
{
  _users.Insert(user);
  db.Save();
}

به‌وضوح قطعه کد sql، قبل از خوب یا بد بودن، زشت است. همچنین پارامتر x آن که فرضاً به تازگی اضافه شده، مقداری را دریافت نکرده و باعث بروز خطا خواهد شد.

- نبود Query Chaining؛ یکی از ویژگی‌های جذاب ORM‌‌های امروزی، امکان تشکیل یک کوئری با قابلیت خوانایی بالا و افزودن شرط‌های بیشتر از طریق  الگوی builder است. قطعه کد زیر یک SP برای جستجوی داینامیک نام و نام خانوادگی در یک جدول فرضی به اسم Users است:

public ICollection<User> GetUsers(string firstName,string lastName,Func<User, bool> orderBy)
{
    var query = _users.where(u => u.LastName.StartsWith(lastName));
    query = query.where(u => u.FirstName.StartsWith(firstName));
    query = query.OrderBy(orderBy);
    return  query.ToList();
}

در مقایسه با معادل SP آن:

CREATE PROCEDURE DynamicWhere 
    @LastName varchar(50) = null,
    @FirstName varchar(50) = null,
    @Orderby varchar(50) = null
AS
BEGIN
    DECLARE @where nvarchar(max)
    SELECT @where = '1 = 1'
 
    IF @LastName IS NOT NULL
        SELECT @Where = @Where + " AND A.LastName LIKE @LastName + '%'"
 
    IF @FirstName IS NOT NULL
        SELECT @Where = @Where + " AND A.FirstName LIKE @FirstName + '%'"
 
    DECLARE @orderBySql nvarchar(max)
    SELECT @orderBySql = CASE
        WHEN @OrderBy = "LastName" THEN "A.LastName"
        ELSE @OrderBy = "FirstName" THEN "A.FirstName"
    END
 
    DECLARE @sql nvarchar(max)
    SELECT @sql = "
    SELECT A.Id , A.AccountNoId, A.LastName, A.FirstName, A.PostingDt, 
    A.BillingAmount
    FROM Users 
    WHERE " + @where + " 
    ORDER BY " + @orderBySql
 
    exec sp_executesql @sql,  N'@LastName varchar(50), @FirstName varchar(50)
        @LastName, @FirstName
END

حاجت به گفتن نیست که قطعه کد اول چقدر خواناتر، انعطاف پذیرتر، خلاصه‌تر و قابل نگهداری‌تر است.

- نداشتن امکانات زبان‌های مدرن؛ زبان‌ها و IDE‌های مدرن، امکانات قابل توجهی را برای نگهداری بهتر، انعطاف پذیری بیشتر، مقیاس پذیری بالاتر، تست پذیری دقیق‌تر و... ارائه میکنند. به عنوان مثال:

• شیءگرایی و امکانات آن که در SP موجود نیست و در مورد قبلی معایب، به آن مختصرا اشاره شد. در نظر بگیرید اگر SQL زبانی شیء گرا بود و مجهز به ارث بری و کپسوله سازی بود، چقدر قابلیت نگهداری آن بالاتر میرفت و حجم کد‌های نوشته شده میتوانست کمتر باشند.
• نداشتن Lazy Loading که باعث مصرف زیاد حافظه میشود.
• نداشتن intellisense حین فراخوانی‌ها.
  
• نداشتن Navigation Property که باعث join نویسی‌های زیاد خواهد شد.
• SQL در مقایسه با یک زبان مدرن ناقص بنظر میرسد و این نوشتن کد آن را سخت‌تر میکند.‌
• نداشتن امکان تغییر منطقی نام جداول و ستون ها
• مدیریت تراکنش‌ها بصورت دستی، حال آنکه با الگوی Unit Of Work  این مشکل در یک ORM قدرتمند مثل EF حل شده است.

- زمان بر بودن نوشتن SP؛ گاهی نوشتن یک تابع در یک ORM یا بعضا نوشتن یک کوئری SQL کوتاه در یک رشته متنی، ساده‌تر از نوشتن کد SP است. آیا برای هر وظیفه کوچک در دیتابیس، نوشتن یک SP ضروری است؟

مزایای SP :

- کمتر کردن Round Trips در شبکه و متعاقبا کاهش ترافیک شبکه؛ اگر از یک فراخوانی استفاده کنیم، کاهش Round Trip‌ها تاثیر چندانی نخواهد داشت. همچنین ارسال یک کوئری کامل، نسبت به ارسال فقط اسم SP و پارامتر‌های آن، پهنای باند بیشتری اِشغال میکند. البته در یک شبکه با سرعت قابل قبول، بعید است این دو مزیت محسوس باشند؛ اما به هر حال برای موارد خاص، دو مزیت محسوب میشوند. نکته دیگر آنکه بدلیل Pre-Compiled بودن SP‌ها و همچنین کَش شدن Execution Plan آنها، اندکی با سرعت بالاتری اجرا میشوند.

- امکان چک کردن سینتکس قبل از اجرای آن؛ در مقایسه با Raw Query مزیت محسوب میشود.

-  امکان به اشتراک گذاری کد؛ برای پروژه‌هایی که چندین اپلیکیشن با چندین زبان برنامه نویسی مختلف در حال تهیه هستند و نیازمند دسترسی مستقیم به داده‌ها با سرعت به نسبت بالاتری هستند، SP  میتواند یک راه حل ایده آل محسوب شود. بجای پیاده سازی منطق برنامه در هر اپلیکیشن بصورت جداگانه و زحمت کدنویسی هرکدام، میتوان از SP  استفاده کرد. هرچند امروزه معمولا برای حل این مشکل، API های مشترک معماری Restful  ارجحیت دارد. 

- کمک به ایجاد یک پَک؛ در یک زیر سیستم با نیازمندی مشخص که اعمال تغییرات در آن محتمل نمیباشد نیز SP میتواند یک گزینه مناسب به حساب آید. مثلا یک سیستم Membership را در نظر بگیرید که در پروژه‌های مختلف شما مورد استفاده قرار خواهد گرفت. برای مثال میشود یک سیستم Membership  سفارشی را با امکان  Hash  پسورد و  رمز کردن داده‌های حساس،  به کمک SP و Function ‌های مناسب فراهم کرد و در واقع بین Application Login  و Data Logic تمایز قائل شد. شخصا معماری Restful را به این روش هم ترجیح میدهم. 

-  بهرمند شدن از امکانات بومی SQL ؛ به عنوان نمونه برای ترانهاده کردن خروجی یک کوئری میتوان از فانکشن  Pivot  استفاده کرد. یا فانکشن‌های تحلیلی  Lead  و  Lag  (لینک مستندات اوراکل این دو فانکشن به ترتیب در ^ و ^ ) که بنظر نمیرسد هنوز معادل مستقیمی درORM  ها  داشته باشند. 

-  تسلط و کنترل بیشتر و دقیقتر بر کوئری نهایی؛ گفته میشود SP و عبارات SQL در دیتابیس، حکم assembly را در سایر زبان‌ها دارند. بنابراین با SP میتوان عبارات SQL و نحوه اجرای آن را در دیتابیس، بطور کامل تحت فرمان داشت. این در حالی است که هر یک از ORM‌ها دستورات زبان برنامه نویسی مبداء را به یک عبارت SQL ترجمه میکنند که این عبارت چندان تحت کنترل برنامه نویس نیست و بیشتر به مدل کاری ORM بستگی دارد. 

-  امکان join بین دو یا چند دیتابیس مجزا؛ حال آنکه امکان join بین دو Context در ORM ‌ها وجود ندارد. بعلاوه اگر دو دیتابیس مدنظر ما روی دو سرور مجزا باشند، با SP و  کانفیگ Linked Server  کماکان میشود کوئری join  دار نوشت.

-  برای عملیات‌های Batch مناسب‌تر است؛ در مقام مقایسه با ORM ‌ها که با تکنیک‌های مختلفی سعی در افزایش سرعت عملیات Batch، بخصوص Insert و Update را دارند، SP  با سرعت قابل قبول‌تری اجرا میشود.

-  عدم نیاز به یادگیری سینتکس و ابزاری جدید؛ موارد بسیاری وجود دارند که فرصت یادگیری تکنولوژی جدیدی مثل یک ORM و یا SQL Bulk و حتی کتابخانه‌های ثالث مبتنی بر این ابزارها  وجود ندارند و ممکن است مجبور شوید برای باقی ماندن در بازار رقابتی، از دانسته‌های قبلی خود استفاده کنید .

-  تخصصی‌تر کردن وظایف؛ برنامه نویس‌های دیتابیس به صورت تخصصی اقدام به تحلیل روابط و ایندکس‌ها میکنند، دیتابیس را ایجاد و نرمال سازی مینمایند، SP های متناسب را میسازند و به بهترین شکل Optimize و در آخر تست میکنند.

- امنیت به نسبت بالاتر؛ میتوان مجوز اجرای SP را به یک کاربر اعطا کرد، بدون آنکه مجوز دسترسی به جداول مورد استفاده در آن SP را داد. همچنین نسبت به کوئری‌های پارامتری نشده، SQL ارجیحت دارند چون احتمال آسیب پذیری در مقابل SQL Injection را کمتر میکنند.

نتیجه‌گیری

اگرچه SP ها برای پردازش داده‌ها آنقدر هم که در وبلاگ‌ها میخوانیم بد نیستند، اما سوء استفاده از آن، مشکلات عدیده‌ای را ایجاد خواهد کرد. با توجه به روند تغییرات تکنولوژی‌های دسترسی به داده‌ها و معماری‌های مدرن بنظر میرسد SP در بهترین حالت، ابزار مناسبی برای انجام عملیات CRUD است و نه بیشتر؛ مگر در مواردی خاص که به تشخیص شما نیاز به استفاده بیشتر از آن وجود داشته باشد.

مقدمه

1- Full Load vs Incremental Load

پروسه Full Load شامل مراحل زیر بود:

1. Drop Indexes: از آنجا که Index‌ها زمان بارگذاری را افزایش می‌دادند، این عمل صورت می‌پذیرفت.
2. Truncate Tables: تمامی رکوردهای موجود در جداول حذف می‌شدند.
3. Bulk Copy
4. Load Data
5. Post Process: شامل عملیاتی نظیر شاخص گذاری روی داده هایی است که اخیراً بارگذاری شده اند و....

روی  هم رفته Full Load مسئله ای مشکل ساز بود، زیرا نیاز به زمانی برای بارگذاری مجدد داده‌ها داشت و مسئله‌ی مهم‌تر نداشتن امکان دستیابی به گزارشاتی تاریخچه ای با ماهیت زمان برای مشتریان کسب وکار بود. به این دلیل که همواره یک کپی از آخرین داده‌های موجود در سیستم عملیاتی درون DW قرار می‌گرفت؛ که با بکارگیری Full Load اغلب قادر به ارائه‌ی این نوع از گزارشات نبودیم، بدین ترتیب سازمان‌ها به نسل دوم روی آورند که در این دیدگاه از مفهوم Incremental Load استفاده می‌شود. اشکال زیر مراحلی که در این روش انجام می‌شود را نمایان می‌سازد:

Incremental Load with an Extract In area

Incremental Load without an Extract In area

مراحل Incremental Load شامل:

1. بارگذاری تغییرات نسبت به آخرین فرآیند بارگذاری انجام شده
2. درج / بروزرسانی تغییرات درون Production area
3. درج / بروزرسانی Consumption area نسبت به Production area

تفاوت‌های اصلی میان Full Load و Incremental Load در این است که در Incremental Load:

• نیازی به پردازش‌های اضافی جهت حذف شاخص ها، پاک کردن تمامی رکورد‌های جداول و ساخت مجدد شاخص‌ها نیست.
• البته نیاز به رویه ای جهت شناسایی تغییرات می‌باشد.
• و همچنین نیاز به بروزرسانی  بعلاوه درج رکوردهای جدید نیز می‌باشد.

ترکیب این عوامل برای ساخت Incremental Load کارآمد تر، منجر به پیچیده‌تر شدن پیاده سازی و نگهداری آن نیز می‌شود.

2- Detecting Net Changes

فرآیند لود افزایشی ETL، بایست قادر به شناسائی رکورد‌های تغییریافته در مبداء باشد، که این عمل با استفاده از هر یک از تکنیک‌های Push یا Pull انجام می‌شود.

• در تکنیک Pull، فرآیند ETL رکوردهای تغییریافته در مبداء را انتخاب می‌کند:

• ایده‌آل وجود داشتن یک ستون Last Changed در سیستم مبداء است؛ که از آن می‌توان جهت انتخاب رکوردهای تغییر یافته استفاده نمود.
• چنانچه ستون Last Changed وجود نداشته باشد، تمامی رکوردهای مبداء باید با رکورد‌های مقصد مقایسه شود.

• در تکنیک Push، مبداء تغییرات را شناسائی می‌کند و آنها را به سمت مقصد Push می‌کند؛ این درخواست می‌تواند توسط فرآیند ETL انجام شود.

2-1- Pulling Net Changes – Last Change Column

2-2- Pulling Net Changes – No Last Change Column

1. Join میان مبداء و مقصد با استفاده از یک دستور Left Outer Join است.
2. تمامی رکورد‌های مبداء که در مقصد وجود ندارند، پردازش می‌شوند.
3. زمانی که رکوردی در مقصد وجود داشته باشد مقادیر داده‌های مبداء و مقصد مقایسه می‌شوند.
4. تمامی رکوردهای مبداء که تغییر یافته اند پردازش می‌شوند.

2-3- Pushing Net Changes

تفاوت این دو روش به شرح زیر است:

1. در سناریو اول (شکل سمت چپ)؛ بانک اطلاعاتی رابطه ای سیستم مبداء Transaction Log را مرتب مانیتور می‌کند تا تغییرات را شناسائی کرده و در ادامه تمامی این تغییرات را در جدولی در مقصد درج می‌کند.
2. در سناریو دوم؛ توسعه دهندگان Trigger هایی ایجاد می‌کنند تا هر زمان که رکوردی تغییر یافت، تغییرات در جدولی که در مقصد وجود دارد درج گردد.

مسئله ای که در هر دو مورد وجود دارد Load اضافه ای است؛ که روی سیستم مبداء وجود دارد و می‌تواند Performance سیستم‌های OLTP را تحت تاثیر قرار دهد. به هر روی سناریو نخست معمولاً کاراتر از سناریویی است که از Trigger استفاده می‌کند.

3- ETL Patterns

پس از شناسائی رکوردهایی که در مبداء تغییر یافته اند، نیاز داریم تا این تغییرات در مقصد اعمال شود. در این قسمت به معرفی الگوهایی که برای اعمال این تغییرات وجود دارد می‌پردازیم.

3-1- Destination load Patterns

تشخیص چگونگی اضافه نمودن تغییرات در مقصد تابع دو عامل زیر است:

• آیا رکورد هم اینک در مقصد وجود دارد؟
• الگوی استفاده شده برای جدول مقصد به کدام شکل است؟ (Update یا Versioned Insert)

فلوچارت زیر نشان می‌دهد، به چه شکل جداول مقصد متاثر از چگونگی پردازش رکوردهای مبداء قرار دارند. توجه داشته باشید که عمل بررسی بطور جداگانه و در یک لحظه صورت می‌گیرد.
 

3-2- Versioned Insert Pattern

Kimball Type II Slowly Changing Dimension نمونه ای از الگوی Versioned Insert است؛ که در آن نمونه ای از یک موجودیت دارای ورژن‌های متعددی است. مطابق تصویر زیر؛ این الگو به ستون‌های اضافه ای نیاز دارند که وضعیت نمونه ای از یک رکورد را نمایش دهد.

این ستون‌ها به شرح زیر هستند:

• Start Date: زمانی که وضعیت آن نمونه از رکورد فعال می‌شود.
• End Date: زمانی که وضعیت آن نمونه از رکورد غیر فعال می‌شود.
• Record Status: وضعیت‌های یک رکورد را نشان می‌دهد، که حداقل به شکل Active یا Inactive است.
• # Version: این ستون که اختیاری می‌باشد، ورژن آن نمونه از رکورد را ثبت می‌کند.

برای مثال شکل زیر؛ بیانگر وضعیت اولیه رکوردی در این الگو است:

فرض کنید که این رکورد در تاریخ March 2 , 2010 در سیستم مبداء تغییر می‌کند. فرآیند ETL این تغییر را شناسائی می‌کند و همانند تصویر زیر؛ به شکل نمونه ای ثانویه از این رکورد، اقدام به درج آن می‌کند.

توجه داشته باشید زمانی که رکورد دوم در جدول درج می‌شود، به منظور بازتاب این تغییر؛ رکورد اول به شکل زیر بروزرسانی می‌گردد:

• End Date: تا این زمان وضعیت این رکورد فعال بوده است.
• Record Status:که Active به Inactive تغییر پیدا می‌کند.

در برخی از پیاده سازی‌های DW عمدتاً از الگوی Versioned Insert استفاده می‌شود و هرگز از الگوی Update استفاده نمی‌شود. مزیت این استراتژی در این است که تمامی تاریخچه تغییرات ردیابی و ثبت می‌شود. به هر روی غالباً هزینه ثبت کردن این تغییرات منجر به ایجاد نسخه‌های زیادی از تغییرات می‌شود. تیم DW برای مواردی که تغییرات متاثر از گزارشات تاریخچه ای نیستند، می‌توانند الگوی Update را در نظر گیرند.

3-3- Update Pattern

الگوی Update روی رکورد موجود، تغییرات سیستم مبداء را بروزرسانی می‌کند. مزیت این روش در این است که همواره یک رکورد وجود دارد و در نتیجه باعث ایجاد Query‌های کارآمدتر می‌شود. تصویر زیر بیانگر ستون هایی است که برای پشتیبانی از الگوی Update بایست ایجاد کرد.

این ستون‌ها به شرح زیر هستند:

• Record Status: وضعیت‌های یک رکورد را نشان می‌دهد که حداقل به شکل Active یا Inactive است.
• # Version: این ستون که اختیاری می‌باشد، ورژن آن نمونه از رکورد را ثبت می‌کند.

موارد اصلی الگوی Update عبارتند از:

• تاریخ ثبت نمی‌شود. ابزاری ارزشمند برای نظارت بر داده ها، تغییرات تاریخی است و زمانی که ممیزی داده رخ می‌دهد؛ می‌تواند مفید واقع شود.
• بروزرسانی‌ها یک الگوی مبتنی بر مجموعه هستند. استفاده از بروزرسانی هر بار یک رکورد در ابزار ETL خیلی کارآمد (موجه) نیست.

یک روش دیگر برای در نظر گرفتن موارد فوق؛ اضافه کردن یک جدول برای درج ورژن‌ها به الگوی Update است که در شکل زیر نشان داده شده است.

اضافه نمودن یک جدول تاریخچه، که تمامی تغییرات سیستم مبداء را ثبت  می‌کند؛ نظارت و ممیزی داده‌ها را نیز فراهم می‌کند و همچنین بروزرسانی‌های کارآمد مبتنی بر مجموعه را برای جداول DW به ارمغان می‌آورد.

3-4- Versioned Insert: Net Changes 

این الگو غالباً در جداول حجیم Fact که بروزرسانی آنها پر هزینه است استفاده می‌شود. شکل زیر منطق استفاده شده در این الگو را نشان می‌دهد.

• مقادیر مالی و عددی محاسبه شده؛ به عنوان یک Net Change از نمونه قبلی رکورد در جدول Fact ذخیره می‌شود.
• هیچ گونه فعالیت Post Processing صورت نمی‌گیرد (از قبیل بروزرسانی جداول Fact پس از کامل شدن Data Flow). هدف استفاده از این الگو اجتناب از بروزرسانی روی جداول بسیار حجیم می‌باشد.
• عدم بروزرسانی و همچنین اندازه جدول Fact زمینه ای را فراهم می‌کند که منطق شناسائی رکوردهای تغییریافته پیچیده تر  می‌شود. این پیچیدگی از آنجا ناشی می‌شود که نیاز به مقایسه رکوردهای جدول Fact آتی با جدول Fact موجود می‌باشد.

4- Data Integration Best Practices

هم اکنون پس از آشنایی با مفاهیم و الگو‌های توزیع داده‌ها به ارائه تعدادی نمونه می‌پردازیم؛ که بتوان این ایده‌ها و الگوها را در عمل پوشش داد.

4-1- Basic Data Flow Patterns

هر یک از الگوهای Update Pattern و Versioned Insert Pattern می‌توانند برای انواعی از جداول بکار روند که معروفترین آن‌ها توسط Kimball ساخته شده اند.

• (Slowly Changing Dimension Type I (SCD I: از Update Pattern استفاده می‌کند.
• (Slowly Changing Dimension Type II (SCD II: از Versioned Insert Pattern استفاده می‌کند.
• Fact Table: نوع الگویی که استفاده می‌کند به نوع جدول Fact ای که Load خواهد شد بستگی دارد.

4-1-1- Update Pattern 

مطابق تصویر زیر جدولی که تنها حاوی ورژن فعلی رکورد هاست؛ از Update Dataflow Pattern استفاده می‌کند.

مواردی که در مورد این گردش کاری باید در نظر داشت به شرح زیر است:

• این Data Flow فقط سطرهایی را به یک مقصد اضافه خواهد کرد. SSIS دارای گزینه “Table or view fast load” می‌باشد که بارگذاری‌های انبوه و سریع را پشتیبانی می‌کند.
• درون یک Data Flow بروزرسانی  رکورد‌ها را می‌توان با استفاده از تبدیل OLE DB Command انجام داد. توجه داشته باشید خروجی‌های این تبدیل در یک دستور Update به ازای هر رکورد بکار می‌رود؛ مفهوم بروزرسانی انبوه در این Data Flow وجود ندارد. بدین ترتیب الگوی فعلی ارائه شده؛ تنها رکوردها را درج می‌کند و هرگز در این Data Flow رکوردها Update نمی‌شوند.
• هر جدول دارای یک جدول تاریخچه است که برای ذخیره همه فعالیت‌های مرتبط با آن بکار می‌رود. یک رکورد در جدول تاریخچه زمانی درج خواهد شد؛ که رکورد مبداء در مقصد وجود داشته باشد ولی دارای مقداری متفاوت باشد.

• راه دیگر فرستادن تغییرات رکوردها به یک جدول کاری است که پس از پایان یافتن فرآیند Update ، خالی (Truncate) می‌شود.
• مزیت نگهداری تمامی رکوردها در یک جدول تاریخچه؛ ایجاد یک دنباله ممیزی است که می‌تواند برای نظارت بر داده‌ها به منظور نمایان ساختن موارد مطرح شده توسط مصرف کننده‌های کسب و کار استفاده شود.

• گزینه‌های متفاوتی برای تشخیص تغییرات رکوردها وجود دارد که در ادامه به شرح آنها می‌پردازیم.

شکل زیر نمایش دهنده چگونگی پیاده سازی Update Dataflow Pattern در یک SSIS می‌باشد:

این SSIS شامل عناصر زیر است:

• Destination table lookup:

به منظور تشخیص اینکه رکورد در جدول مقصد وجود دارد از “lkpPersonContact” استفاده می‌کنیم.

• Change detection logic:

با استفاده از “DidRecordChange” مبداء و مقصد مقایسه می‌شوند. اگر تفاوتی بین مبداء و مقصد وجود نداشت؛ رکورد نادیده گرفته می‌شود. چنانچه بین مبداء و مقصد تفاوت وجود داشت؛ رکورد در جدول تاریخچه درج خواهد شد.

• Detection Inserts:

رکوردها در جدول مقصد درج خواهند شد در صورتیکه در آن وجود نداشته باشند.

• Destination History Inserts:

رکوردها در جدول تاریخچه مقصد درج خواهند شد، در صورتیکه (در مقصد) وجود داشته باشند.

پس از اتمام Data Flow یک روال Post-processing مسئولیت بروزرسانی رکوردهای جدول اصلی و رکوردهای ذخیره شده در جدول تاریخچه را بر عهده دارد که می‌تواند مطابق تصویر زیر با استفاده از یک Execute Process Task پیاده سازی شود.

PostProcess مسئولیت اجرای تمامی فعالیت‌های زیر را در این الگو برعهده دارد که شامل:

• بروزرسانی رکوردهای جداول با استفاده از رکوردهای درج شده در جدول تاریخچه.
• درج تمامی رکوردهای جدید (نسخه اولیه و در درون جدول تاریخچه). کلید اصلی جداولی که ستون  آنها IDENTITY است مقدار نامشخصی دارد؛ تا زمانی که درج صورت گیرد، این به معنای آن است که پیش از انتقال آنها به جدول تاریخچه نیاز است منتظر درج شدن آنها باشیم.

4-1-2- Update Pattern – ETL Framework

تصویر زیر بیانگر انجام این عملیات با استفاده از ابزارهای ETL است.
در نگاه نخستین ممکن است Data Flow از نوع اصلی خود پیچیده‌تر به نظر آید؛ که در واقع این گونه نیز هست، زیرا در فاز توسعه بیشتر Framework‌ها جهت پیاده سازی به یک زمان اضافه‌تری نیاز دارند. به هر روی این زمان جهت اجتناب از هزینه روزانه تطبیق داده‌ها گرفته خواهد شد.
مزایای حاصل شده از افزودن این منطق اضافی عبارت است از:

• پشتیبانی از ستون هایی که کارهای ممیزی و نظارت بر داده‌ها را آسانتر می‌کنند.
• تعداد سطرها شاخص مناسبی است که می‌تواند بهبود آن Data Flow خاص را فراهم کند. ناظر اطلاعات با استفاده از تعداد رکوردها می‌تواند ناهنجاری‌ها را شناسائی کند.

بهره برداران ETL و ناظران اطلاعات می‌توانند با استفاده از خلاصه تعداد رکوردها درک بیشتری درباره فعالیت‌های آن کسب کنند. پس از آنکه تعداد رکوردها، مشکوک به نظر آمد؛ تحقیقات بیشتری می‌تواند اتفاق افتد. (با عمیق‌تر شدن در جزئیات گزارشات)
 

4-1-3- Versioned Insert Pattern

جدولی که به صورت Versioned Insert پر شده است می‌تواند از Versioned Insert Dataflow Pattern استفاده کند. همانند شکل زیر که گردش کار در آن برای کارآئی بیشتر بازنگری شده است.

توجه داشته باشید Data Flow در این روش شامل:

• تمامی رکوردهای جدید و تغییر یافته در جدول Versioned Insert قرار می‌گیرند.
• این روش دارای Data Flow ساده‌تری نسبت به الگوی Update می‌باشد.

شکل زیر SSIS versioned insert data flow pattern را نشان می‌دهد:
 

تعدادی نکته در Data Flow فوق وجود دارد که عبارتند از:

• در شیء “lkpDimGeography” گزینه “Redirect rows to no match output” با مقدار “Ignore Failures” تنظیم شده است.
• شیء “DidRecordChange” بررسی می‌کند چنانچه ستون‌های مبداء و مقصد یکسان باشند، آیا کلید اصلی جدول مقصد Not Null است. اگر این عبارت True ارزیابی شود، رکورد نادیده گرفته می‌شود.
• منطق شناسائی تغییرات دربردارنده تغییرات ستون داده ای در مبداء نمی‌باشد.
• ستون و تعداد رکوردها مشابه با Data Flow قبلی (ETL Framework) می‌باشد.

4-1-4- Update vs. Versioned Insert

الگوی Versioned Insert نسبت الگوی Update دارای پیاده سازی ساده‌تر و فعالیت‌های I/O کمتری است. از منظر دیگر، جدولی که از الگوی Update استفاده می‌کند، دارای تعداد رکوردهای کمتری است که می‌تواند به معنای Performance بهتر نیز تعبیر شود. ممکن است سوالی مطرح شود، اینکه چرا برای انجام کار به جدول تاریخچه نیاز است؛ این جدول را که نمی‌توان Truncate نمود، پس چرا به منظور بروزرسانی از جدول اصلی استفاده می‌شود؟ پاسخ این پرسش در این است که جدول تاریخچه، ناظر اطلاعات و ممیزین داده را قادر می‌سازد، تغییرات در طول زمان را پیگیری نمایند.
 

4-2- Dimension Patterns

بروزرسانی Dimension موارد زیر را شامل می‌شود:

• پیگیری تاریخچه
• انجام بروزرسانی
• تشخیص رکوردهای جدید
• مدیریت surrogate keys

چنانچه با یک Dimension کوچک مواجه هستید (با مقدار هزاران رکورد یا کمتر، که با صدها هزار رکورد یا بیشتر ضدیت دارد)،  می‌توانید از تبدیل “Slowly Changing Dimension” که بصورت Built-in در SSIS موجود است، استفاده نمائید. به هر روی با آنکه این تبدیل چندین ویژگی محدودکننده Performance دارد، اغلب کارآمدتر از پروسسه هایی که توسط خودتان ایجاد می‌شود. در واقع فرآیند بارگذاری در جداول Dimension با مقایسه داده‌ها بین مبداء و مقصد انجام می‌شود. به طور معمول مقایسه روی یک ورژن جدید و یا مجموعه ای از سطرهای جدید یک جدول با مجموعه داده‌های موجود در جدول متناظرش صورت می‌گیرد. پس از تشخیص چگونگی تغییر در داده ها، یک سری عملیات درج و بروزرسانی انجام می‌شود. شکل زیر نمونه ای از پردازش سریع در Dimension را نمایش می‌دهد؛ که شامل مراحل اساسی زیر است:

• منبع فوقانی سمت چپ، رکوردها را در یک SSIS از یک سیستم مبداء (یا یک سیستم میانی) به شکل Pull دریافت می‌کند. منبع فوقانی سمت راست، داده‌ها را از خود جدول Dimension به شکل Pull دریافت می‌کند.
• با استفاده از Merge Join رکوردها از طریق Source Key شان مقایسه می‌شوند. (در شکل بعدی جزئیات این مقایسه نمایش داده شده است.)
• با استفاده از یک Conditional Spilt داده‌ها ارزیابی می‌شوند؛ سطرها یا مستقیماً در جدول Dimension درج می‌شوند (منبع تحتانی سمت چپ) و یا در یک جدول عملیاتی (منبع تحتانی سمت راست) جهت انجام بروزرسانی درج می‌شوند.
• در گام پایانی (که نمایش داده نشده) مجموعه ای از بروزرسانی بین جدول عملیاتی و جدول Dimension صورت می‌گیرد.

با Merge Join ارتباطی بین رکوردهای مبداء و رکوردهای مقصد برقرار می‌شود. (در این مثال “CustomerAlternateKey”). هنگامی که از این دیدگاه استفاده می‌کنید، خاطر جمع شوید که نوع Join با مقدار “Left outer join” تنظیم شده است؛ بدین ترتیب قادر هستید تا رکوردهای جدید را از مبداء تشخیص دهید؛ از آنجا که هنوز در جدول Dimension قرار نگرفته اند.

گام پایانی به منظور تشخیص اینکه آیا رکورد، جدید یا تغییر یافته است (یا بلاتکلیف است)، مقایسه داده هاست. شکل زیر نمایش می‌دهد چگونه این ارزیابی با استفاده از تبدیل “Conditional Spilt” صورت می‌گیرد.

Conditional Spilt مستقیماً با استفاده از یک Adapter تعریف شده روی مقصد یا یک جدول کاری بروزرسانی که از یک Adapter تعریف شده روی مقصد استفاده می‌کند؛ توسط مجموعه دستور Update زیر، رکوردها را در جدول Dimension قرار می‌دهد. دستور Update زیر مستقیماً با استفاده از روش Join روی جدول Dimension و جدول کاری، مجموعه ای را بصورت انبوه بروزرسانی می‌کند.

UPDATE AdventureWorksDW2008R2.dbo.DimCustomer
    SET AddressLine1 = stgDimCustomerUpdates.AddressLine1
    , AddressLine2 = stgDimCustomerUpdates.AddressLine2
    , BirthDate = stgDimCustomerUpdates.BirthDate
    , CommuteDistance = stgDimCustomerUpdates.CommuteDistance
    , DateFirstPurchase = stgDimCustomerUpdates.DateFirstPurchase
    , EmailAddress = stgDimCustomerUpdates.EmailAddress
    , EnglishEducation = stgDimCustomerUpdates.EnglishEducation
    , EnglishOccupation = stgDimCustomerUpdates.EnglishOccupation
    , FirstName = stgDimCustomerUpdates.FirstName
    , Gender = stgDimCustomerUpdates.Gender
    , GeographyKey = stgDimCustomerUpdates.GeographyKey
    , HouseOwnerFlag = stgDimCustomerUpdates.HouseOwnerFlag
    , LastName = stgDimCustomerUpdates.LastName
    , MaritalStatus = stgDimCustomerUpdates.MaritalStatus
    , MiddleName = stgDimCustomerUpdates.MiddleName
    , NumberCarsOwned = stgDimCustomerUpdates.NumberCarsOwned
    , NumberChildrenAtHome = stgDimCustomerUpdates.NumberChildrenAtHome
    , Phone = stgDimCustomerUpdates.Phone
    , Suffix = stgDimCustomerUpdates.Suffix
    , Title = stgDimCustomerUpdates.Title
    , TotalChildren = stgDimCustomerUpdates.TotalChildren
FROM AdventureWorksDW2008.dbo.DimCustomer DimCustomer
  INNER JOIN dbo.stgDimCustomerUpdates ON
DimCustomer.CustomerAlternateKey = stgDimCustomerUpdates.CustomerAlternateKey

4-3- Fact Table Patterns

جداول Fact به پردازش‌های منحصر به فردی نیازمند هستند، نخست به کلیدهای Surrogate جدول Dimension نیاز دارند تا Measure‌های محاسبه شدنی را بدست آورند. این اعمال از طریق تبدیلات Lookup، Merge Join و Derived Column صورت می‌گیرد. با بروزرسانی ها، تفاضل رکورد‌ها و یا Snapshot بیشتر این فرآیندهای دشوار انجام می‌شوند.

4-3-1- Inserts

روی اغلب جداول Fact عمل درج صورت می‌گیرد؛ که کار متداولی در جدول Fact می‌باشد. شاید ساده‌ترین کار که در فرآیند ساخت ETL صورت می‌گیرد، عملیات درج روی تنها تعدادی از جدول Fact می‌باشد. درج کردن در صورت لزوم بارگذاری انبوه داده ها، مدیریت شاخص‌ها و مدیریت پارتیشن‌ها را شامل می‌شود.

4-3-2- Updates

بروزرسانی روی جداول Fact معمولاً به یکی از سه طریق زیر انجام می‌گیرد:

• از طریق یک تغییر یا بروزرسانی رکورد
• از طریق یک دستور Insert خنثی کننده (Via an Insert of a compensating transaction)
• با استفاده از یک SQL MERGE

در موردی که تغییرات با فرکانس کمی روی جدول Fact صورت می‌گیرد و یا فرآیند بروزرسانی قابل مدیریت است؛ ساده‌ترین روش انجام یک دستور Update روی جدول Fact می‌باشد. نکته  مهمی که هنگام انجام بروزرسانی باید به خاطر داشته باشید، استفاده از روش بروزرسانی مبتنی بر مجموعه است؛ به همان طریق که در قسمت الگوهای Dimension ذکر آن رفت.
در طریقی دیگر (درج compensating) می‌توان اقدام به درج رکورد تغییر یافته نمود، تا ترجیحاً بروزرسانی روی آن صورت گیرد. این استراتژی به سادگی داده‌های جدول Fact میان سیستم مبداء و مقصد را که تغییر یافته اند، به صورت یک رکورد جدید درج خواهد کرد. تصویر زیر مثالی از اجرای موارد فوق را نمایش می‌دهد.
 

در آخرین روش از یک دستور SQL MERGE استفاده می‌شود که در آن با استفاده از ادغام و مقایسه، تمامی داده‌های جدید و تغییر یافته جدول Fact، درج و یا بروزرسانی می‌شوند. نمونه ای از استفاده دستور Merge به شرح زیر است:

MERGE dbo.FactSalesQuota AS T
USING SSIS_PDS.dbo.stgFactSalesQuota AS S
ON T.EmployeeKey = S.EmployeeKey
AND T.DateKey = S.DateKey
WHEN MATCHED AND BY target
THEN INSERT(EmployeeKey, DateKey, CalendarYear, CalendarQuarter, SalesAmountQuota)
VALUES(S.EmployeeKey, S.DateKey, S.CalendarYear, S.CalendarQuarter, S.SalesAmountQuota)
WHEN MATCHED AND T.SalesAmountQuota != S.SalesAmountQuota
THEN UPDATE SET T.SalesAmountQuota = S.SalesAmountQuota
;

4-3-3- Managing Inferred Members

زمانیکه یک ارجاع در جدول Fact به یک عضو Dimension که هنوز بارگذاری نشده‌است بوجود  آید؛ یک Inferred Member تعبیر می‌شود. به سه طریق می‌توان این Inferred Member‌ها را مدیریت نمود:

• رکوردهای جدول Fact پیش از درج اسکن شوند؛ ایجاد هر Inferred Member در Dimension و سپس بارگذاری رکوردها در جدول Fact
• در طول عملیات بارگذاری روی Fact؛ هر رکورد مفقوده شده به یک جدول موقتی ارسال شود، رکوردهای مفقوده شده به Dimension اضافه شود، در ادامه مجدداً آن رکوردهای Fact در جدول Fact بارگذاری شوند.
• در یک Data Flow زمانی که یک رکورد مفقود شده، بلاتکلیف تعبیر می‌شود؛ آن زمان یک رکورد به Dimension اضافه شود و Surrogate Key بدست آمده را برگردانیم؛ سپس Dimension بارگذاری شود.

شکل زیر این موارد را نمایش می‌دهد: