هوش تجاری (Business Intelligence)
به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری
هوش تجاری (Business Intelligence)
به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاریثبت رویدادها در SSIS
زمانی که با یک پکیج بزرگ و پیچیده سروکار داریم بررسی روند اجرای کامپننت‌ها و خطایابی کمی سخت می‌شود. در چنین شرایطی وجود یک فایل یا جدولی که Log پکیج را در هر بار اجرا ذخیره کند ضروری است. در SSIS این امکان وجود دارد که از رویدادهای مختلف Log گرفته شود. این کار از دو طریق امکان پذیر است؛ مدیریت رخدادها با تنظیمات دستی و مدیریت رخدادها توسط SSIS Loging
در این مقاله روش اول را شرح می‌دهم و به روش دوم در مقاله‌ای جداگانه خواهم پرداخت.
مدیریت رخدادها به صورت دستی
اولین روشی که قصد دارم به آن بپردازم، ذخیره Log به صورت دستی است. در این روش باید از سربرگ Event Handlers استفاده کنید. در این قسمت باید از منوی باز شونده‌ی Executable کامپننت مورد نظر و سپس از منوی باز شونده‌ی Event handler رخداد مورد نظر را انتخاب کنید.
برای انجام این کار مراحل زیر را انجام دهید.
۱- ابتدا توسط کوئری زیر یک جدول Log ایجاد کنید. از این جدول برای ذخیره خروجی رویدادها استفاده می‌شود.
CREATE TABLE [dbo].[Log]( [ID] [int] IDENTITY(1,1) NOT NULL, [PackageID] [uniqueidentifier] NULL, [Error] [nvarchar](max) NULL, [Source] [nvarchar](100) NULL, [PackageName] [nvarchar](100) NULL ) ON [PRIMARY] GO |
۲- در Control Flow با استفاده از Execute SQL Task و کوئری زیر یک پکیج ساده ایجاد کنید
select * from shop.Test
۳- به سربرگ Event Handlers رفته و از منوی باز شونده‌ی Executable کامپننت Execute SQL Task و از منوی باز شونده‌ی Event handler رخداد onError را انتخاب کنید. برروی Click here to create an ‘onError’ event handler for executable ‘Execute SQL Task’ کلیک کنید تا فعال شود.
۴- از جعبه ابزار یک Execute SQL Task به این قسمت اضافه کنید.
۵- برروی Execute SQL Task دوبار کلیک کنید تا صفحه مربوط به تنظیمات آن باز شود. از کوئری زیر در قسمت SQL Statement استفاده کنید.
insert into Log(PackageID,Error,Source,PackageName) values (?, ?, ?, ?) |
۶- از لیست سمت چپ به قسمت Parameter Mapping رفته و مطابق با شکل زیر آن را تنظیم کنید.
توسط کلید F5 پکیج را اجرا کنید. از آنجایی که جدولی با نام Test وجود ندارد پکیج با خطا مواجه می‌شود و در این لحظه کامپننت Execute SQL Task که در سربرگ Event Handlers ایجاد شده است شروع به کار می‌کند و از طریق پارامترهایی که به آن تخصیص دادیم مشخصات مورد نظر را به جدول Log ساخته شده در ابتدای آموزش انتقال می‌دهد.
در پایان جدول Log به شکل زیر می‌باشد.
پشتیبان‌گیری خودکار از بانک‌اطلاعاتی توسط MaintenancePlan Wizard
فایل پشتیبان (Backup) یک جزء اساسی و جدا نشدنی در حفظ و نگه داری داده‌ها است. بانک‌های اطلاعاتی که وظیفه ذخیره داده‌ها را بر عهده دارند نیز از این قاعده مستثنی نیستند و در واقع می‌توان گفت که فایل‌های پشتیبانی در بانک‌های اطلاعاتی حیاتی هستند.
روش‌های گوناگونی برای گرفتن فایل پشتیبان از بانک اطلاعاتی وجود دارد و گاهی برای انجام اینکار نیاز به کوئری‌های پیچیده است. در این پست قصد دارم به یک روش ساده جهت پشتیبان گیری خودکار بپردازم.
در 2008 SQL Server برای اینکار از Maintenance Plan Wizard استفاده می‌کنیم. البته Maintenance Plan Wizard کارهای مختلفی انجام می‌دهد که یکی از این کارها Backupگیری است.
برای انجام این کار مراحل زیر را انجام دهید.
۱- ابتدا مطمئن شوید که SQL Server Agent در حالت Start قرار دارد.
۲- در Object Explorer به قسمت Maintenance رفته و برروی Maintenance Plans کلیک راست کنید و از منوی باز شده Maintenance Plan Wizard را انتخاب نمایید.
۳- در صفحه‌ی Select Plan Properties نام دلخواه خود را در قسمت Name وارد کرده و گزینه‌ی Separate schedules for each task را انتخاب کنید.
با انتخاب این گزینه می‌توانید در ادامه زمانبندی اجرای عملیات پشتیبان گیری را مدیریت کنید.
۴- برروی Next کلیک کنید.
۵- در صفحه‌ی Select Maintenance Tasks گزینه Back Up Databases(Full) را انتخاب کنید.
همانطور که مشاهده می‌کنید، Maintenance Plans کارهای مختلفی انجام می‌دهد که یکی از آن‌ها پشتیبان گیری است.
۶- دوبار Next کنید تا به صفحه‌ی Define Back Up Database (Full) Task برسید. در این صفحه تنظیمات مربوط به ایجاد فایل پشتیبان قرار دارد.
این تنظیمات شامل، انتخاب بانک اطلاعاتی، محل ذخیره سازی فایل پشتیبان و زمانبندی پشتیبان‌گیری است. مطابق شکل زیر تنظیمات را انجام دهید و زمانبندی پشتیبان گیری را با کلیک برروی Change انجام دهید.
۷- مراحل را ادامه دهید تا پس از کلیک بر روی Finish مراحل کار به شکل زیر تمام شود.
حال در زمان و تاریخ مشخص شده از بانک اطلاعتی شما فایل پشتیبان در آدرس تعیین شده، گرفته می‌شود.
مقایسه رکوردهای دو جدول و ثبت نتایج مقایسه
برای مقایسه اطلاعات دو جدول راه‌های زیادی وجود دارد. یکی از این روش‌ها را در اینجا شرح دادم. اما اگر بخواهیم پس از مقایسه اطلاعات دو جدول آن‌ها را دقیقا مشابه هم کنیم، باید از کوئری‌های پیچیده جهت انتقال اطلاعات و یکسان سازی استفاده کنیم.
برای حل این مسئله روش بهتر و ساده‌تری نیز وجود دارد. TableDiff این امکان را برای ما فراهم می‌کند که بتوانیم رکوردهای دو جدول را با هم مقایسه و مطابق کنیم. در واقع TableDiff با مقایسه‌ی تمامی رکوردهای جدول A با تمامی رکوردهای جدول B اختلافات را شناسایی ذخیره می‌کند.
جهت استفاده از TableDiff مراحل زیر را انجام دهید.
۱- ابتدا باید از نصب بودن TableDiff در SQL اطمینان حاصل کنیم. برای اینکار به آدرس C:\Program Files\Microsoft SQL Server\100\COM\ رفته تا فایل tablediff.exe را مشاهده کنید.
۲- فعال کردن دسترسی به تابع xp_cmdshell.
اجرای دستورات tablediff از طریق تابع xp_cmdshell امکان پذیر است. برای فعال کردن این تابع از دستور زیر استفاده کنید.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO |
۳- دو جدول مشابه با نام‌های A و B در یک بانک اطلاعاتی به نام Shop ایجاد کنید. در جدول A چند سطر رکورد وارد کنید.
دقت داشته باشید که این جداول حتما باید فیلد‌های یکسان داشته باشند.
۴- با اجرای کوئری زیر دو جدول A و B با هم مقایسه می‌شوند و نتیجه‌ی آن در فایل Result.sql در درایو C ذخیره می‌شود.
exec master..xp_cmdshell'"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver (local) -sourcedatabase Shop -sourcetable A -destinationserver (local) -destinationdatabase Shop -destinationtable B -f C:\ Result.sql' |
۵- با اجرای کوئری ساخته شده در فایل Result.sql اطلاعات جدول B همانند اطلاعات جدول A خواهد شد.
ایجاد یک Package ساده در SSIS
در این پست قصد دارم برای آشنایی بیشتر و بهتر با SSIS یک پکیج ساده ایجاد کنم. اگر تا به حال از SSIS استفاده نکرده اید بهتر است مراحل اولیه ایجاد یک پروژه را از اینجا مطالعه کنید.
۱- یک پروژه‌ی جدید با نام Learn-SSIS1 ایجاد کنید.
در هر پروژه‌ی SSIS پیش از هر چیز باید نوع ارتباط و منبع داده‌های خود را مشخص کنیم. از آنجایی که در این پروژه قصد استفاده از بانک اطلاعاتی SQL را داریم، باید از اتصال دهنده‌ی OLEDB استفاده کنیم.
۲- در قسمت Connection Managers راست کلیک کرده و NEW OLEDB را انتخاب نمایید.
۳- از صفحه‌ی باز شده بر روی New کلیک کنید و مطابق شکل زیر تنظیمات مربوط به Server Name و Provider و بانک اطلاعاتی را انجام دهید. سپس برروی ok کلیک کنید.
همان طور که در تصویر بالا مشخص است از بانک اطلاعاتی AdventureWorksDW2008 استفاده شده. پیشتر در اینجا نحوه افزودن انبار داده AdventureWorksDW2008 را شرح داده‌ام. چنانچه AdventureWorksDW2008 را در SQL خود ندارید در صورت نیاز از اینجا کمک بگیرید.
۴- فایل learn-ssis1.sql را دانلود کنید.
این فایل شامل یک کوئری ساده جهت بروز رسانی یکی از رکوردهای جدول DimProductCategory است.
۵- از جعبه ابزار بر روی Execute SQL Task دوبار کلیک کنید تا به ناحیه Control Flow منتقل شود.
۶- برروی کامپننت اضافه شده دو بار کلیک کنید تا صفحه‌ی Execute SQL Task Editor باز شود. در این صفحه تنظیمات مربوط به کامپننت انجام می‌شود.
۷- تنظیمات مربوط به کامپننت را به شکل زیر انجام دهید.
ConnectionType=OLE DB
Connection=localhost.AdventureWorksDW2008
SQLSourceType=File connection
FileConnection=learn-ssis1.sql
برای تنظیم FileConnection ابتدا New connection را انتخاب کنید و از صفحه باز شده Existing file را انتخاب و سپس توسط Browse به محل فایل دانلود شده رفته و آن را انتخاب کنید.
در نهایت Execute SQL Task Editor باید به شکل زیر باشد.
کار تمام است! فقط کافیست کلید F5 را بزنید.
برای دیدن نتیجه به جدول DimProductCategory در SQL مراجعه کنید.
معرفی بخش‌های مختلف Solution Explorer در SSAS
در SSAS قسمت‌های مختلفی وجود دارد که هر کدام وظایف جداگانه‌ای را بر عهده دارند. پیش از ایجاد یک پروژه‌ی SSAS درSQL Server Business Intelligence Development Studio باید آگاهی کافی از اجزاء مختلف آنالیز سرویس داشته باشید. برای درک بهتر و آشنایی بیشتر با هر یک از این اجزاء ، در این پست سعی می‌کنم بخش‌های مختلف SSAS را بطور خلاصه و به زبان ساده شرح دهم.
Data Sources
اولین قدم برای شروع یک پروژه، ایجاد منبع داده جهت دسترسی به اطلاعات است. برای این کار ازDate Source استفاده می‌کنیم. DTS توسط Providerهای مختلف این امکان را فراهم می‌کند تا با منبع داده‌های مختلفی همچون SQL، Oracle و ... ارتباط برقرار کنیم.
Data Source Views
پس از آنکه ارتباط با منبع داده‌ها برقرار شد، جهت خواندن اطلاعات موجود در جداول و Viewها از DSV استفاده می‌شود. هر گونه تغییر در فیلد‌های جداول هیچ گونه تاثیری در ساختار جداول نخواهد داشت و تغییرات اعمال شده تنها در SSAS قابل مشاهده و استفاده است.
Cubes
هر پروژه‌ی OLAP در SSAS یک یا چند مکعب داده (Cube) را شامل می‌شود. این مکعب‌ها در این قسمت تعریف می‌شوند. هر Cube خود شامل بخش‌های مختلفی است که در پست‌های بعدی به آن می‌پردازم.
Dimensions
به معنای بُعد می‌باشد که که ابعاد مختلف Cube را تشکیل می‌دهند. تمامی ابعاد یک پروژه در این قسمت تعریف می‌شوند.
Mining Structures
برای ساخت پروژه‌های داده کاوی و استفاده از مدل‌های مختلف داده‌کاوی باید از این قسمت استفاده کرد.
Roles
مسائل امنیتی مانند نقش‌ها و دسترسی‌ها برای OLAP و Data Mining در این قسمت مدیریت می‌شوند. این دسترسی‌ها می‌توانند بر روی Cubeها، Dimensionها و Mining Structureها اعمال شوند.
Assemblies
در SSAS از زبان‌های برنامه نویسی MDX و DMX جهت نوشتن عبارات استفاده می‌شود. گاهی نیاز است که از سایر زبان‌های برنامه نویسی نیز در این عبارات استفاده شود. جهت استفاده از این توابع در عبارات MDX و DMX از این قسمت استفاده می‌شود.
Miscellaneous
در صورتی که در SSAS فایل خاصی را اضافه کرده باشیم، در این قسمت دسته بندی می‌شود. این فایل‌ها از هر نوعی می‌توانند باشند.