بانک اطلاعاتی انواع بانک اطلاعاتیها
EF Code First #2
CREATE DATABASE permission denied in database 'master'
در طول این سری آموزشهای MDX (البته هنوز نمیدانم چند قسمت خواهد بود) تلاش خواهم کرد تمامی موارد موجود در MDXها را به طور کامل با شرح و توضیح مناسب پوشش دهم.
امیدوارم شما دوستان عزیز پس از مطالعهی این مجموعه مقالات به دانش کافی در خصوص MDX Queryها دست پیدا کنید.
در قسمت اول این آموزشها در نظر دارم در ابتدا مفاهیم اولیه OLAP و همچنین مفاهیم مورد نیاز در Multi Dimentional Data Base ها برای شما عزیزان توضیح دهم و در قسمتهای بعدی این مجموعه در خصوص MDX Queryها صحبت خواهم کرد.
انباره داده (Data Warehouse)
عملا یک یا چند پایگاه داده میباشد که اطلاعات تجمیع شده از دیگر پایگاههای داده را درخود نگه داری میکند. برای ارایه گزارشاتی که از پایگاه دادههای OLTP نمیتوانیم به راحتی بگیریم.
(OLTP (Online transaction processing
سیستم پردازش تراکنش برخط میباشند . که عملا همان سیستم هایی میباشند که در طول روز دارای تغییرات بسیار زیادی میباشند (مانند سیستمهای حسابداری، انبار داری و ... که در طول روز دایما دارای تغییرات در سطح داده میباشند.)
(OLAP (OnLine Analysis Processing
این سیستمها خدماتی در نقش تحلیلگر داده و تصمیم گیرنده ارائه میکند. چنین سیستمهایی میتوانند، داده را در قالبهای مختلف برای هماهنگ کردن نیازهای مختلف کاربران مختلف، سازماندهی کنند.
تفاوت انبار داده (Data Warehouse) و پایگاه داده(Data Base)
وظیفه اصلی سیستمهای پایگاهداده کاربردی OnLine ،پشتیبانی از تراکنشهای برخط و پردازش کوئری است. این سیستمها، سیستم پردازش تراکنش برخط(OLTP) نامیده میشوند و بیشتر عملیات روزمره یک سازمان را پوشش میدهند. از سوی دیگر انبارداده، خدماتی در نقش تحلیلگر داده و تصمیم گیرنده ارائه میکند. چنین سیستمهایی میتوانند داده را در قالبهای مختلف برای هماهنگ کردن نیازهای مختلف کاربران مختلف، سازماندهی و ارائه میکند. این سیستمها با نام سیستمهای پردازش تحلیلی برخط (OLAP) شناختهمیشوند.
موارد تفاوت انبار داده (Data Warehouse) و پایگاه داده(Data Base)
• از لحاظ مدلهای داده: پایگاههای داده برای مدل OLTP بهینه سازی شدهاست. که بر اساس مدل داده رابطهای امکان پردازش تعداد زیادی تراکنش همروند، که اغلب حاوی رکوردهای اندکی هستند را دارد. اما در انبارهای داده که برای پردازش تحلیلی بر خط، طراحی شدهاند امکان پردازش تعداد کمی کوئری پیچیده بر روی تعداد بسیار زیادی رکورد داده فراهم میشود. سرورهای OLAP میتوانند از دو نوع رابطهای (ROLAP) یا چندبعدی باشند (MOLAP).
• از لحاظ کاربران: کاربران پایگاهداده کارمندان دفتری و مسؤولان هستند در حالیکه کاربران انبارداده مدیران و تصمیمگیرندهها هستند.
• از لحاظ عملیات قابل اجرا بر روی آنها: عملیات انجام شده برروی پایگاههای داده عمدتا عملیات (Select/Insert/Update/Delete) میباشد ، در حالی که عملیات روی انبار داده عمدتا Select ها میباشند.
• از لحاظ مقدار دادهها: مقدار دادههای یک پایگاهداده در حدود چند مگابایت تا چند گیگابایت است در حالی که این مقدار در انبار داده در حدود چند گیگابایت تا چند ترابایت است.
• از لحاظ زمان پرس و جو : به طور کلی سرعت پرس و جو ها روی انبارهی داده بسیار بالاتر از کوئری مشابه آن روی پایگاه داده میباشد.
• پاکسازی داده (Data Cleansing)
پاکسازی دادهها عبارت است از شناسایی و حذف خطاها و ناسازگاریهای داده ای به منظور دستیابی به دادههایی با کیفیت بالاتر.
اگر دادهها از منابع یکسان مثل فایلها یا پایگاههای داده ای گرفته شوند خطاهایی از قبیل اشتباهات تایپی، دادههای نادرست و فیلدهای بدون مقدار را خواهیم داشت و چنانچه دادهها از منابع مختلف مثل پایگاه دادههای مختلف یا سیستم اطلاعاتی مبتنی بر وب گرفته شوند .با توجه به نمایشهای دادهای مختلف خطاها بیشتر بوده و پاکسازی دادهها اهمیت بیشتری پیدا خواهد کرد. برای دستیابی به دادههای دقیق و سازگار، بایستی دادهها را یکپارچه نموده و تکرارهای آنها را حذف نمود.
وجود خطاهای نویزی، ناسازگاری در دادههای انبار داده و ناقص بودن دادهها امری طبیعی است. فیلدهای یک جدول ممکن است خالی باشند و یا دارای دادههای خطا دار و ناسازگار باشند. برای هر کدام از این حالتها روشهایی جهت پاکسازی و اصلاح دادهها ارایه میشود.
در این بخش عملیات مختلفی برای پاکسازی دادهها قابل انجام است:
• نادیده گرفتن تاپلهای نادرست
• پرکردن فیلدهای نادرست به صورت دستی
• پرکردن فیلدهای نادرست با یک مقدار مشخص
• پرکردن فیلدها با توجه به نوع فیلد و دادهها ی موجود
• پرکردن فیلدها با نزدیکترین مقدار ممکن (مثلا میانگین فیلد تاپلهای دیگر میتواند به عنوان یک مقدار مناسب در نظر گرفته شود)
• یکپارچهسازی (Integration)
• تبدیل دادهها(Data Transformation)• شناسایی فیلدهای یکسان: فیلدهای یکسان که در جدولها ی مختلف دارای نامهای مختلف میباشند.
• شناسایی افزونگیها ی موجود در دادهها ی ورودی: دادههای ورودی گاهی دارای افزونگی است. مثلا بخشی از رکورد در جداول مختلف وجود دارد.
• مشخص کردن برخوردهای داده ای: مثالی از برخوردهای داده ای یکسان نبودن واحدهای نمایش داده ای است. مثلا فیلد وزن در یک جدول بر حسب کیلوگرم و در جدولی دیگر بر حسب گرم ذخیره شده است.
در این فاز، دادههای ورودی طی مراحل زیر به شکلی که مناسب عمل داده کاوی باشند، در میآیند:
• از بین بردن نویز داده¬ها(Smoothing)
• تجمیع داده¬ها(Aggregation)
• کلی¬سازی(Generalization)
• نرمال¬سازی(Normalization)
• افزودن فیلدهای جدید
• استفاده از مقادیر مجاور برای تعیین یک مقدار مناسب برای فیلدهای دارای نویز
• دسته بندی دادههای موجود و مقداردهی فیلد دارای داده نویزی با استفاده از دسته نزدیکتر
• ترکیب روشهای فوق با ملاحظات انسانی، در این روش، اصلاح مقادیر نویزی با استفاده از یکی از روشهای فوق انجام میگیرد اما افرادی برای بررسی و اصلاح نیز وجود دارند
4. نرمال سازی(Normalization): منظور از نرمال سازی، تغییر مقیاس دادهها است. به عنوان مثالی از نرمال سازی، میتوان به تغییر بازه یک فیلد از مقادیر موجود به بازه 0 تا 1 اشاره کرد.
5. افزودن فیلدهای جدید: گاهی اوقات برای سهولت عمل داده کاوی میتوان فیلدهایی به مجموعه فیلدهای موجود اضافه کرد. مثلا میتوان فیلد میانگین حقوق کارمندان یک شعبه را به مجموعه فیلدهای موجود اضافه نمود.
در این مرحله، عملیات کاهش دادهها انجام میگیرد که شامل تکنیکهایی برای نمایش کمینه اطلاعات موجود است
. این فاز از سه بخش تشکیل میشود:
• کاهش دامنه و بعد: فیلدهای نامربوط، نامناسب و تکراری حذف میشوند. برای تشخیص فیلدهای اضافی، روشهای آماری و تجربی وجود دارند ؛ یعنی با اعمال الگوریتمهای آماری و یا تجربی بر روی دادههای موجود در یک بازه زمانی مشخص، به این نتیجه میرسیم که فیلد یا فیلدهای خاصی کاربردی در انباره داده ای و داده کاوی نداشته و آنها را حذف میکنیم.
• فشرده سازی داده ها: از تکنیکهای فشرده سازی برای کاهش اندازه دادهها استفاده میشود.
• کدکردن داده ها: دادهها در صورت امکان با پارامترها و اطلاعات کوچکتر جایگزین میشوند.
مدل دادهای رابطهای (Relational) وچند بعدی (Multidimensional) :
1. مدل داده رابطهای (Relational data modeling) بر اساس دو مفهوم اساسی موجودیت (entity) و رابطه (relation) بنا نهاده شده است. از این رو آن را با نام مدل ER نیز میشناسند.
• موجودیت (entity) : نمایانگر همه چیزهایی که در پایگاه داده وجود خارجی دارند یا به تصور در میآیند. پدیدهها دارای مشخصاتی هستندکه به آنها صفت (attribute) گفته میشود.
• رابطه (relation) : پدیدهها را به هم میپیوندد و چگونگی در ارتباط قرار گرفتن آنها با یکدیگر را مشخص میکند.
2. مدل داده چندبعدی ( Multidimensional modeling ) یا MD بر پایه دو ساختار جدولی اصلی بنا نهاده شده است:
این ساختار امکان داشتن یک نگرش مدیریتی و تصمیمگیری به دادههای موجود در پایگاه داده را تسهیل میکند.
• جدول حقایق (Fact Table)
• جداول ابعاد (Dimension Table)
جدول حقایق : قلب حجم دادهای ما را تشکیل میدهد و شامل دو سری فیلد است : کلیدهای خارجی به ابعاد و شاخصها (Measure).
شاخصها (Measure) : معیارهایی هستند که بر روی آنها تحلیل انجام میگیرد و درون جدول حقایق قرار دارند. شاخصها قبل از شکلگیری انبار داده توسط مدیران و تحلیلگران به دقت مشخص میشوند. چون در مرحله کار با انبار اطلاعات اساسی هر تحلیل بر اساس همین شاخصها شکل میگیرد. شاخصها تقریباً همیشه مقادیر عددی را شامل میشوند. مثلا برای یک فروشگاه زنجیرهای این شاخصها میتوانند واحدهای فروختهشده کالاها و مبلغ فروش به تومان باشند.
بعد (Dimension) : هر موجودیت در این مدل میتواند با یک بعد تعریف شود. ولی بعدها با موجودیتهای مدل ER متفاوتند زیرا آنها سازمان شاخصها را تعیین میکنند. علاوه بر این دارای یک ساختار سلسله مراتبی هستند و به طور کلی برای حمایت از سیستمهای تصمیم گیری سازماندهی شدهاند.
اجزای بعدها member نام دارند و تقریباٌ همه بعدها، memberهای خود را در یک یا چند سطح سلسله مراتبی (hierarchies) سازماندهی مینمایند، که این سلسله مراتب نمایانگر مسیر تجمیع (integration) و ارتباط بین سطوح پایینتر (مثل روز) و سطوح بالاتر (مثل ماه و سال) است. وقتی یک دسته از memberهای خاص با هم مفهوم جدیدی را ایجاد میکنند، به آنها یک سطح (Level) میگوییم. ( مثلاٌ هر سی روز را ماه میگوییم. در این حالت ماه یک سطح است. )
حجمهای دادهای (Data Cube)
حجمهای دادهای یا Cube از ارتباط تعدادی بعد با تعدادی شاخص تعریف میشود. ترکیب memberهای هر بعد از حجم دادهای فضای منطقی را تعریف میکند که در آن مقادیر شاخصها ظاهر میشوند. هر بخش مجزا که شامل یکی از memberهای بعد در حجم دادهای است ، سلول (cell) نامیدهمیشود. سلولها شاخصهای مربوط به تجمیعهای مختلف را در خود نگهداری مینمایند. در واقع مقادیر مربوط به حقایق (Fact) که در جدول حقایق (Fact) تعریف میشوند در حجم دادهای (Data Cube) در سلولها (Cell) نمایان میگردند.
شماهای دادهای (Data Schema) : سه نوع Schema در طراحی Data Warehouse وجود دارد
1. Stare
2. Snowflake
3. Galaxy1. شمای ستارهای (Star Schema) : متداولترین شما، همین شمایستارهای است. که در آن انبارداده با استفاده از اجزای زیر تعریف میشود:
• یک جدول مرکزی بزرگ به نام جدول حقایق که شامل حجم زیادی از دادههای بدون تکرار است.
• مجموعهای از جدولهای کمکی کوچکتر به نام جدول بعد ، که به ازای هر بعد یکی از این جداول موجود خواهد بود.
• شکل این شما به صورت یک ستاره است که جدول حقایق در مرکز آن قرار گرفته و هر یک از جداول بعد به وسیله شعاعهایی به آن مربوط هستند.
مشکل این مدل احتمال پیشامد افزونگی در آن است.
2. شمای دانهبرفی ( Snowflake Schema ) : در واقع شمای دانهبرفی، نوعی از شمای ستارهای است که در آن بعضی از جداول بعد نرمال شدهاند. و به همین خاطر دارای تقسیمات بیشتری به شکل جداول اضافی میباشد که از جداول بعد جدا شدهاند.
تفاوت این دو شما در این است که جداول شمای دانه برف نرمال هستند و افزونگی در آنها کاهش یافته است. که این برای کار کردن با دادهها و از لحاظ فضای ذخیرهسازی مفید است. ولی در عوض کارایی را پایین میآورد، زیرا در محاسبه کوئریها به joinهای بیشتری نیاز داریم.
3. شمای کهکشانی (galaxy schema) : در کاربردهای پیچیده برای به اشتراک گذاشتن ابعاد نیاز به جداول حقایق چندگانه احساس میشود که یک یا چند جدول بعد را در بین خود به اشتراک میگذارند. این نوع شما به صورت مجموعهای از شماهای ستارهای است و به همین دلیل شمای کهکشان یا شمای منظومهای نامیدهمیشود. این شما به ما این امکان را میدهد که جداول بعد بین جداول حقایق مختلف به اشتراک گذاشته شوند.
عملیات بر روی حجمهای دادهای :
• Roll Up (یا Drill-up) : با بالا رفتن در ساختار سلسله مراتبی مفهومی یک حجم دادهای، یا با کاهش دادن بعد، یک مجموعه با جزئیات کمتر (خلاصه شده) ایجاد مینماید. بالا رفتن در ساختار سلسله مراتبی به معنای حذف قسمتی از جزئیات است. برای مثال اگر قبلاٌ بعد مکان بر حسب شهر بوده آن را با بالا رفتن در ساختار سلسله مراتبی بر حسب کشور درمیآوریم. ولی وقتی با کاهش دادن بعد سروکار داریم منظور حذف یکی از ابعاد و جایگزین کردن مقادیر کل است. در واقع همان عمل تجمیع (aggregation) است.
• Drill Down : بر عکس عملRoll-up است و از موقعیتی با جزئیات دادهای کم به جزئیات زیاد میرود. این کار با پایین آمدن در ساختار سلسله مراتبی( به سمت جزئیات بیشتر) یا با ایجاد ابعاد اضافی انجام میگیرد.
نمونهای از عملیات Drill Down و Roll Up
• Slice : با انتخاب و اعمال شرط بر روی یکی از ابعاد یک subcube به شکل یک برش دو بعدی ایجاد میکند. در واقع همان عمل انتخاب (select) است.
• Dice : با انتخاب قسمتی از ساختار سلسله مراتبی بر روی دو یا چند بعد یک subcube ایجاد مینماید.
نمونهای از عملیات Dice و Slice
• Pivot (یا Rotate) : این عملیات بردارهای بعد را در ظاهر میچرخاند.
نمونهای از عملیات pivot
• Drill-across : نتیجه اجرای کوئریهایی که نتیجه اجرای آنها حجمهای دادهایهای مرکب با بیش از یک fact-table است.
• Ranking : سلولهایی را باز میگرداند که در بالا یا پایین شرط خاصی واقع هستند. مثلاٌ ده محصولی که بهترین فروش را داشتهاند.
سرورهای OLAP :
در تکنولوژیOALP دادهها به دو صورت چندبعدی (Multidimensional OLAP) (MOLAP) و رابطهای (Relational OLAP) (ROLAP) ذخیره میشوند. OLAP پیوندی(HOLAP) تکنولوژیی است که دو نوع قبل را با هم ترکیب میکند.
MOLAP : روشی است که معمولاٌ برای تحلیلهای OLAP در تجارت مورد استفاده قرار میگیرد. در MOLAP، دادهها با ساختار یک حجم دادهای ( Data Cube ) چند بعدی ذخیره میشوند. ذخیرهسازی در پایگاهدادههای رابطهای انجام نمیگیرد، بلکه با یک فرمت خاص انجام میشود. اغلب محصولات موفق MOLAP از یک روش چندبعدی استفاده مینمایند که در آن یک سری حجمهای دادهای کوچک، انبوه و از پیش محاسبهشده، یک حجم دادهای بزرگ (hypercube ) را میسازند.
علاوه براین MOLAP به شما امکان میدهد دادههای دیدهای (View) تحلیلگران را دسته بندی کنید، که این در حذف اشتباهات و برخورد با ترجمههای پرغلط کمک بزرگی است.
گذشته از همه اینها از آنجا که دادهها به طور فیزیکی در حجمهای دادهای بزرگ چندبعدی ذخیره میشوند، سرعت انجام فعالیتها بسیار زیاد خواهد بود.
از آنجا که یک کپی از دادههای منبع در کامپیوتر Analysis server ذخیرهمیشود، کوئریها میتوانند بدون مراجعه به منابع مجدداً محاسبه شوند. کامپیوتر Analysis server ممکن است کامپیوترسرور که تقسیم بندیها در آن انجام شده یا کامپیوتر دیگری باشد. این امر بستگی به این دارد که تقسیمبندیها در کجا تعریف شدهاند. حتی اگر پاسخ کوئریها از روی تقسیمات تجمیع (integration) شده قابل دستیابی نباشند، MOLAP سریعترین پاسخ را فراهم میکند. سرعت انجام این کار به طراحی و درصد تجمیع تقسیمبندیها بستگی دارد.
مزایا : کارایی عالی- حجمهای دادهای MOLAP برای بازیابی سریع دادهها ساخته شدهاند و در فعالیتهای slice و dice به صورت بهینه پاسخ میدهند. ترکیب سادگی و سرعت مزیت اصلی MOLAP است.
در ضمنMOLAP قابلیت محاسبه محاسبات پیچیده را فراهم میکند. همه محاسبات از پیش وقتی که حجمهای دادهای ساخته میشود، ایجاد میشوند. بنابراین نه تنها محاسبات پیچیده انجام شدنی هستند بلکه بسیار سریع هم پاسخ میدهند.
معایب : عیب این روش این است که تنها برای دادههایی با مقدار محدود کارکرد خوبی دارد. از آنجا که همه محاسبات زمانی که حجمهای دادهای ساخته میشود، محاسبه میگردند، امکان این که حجمهای دادهای مقدار زیادی از دادهها را در خود جای دهد، وجود ندارد. ولی این به این معنا نیست که دادههای حجمهای دادهای نمیتوانند از مقدار زیادی داده مشتق شده باشند. دادهها میتوانند از مقدار زیادی داده مشتق شدهباشند. اما در این صورت، فقط اطلاعات level خلاصه (level ای که دارای کمترین جزئیات است یعنی سطوح بالاتر) میتوانند در حجمهای دادهای موجود باشند.
ROLAP : محدودیت MOLAP در حجم دادههای قابل پرسوجو و نیاز به روشی که از دادههای ذخیرهشده به روش رابطهای حمایت کند، موجب پیشرفت ROLAP شد.
مبنای این روش کارکردن با دادههایی که در پایگاهدادههای رابطهای ذخیرهشدهاند، برای انجام اعمال slicing و dicing معمولی است. با استفاده از این مدل ذخیرهسازی میتوان دادهها را بدون ایجاد واقعی تجمیع در پایگاهدادههای رابطهای به هم مربوط کرد.
مزایا : با این روش میتوان به حجم زیادی از دادهها را رسیدگی کرد. محدودیت حجم داده در تکنولوژی ROLAP مربوط به محدودیت حجم دادههای قابل ذخیرهسازی در پایگاهدادههای رابطهای است. به بیان دیگر، خود ROLAP هیچ محدودیتی بر روی حجم دادهها اعمال نمیکند.
معایب : ممکن است کارایی پایین بیاید. زیرا هر گزارش ROLAP در واقع یک کواِری SQL (یا چند کواِری SQL )در پایگاه دادههای رابطهای است و اگر حجم دادهها زیاد باشد ممکن است زمان پاسخ کواِری طولانی شود. در مجموع ROLAP سنگین است، نگهداری آن سخت است و کند هم هست. بخصوص زمانی که نیاز به آدرس دهی جدولهای ذخیره شده در سیستم چند بعدی داریم.
این محدودیت ناشی از عملکرد SQL است. زیرا تکنولوژی ROLAP بر پایه عبارات مولد SQL برای پرسش و پاسخ بر روی پایگاه داده رابطهای است و عبارات SQL به همه نیازها پاسخ نمیدهند (مثلاٌ محاسبه حسابهای پیچیده در SQL مشکل است)، بنابراین فعالیتهای ROLAP به آن چه SQL قادر به انجام آن است محدود میگردد.
تفاوت ROALP و MOLAP : تفاوت اصلی این دو در معماری آنها است. محصولات MOLAP دادههای مورد نیاز را در یک حافظه نهان (cache) مخصوص میگذارد. ولی ROLAP تحلیلهای خود را بدون استفاده از یک حافظه میانی انجام میدهد، بدون آن که از یک مرحله میانی برای گذاشتن دادهها در یک سرور خاص استفاده کند.
با توجه به کند بودن ROLAP در مقایسه باMOLAP ، باید توجه داشت که کاربرد این روش بیشتر در پایگاه دادههای بسیار بزرگی است که گاهگاهی پرس و جویی بر روی آنها شکل میگیرد، مثل دادههای تاریخی و کمتر جدید سالهای گذشته.
نکته: اگر از Analysis Services که به وسیله Microsoft OLE DB Provider مهیا شده استفاده میکنید، تجمیعها نمیتوانند برای تقسیمبندی از روش ROLAP استفاده نمایند.
HOLAP : با توجه به نیاز رو به رشدی که برای کارکردن با دادههای بلادرنگ (real time) در بخشهای مختلف در صنعت و تجارت احساس میشود، مدیران تجاری انتظار دارند بتوانند با دامنه وسیعی از اطلاعات که فوراً و بدون حتی لحظهای تأخیر در دسترس باشند، کار کنند. در حال حاضر شبکه اینترنت و سایر کاربردها یی که به دادههایی از منابع مختلف مراجعه دارند و نیاز به فعالیت با یک سیستم بلادرنگ هم دارند، همگی از سیستم HOLAP بهره میگیرند.
named set :
Named Set مجموعهای از memberهای بعد یا مجموعهای از عبارات است که برای استفاده مجدد ایجاد میشود.
Calculated member
Calculated Memberها memberهایی هستند که بر اساس دادهها نیستند بلکه بر اساس عبارات ارزیابی MDX هستند. آنها دقیقاَ به سبک سایر memberهای معمولی هستند. MDX یک مجموعه قوی از عملیاتی را تامین میکند که میتوانند برای ساختCalculated Memberها مورد استفاده قرار گیرند به طوری که به شما امکان داشتن انعطاف زیاد در کار کردن با دادههای چند بعدی را بدهد.
امیدوارم در این قسمت با مفاهیم نخستین OLAP آشنا شده باشید.
تلاش خواهم کرد در قسمت بعدی در خصوص نصب SQL Server Analysis Services و نصب پایگاه دادهی Adventure Work DW 2008 شرح کاملی را ارایه کنم.
ایندکسهای XML ایی
ایندکسهای XML ایی، ایندکسهای خاصی هستند که بر روی ستونهایی از نوع XML تعریف میشوند. هدف از تعریف آنها، بهینه سازی اعمال مبتنی بر XQuery، بر روی دادههای این نوع ستونها است. چهار نوع XML Index قابل تعریف هستند؛ اما primary xml index باید ابتدا ایجاد شود. در این حالت جدولی که دارای ستون XML ایی است نیز باید دارای یک clustered index باشد. هدف از primary XML indexها، ارائهی تخمینهای بهتری است به بهینه ساز کوئریها در SQL Server.
جزئیات primary XML indexها
زمانیکه یک primary xml index را ایجاد میکنیم، node table یاد شده در قسمت قبل را، بر روی سخت دیسک ذخیره خواهیم کرد (بجای هربار محاسبه در زمان اجرا). متادیتای این اطلاعات ذخیره شده را در جداول سیستمی sys.indexes و sys.columns میتوان مشاهده کرد. باید دقت داشت که تهیهی این ایندکسها، فضای قابل توجهی را از سخت دیسک به خود اختصاص خواهند داد؛ چیزی حدود 2 تا 5 برابر حجم اطلاعات اولیه. بدیهی است تهیهی این ایندکسها که نتیجهی تجزیهی اطلاعات XML ایی است، بر روی سرعت insert تاثیر خواهند گذاشت. Node table دارای ستونهایی مانند نام تگ، آدرس تگ، نوع داده آن، مسیر و امثال آن است.
زمانیکه یک Primary XML Index تعریف میشود، اگر به Query Plan حاصل دقت کنید، دیگر خبری از XML Readerها مانند قبل نخواهد بود. در اینجا Clustered index seek قابل مشاهدهاست.
ایجاد primary XML indexها
همان مثال قسمت قبل را که دو جدول از آن به نامهای xmlInvoice و xmlInvoice2 ایجاد کردیم، درنظر بگیرید. اینبار یک xmlInvoice3 را با همان ساختار و همان 6 رکوردی که معرفی شدند، ایجاد میکنیم. بنابراین برای آزمایش جاری، در مثال قبل، هرجایی xmlInvoice مشاهده میکنید، آنرا به xmlInvoice3 تغییر داده و مجددا جدول مربوطه و دادههای آنرا ایجاد کنید.
اکنون برای ایجاد primary XML index بر روی ستون invoice آن میتوان نوشت:
CREATE PRIMARY XML INDEX invoice_idx ON xmlInvoice3(invoice) SELECT * FROM sys.internal_tables
در ادامه علاقمند هستیم که بدانیم داخل آن چه چیزی ذخیره شدهاست:
SELECT * FROM sys.xml_index_nodes_325576198_256000
اگر به این جدول دقت کنید، 6 ردیف اطلاعات XML ایی، به حدود 100 ردیف اطلاعات ایندکس شده، تبدیل گردیدهاست. با استفاده از دستور ذیل میتوان حجم ایندکس تهیه شده را نیز مشاهده کرد:
sp_spaceused 'xmlInvoice3'
--DROP INDEX invoice_idx ON xmlInvoice3
تاثیر primary XML indexها بر روی سرعت اجرای کوئریها
همان 10 کوئری قسمت قبل را درنظر بگیرید. اینبار برای مقایسه میتوان به نحو ذیل عمل کرد:
SELECT * FROM xmlInvoice WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1 SELECT * FROM xmlInvoice3 WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1
چند نکته در این تصویر حائز اهمیت است:
- Query plan کوئری انجام شده بر روی جدول دارای primary XML index، مانند قسمت قبل، حاوی XML Readerها نیست.
- هزینهی انجام کوئری بر روی جدول دارای XML ایندکس نسبت به حالت بدون ایندکس، تقریبا نزدیک به صفر است. (بهبود کارآیی فوق العاده)
اگر کوئریهای دیگر را نیز با هم مقایسه کنید، تقریبا به نتیجهی کمتر از یک سوم تا یک چهارم حالت بدون ایندکس خواهید رسید.
همچنین اگر برای حالت دارای Schema collection نیز ایندکس ایجاد کنید، اینبار کوئری پلن آن اندکی (چند درصد) بهبود خواهد یافت ولی نه آنچنان.
ایندکسهای XMLایی ثانویه یا secondary XML indexes
سه نوع ایندکس XML ایی ثانویه نیز قابل تعریف هستند:
- VALUE : کار آن بهینه سازی کوئریهای content و wildcard است.
- PATH : بهینه سازی انتخابهای مبتنی بر XPath را انجام میدهد.
- Property: برای بهینه سازی انتخاب خواص و ویژگیها بکار میرود.
این ایندکسها یک سری non-clustered indexes بر روی node tables هستند. برای ایجاد سه نوع ایندکس یاد شده به نحو ذیل میتوان عمل کرد:
CREATE XML INDEX invoice_path_idx ON xmlInvoice3(invoice) USING XML INDEX invoice_idx FOR PATH
پس از ایجاد ایندکس ثانویه بر روی مسیرها، اگر اینبار کوئری دوم را اجرا کنیم، به Query Plan ذیل خواهیم رسید:
همانطور که مشاهده میکنید، نسبت به حالت primary index، وضعیت clustered index seek به index seek تغییر کردهاست و همچنین دقیقا مشخص است که از کدام ایندکس استفاده شدهاست.
در ادامه دو نوع ایندکس دیگر را نیز ایجاد میکنیم:
CREATE XML INDEX invoice_value_idx ON xmlInvoice3(invoice) USING XML INDEX invoice_idx FOR VALUE CREATE XML INDEX invoice_prop_idx ON xmlInvoice3(invoice) USING XML INDEX invoice_idx FOR PROPERTY
سؤال: اکنون پس از تعریف 4 ایندکس یاد شده، کوئری دوم از کدام ایندکس استفاده خواهد کرد؟
در اینجا مجددا کوئری دوم را اجرا کرده و به قسمت Query Plan آن دقت خواهیم کرد:
برای مشاهده دقیق نام ایندکس مورد استفاده، کرسر ماوس را بر روی index seek قرار میدهیم. در اینجا اگر به قسمت object گزارش ارائه شده دقت کنیم، نام invoice_value_idx یا همان value index ایجاد شده، قابل مشاهدهاست؛ به این معنا که در کوئری دوم، اهمیت مقادیر بیشتر است از اهمیت مسیرها.
کوئریهایی مانند کوئری ذیل از property index استفاده میکنند:
SELECT * FROM xmlInvoice3 WHERE invoice.exist('/Invoice//CustomerName[text() = "Vahid"]') = 1
SELECT * FROM xmlInvoice3 WHERE invoice.exist('/Invoice//CustomerName[. = "Vahid"]') = 1
خلاصه نکات بهبود کارآیی برنامههای مبتنی بر فیلدهای XML
- در حین استفاده از XPath، ذکر محور parent یا استفاده از .. (دو دات)، سبب ایجاد مراحل اضافهای در Query Plan میشوند. تا حد امکان از آن اجتناب کنید و یا از روشهایی مانند cross apply و xml.nodes برای مدیریت اینگونه موارد تو در تو استفاده نمائید.
- ordinals را به انتهای Path منتقل کنید (مانند ذکر [1] جهت مشخص سازی نودی خاص).
- از ذکر predicates در وسط یک Path اجتناب کنید.
- اگر اسناد شما fragment با چند root elements نیستند، بهتر است document بودن آنها را در حین ایجاد ستون XML مشخص کنید.
- xml.value را به xml.query ترجیح دهید.
- عملیات casting در XQuery سنگین بوده و استفاده از ایندکسها را غیرممکن میکند. در اینجا استفاده از اسکیما میتواند مفید باشد.
- نوشتن sub queryها بهتر هستند از چندین XQuery در یک عبارت SQL.
- در ترکیب اطلاعات رابطهای و XML، استفاده از متدهای xml.exist و sql:column نسبت به xml.value جهت استخراج و مقایسه اطلاعات، بهتر هستند.
- اگر قصد تهیه خروجی XML از جدولی رابطهای را دارید، روش select for xml کارآیی بهتری را نسبت به روش FLOWR دارد. روش FLOWR برای کار با اسناد XML موجود طراحی و بهینه شدهاست؛ اما روش select for xml در اصل برای کار با اطلاعات رابطهای بهینه سازی گردیدهاست.
انتشار SQL Server 2016 CTP 3.1
New In-Memory OLTP improvements in CTP3.1 include:
- Unique indexes in memory-optimized tables, to complement the support for unique constraints that was released in CTP3
- LOB data types varchar(max), nvarchar(max), and varbinary(max) in memory-optimized tables and natively compiled modules
- Indexes with NULLable key columns in memory-optimized tables
Syntax inputdate AT TIME ZONE timezone.
- Inputdate: An expression that can be resolved to a smalldatetime, datetime, datetime2, or datetimeoffset value.
- Timezone: Name of the destination time zone in standard format as enumerated by Windows. Available time zones can be found by querying sys.time_zone_info.
SQL Server Analysis Services (SSAS) updates allow upgrading your existing models to 1200 compatibility level and a JSON editor for SSDT;
EF Code First #5
در قسمت قبل خاصیت AutomaticMigrationsEnabled را در کلاس Configuration به true تنظیم کردیم. به این ترتیب، عملیات ساده شده، اما یک سری از قابلیتهای ردیابی تغییرات را از دست خواهیم داد و این عملیات، صرفا یک عملیات رو به جلو خواهد بود.
اگر AutomaticMigrationsEnabled را مجددا به false تنظیم کنیم و هربار به کمک دستوارت Add-Migration و Update-Database تغییرات مدلها را به بانک اطلاعاتی اعمال نمائیم، علاوه بر تشکیل تاریخچه این تغییرات در برنامه، امکان بازگشت به عقب و لغو تغییرات صورت گرفته نیز مهیا میگردد.
هدف قرار دادن مرحلهای خاص یا لغو آن
به همان پروژه قسمت قبل مراجعه نمائید. در کلاس Configuration آن، خاصیت AutomaticMigrationsEnabled را به false تنظیم کنید. سپس یک خاصیت جدید را به کلاس Project اضافه نموده و برنامه را اجرا نمائید. بلافاصله خطای زیر را دریافت خواهیم کرد:
Unable to update database to match the current model because there are pending changes and
automatic migration is disabled. Either write the pending model changes to a code-based migration
or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true
to enable automatic migration.
EF تشخیص داده است که کلاس مدل برنامه، با بانک اطلاعاتی تطابق ندارد و همچنین ویژگی مهاجرت خودکار نیز فعال نیست. بنابراین اعمال code-based migration را توصیه کرده است.
برای این منظور به کنسول پاورشل NuGet مراجعه نمائید (منوی Tools در ویژوال استودیو، گزینه Library package manager آن و سپس انتخاب گزینه package manager console). در ادامه فرمان add-m را نوشته و دکمه tab را فشار دهید. یک منوی Auto Complete ظاهر خواهد شد که از آن میتوان فرمان add-migration را انتخاب نمود. در اینجا یک نام را هم نیاز است وارد کرد؛ برای مثال:
Add-Migration AddSomeProp2ToProject
به این ترتیب کلاس زیر را به صورت خودکار تولید خواهد کرد:
namespace EF_Sample02.Migrations
{
using System.Data.Entity.Migrations;
public partial class AddSomeProp2ToProject : DbMigration
{
public override void Up()
{
AddColumn("Projects", "SomeProp", c => c.String());
AddColumn("Projects", "SomeProp2", c => c.String());
}
public override void Down()
{
DropColumn("Projects", "SomeProp2");
DropColumn("Projects", "SomeProp");
}
}
}
مدلهای برنامه را با بانک اطلاعاتی تطابق داده و دریافته است که هنوز دو خاصیت در اینجا به بانک اطلاعاتی اضافه نشدهاند.
از متد Up برای اعمال تغییرات و از متد Down برای بازگشت به قبل استفاده میگردد. نام فایل این کلاس هم طبق معمول چیزی است شبیه به timeStamp_AddSomeProp2ToProject.cs .
در ادامه نیاز است این تغییرات به بانک اطلاعاتی اعمال شوند. به همین منظور دستور زیر را در کنسول پاورشل وارد نمائید:
Update-Database -Verbose
پارامتر Verbose آن سبب خواهد شد تا جزئیات عملیات به صورت مفصل گزارش داده شود که شامل دستورات ALTER TABLE نیز هست:
Using NuGet project 'EF_Sample02'.
Using StartUp project 'EF_Sample02'.
Target database is: 'testdb2012' (DataSource: (local), Provider: System.Data.SqlClient, Origin: Configuration).
Applying explicit migrations: [201205061835024_AddSomeProp2ToProject].
Applying explicit migration: 201205061835024_AddSomeProp2ToProject.
ALTER TABLE [Projects] ADD [SomeProp] [nvarchar](max)
ALTER TABLE [Projects] ADD [SomeProp2] [nvarchar](max)
[Inserting migration history record]
اکنون مجددا یک خاصیت دیگر را مثلا به نام public string SomeProp3، به کلاس Project اضافه نمائید.
سپس همین روال باید مجددا تکرار شود. دستورات زیر را در کنسول پاورشل NuGet اجرا نمائید:
Add-Migration AddSomeProp3ToProject
Update-Database -Verbose
اینبار نیز یک کلاس جدید به نام AddSomeProp3ToProject به پروژه اضافه خواهد شد و سپس بر اساس آن، امکان به روز رسانی بانک اطلاعاتی میسر میگردد.
در ادامه برای مثال به این نتیجه رسیدهایم که نیازی به خاصیت public string SomeProp3 اضافه شده، نبوده است. روش متداول، باز هم مانند سابق است. ابتدا خاصیت را از کلاس Project حذف خواهیم کرد و سپس دو دستور Add-Migration و Update-Database را اجرا خواهیم نمود.
اما با توجه به اینکه مهاجرت خودکار را غیرفعال کردهایم و هربار با فراخوانی دستور Add-Migration یک کلاس جدید، با متدهای Up و Down به پروژه، جهت نگهداری سوابق عملیات اضافه میشوند، میتوان دستور Update-Database را جهت فراخوانی متد Down صرفا یک مرحله موجود نیز فراخوانی نمود.
نکته:
اگر علاقمند باشید که راهنمای مفصل پارامترهای دستور Update-Database را مشاهده کنید، تنها کافی است دستور زیر را در کنسول پاورشل اجرا نمائید:
get-help update-database -detailed
به عنوان نمونه اگر در حین فراخوانی دستور Update-Database احتمال از دست رفتن اطلاعات باشد، عملیات متوقف میشود. برای وادار کردن پروسه به انجام تغییرات بر روی بانک اطلاعاتی میتوان از پارامتر Force در اینجا استفاده کرد.
در ادامه برای اینکه دستور Update-Database تنها یک مرحله مشخص را که سابقه آن در برنامه موجود است، هدف قرار دهد، باید از پارامتر TargetMigration به همراه نام کلاس مرتبط استفاده کرد:
Update-Database -TargetMigration:"AddSomeProp2ToProject" -Verbose
اگر دقت کرده باشید در اینجا AddSomeProp2ToProject بجای AddSomeProp3ToProject بکارگرفته شده است. اگر یک مرحله قبل را هدف قرار دهیم، متد Down را اجرا خواهد کرد:
Using NuGet project 'EF_Sample02'.
Using StartUp project 'EF_Sample02'.
Target database is: 'testdb2012' (DataSource: (local), Provider: System.Data.SqlClient, Origin: Configuration).
Reverting migrations: [201205061845485_AddSomeProp3ToProject].
Reverting explicit migration: 201205061845485_AddSomeProp3ToProject.
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'Projects')
AND col_name(parent_object_id, parent_column_id) = 'SomeProp3';
IF @var0 IS NOT NULL
EXECUTE('ALTER TABLE [Projects] DROP CONSTRAINT ' + @var0)
ALTER TABLE [Projects] DROP COLUMN [SomeProp3]
[Deleting migration history record]
همانطور که ملاحظه میکنید در اینجا عملیات حذف ستون SomeProp3 انجام شده است. البته این خاصیت به صورت خودکار از کدهای برنامه (کلاس Project در این مثال) حذف نمیشود و فرض بر این است که پیشتر اینکار را انجام دادهاید.
سفارشی سازی کلاسهای مهاجرت
تمام کلاسهای خودکار مهاجرت تولید شده توسط پاورشل، از کلاس DbMigration ارث بری میکنند. در این کلاس امکانات قابل توجهی مانند AddColumn، AddForeignKey، AddPrimaryKey، AlterColumn، CreateIndex و امثال آن وجود دارند که در تمام کلاسهای مشتق شده از آن، قابل استفاده هستند. حتی متد Sql نیز در آن پیش بینی شده است که در صورت نیاز به اجرای دستوارت خام SQL، میتوان از آن استفاده کرد.
برای مثال فرض کنید مجددا همان خاصیت public string SomeProp3 را به کلاس Project اضافه کردهایم. اما اینبار نیاز است حین تشکیل این فیلد در بانک اطلاعاتی، یک مقدار پیش فرض نیز برای آن درنظر گرفته شود که در صورت نال بودن مقدار خاصیت آن در برنامه، به صورت خودکار توسط بانک اطلاعاتی مقدار دهی گردد:
namespace EF_Sample02.Migrations
{
using System.Data.Entity.Migrations;
public partial class AddSomeProp3ToProject : DbMigration
{
public override void Up()
{
AddColumn("Projects", "SomeProp3", c => c.String(defaultValue: "some data"));
Sql("Update Projects set SomeProp3=N'some data'");
}
public override void Down()
{
DropColumn("Projects", "SomeProp3");
}
}
}
متد String در اینجا چنین امضایی دارد:
public ColumnModel String(bool? nullable = null, int? maxLength = null, bool? fixedLength = null,
bool? isMaxLength = null, bool? unicode = null, string defaultValue = null, string defaultValueSql = null,
string name = null, string storeType = null)
که برای نمونه در اینجا پارامتر defaultValue آنرا در کلاس AddSomeProp3ToProject مقدار دهی کردهایم.
برای اعمال این تغییرات تنها کافی است دستور Update-Database -Verbose اجرا گردد. اینبار خروجی SQL اجرا شده آن به نحو زیر است که شامل مقدار پیش فرض نیز شده است:
ALTER TABLE [Projects] ADD [SomeProp3] [nvarchar](max) DEFAULT 'some data'
تعیین مقدار پیش فرض، زمانیکه یک فیلد not null تعریف شدهاست نیز میتواند مفید باشد. همچنین در اینجا امکان اجرای دستورات مستقیم SQL نیز وجود دارد که نمونهای از آنرا در متد Up فوق مشاهده میکنید.
افزودن رکوردهای پیش فرض در حین به روز رسانی بانک اطلاعاتی
در قسمتهای قبل با متد Seed که به همراه آغاز کنندههای بانک اطلاعاتی EF ارائه شدهاند، جهت افزودن رکوردهای اولیه و پیش فرض به بانک اطلاعاتی آشنا شدید. در اینجا نیز با تحریف متد Seed در کلاس Configuration، چنین امری میسر است:
namespace EF_Sample02.Migrations
{
using System;
using System.Data.Entity.Migrations;
internal sealed class Configuration : DbMigrationsConfiguration<EF_Sample02.Sample2Context>
{
public Configuration()
{
this.AutomaticMigrationsEnabled = false;
this.AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(EF_Sample02.Sample2Context context)
{
context.Users.AddOrUpdate(
a => a.Name,
new Models.User { Name = "Vahid", AddDate = DateTime.Now },
new Models.User { Name = "Test", AddDate = DateTime.Now });
}
}
}
متد AddOrUpdate در EF 4.3 اضافه شده است. این متد ابتدا بررسی میکند که آیا رکورد مورد نظر در بانک اطلاعاتی وجود دارد یا خیر. اگر خیر، آنرا اضافه خواهد کرد در غیراینصورت، نمونه موجود را به روز رسانی میکند. اولین پارامتر آن، identifierExpression نام دارد. توسط آن مشخص میشود که بر اساس چه خاصیتی باید در مورد update یا add تصمیمگیری شود. دراینجا اگر نیاز به ذکر بیش از یک خاصیت وجود داشت، از anonymously type object میتوان کمک گرفت new { p.Name, p.LastName } .
تولید اسکریپت به روز رسانی بانک اطلاعاتی
بهترین کار و امنترین روش حین انجام این نوع به روز رسانیها، تهیه اسکریپت SQL فرامینی است که باید بر روی بانک اطلاعاتی اجرا شوند. سپس میتوان این دستورات و اسکریپت نهایی را دستی هم اجرا کرد (که روش متداولتری است در محیط کاری).
برای اینکار تنها کافی است دستور زیر را در کنسول پاورشل اجرا نمائیم:
Update-Database -Verbose -Script
پس از اجرای این دستور، یک فایل اسکریپت با پسوند sql تولید شده و بلافاصله در ویژوال استودیو جهت مرور نیز گشوده خواهد شد. برای نمونه محتوای آن برای افزودن خاصیت جدید SomeProp5 به صورت زیر است:
ALTER TABLE [Projects] ADD [SomeProp5] [nvarchar](max)
INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES
('201205060852004_AutomaticMigration', '2012-05-06T08:52:00.937Z', 0x1F8B0800000............ '4.3.1')
همانطور که ملاحظه میکنید، در یک مرحله، جدول پروژهها را به روز خواهد کرد و در مرحله بعد، سابقه آنرا در جدول __MigrationHistory ثبت میکند.
یک نکته:
اگر دستور فوق را بر روی برنامهای که با بانک اطلاعاتی هماهنگ است اجرا کنیم، خروجی را مشاهده نخواهیم کرد. برای این منظور میتوان مرحله خاصی را توسط پارامتر SourceMigration هدف گیری کرد:
Update-Database -Verbose -Script -SourceMigration:"stepName"
استفاده از DB Migrations در عمل
البته این یک روش پیشنهادی و امن است:
الف) در ابتدای اجرا برنامه، پارامتر ورودی متد System.Data.Entity.Database.SetInitializer را به نال تنظیم کنید تا برنامه تغییری را بر روی بانک اطلاعاتی اعمال نکند.
ب) توسط دستور enable-migrations، فایلهای اولیه DB Migration را ایجاد کنید. پیش فرضهای آن را نیز تغییر ندهید.
ج) هر بار که کلاسهای مدل برنامه تغییر کردند و پس از آن نیاز به به روز رسانی ساختار بانک اطلاعاتی وجود داشت دو دستور زیر را اجرا کنید:
Add-Migration AddSomePropToProject
Update-Database -Verbose -Script
به این ترتیب سابقه تغییرات در برنامه نگهداری شده و همچنین بدون اجرای دستورات بر روی بانک اطلاعاتی، اسکریپت نهایی اعمال تغییرات تولید میگردد.
د) اسکریپت تولید شده را بررسی کرده و پس از تائید و افزودن به سورس کنترل، به صورت دستی بر روی بانک اطلاعاتی اجرا کنید (مثلا توسط management studio).
مروری مختصر بر زبان DMX
برای بسیاری داده کاوی تنها مجموعه ای از تعدادی الگوریتم تعبیر میشود؛ به همان طریقی که در گذشته تصورشان از بانک اطلاعاتی تنها ساختاری سلسله مراتبی به منظور ذخیره دادهها بود. بدین ترتیب داده کاوی به ابزاری تبدیل شده که تنها در انحصار تعدادی متخصص (بویژه PhDهای علم آمار و یادگیری ماشین) قرار دارد که آشنائی با اصطلاحات یک زمینه خاص را دارند. هدف از ایجاد زبان DMX تعریف مفاهیمی استاندارد و گزارهایی متداول است که در دنیای داده کاوی استفاده میشود به شکلی که زبان SQL برای بانک اطلاعاتی این کار را انجام میدهد.
فرضیه اساسی در داده کاوی و همچنین یادگیری ماشین از این قرار است که تعدادی نمونه به الگوریتم نشان داده میشود و الگوریتم با استفاده از این نمونهها قادر است به استخراج الگوها بپردازد. بدین ترتیب به منظور بازبینی و همچنین استنتاج از اطلاعات درباره نمونههای جدید میتواند مورد استفاده قرار گیرد.
ذکر این نکته ضروری است که الگوهای استخراج شده میتوانند مفید، آموزنده و دقیق باشند. تصویر زیر به اختصار مراحل فرآیند داده کاوی را نمایان میسازد:
در گام نخست اقدام به تعریف مسئله و فرموله کردن آن میکنیم که اصطلاحاً Mining Model نامیده میشود. در واقع Mining Model توصیف کننده این است که داده نمونه به چه شکل به نظر میرسد و چگونه الگوریتم داده کاوی باید دادهها را تفسیر کند. در گام بعدی به فراهم کردن نمونههای داده برای الگوریتم میپردازیم، الگوریتم با بهره گیری از Mining Model به طریقی که یک لنز دادهها را مرتب میکند، به بررسی دادهها و استخراج الگوها میپردازد؛ این عملیات را اصطلاحاً Training Model مینامیم. هنگامی که این عملیات به پایان رسید، بسته به اینکه چگونه آنرا انجام داده اید، میتوانید به تحلیل الگوهایی که توسط الگوریتم از روی نمونه هایتان بدست آمده بپردازید. و در نهایت میتوانید اقدام به فراهم کردن دادههای جدید و فرموله کردن آنها، به همان طریقی که نمونهها آموزش دیده اند، به منظور انجام پیش بینی و استنتاج از اطلاعات با استفاده از الگوهای کشف شده توسط الگوریتم پرداخت.
زبان DMX وظیفه تبدیل دادههای موجودتان (سطرها و ستونهای Tables) به دادههای مورد نیاز الگوریتمهای داده کاوی (Cases و Attributes) را دارد. به منظور انجام این تبدیل به Mining Structure و Mining Model (که در قسمت اول به شرح آن پرداخته شد) نیاز است. بطور خلاصه Mining Structure صورت مسئله را توصیف میکند و Mining Model وظیفه تبدیل سطرهای داده ای به درون Caseها و انجام عملیات یادگیری ماشین با استفاده از الگوریتم داده کاوی مشخص شده را بر عهده دارد.
Syntax زبان DMX
مشابه زبان SQL دستورات زبان DMX نیز به محیطی جهت اجرا نیاز دارند که میتوان با استفاده از (SQL Server Management Studio (SSMS به اجرای دستورات DMX اقدام نمود. ایجاد ساختار کاوش (Mining Structure) و مدل کاوشی (Mining Model) مشابه دستورات ایجاد Table در زبان SQL میباشد. همانطور که اشاره شد، گام اول (از سه مرحله اصلی در داده کاوی) ایجاد یک مدل کاوش است؛ شامل تعیین تعداد ستونهای ورودی، ستونهای قابل پیش بینی و مشخص کردن نام الگوریتم مورد استفاده در مدل. گام دوم آموزش مدل که پردازش نیز نامیده میشود و گام سوم مرحله پیش بینی است که نیاز به یک مدل کاوش آموزش دیده و مجموعه اطلاعات جدید دارد. در طول پیش بینی، موتور داده کاوی قوانین (Rules) پیدا شده در مرحلهی آموزش (یادگیری) را با مجموعه اطلاعات جدید تطبیق داده و نتیجه پیش بینی را برای هر Case ورودی انجام میدهد. دو نوع پرس و جوی پیش بینی وجود دارد Batch و Singleton که به ترتیب چند Case ورودی دارد و خروجی در یک جدول ذخیره میشود و دیگری تنها یک Case ورودی دارد و خروجی در زمان اجرا ساخته میشود.
در زبان DMX دو روش برای ساخت مدلهای کاوش وجود دارد:
• ایجاد یک ساختار کاوش و مدل کاوش مربوط به هم و تحت یک نام، زمانی کاربرد دارد که یک ساختار کاوش فقط شامل یک مدل کاوش باشد.
• ایجاد یک ساختار کاوش و سپس اضافه نمودن یک مدل کاوش به ساختار تعریف شده، زمانی کاربرد دارد که یک ساختار کاوش شامل چندین مدل کاوشی باشد. دلایل مختلفی وجود دارد که ممکن است نیاز به این روش باشد، برای مثال ممکن است مدلهای متعددی را با استفاده از الگوریتمهای مختلف ساخت و سپس بررسی نمود که کدام مدل بهتر عمل خواهد کرد و یا مدلهای متعددی را با استفاده از یک الگوریتم ولی با مجموعه پارامترهای متفاوت برای هر مدل ساخت و سپس بهترین را انتخاب نمود.
عناصر سازندهی ساختار کاوش، ستونهای ساختار کاوشی هستند که داده هایی را که منبع اصلی داده فراهم میکند، توصیف میکند. این ستونها شامل اطلاعاتی از قبیل نوع داده (Data Type)، نوع محتوا (Content Type)، ماهیت داده و اینکه داده چگونه توزیع شده است میباشند. نوع محتوا پیوسته و یا گسسته بودن آن را مشخص میکند و بدین ترتیب به الگوریتم راه درست مدل کردن ستون را نشان میدهیم. کلمه کلیدی Discrete برای ماهیت گسسته داده و از کلمه Continuous برای ماهیت پیوسته داده استفاده میشود. مقادیر نوع داده و نوع محتوا به قرار زیر میباشند:
Data Type | کاربرد |
LONG | اعداد صحیح |
DOUBLE | اعداد اعشاری |
TEXT | دادههای رشته ای |
DATE | دادههای تاریخی |
BOOLEAN | دادههای منطقی (True و False) |
TABLE | برای تعریف Nested Case |
Content Type | کاربرد |
KEY | مشخص کننده کلید |
DISCRETE | دادههای گسسته |
CONTINUOUS | دادههای پیوسته |
DISCRETIZED | دادههای گسسته شده |
KEY TIME | کلید زمان، تنها در مدلهای Time Series استفاده میشود |
KEY SEQUENCE | کلید توالی، تنها در بخش Nested Table مدلهای Sequence Clustering استفاده میشود |
همچنین یک مدل کاوش استفاده و کاربرد هر ستون و الگوریتمی که برای ساخت مدل استفاده میشود را تعریف میکند، میتوانید با استفاده از کلمه کلیدی Predict و یا Predict_Only خاصیت پیش بینی را به ستونها اضافه نمود، برای نمونه به دستورات زیر توجه نمائید:
CREATE MINING STRUCTURE [New Mailing] ( CustomerKey LONG KEY, Gender TEXT DISCRETE, [Number Cars Owned] LONG DISCRETE, [Bike Buyer] LONG DISCRETE ) GO ALTER MINING STRUCTURE [New Mailing] ADD MINING MODEL [Naive Bayes] ( CustomerKey, Gender, [Number Cars Owned], [Bike Buyer] PREDICT ) USING Microsoft_Naive_Bayes
به منظور آموزش یک مدل کاوش از دستور Insert به شکل زیر استفاده میشود:
INSERT INTO <mining model name> [<mapped model columns>] <source data query>
در ادامه به شکل عملی میتوانید با طی مراحل و اجرای کوئریهای زیر به بررسی بیشتر موضوع بپردازید.
ابتدا به سرویس SSAS متصل شوید و اقدام به ایجاد یک Database با تنظیمات پیش فرض (مثلاً با نام DM-02) نمائید و در ادامه کوئری XMLA زیر را جهت ایجاد Data Source ای به بانک AdventureWorksDW2012 موجود روی دستگاه تان، اجرا نمائید.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ParentObject> <DatabaseID>DM-02</DatabaseID> </ParentObject> <ObjectDefinition> <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xsi:type="RelationalDataSource"> <ID>Adventure Works DW2012</ID> <Name>Adventure Works DW2012</Name> <ConnectionString>Provider=SQLNCLI11.1;Data Source=(local);Integrated Security=SSPI; Initial Catalog=AdventureWorksDW2012</ConnectionString> <ImpersonationInfo> <ImpersonationMode>ImpersonateCurrentUser</ImpersonationMode> </ImpersonationInfo> <Timeout>PT0S</Timeout> </DataSource> </ObjectDefinition> </Create>
/* Step 1 */ CREATE MINING MODEL [NBSample] ( CustomerKey LONG KEY, Gender TEXT DISCRETE, [Number Cars Owned] LONG DISCRETE, [Bike Buyer] LONG DISCRETE PREDICT ) USING Microsoft_Naive_Bayes Go /* Step 2 */ INSERT INTO NBSample (CustomerKey, Gender, [Number Cars Owned], [Bike Buyer]) OPENQUERY([Adventure Works DW2012],'Select CustomerKey, Gender, [NumberCarsOwned], [BikeBuyer] FROM [vTargetMail]') /* */ SELECT * FROM [NBSample].CONTENT /* */ SELECT * FROM [NBSample_Structure].CASES /* Step 3*/ SELECT FLATTENED MODEL_NAME, (SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT], [PROBABILITY], VALUETYPE FROM NODE_DISTRIBUTION) AS t FROM [NBSample].CONTENT WHERE NODE_TYPE = 26
MongoDB #13
>db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)
{ _id: ObjectId(7df78ad8902c) title: 'MongoDB Overview', description: 'MongoDB is no sql database', by_user: 'user1', url: 'http://www.site.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 100 }, { _id: ObjectId(7df78ad8902d) title: 'NoSQL Overview', description: 'No sql database is very fast', by_user: 'user1', url: 'http://www.site.com', tags: ['mongodb', 'database', 'NoSQL'], likes: 10 }, { _id: ObjectId(7df78ad8902e) title: 'Neo4j Overview', description: 'Neo4j is no sql database', by_user: 'Neo4j', url: 'http://www.neo4j.com', tags: ['neo4j', 'database', 'NoSQL'], likes: 750 },
> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : 1}}}]) { "result" : [ { "_id" : "user1", "num_tutorial" : 2 }, { "_id" : "Neo4j", "num_tutorial" : 1 } ], "ok" : 1 } >
select by_user, count(*) from mycol group by by_user
عبارت | توضیحات | مثال |
$sum | مقدار تعیین شده از همه سندهای مجموعه را جمع میکند. | db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}]) |
$avg | میانگین همه مقادیر بدست آمده از سندهای مجموعه را محاسبه میکند. | db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}]) |
$min | کمترین مقادیر مشابه را از همه سندهای مجموعه، بر میگرداند. | db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}]) |
$max | بیشترین مقادیر مشابه را از همه سندهای مجموعه، بر میگرداند. | db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}]) |
$push | یک مقدار را در سند نتیجه، در یک آرایه درج میکند. | db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}]) |
$addToSet | یک مقدار را در سند نتیجه در یک آرایه درج میکند، اما مقدار تکراری ایجاد نمیکند. | db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}]) |
$first | اولین سند از اسناد را برطبق گروه بندی بر میگرداند. معمولا این عبارت بعد از عبارتهای مرتب سازی مرحلهای استفاده میشود. | db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}]) |
$last | آخرین سند از اسناد را برطبق گروه بندی بر میگرداند. معمولا این عبارت بعد از عبارتهای مرتب سازی مرحلهای استفاده میشود. | db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}]) |
- $project : برای انتخاب چندین فیلد از یک مجموعه استفاده میشود.
- $match : این یک عملگر فیلترگذاری است که میتواند میزان اسنادی را که بعنوان ورودی در مرحله بعد گرفته میشوند، کاهش دهد.
- $group : این همان تابع جمعی است که در بالا توضیح داده شد.
- $skip : توسط این عبارت، در یک لیست بدست آمده (نتیجه)، میتوانید از لیست اسناد بصورت روبه جلو صرفنظر کنید.
- $limit : این عبارت تعداد اسناد را توسط عدد گرفته شده، از موقعیت فعلی برای نمایش محدود میکند.
- $unwind : این عبارت برای باز کردن (unwind) سندی که از آرایهها بهره گیری میکند استفاده میشود. وقتی از آرایه استفاده میکنید، داده از نوع پیش پیوست (Pre-joined) است و با این نوع داده، این عمل برای داشتن سندهای اختصاصی نا تمام خواهد ماند. بنابراین با این مرحله میتوانید میزان اسناد را برای مرحله بعد افزایش دهید.