Olap (پردازش تحلیلی برخط) و چگونگی کارکرد آن (Online analytical processing)

MS SQL Server یکی از سیستم های مدیریت بانک های اطلاعاتی رابطه ای (Relational) است که توسط شرکت مایکروسافت ارائه شده است  . SQL Server از مدل سرویس دهنده – سرویس گیرنده (Client/Server) تبعیت می نماید . در این مدل ، درخواست های (InQuery) سرویس گیرندگان برای سرویس دهنده ارسال و در سمت سرویس دهنده بررسی و آنالیز می گردند . در ادامه ، پردازش های‌ مورد نیاز  بر روی اطلاعات ذخیره شده در بانک های اطلاعاتی انجام و در نهایت ، نتایج برای سرویس گیرنده ارسال خواهد شد .MS SQL Server با استفاده از  مجموعه عناصری ( Components ) که به صورت هدفمند اجراء می­گردند ، قادر به تامین نیازها و درخواست ها  از مخازن داده (Data Storages) می باشد . مخازن داده در SQL Server به دو روش زیر مدیریت می گردند :

• OLTP ( برگرفته از  OnLine Transaction Processing System )
• OLAP ( برگرفته از OnLine Analytical Processing System )

در مدل OLTP ، مخازن داده به صورت جداول رابطه ای که عموما” به جهت جلوگیری از تکرار و ناهمگونی اطلاعات به صورت هنجار (Normalize) درآمده اند ، سازماندهی می شوند. این نوع از بانک های اطلاعاتی برای درج و تغییر سریع اطلاعات توسط چندین کاربر بطور همزمان مناسب می باشند . در مدل OLAP مخازن داده جهت تجزیه و تحلیل و خلاصه سازی حجم زیادی از اطلاعات سازماندهی می شوند . مخازن داده و ارتباط بین اطلاعات در این مدل توسط SQL Server مدیریت می گردد .

یکی از اهداف مهم سیستم های مدیریت بانک های اطلاعاتی ، قابلیت رشد و توسعه (Scalability) است .  MS SQL Server مجموعه ای از پتانسیل ها را به منظور تامین هدف فوق ارائه نموده است که به برخی از مهمترین آنها اشاره می گردد :

• قابلیت کار با بانکهای اطلاعاتی حجیم (در حد ترابایت)
• قابلیت دسترسی هزاران کاربر بطور همزمان به بانک اطلاعاتی
• قابلیت خود سازگاری (Self Compatibility) . با استفاده از ویژگی فوق ، منابع مورد نیاز هر کاربر (نظیر حافظه ، فضای دیسک و …) به محض اتصال به سرور (Log in) به صورت اتوماتیک به وی تخصیص داده می شود و  پس از Log off ، منابع اختصاص یافته به منظور استفاده سایر کاربران آزاد  می شوند .
• قابلیت اعتماد و در دسترس بودن (Reliability) . با استفاده از ویژگی فوق می توان بسیاری از فعالیت های مدیریتی را بدون توقف سرور انجام داد (نظیر BackUp) .
• برخورداری از سطوح امنیتی بالا . بدین منظور اعتبار سنجی کاربران توسط SQL با اعتبار سنجی ویندوز تجمیع می گردد . در چنین مواردی ، ضرورتی به تعریف کاربر در MS SQL نخواهد بود و اعتبار سنجی وی توسط ویندوز انجام خواهد شد .
• پشتیبانی از حجم بالائی از حافظه در سرور (در نسخه 2000 تا 64 گیگابایت و در نسخه 2005 متناسب با حافظه ای که سیستم عامل از آن حمایت می نماید ) .
• استفاده از چندین پردازنده به صورت موازی (در نسخه 2000 تا 32 پردازنده همزمان و در نسخه 2005 محدودیتی وجود ندارد )
• پشتیبانی از لایه ها و سوکت های امنیتی نظیر SSL ، خصوصا” جهت استفاده در وب .

یکی دیگر از ویژگی های مهم سیستم های مدیریت بانک های اطلاعاتی‌ ، ایجاد تسهیلات لازم به منظور مدیریت بانک های اطلاعاتی است .  SQL Server با ارائه برنامه های جانبی نظیر Enterprise Manager  استفاده و مدیریت بانک های اطلاعاتی را آسان نموده است . MS SQL Server بطور اتوماتیک در Active Directory ثبت می شود (Register) ، بنابراین کاربران شبکه به راحتی می توانند  آن را در Active Directory جستجو و در صورت نیاز به آن متصل شوند . همچنین ،  MS SQL Server توسط IIS پشتیبانی می گردد و  مرورگرها با استفاده از پروتکل HTTP  قابلیت استفاده از آن را خواهند داشت .

از جمله نکات مهم در خصوص MS SQL Server ، اجرای آن به صورت یک سرویس است . بنابراین ، در صورتی که کاربری به ماشینی که MS SQL Server بر روی آن اجراء شده است ، Log on نکرده باشد ، همچنان سیستم در دسترس کاربران خواهد بود . علاوه بر این ، می توان از سیستم مانیتورینگ ویندوز به منظور مانیتورینگ SQL Server استفاده نمود . یکی از مهمترین و شاخص ترین ویژگی های MS SQL Server که از نسخه 2000  در آن ایجاد شده است ، امکان نصب چندین نسخه SQL بر روی یک ماشین می باشد (Multi Instance) ، بطوریکه هر یک از نسخه ها  فایل های باینری مخصوص به خود را داشته و بطور جداگانه مدیریت و راهبری می گردند ، ولی تمام نسخه ها بطور همزمان اجرا می شوند (دقیقا” مشابه این است که چندین نسخه بر روی چندین کامپیوتر نصب شده باشد ) .

با توجه به این که نسخه (Instance) قراردادی فاقد نام است و سایر نسخه ها می بایست دارای نام باشند به آنها نسخه های دارای نام (Named Instance) می گویند . نسخه های دارای نام را می توان هر زمان نصب نمود (قبل  و یا بعد از نسخه قراردادی) .  ابزارهای همراه SQL نظیر SQL Enterprise Manager یک مرتبه نصب خواهند شد و در تمامی نسخه های SQL به صورت مشترک استفاده خواهند شد .

تاکنون نسخه های متعددی از  MS SQL Server ارائه شده است . استفاده از نسخه های  2000 و 2005  بیش از سایر نسخه ها ، متداول است . هر یک از نسخه های MS SQL Server ، در مدل های مختلف ( با توجه به نوع استفاده و اندازه سازمان متقاضی ) ، ارائه شده اند که در یک مقاله جداگانه به بررسی‌ ویژگی هر یک از آنها خواهیم پرداخت.

مقدمه :

امروزه پس از جمع آوری و بدست آوردن اطلاعات  ، تجزیه و تحلیل داده ها ، یکی از مهمترین  کارهای بانک های اطلاعاتی می باشد . بر همین اساس اکنون یکی از مهمترین معیار تشخیص بانک اطلاعاتی برتر ، امکانات تجزیه و تحلیل  داده ها در آن بانک می باشد .

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

درسالهای گذشته و قبل از مطرح شدن عملیات Olap ، نگهداری اطلاعات گذشته یا انبار کردن داده ها مطرح نبود  اما اکنون  جهت تحلیل داده ها برای انجام عملیات Olap نیازمند انبار کردن داده ها می باشیم .

جهت بررسی به عملکرد Olap میتوان  به نرم افزار های پایگاه داده قدرتمند مانند  Sql Server  یا Oracle مراجعه نمود و با نحوه عملیات Olaping آشنا شد.

بر همین اساس از آنجا که کار با Sql Server راحت تر و در عموم بیشتر کاربرد دارد و همچنین آشنایی با آن نزد برنامه نویسان پایگاه داده بیشتر است ، بحث Olaping در این مقاله در Sql server 2000 مطرح می گردد.

درک مفاهیم انبار کردن داده ها :

انبار کردن داده ها هنر کنار هم قرار دادن داده ها به روشی است که امکان تجزیه و تحلیل و استخراج  داده های عملیاتی (Operation Data) را فراهم آورد . داده های عملیاتی به داده هایی گفته می شود که سیستم های تولیدی برای عملیات و راهبری فعالیت های کاری به آن نیاز دارند .

در زمانهای نه چندان دور داده های جمع آوری شده درسیستم های عملیاتی برای نیازهای روز مره بکار رفته و بر اساس محدودیت های کاری یا قانونی بایگانی وسپس دور انداخته می شدند .

پس از آن شرکت های هوشمند این سوال را مطرح کردند که چرا باید این داده ها را دور انداخت ؟

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

برای روشن شدن مطلب به یک مثال توجه شود :

فرض می کنیم در یک شرکت تولیدی گزارش های عملیات کاری شرکت در دسترس است اما خوانایی کافی ندارد . به عبارت دیگر مجبوریم برای جمع آوری گزارش های داده ای منتظر بمانیم ،

سپس از آنها نسخه های چاپی تهیه کنیم و با مطالعه دقیق اطلاعات مورد نظر خود را بیابیم .

بدیهی است این روش برای مدل سازی شرایط کاری و تصمیم گیری های سریع مناسب نیست .

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

این داده ها را می توان  در قالب طبقه بندی های مختلف نسبت به زمان جمع بندی کرد .

با این انبار داده ای می توانیم به سوالاتی نظیر مقایسه بین فروش یک محصول در دو منطقه متفاوت ، نوع فروش در دو فروشگاه متفاوت ، توانایی ارتباط دادن فروش با سایر عوامل ، مقایسه فروش هفتگی یک محصول در یک طبقه با همان محصول در هفته گذشته و . . .  به شکل Online انجام دهیم .

انبار های داده معمولا مبتنی بر زمان هستند و شامل اجزای متعددی می باشند : جداول Fact ، ابعاد (Dimension) و سطوح جمع بندی . در این انباره ها انواع داده مربوط به سیستم های مختلف با فرمت های متفاوت نگهداری می شود و از آنها اطلاعات مفیدی استخراج می شود .

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

 درک مفاهیم انبار داده خاص  (Data Marts):

انبارهای داده ای خاص بطور روز افزونی محبوبیت می یابند . یک انبار داده ای خاص در حقیقت یک انبار داده ای کوچک است . تصور کنید می خواهید بهترین انبار داده ای را برای شرکت خود بسازید . چند کاربر بطور همزمان از آن استفاده خواهند کرد ؟ چه مقدار داده در آن نگهداری خواهد شد ؟

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

نماینده فروش یک بخش علاقه مند به داده های یک مشتری خاص است .شما باید بتوانید برشی (Slice) از این اطلاعات را آماده ودر اختیار وی قرار دهید . با در اختیار داشتن ابزار Sql Server 2000 می توان براحتی انبار های داده خاص را ایجاد و نگهداری کرد .

 ایجاد انبار داده ها :

برای ساخت یک انبار داده چند مرحله را باید دنبال کنیم . هر یک از این مراحل با استفاده از یکی از ابزار های Sql Server 2000 انجام می شود . نخستین مرحله طراحی انبار داده و مشخص کردن محل دریافت داده ها است . این مرحله یک کار تیمی است و زمان نسبتا زیادی را طلب می کند .

 مرحله اول : ایجاد محل نگهداری داده ها

پس از آنکه محل دریافت داده ها  مشخص شد نیازمند محل نگهداری موقت داده ها می باشیم . این محل ممکن است یک کپی مجزا از Sql Server و یا یک پایگاه داده مجزا می باشد . بسیار محتمل است که داده ها برای بارگذاری در انبار داده ها مهیا نباشد . بنابراین نگهداری موقت آنها تا زمان مناسب برای تبدیل به فرمتهای صحیح و فیلتر سازی اطلاعات مورد نیاز ایده بسیار خوبی است .

 مرحله دوم : دریافت داده ها از مخزن  ( Legace ) با یک سیستم Oltp ( با پردازش تراکنش) :

قدم دوم همکاری با تعدادی دیگر از راهبران و توسعه دهندگان سیستم در شرکت است . بنابر این باید برای هر بخش از داده ها که مایل هستیم در انبار داده ای نگهداری کنیم مجوز های لازم را دریافت نماییم . همچنین باید یک برنامه منظم برای تغذیه داده های سایر سیستم ها به انبار داده ای خودمان ترتیب دهیم . تکثیر Sql Server و پرس و جوهای توزیع شده ناهمگن می توانند برای اتصال به این سیستم ها و دریافت داده ها مفید واقع شود .

 مرحله سوم : تبدیل داده ها در صورت لزوم

بسیار محتمل است داده های مورد نظر،  فرمت دقیق مورد نیاز ما را  دارا نباشند و برای تغییر فرمت ، نیاز  به ترکیب و یا تجزیه برخی از ستونهای داده ای داشته باشیم .

یک نمونه کلاسیک ستون ، نام است . اگر پایگاه داده را نرمال سازی کنیم ممکن است نام ، نام خانوادگی، عنوان و . . .  را داشته باشیم . اما اگر فرض شود انبار داده ای صرفا نیاز به یک فیلد نام مشخص دارد  در این حالت می توان با ترکیب انواع فیلدهای نام ، داده مورد نظر را بدست آورد .

سرویسهای تبدیل داده (DTS) معمولا در این مرحله بکار می روند. Dts برای اتصال به منبع داده ای، OLEDB را بکار می برد . بنابر این این مرحله و مرحله دوم اغلب می توانند ترکیب شوند .

 مرحله چهارم : بارگذاری داده های تبدیل شده در انبار داده

پس از تبدیل داده ها به فرمت مناسب ، آنها را در انبار داده ای بارگذاری می نماییم . در این مرحله نیز می توانیم DTS ، برنامه های BCP  و یا درج انبوه (Block Insert) را بکار بریم .

مرحله پنجم : ساخت و بروز رسانی مکعب های  Olap

در سرویسهای تجزیه و تحلیل  پس از ایجاد جداول  Fact  پایه می توانیم جمع بندی و آماده سازی پاسخ سوالات رایج زمینه کاری خودمان را انجام دهیم . این کار احتمالا توسط مکعب های Olap و سرویسهای تجزیه و تحلیل مایکروسافت انجام می گیرد . با این کار سرعت دریافت پاسخ سوالات افزایش می یابد ، حتی اگر پرس و جو ها بر روی حجم وسیعی از اطلاعات و تلفیق یک یا چند جداول Fact و جداول ابعاد انجام شود .

• 1- مقدمه – 1
• 2- تاریخچه Sql Server
• 2-1- Sql Server چیست ؟ – 2
• 2-2- اهداف Sql Server
• 3- آشنایی با Analysis Services و نحوه نصب آن – 4
• 4- نصب پایگاه داده و منبع آن – 6
• 5- نحوه ایجاد Cube
• 6- پیمانه چیست؟ – 11
• 6-1- نحوه اضافه کردن بعد به مکعب – 14
• 6-2- طراحی منبع و پردازش مکعب – 16
• 6-3- ایجاد مکعب پدر و فرزند – 18
• 6-4- ویرایش اطلاعات مکعب – 20
• 7- سرجمع (Aggregation) چیست؟ – 23
• 7-1- درک مفاهیم انبار کردن داده ها – 24
• 7-2- درک مفاهیم انبار داده خاص (Data Marts)
• 7-3- ایجاد انبار داده ها – 26
• 8- سرویسهای Meta data
• 9- عضو محاسبه شده Calculated Member
• 9-1- ایجاد عضو محاسبه شده – 28
• 9-2- چگونگی مشاهده اطلاعات عضو محاسبه شده – 33
• 9-2-1- Roles در مکعب چیست ؟ – 34
• 9-2-2- نحوه ایجاد Role در مکعب – 35
• 9-2-3- نحوه ایجاد Role در بانک اطلاعات – 37
• 9-2-4 – خواص اعضا (member property)
• 10- نحوه ایجاد Member Property
• 11- ایجاد ابعاد مجازی – 39
• 12- استخراج داده چیست ؟ ( Olap Data Mining)
• 13- نحوه ایجاد Data Mining Model
• 14- فهرست منابع – 47