Running Total

مساله:

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

تصویر 1

راه حل:

تکنیکی به نام Running Total وجود دارد که از آن برای محاسبه سرجمع مقادیر یک ستون از اولین سطر تا سطر جاری استفاده می شود.

استفاده از این تکنیک یکی از بهترین راه کارهای سیستم های مالی، انبارداری و ... می باشد. روش های متفاوتی برای استفاده از تکنیک Running Total وجود دارد که در این مقاله با روش های Sub Query، Join و Window Function به صورت Set Based بررسی می شود.

کوئری های این تمرین برروی جدولی شامل تراکنش های بانکی انجام میشود.

ستون UserID شناسه کاربری، ستون TransactionID شناسه تراکنش و ستونQuantity  مبلغ تراکنش انجام شده را در خود ذخیره می کنند. این جدول دارای اطلاعات تراکنش های دو کاربر با شناسه های 1 و 2 است که هر کدام تعداد 10 تراکنش داشته اند.

تصویر 2

دیتابیسی با نام Test (یا هر اسم دلخواه دیگر) ساخته و کد زیر را اجرا کرده تا جدول مورد نظر ساخته شود.

1. USE    Test;--DataBase Name
3. IF      OBJECT_ID('dbo.TransactTable', 'U') IS NOT NULL DROP TABLE dbo.TransactTable;
5. CREATE TABLE  dbo.TransactTable
6. (
7.    UserID INT NOT NULL
8. ,  TransactionID INT NOT NULL
9. ,  Quantity INT NULL
10. ,  CONSTRAINT PK_TransactTable PRIMARY KEY CLUSTERED
11.          (
12.       UserID ASC
13.    ,  TransactionID ASC
14.    )
15. );
17. INSERT INTO dbo.TransactTable (UserID, TransactionID, Quantity) VALUES
18.               (1, 1, 50),( 2, -7),(1, 3, 10),(1, 4, 3), (1, 5, -2),(1, 6, 3),(1, 7, -1)
19.      ,(1, 8, 17),(1, 9, -6),(1, 10, 1),(2, 1, 10),(2, 2, 5),(2, 3, 4),(2, 4, 7),
20.      (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 نمایش می دهد. این عملیات به ازای هر یک از رکوردهای کوئری بیرونی، یک بار انجام می شود.

1. SELECT     UserID
2. ,          TransactionID
3. ,          Quantity
4. ,          (
5.               SELECT     SUM(T2.Quantity)
6.               FROM       dbo.TransactTable    AS     T2
7.               WHERE      T2.TransactionID     =      T1.TransactionID
8.                          AND
9.                          T2.TransactionID     <=     T1.TransactionID
10.            )  AS         Balance
11. 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) می شود.

1. SELECT        T1.UserID
2. ,             T1.TransactionID
3. ,             T1.Quantity
4. ,             SUM(T2.Quantity)     AS     Balance
5. FROM          dbo.TransactTable    AS     T1
6. INNER JOIN    dbo.TransactTable    AS     T2
7.         ON    T2.UserID            =      T1.UserID
8.               AND
9.               T2.TransactionID     <=     T1.TransactionID
10. GROUP BY      T1.UserID
11. ,             T1.TransactionID
12. ,             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 است، انجام می شود.

1. SELECT     UserID
2. ,          TransactionID
3. ,          Quantity
4. ,          SUM(Quantity) OVER(PARTITION BY UserID ORDER BY TransactionID
5.               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)             AS     Balance
6. 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

برقراری ارتباط کلیک ویو با Cube

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

استفاده از تمامی Factها و Dimensionها در کلیک ویو نیازمند عبارات MDX پیشرفته و پیچیده‌ است، به همین دلیل پیشنهاد می‌شود در صورت امکان، ارتباط کلیک ویو با انبار داده را به صورت مستقیم برقرار نمایید.

در این آموزش فرض بر آماده بودن یک مدل OLAP برروی سیستم شما است.

توجه: از آنجایی که در این آموزش از پایگاه داده‌ AdventureWorksDW2008 استفاده شده است، باید آن را به بانک اطلاعاتی خود اضافه نمایید. پیشتر نحوه استفاده از AdventureWorksDW2008 در اینجا شرح داده‌ شده است.

به منظور انجام این کار مراحل زیر را دنبال کنید.

1- ابتدا باید یک پروژه‌ی OLAP ایجاد و سپس آن را Deploy و پردازش کرد تا بانک اطلاعاتی آن در Analysis Services قرار گیرد.

برای این کار می‌توانید از Adventure Works Sample نیز استفاده نمایید.

2- به صفحه‌ی Edit Script در qlikview رفته و از قطعه کد زیر جهت برقراری ارتباط با Analysis Services استفاده نمایید.

OLEDB CONNECT TO [Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=نام بانک اطلاعاتی;Data Source=نام سرور];

قطعه کد بالا ارتباط Cube با qlikview را برقرار می‌کند.

3- برای خواندن جداول ابعاد و حقایق باید از عبارات MDX استفاده شود. برای مثال عبارت MDX زیر معیار Internet Sales Amount و اطلاعات جداول Ship Date و Product را به QlikView اضافه می‌کند.

SELECT NON EMPTY {[Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY

{ ([Ship Date].[Date].[Date].members* [Product].[Category].[Category].members)}

 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works]

  CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

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

همانطور که در تصویر زیر ملاحظه می‌کنید، دو نمودار و یک جدول از اطلاعات موجود در Cube ایجاد شده است. نمودار دایره‌ای بیانگر مبلغ فروش به تفکیک محصول، نمودار خطی، روند فروش محصولات در بازه زمانی و جدول نیز مبلغ فروش محصولات به تفکیک محصول و تاریخ فروش نمایش می‌دهد.

کاربرد Unary Operator در OLAP

می‌توان گفتUnary Operatorاز برای تحلیل‌ بهتر گزارشات مالی استفاده می‌شود. برای درک بهتر این موضوع به مثال زیر توجه کنید.

فرض کنید در یک Cube مالی نیاز به محاسبه ترازنامه (صورتی که وضع مالی یک موسسه را در یک تاریخ معین نشان می دهد) دارید، اگر در SSAS به صورت معمول بعد حساب (Account Dimension) را تعریف و ارتباط آن را با Cube مورد نظر برقرار کنید نتیجه‌ی بدست آمده در خروجی مطلوب نمی‌باشد، چراکه در ترازنامه باید بدهی‌ها ودارایی‌ها با هم برابر باشند و در نهایت تفاضل آن‌ها صفر شود. در حالیکه در OLAP به صورت پیش فرض از مقادیر Sum گرفته می‌شود و در نتیجه مقدار بدهی و دارایی با هم جمع می‌شوند. تصویر زیر خروجی یک Cube مالی را نشان می‌دهد که در آن بُعد حساب‌ها به صورت عادی و بدون تغییر تعریف شده است.

این مشکل با ایجاد تغییرات اندکی در ساختار جدول ایجاد شده در انباره داده و کمک Unary Operator قابل حل است. برای حل این مسئله مراحل زیر را انجام دهید.

۱-   ابتدا به انبار داده مورد نظر رفته و به جدول حساب‌ها یک ستون با نام Operator اضافه کنید.

۲-   بر اساس نیاز ستون Operator را با علائم ((+ و – و...)) پر کنید.

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

۳- دایمنشن حساب‌ها را مطابق معمول به Cube اضافه کنید.

حال باید تنظیمات مربوط به Unary Operator را انجام دهید.

۴- بر روی دایمنشن ساخته شده راست کلیک کرده و از منوی باز شده گزینه‌ی Add Business Intelligence را انتخاب نمایید. برروی Next کلیک کنید.

۵- در صفحه‌ی Choose Enhancement گزینه‌ی Specify a unary operator را انتخاب و Next کنید.

۶- مطابق شکل زیر در صفحه‌ی Specify a unary operator ستون Operator را که پیشتر به جدول اضافه کردیم را انتخاب کرده و برروی Next کلیک کنید.

 ۷- برروی Finish کلیک کنید.

۸- پروژه را مجدد پردازش کنید.

در صورتی که عملگر‌های موجود در بعد حساب به درستی تعریف شده باشند و مقادیر موجود در جدول حقایق درست باشند، مقدار کل(Total) برابر صفر خواهد بود.

استفاده از فرمت ساعت به عنوان 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-      پروژه را مجدد پردازش کنید. همانطور که مشاهده می‌کنید اطلاعات به فرمت ساعت نمایش داده می‌شود.

با تغییر ابعاد گزارش دلخواه خود را مشاهده کنید.

همگام سازی (Synchronize) دو جدول در SSIS

پیشتر مطالبی در مورد روش‌های مقایسه رکوردهای دو جدول نوشته بودم.  یکی از این روش‌ها استفاده از tablediff است که مطالب مربوط به آن را می‌توانید اینجا مشاهده کنید، اما برای انجام آن دو مسئله وجود دارد. اول اینکه پس از ایجاد فایل تغییرات، باید آن فایل را باز کرده و اجرا کنیم و دوم گرفتن خطا توسط SQL در زمان ایجاد مجدد است، چراکه این فایل قبلا ایجاد شده است. البته از چند طریق می‌توان این مشکلات را حل کرد.

در این پست قصد  دارم به حل این مسئله از طریق ایجاد یک پکیج در SSIS بپردازم.

     1-  در SSIS یک Package مطابق شکل زیر ایجاد کنید.

    

2- قطعه کد زیر را در  Execute TableDiff قرار دهید.

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'

برای اطلاع از سایر پارامترها و ویژگی های Tablediff به اینجا مراجعه کنید.

همانطور که در مطالب مربوط به tablediff گفتم، با اجرای این کوئری رکوردهای دو جدول با هم مقایسه و نتیجه‌ی آن در فایل Result.sql ذخیره می‌شود.

3-  توسط Execute SQL file فایل SQL ایجاد شده در مرحله قبل را اجرا کنید.

4- برای اینکه در زمان اجرای مجدد پکیج و ایجاد فایل به مشکل بر نخوریم، با استفاده از یک دستور ساده فایل ایجاد شده در مرحله قبل را حذف می‌کنیم.

xp_cmdshell 'del c:\Result.sql'

کار تمام است! حال هر زمان که اطلاعات موجود در جدول اول تغییر کرد میتوانید پکیج را اجرا کنید و جدول دوم را همانند جدول اول داشته باشید.