اتصال PowerBI به SharePoint از طریق API و استفاده از تکنیک Incremental Load

بارگذاری افزایشی (Incremental Load)

بارگذاری افزایشی یکی از روش‌های متداول در حوزه‌ی مدیریت داده‌ها و انبار داده (Data Warehouse) است که به منظور به‌روزرسانی اطلاعات موجود استفاده می‌شود. این روش در مقایسه با بارگذاری کامل (Full Load)، تنها داده‌های جدید یا تغییرات رخ داده در داده‌های موجود را بارگذاری می‌کند و از این طریق بهینه‌تر عمل می‌نماید.

مفهوم بارگذاری افزایشی

در فرآیند بارگذاری افزایشی، سیستم تنها به داده‌هایی توجه می‌کند که از زمان آخرین بارگذاری تغییر کرده‌اند. این تغییرات می‌تواند شامل افزودن داده‌های جدید، ویرایش داده‌های موجود یا حذف برخی رکوردها باشد. برای شناسایی این تغییرات، معمولاً از مکانیزم‌هایی نظیر تاریخ آخرین تغییر (Last Modified Date)، پرچم تغییرات (Change Flag) یا مقایسه مستقیم داده‌ها استفاده می‌شود.

مزایای بارگذاری افزایشی

1. صرفه‌جویی در منابع: با کاهش حجم داده‌های پردازش‌شده و انتقال‌یافته، بارگذاری افزایشی بهینه‌تر از نظر مصرف منابع سرور، پهنای باند شبکه و زمان پردازش عمل می‌کند.
2. سرعت بالا: به دلیل انتقال محدود داده‌ها، سرعت بارگذاری افزایش می‌یابد و به‌روزرسانی سریع‌تری را ممکن می‌سازد.
3. مدیریت آسان‌تر: تغییرات داده‌ای کوچک به جای انتقال کل مجموعه داده‌ها، امکان خطایابی و مدیریت آسان‌تر را فراهم می‌کند.

در این مقاله سعی شده بر اساس ترکیب اتصال به لیست SharePoint از طریق API بیان شده در مقاله ۲ با تکنیک Incremental Load ، باعث کاهش هر چه بیشتر زمان ریلود داشبورد و افزایش Performance شویم. این روش برای لیست‌های با ستون و رکورد زیاد به شدت پیشنهاد می‌شود.

بر اساس تکنیک Incremental Load که فیلم آموزش کوتاه و کاربردی آن در اینجا  آمده و پیشنهاد می‌شود حتما مشاهده کنید، دیتای منبع مورد نظر با دو query مجزا یکی مربوط به دیتایی که مطمئن هستیم تغییر نمی‌کند و دیگری مربوط به دیتایی است که در حال تغییر است واکشی و بعد از ریلود با هم ترکیب می‌کنیم.

در PowerQuery تنظیماتی با نام (Include in report refresh) وجود دارد که می‌توان برای جدولی که دیتای آن تغییر نمی‌کند آن را غیرفعال کرد. در این حالت دیتای بخش ثابت فقط یکبار ریلود شده و در refresh های بعدی ریلود نمی‌شود که زمان ریلود اطلاعات را به طور چشم گیری کاهش می‌دهد.

این تکنیک برای منابع داده مختلف مثل SQL، MySQL ، SharePoint و … بسیار مفید است.

در مقاله ۲  واکشی اطلاعات از طریق SharePoint API شرح داده شده است که بر اساس آن می‌توانیم قبل از Import اطلاعات؛ بر روی ستون‌ها و رکوردهای شیرپوینت فیلتر انجام دهیم. لطفا قبل از مطالعه این مقاله حتما مقاله مذکور را مطالعه کنید. بر اساس آنچه در این مقاله شرح داده شده، اگر بخواهیم به لیست SharePoint ای از طریق API متصل شویم با ساختاری به شکل زیر روبرو هستیم :

  Web.Contents(
    "https://<SharePoint Site>/_api/web/lists/GetByID('{<List GUID>}')/items", 
    [
        Query=[
            #"$top" = "10000", 
            #"$expand" = "Field4, Field5",
            #"$select" = "Field1, Field2, Field3, Field4/Title, Field5/Title", 
            #"$filter" = "ColumnInternalName+Operator+Value", 
            #"$orderby" = "ColumnInternalName+Order"
        ], 
        Headers=[Accept="application/json;odata=verbose"]
    ]
)

برای نمونه فرض کنید به لیست SharePoint ای می‌خواهید وصل بشوید که ساختار کد API آن به شکل زیر است و در حقیقت قرار است چند ستون عادی و لوکاپی از آن را واکشی کنید:

let
Source = 
Json.Document(
    Web.Contents(
        "https://...../BPMS/_api/web/lists/GetByID('{6F168A61-2C7E-4568-93E9-B6FD65D3B06B}')/items", 
        [
            Query=[
                #"$top" = "10000", 
                #"$expand" = "Province, City, user",
                #"$select" = "Id, Title, CreateDate, numresult, Description, Province/Title, City/Title, user/Title",
            ], 
            Headers=[Accept="application/json;odata=verbose"]
        ]
    )
)
    
in 
Source

در اینجا جهت کاهش هر چه بیشتر زمان ریلود داشبود و بر اساس تکنیک Incremental Load، می‌توانید دو بار با دو filter متفاوت به لیست مورد نظر API بزنید. بر این اساس مراحل کار به شرح زیر است:

مرحله ۱:

معیاری را برای تفکیک داده‌هایی که تغییر می‌کند و داده‌هایی که تغییر نمی‌کند باید مشخص کنیم. می‌تواند این معیار ID، یا تاریخ یا هر پارامتر دیگری باشد. برای مثال بالا فرض کنید دیتای ستون “تاریخ ایجاد (CreateDate) ” قبل از تاریخ ‘۲۰۲۴-۰۹-۰۱ دیگر تغییر نمی‌کند و این زمان می‌تواند مبنای تفکیک دیتای ما باشد.

مرحله ۲:

یک فایل PowerBI Desktop باز کرده و داده های لیست مذکور  را با دو Query مجزا واکشی کنید.

بر این اساس دو بار و با دو کوئری متفاوت به لیست API می‌زنیم و داده‌ها را تا رسیدن به جدول مطلوب Expand می‌کنیم:

• Query 1: بخش ثابت (داده قبل از تاریخ ​ ۲۰۲۴-۰۹-۰۱) که نام آن را FactCustomerRateOld در نظر می‌گیریم.

  let
  Source = 
  Json.Document(
      Web.Contents(
          "https://...../BPMS/_api/web/lists/GetByID('{6F168A61-2C7E-4568-93E9-B6FD65D3B06B}')/items", 
          [
              Query= [
                  #"$top" = "10000", 
                  #"$expand" = "Province, City, user",
                  #"$select" = "Id, Title, Resolved, Description, Province/Title, City/Title, user/Title",
                  #"$filter" = "CreateDate lt '2024-09-01T00:00:00Z'"
              ], 
              Headers=[Accept="application/json;odata=verbose"]
          ]
      )
  )
      
  in 
  Source
  

  • Query2: بخش متغیر (داده بعد از تاریخ​ ۲۰۲۴-۰۹-۰۱) که نام آن را FactCustomerRate در نظر می‌گیریم:

    let
    Source = 
    Json.Document(
        Web.Contents(
            "https://...../BPMS/_api/web/lists/GetByID('{6F168A61-2C7E-4568-93E9-B6FD65D3B06B}')/items", 
            [
                Query=[
                    #"$top" = "10000", 
                    #"$expand" = "Province,City,user",
                    #"$select" = "Id, Title, Resolved, Description, Province/Title, City/Title, user/Title",
                    #"$filter" = "CreateDate ge '2024-09-01T00:00:00Z'"
                ], 
                Headers=[Accept="application/json;odata=verbose"]
            ]
        )
    )
        
    in 
    Source
    

    مرحله ۳: حالا نوبت آن است که ریلود جدولی که داده‌های آن تغییر نمی‌کند را در هنگام رفرش غیرفعال کنیم. بدین منظور بر روی جدول مربوطه(FactCustomerRateOld) راست کلیک کنید و  Include in report refresh را برای آن غیرفعال کرد.

مرحله ۴:

بعد از تمیز کردن دیتا، با زدن بر روی دکمه Close & Apply اقدام به لود دیتا کنید. همانطور که مشاهده می کنید در هنگام ریلود هر دو جدول و اطلاعات آن واکشی می‌شود.

مرحله ۵:

حالا باید با استفاده از تابع UNION در DAX اقدام به ایجاد یک جدول جدید کنیم که شامل اطلاعات هر دو جدول FactCustomerRate و FactCustomerRateOld باشد. نام آن را FactCustomerRateFinal می‌نامیم.

مرحله ۶:

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

مرحله ۷:

مجدد بر روی گزینه refresh بزنید، همانطور که مشاهده می‌کنید دیگر دیتای جدول FactCustomerRateOld که دیتای آن تغییر نمی‌کرد، در صف ریلود مشاهده نمی‌شود و تنها دیتای FactCustomerRate رفرش می‌شود.

کار تمام است.

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

همانطور که مشاهده می‌کنید کاهش زمان ریلود بر اساس ترکیب SharePoint API با تکنیک Incremental Load فوق العاده و چشم گیر بوده است.

آموزش پیاده سازی لاگین سفارشی در Power BI report Server

آموزش ایجاد تقویم شمسی در Power Bi report Server

آموزش ایجاد محیط فارسی  و بومی شده  در Power BI report Server