یکی از قدم‌های خیلی مهم در شروع تحلیل و ساخت انباره داده شناخت کیفیت داده‌های جداول OLTP هستش کامپوننت Data Profiling میتونه تو این راه کمکمون کنه

نیازمندی‌هاو محدودیتها

Data Profiling task فقط با دیتاهای ذخیره شده بر روی SQL Server کار میکنه و این تسک با third-partyها یا providerهای دیتابیسهای دیگه و یا با سورسهای فایل بیس کار نمیکنه

برای اجرای پکیج دارای این تسک شما باید دسترسی read/write و create table بر روی دیتابیس tempdb رو داشته باشید

ایجاد پروژه

یک پروژه جدید از نوع Integration Service در SSDT باز میکنیم

در قسمت Control Flow(جریان اصلی برنامه) در SSIS Toolbox زیر مجموعه بخش Common(کامپوننتهایی که خیلی عمومی هستند) شما کامپوننت Data Profiling Task رو می‌بینید

هونطور که در قسمت توضیحات می‌بینید Data Profiling Task مشکلات کیفیت داده و شناخت کیفیت داده رو در قدم اول که استخراج اطلاعات (Extract) هستش رو برای ما انجام میده

نکته: هر تسکی در SSIS قابلیت جا به جایی، ریسایز، تغییر نام و ... داره و همه این مشخصات در یک فایل xml ذخیره میشه

نکته: با راست کلیک بر روی هر کامپوننت می‌تونید به قابلیت‌های Autosize و Groupبندی دست پیدا کنید و همه تسک‌ها قابلیست copy, cut بین پکیج‌ها رو دارند.

برای کار کردن با این تسک من به دیتابیس AdvantureWorks وصل میشم

مثال: میخوایم شناختی بر آدرسهای ذخیره شده برای مستری‌هامون داشته باشیم:

بر روی تسک راست کلیک میکنیم و محیط Edit کامپوننت رو باز میکنیم تا به ویزارد Data Profiling Task Editor برسیم(تمام این تنظیمات از قسمت propertices هم قابلیت انجام داره)

-- بخش General --

Data Profiler Options

Timeout = مدت زمانی که به این تسک فرصت میدیم تا کار رو انجام بده، 0 در اینجا یعنی ما بی‌نهایت بهش فرصت میدیم تا این کار رو انجام بده توجه کنید زمان به ثانیه است

Destination

Destination Type = شما می‌تونید خروجی رو در یک فایل و یا یک متغیر ذخیره کنید

Destination = در صورت انتخاب خروجی فایل باید فایل رو از طریق کانکشن منیجر به تسک معرفی کنید من اینجا با ایجاد یک فایل کانکشن خروجی رو در یک فایل txt ذخیره میکنم

OverwriteDestination = مشخص میکنه اگه فایلی تو این مسیر با این اسم وجود داشت عملیات بازنویسی روی فایل انجام بشه یا نه

-- بخش Profile Request --

اینجا باید درخواستهامون رو برای ایجاد پروفایل مشخص کنیم

  • Candidate Key Profile Request = یعنی پیدا کردن کلیدهای کاندید در یک جدول
  • Column Lenght Distribution Profile Request = توزیع طول فیلدها به چه میزان است (مثلاً آدرسهایی که ما برای هر مشتری ذخیره کردیم چند درصد ۲۰ کاراکتری هستند یا چند درصد null هستند) به کمک این پروفایل می‌تونیم ایرادهای موجود در داده رو شناسایی کنیم مثلا اینکه کدوم داده‌ها و مقادیر valid نیستند. مثلاً کد ایالتی آمریکا باید دو کاراکتر باشه، ولی مقادیر با بیش از دو کاراکتر در جدول ذخیره شده که غلطه
  • Column Null Ratio Profile Request = نرخ رکوردهای Null در یک Field را مشخص میکنه. این پروفیال به طور مثال کمک میکنه که درصد بالای null فیلد مورد نظر شناسایی بشه و در صورتی که بیش از مقداری خاص بود و برامون غیرمنتظره بود بررسی توسط ما انجام بشه
  • Column Pattern Profile Request = ایجاد مجموعه‌ای از عبارتهای باقاعده (regular expressions) که درصد مشخصی از رکوردهای یک فیلد رو تشکیل میدن رو نشون میده به کمک این پروفایل میشه به عنوان مثال رشته‌های غیر معتبر رو تشخیص داد
  • Column Statistics Profile Request = ایجاد آمار مثل min, max, average و انجراف معیار برای ستونهای عددی و min و max برای فیلدهای datetime گزارش‌گیری می‌کنه مثلا ممکنه تاریخی رو ذخیره کرده باشید که در آینده درست نباشه
  • Column Value Distribution Profile Request = تمام مقادیر متمایز در فیلد انتخاب شده رو انتخاب میکنه و درصد رکوردهای اون فیلد رو هم نشون میده به طور مثال تعداد ایالتهای آمریکا ۵۰ ایالته ولی مقادیر unique فیلد بیشتر از ۵۰ تا مقدار رو نشون میده پس نیاز به بررسی داره
  • Functional Dependency Profile Request = گزارش وابستگی رکوردهای موجود در یک فیلد(dependent column) با رکوردهای موجود در فیلد دیگر(determiant column) را گزارش‌گیری می‌کند. این پروفایل مقادیر نامعتبر را شناسایی می‌کند.
  • Value Inclusion Profile Request = هم‌پوشانی رکوردها بین ۲ فیلد رو پردازش میکنه. این پروفایل میتونه مشخص کنه یه فیلد یا مجموعه‌ای فیلدها برای استفاده شدن به عنوان کلید خارحی بین مجموعه‌ای از جدولها مناسب هست یا نه

Column Lenght Distribution Profile Request

هر کدوم از این درخواستها یک پنجره تنظیمات دارند که باید برای اجرای اون درخواست مقادیر رو تنظیم کنیم

Data

  • Connection Manager = دیتا سورسی که باید این درخواست بر روی انجام بشه رو مشخص میکنیم
  • TableOrView = جدول یا view ای که میخوایم روش این درخواست انجام بشه رو مشخص میکنیم
  • Column = مشخص کردن فیلد مورد نظر

General

  • RequestID = یک نام یکتا برای این درخواست (از اونجایی که ممکنه از یک نوع درخواست برای چند فیلد یک جدول بخوایم ایجاد کنیم بهتره اسامی رو به درستی انتخاب کنیم)
  • Options = قسمت IgnoreLeadingSpaces به معنی در نظر نگرفتن spaceهای شروع هستش و IgnoreTrinlingSpaces به معنی در نظر نگرفتن spaceهای انتهایی هستش

Column Null Ratio Profile Request

همانند ریکوست قبلی تنظمیات رو اعمال میکنیم:

قسمت Expressions

اگه ما بخوایم از یکسری متغیرها با شرایطی خاص در درخواستهامون استفاده کنیم از این قسمت استفاده می‌کنیم

اجرای پکیج

اگه این پکیج اولین پروژه پروسه ETL اتون هستش بر روی پکیج در solution راست کلیک کنید و set as startup package رو بزنید

بر روی Start Debuging کلیک کنید یا از دکمه پیشفرض f5 استفاده کنید

تو محیط debug اگه به قسمت Progress مراجعه کنید لیست تمام کارهای انجام شده تو اون پکیج رو می‌تونید مشاهده کنید:

خروجی

خب حالا اگه به فایل ساخته شده مراجعه کنید داده رو در غالب ساختار xml مشاهده می‌کنید:

برای اینکه بتونید این فایل رو به درستی مورد بررسی قرار بدید باید از ابزار Data Profiler Viewer این کامپوننت استفاده کنید:

همونطور که می‌بینید این نرم‌افزار تمام فایلهای پروفایلهای ساخته شده توسط شما رو باز میکنه

نتیجه تحلیل‌های درخواستی با کلیک بر روی هر درخواست قابل مشاهده است:

منابع

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-profiling-task