هوش تجاری (Business Intelligence)
به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاری
هوش تجاری (Business Intelligence)
به اشتراک بگذاریم برای یادگیری، یاد بگیریم برای به اشتراک گذاریRunning Total
مساله:
جدولی در یک دیتابیس بانکی وجود دارد که اطلاعات تراکنش های مشتریان بانک در آن ذخیره می شود. به گزارشی نیاز داریم که لیست تراکنش های مشتریان به همراه موجودی حساب مشتری را پس از هر تراکنش نمایش دهد.
تصویر 1
راه حل:
تکنیکی به نام Running Total وجود دارد که از آن برای محاسبه سرجمع مقادیر یک ستون از اولین سطر تا سطر جاری استفاده می شود.
استفاده از این تکنیک یکی از بهترین راه کارهای سیستم های مالی، انبارداری و ... می باشد. روش های متفاوتی برای استفاده از تکنیک Running Total وجود دارد که در این مقاله با روش های Sub Query، Join و Window Function به صورت Set Based بررسی می شود.
کوئری های این تمرین برروی جدولی شامل تراکنش های بانکی انجام میشود.
ستون UserID شناسه کاربری، ستون TransactionID شناسه تراکنش و ستونQuantity مبلغ تراکنش انجام شده را در خود ذخیره می کنند. این جدول دارای اطلاعات تراکنش های دو کاربر با شناسه های 1 و 2 است که هر کدام تعداد 10 تراکنش داشته اند.
تصویر 2
دیتابیسی با نام Test (یا هر اسم دلخواه دیگر) ساخته و کد زیر را اجرا کرده تا جدول مورد نظر ساخته شود.
- USE Test;--DataBase Name
- IF OBJECT_ID('dbo.TransactTable', 'U') IS NOT NULL DROP TABLE dbo.TransactTable;
- CREATE TABLE dbo.TransactTable
- (
- UserID INT NOT NULL
- , TransactionID INT NOT NULL
- , Quantity INT NULL
- , CONSTRAINT PK_TransactTable PRIMARY KEY CLUSTERED
- (
- UserID ASC
- , TransactionID ASC
- )
- );
- INSERT INTO dbo.TransactTable (UserID, TransactionID, Quantity) VALUES
- (1, 1, 50),( 2, -7),(1, 3, 10),(1, 4, 3), (1, 5, -2),(1, 6, 3),(1, 7, -1)
- ,(1, 8, 17),(1, 9, -6),(1, 10, 1),(2, 1, 10),(2, 2, 5),(2, 3, 4),(2, 4, 7),
- (2, 5, -9),(2, 6, 90),(2, 7, -10),(2, 8, -5),(2, 9, -7),(2, 10, -50);
کد ساخت جدول
Sub Query:
در این روش کوئری درونی(کوئریی که خروجی آن بوسیله کوئری دیگر استفاده می شود) UserIDهای برابر با UserIDهای کوئری بیرونی(کوئریی که از خروجی کوئری درونی استفاده می کند) و TransactionIDهای کوچکتر از TransactionIDهای کوئری بیرونی را فیلتر کرده، سپس مجموع فیلدهای ستون Quantity را محاسبه می کند و در ستونی به نام Balance نمایش می دهد. این عملیات به ازای هر یک از رکوردهای کوئری بیرونی، یک بار انجام می شود.
- SELECT UserID
- , TransactionID
- , Quantity
- , (
- SELECT SUM(T2.Quantity)
- FROM dbo.TransactTable AS T2
- WHERE T2.TransactionID = T1.TransactionID
- AND
- T2.TransactionID <= T1.TransactionID
- ) AS Balance
- FROM dbo.TransactTable AS T1;
کوئری اجرای Running Total به وسیله Sub Query
Join:
انجام Running Total به وسیله Join مانند روش Sub Query می باشد. در این روش جدول TransactTable با خودش Join زده می شود و شرط Join آن UserIDهای برابر و TransactionIDهای بزرگتر مساوی جدول اول از TransactionIDهای جدول دوم می باشد. حاصل جوین جدول TransactTable با خودش بر اساس شرط های گفته شده، به ازای هر UserID و TransactionID جدول اول، TransactionIDهای کوچکتر مساوی آن UserID تکرار می شود. سپس سرجمع ستون Quantity محاسبه شده و بر اساس ستون های UserID و TransactionID دسته بندی(GROUP BY) می شود.
- SELECT T1.UserID
- , T1.TransactionID
- , T1.Quantity
- , SUM(T2.Quantity) AS Balance
- FROM dbo.TransactTable AS T1
- INNER JOIN dbo.TransactTable AS T2
- ON T2.UserID = T1.UserID
- AND
- T2.TransactionID <= T1.TransactionID
- GROUP BY T1.UserID
- , T1.TransactionID
- , T1.Quantity;
کوئری اجرای Running Total به وسیله Join
Window Function:
با آمدن Window Function به SQL، پیاده سازی Running Total بسیار آسان تر و کم هزینه تر شد. در این روش ستون Quantity را در تابع SUM قرار داده و در OVER، پارتیشن(PARTITION BY) را UserID قرار داده و ترتیب(ORDER BY) را بر اساس TransactionID اعمال میکنیم. محاسبه سرجمع، از اولین رکورد (UNBOUNDED PRECEDING) هر پارتیشن تا سطر جاری(CURRENT ROW) که سرجمع در حال درج در ستون Balance است، انجام می شود.
- SELECT UserID
- , TransactionID
- , Quantity
- , SUM(Quantity) OVER(PARTITION BY UserID ORDER BY TransactionID
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance
- FROM dbo.TransactTable ;
کوئری اجرای Running Total به وسیله Window Function
مقایسه Execution Planها:
در جدولی که کوئری های فوق بر روی آن اجرا شده است، ستون های UserID و TransactionID کلید اصلی می باشند.
لازم به ذکر است که هزینه اجرای کوئری های فوق، می تواند با افزایش تعداد تراکنش ها نسبت به تعداد کاربران و یا بالعکس، متغیر باشد.
منبع: برگرفته از کتاب Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions نوشته Itzik Ben-Gan
الگوی طراحی جداول ابعاد- slowly changing dimension
بیشتر اوغات مقادیر موجود در جداول ابعاد (Dimensions) ثابت هستند و تغییری در آن‌ها رخ نمی‌دهد. به عنوان مثال تغییر در نام ماه‌های سال تقریبا غیر ممکن است. اما برخی از اطلاعات قابل تغییر هستند، مانند نام یا نام خانوادگی و آدرس افراد.
جهت شرح چنین شرایطی از اصطلاح SCD یا slowly changing dimension استفاده میشود. SCD الگویی جهت طراحی جداول ابعاد است.
در برخی از گزارشات، زمانی که چنین تغییری در داده رخ دهد، می‌بایست تغییرات را به صورتی اعمال کرد که ردپای (تاریخچه تغییرات) آن مشخص باشد. به طور مثال در نظر بگیرید که در گزارشی میزان فروش شخصی ثبت شده باشد، این شخص پس از مدتی نام خود را تغییر می‌دهد، اگر نام شخص را بروزرسانی کنیم تمامی اطلاعات قدیمی با نام جدید نمایش داده میشود که در این صورت امکان رسیدن به نام قدیم از بین می‌رود.
SCD یک مفهموم جدید نیست بلکه نوع دیگری از طراحی می‌باشد که برای کمک به حل چنین مشکلاتی مفید واقع میشود.
سه نوع طراحی برای SCD وجود دارد.
نوع اول: در این نوع از طراحیSCD نیازی به رهگیری تغییرات نمی‌باشد. استفاده از حالت نرمال بروزرسانی جداول ابعاد بدون اضافه کردن سطر یا ستون خاصی به جدول جهت رهگیری وضعیت تغییرات در دادهها. گزارش با نام جدید نمایش داده میشود و نام قدیمی نادرست در نظر گرفته خواهد شد. همچنین در نوع اول تاریخچه تغییرات ثبت و رهگیری نمی‌شود.
نوع دوم: SCD نوع دوم کاملا متفاوت و برعکس نوع اول است. در این نوع تمامی تغییرات بدون اعمال تغییر روی دادهها ثبت و رهگیری میشود. برای انجام این کار سطر و ستون‌هایی به جدول مورد نظر اضافه میکنیم.
تاریخ شروع و تاریخ پایان به همراه یک کلید اصلی جدید جهت ثبت تاریخچه تغییرات به جدول اضافه میکنیم.
به تصویر زیر دقت کنید، به منظور ثبت تغییرات، جدول سمت چپ به جدول سمت راست تغییر می‌کند و توسط فرآیند ETL دادهها به شکل نمایش داده شده در جدول پایین درج و بروزرسانی می‌شوند.
نوع سوم: در این حالت پردازش جهت رهگیری تغییرات تا حدودی ساده شده به طوری که فقط نام فعلی و نام قدیمی به همراه تاریخ تغییر را در جدول ذخیره میکنیم. نام جدید با نام قدیمی جایگزین میشود و نام قدیمی در فیلد دیگری به همراه تاریخ تغییرات ثبت میشود. در نوع سوم SCD سطری اضافه نمی‌شود و فقط ستون‌هایی برای درج تاریخ تغییرات و مقدار قبلی افزوده میشود. در صورتی که نیاز به ثبت تاریخچه تمامی تغییرات باشیم، باید ستون های دیگری به جدول اضافه کنیم. از این نوع زمانی استفاده میشود که فقط نیاز به اطلاع از داده قبلی باشد.
استفاده از فرمت ساعت به عنوان Measure در Cube
فرض کنید بنا به نیاز سازمان باید اطلاعات مربوط به دوره‌های آموزشی و تعداد ساعات سپری شده هر یک از پرسنل در کلاس‌های آمورشی را در داشبورد نمایش دهید. می‌دانیم که برای اینکار ابتدا باید جداول Fact و Dimension مربوطه را در انبار داده طراحی و سپس مدل OLAP و Cube مورد نظر را ایجاد کنیم. به نظر می‌رسد برای اینکار مشکل خاصی وجود نداشته باشد و به سادگی این کار انجام گیرد اما با کمی دقت متوجه می‌شوید که برای ایجاد معیار (Measure) با فرمت زمان (DateTime) با مشکل مواجه هستید چراکه MSBI به شما اجازه نمی‌دهد تا از نوع DateTime به عنوان Measure استفاده کنید.
برای حل این مشکل مراحل زیر را انجام دهید.
1- ابتدا به جدول Fact خود فیلدی با نوع Float اضاقه نمایید.
2- از کوئری زیر برای تبدیل اطلاعات فیلد CourseTime_TimeFormat به فرمت Float استفاده کنید.
update [TimeMeasure].[dbo].[FactPersonnelCourse]
set [CourseTime]=convert(float,CourseTime_TimeFormat)
3- به SQL Server Business Intelligence Development Studio رفته و یک پروژه‌ی SSAS جدید با عنوان PersonnelCourse ایجاد کنید.
4- همانطور که در شکل زیر مشاهده میکنید، در هنگام انتخاب Measure فیلد CourseTime_TimeFormat نمایش داده نمی‌شود زیرا نوع آن بعنوان معیار قابل قبول نمی‌باشد و فقط فیلد CourseTime که نوع اعشاری دارد نمایش داده میشود.
5- پس از اتمام مراحل ساخت Cube پروژه را پردازش کنید و به صفحه‌ی Browser بروید.
6- معیارها و ابعاد مورد نظر خود را به محل نمایش انتقال دهید. همانطور که در شکل زیر مشاهده میکنید اطلاعات به صورت اعشاری و همانطور که در Fact ذخیره شده است نمایش داده میشود.
7- برای اینکه اطلاعات نمایش داده شده را به فرمت ساعت مشاهده کنید کافی است خصوصیت Format String معیار Course Time را به HH:MM تغییر دهید.
8- پروژه را مجدد پردازش کنید. همانطور که مشاهده میکنید اطلاعات به فرمت ساعت نمایش داده میشود.
با تغییر ابعاد گزارش دلخواه خود را مشاهده کنید.
واکشی دادهها از شیرپوینت و بارگذاری در SQL توسط SSIS
امروزه استفاده از شیرپوینت به عنوان ابزار ایجاد کننده وب سایت به دلیل سرعت بالا در ایجاد و راه‌اندازی بسیار گسترش پیدا کرده است و بسیاری از سازمان‌ها از آن استفاده می‌کنند. بنابراین می‌توان شیرپوینت را به عنوان منبع دادهها در نظر گرفت.
برای خواندن و بارگذاری داده‌های شیرپوینت در SQL Server از طریق SSIS چندین روش وجود دارد که در این مقاله به یکی از بهترین و ساده‌ترین آن‌ها که استفاده از SharePoint Web services است، می‌پردازم.
ابتدا باید Features مربوط به SharePoint List Source and Destination را از اینجا دریافت کنید.
پس از دانلود، مطابق تصاویر زیر مراحل نصب را انجام دهید.
بعد از اتمام مراحل نصب باید کامپننت‌های SharePoint List Source and Destination را به جعبه ابزار SSIS اضافه کنید. برای اینکار مراحل زیر را انجام دهید.
1- برنامه Business Intelligence Development Studio را باز کردهو مطابق تصویر زیر یک پروژه‌ی جدید SSIS ایجاد کنید.
2- از منوی Tools گزینه Choose Toolbox Items را انتخاب کنید.
پنجره Choose Toolbox Items باز میشود.
3- از پنجره بازشده به سربرگ SSIS Data Flow Items رفته و چک‌باکس مربوط به SharePoint List Source و SharePoint List Destination را انتخاب نمایید. برروی Ok کلیک کنید.
این دو کامپننت به قسمت General در جعبه ابزار SSIS اضافه می‌شوند. شما میتوانید هر یک از آن‌ها را به محل مناسب خود منتقل کنید.
آماده سازی یک لیست شیرپوینت جهت انجام یک مثال
1- ایجاد لیستی با عنوان TestSharePointList که شامل اطلاعات زیر باشد.
نام: کارمندان
ستون‌ها:
· شماره پرسنلی (نوع: عدد)
· نام کارمند(نوع: کاراکتر)
· جنسیت (انتخابی؛ انتخاب اول، زن انتخاب دوم، مرد)
ایجاد یک جدول در SQL مطابق با لیست ایجاد شده در شیرپوینت
1- وارد SSMS شده و یک بانک اطلاعاتی با نام TestDB ایجاد کنید.
2- مطابق شکل زیر یک جدول با نام SharePointData ایجاد کنید.
استخراج داده از لیست شیرپوینتی ساخته شده توسط SharePoint List Source
1- در این قسمت ابتدا باید یک اتصال دهنده شیرپوینتی ایجاد نمود. برای انجام اینکار مطابق تصاویر زیر عمل کنید.
این اتصال دهنده پس از نصب Features مربوط به شیرپوینت که مراحل نصب آن در ابتدای مقاله توضیح داده شد، به لیست Add SSIS Conection Manager اضافه میشود.
در صورتی که برای دسترسی به لیست‌های شیرپوینتی نیاز به دسترسی خاصی دارید باید در قسمت Custom Credentials نام کاربری و رمز عبور آن User مربوطه را وارد کنید.
2- از جعبه ابزار یک کامپننت Data Flow Tasks به پکیج خود اضافه کنید. مجدد از جعبه ابزار SharePoint List Source را درون Data Flow Tasks قرار دهید.
3- انجام تنظیمات مربوط به SharePoint List Source، برای اینکار برروی کامپننت SharePoint List Source دوبار کلیک کرده و مطابق تصاویر زیر عمل نمایید.
پس از وارد کردن آدرس مربوط به سایت لیست ساخته شده و نام آن در قسمت SiteUrl و SiteListName بر روی Refresh کلیک کنید تا ارتباط با لیست ساخته شده برقرار شود.
در این قسمت تنظیمات دیگری نیز وجود دارد که در مقاله‌ای جداگانه به آن‌ها خواهم پرداخت.
4- در مرحله بعد باید ستون‌های مورد نظر خود را انتخاب کنید. برای اینکار به سربرگ Column Mappings بروید تا فیلدهای لیست شیرپوینتی نمایش داده شود. برخی از این فیلدها توسط خود شیرپوینت ساخته میشود. فیلدهایی که قصد دارید آن‌ها را در خروجی داشته باشید را انتخاب کرده و بر روی Ok کلیک کنید.
5- از جعبه ابزار یک OLE DB Destination به پکیج اضافه کنید و تنظیمات آن را مطابق تصاویر زیر انجام دهید.
برروی کامپننت OLE DB Destination دوبار کلیک کنید تا صفحه مربوط به تنظیمات آن باز شود. سپس جدولی که پیشتر در SQL ساخته بودید را انتخاب نمایید.
در قسمت Mapping فیلدهای جداول مبدا و مقصد را انتخاب کنید.
با کلیک برروی Ok کار تمام است و میتوانید پکیج را اجرا کنید.
SSIS و کاربرد آن در پروژه
شرکت ماکروسافت سرویس SSIS را برای ایجاد راه‌حل‌های مختلف جهت مدیریت و انتقال دادهها فراهم کرده است.
با یک مثال به معرفی بهتر SSIS می‌پردازم؛ فرض کنید توسط SSAS یک پروژه‌ی بزرگ سازمانی که شامل چندین Cube، Dimension، KPI و ... است، ایجاد کرده‌اید و از آنجایی که در این پروژه از روش ذخیره‌سازی MOLAP استفاده شده، در هر بار به روز رسانی دادهها در انبار داده باید مکعب‌های داده و ابعاد نیز پردازش شوند. از طرفی نه درست است و نه منطقی که کاربر نهایی هر روز وارد SSAS شود و Solution را پردازش کند.
بهترین روشی که برای حل این مشکل معرفی میشود، استفاده از SSIS است. در SSIS با ایجاد یک Package و استفاده از کامپننت‌های مربوطه این کار به راحتی انجام میشود. کاربرد SSIS تنها برای پردازش Solutionها نیست. در واقع SSIS یک پلت‌فرم (Platform) سطح بالا برای فراهم کردن راهکارهای مختلف جهت مدیریت و انتقال اطلاعات است. از این سرویس برای کپی کردن یا دانلود فایل، ارسال و دریافت ایمیل، به‌روز رسانی انبار داده، پاکسازی و کاوش در دادهها، مدیریت شیء‌ها (Objects) و داده‌های SQL استفاده میشود. علاوه بر این SSIS توانایی استخراج (Extract) و تبدیل کردن (Transform) دادهها از فایل‌های داده‌ای XML و منابع داده رابطه‌ای و بارگذاری (Load) در یک یا چند مقصد را دارد.
گرافیکی بودن ابزارها از دیگر مزایای SSIS است. به سادگی می‌توان از این ابزارها برای ساخت Package استفاده نمود بدون نیاز به حتی یک خط کد نویسی! البته در صورت نیاز به کد نویسی ابزار و شرایط آن فراهم است.
هر پکیج دارای یک یا چند کامپننت است که می‌توانند به تنهایی و یا با ترکیبی از هم اجرا شوند. هر پکیج نیز می‌تواند به تنهایی یا با هماهنگی با سایر پکیج‌ها اجرا شود.