اتصال PowerBI به SharePoint از طریق SharePoint API

در صورتی که با لیست شیرپوینتی با تعداد رکورد و ستون‌های زیاد روبرو هستید، استفاده از اتصال مستقیم به شیرپوینت از طریق SharePoint List یا SharePoint Online که در مقاله ۱ به آن پرداخته شد، بسیار زمان بر است. برای کاهش زمان لود و بالا بردن Performance به روشی نیاز داریم که بتوانیم قبل از Import اطلاعات؛ بر روی ستون‌ها و رکوردهای شیرپوینت فیلتر انجام دهیم که روش واکشی اطلاعات از طریق SharePoint API  این نیاز ما را پوشش می‌دهد.

این مقاله بر اساس روش SharePoint API بیان شده در این سایت و تجربیات به دست آمده در اتصال به انواع لیست‌ها از جمله لیست‌های فارسی نگارش شده است.

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

انواع Field در شیرپوینت

در شیرپوینت Field ها را می‌توان به سه دسته تقسیم کرد:

1. فیلدهای غیر لوکاپی و عادی(Non-Lookup Fields) :
   • این دسته شامل فیلدهایی است که مقادیر درون آن بطور مستقل ذخیره می‌شود و وابستگی به منبع دیگری ندارند.
   • از مهمترین نوع این دسته می‌توان به فیلدهای Number، Choice، Yes/No ، Text ، Date and Time  و Calculated  اشاره کرد.
2. فیلدهای لوکاپی (Lookup Fields) :
   • این فیلدها داده ها را از لیست یا لایبرری دیگری در شیرپوینت دریافت می‌کنند.
   • داده ها به صورت پویا از لیست دیگر خوانده می‌شود.
3. فیلدهای کاربری و گروه (User and Group Fields):
   • این فیلدها برای انتخاب کاربران یا گروه‌ها از اکتیودایرکتوری یا گروه‌های شیرپوینت استفاده می‌شود.
   • معمولا برای انتخاب یک یا چند کابر و یا نمایش اطلاعات مرتبط مانند ایمیل و نام استفاده می‌شود.

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

• فیلد استان و شهر لوکاپی بوده و اطلاعات آن از دو لیست دیگر به همین نام خوانده می‌شود.
• فیلد user هم در این لیست از جنسuser and group  است.
• سایر فیلدها عادی و به صورت غیر لوکاپی تعریف شده است.

قصد داریم در ادامه از طریق SharePoint API  به این لیست وصل شویم و ستون‌ها و رکوردهای مشخصی از آن را واکشی کنیم.

ساختار  SharePoint API

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"]
    ]
)

همانطور که مشاهده می‌کنید، در ساختار API به شیرپوینت سه بخش اصلی وجود دارد؛  APIURL، Headers  و  Query.

• API URL
  • API URL را می‌توان با ساختارهای مختلفی تعریف کرد، برای نمونه بر اساس نام لیست یا GUID.
  • برایAPI  به لیست های فارسی که ممکن است Internal Name آن فارسی باشد پیشنهاد می شود از API URL ای با ترکیبی از آدرس سایت/زیرسایت قرار گیری لیست شیرپوینتی و GUID استفاده شود.
  • ساختار پیشنهادی:

    "https://<SharePoint Site>/_api/web/lists/GetByID('{<List GUID>}')/items"

    • Headers
      • هدرها اطلاعاتی هستند که همراه درخواست ارسال می‌شوند و نحوه پردازش درخواست را مشخص می‌کند.
      • در اینجا برای اینکه خروجی دیتا را بجای فرمت XML، با ساختار JSON داشته باشیم، Header را بصورت Accept=”application/json;odata=verbose” تعریف می‌کنیم.
    • Query
      • Query ها از پارامترهای مختلف تشکیل شده که برای محدود کردن داده‌های بازگشتی استفاده می‌شوند.
      • برخی از پارامترهای مهم بخش Query:
    • • • $top
          • محدود کردن تعداد ایتم‌های بازگشتی بکار می‌رود
          • پیشنهاد می‌شود عددی بسیار بیشتر از تعداد رکورد‌های پورتال تعریف شود.
          • در صورت عدم تعریف این پارامتر، ممکن است بر اساس تنظیمات لیست، برای نمونه تنها ۱۰۰ آیتم از رکورد‌های لیست را برگرداند.
    • • • $expand
          • جهت expand داده‌ها برای دسترسی به روابط مرتبط با فیلدهای Lookup ای و User and Group  بکار می رود.
          • اگر فیلد لوکاپی یا از جنس User and Group داشته باشید، حتما باید InternalName این فیلدها را در این قسمت تعریف کنید.
    • • • $select
          • مشخص می‌کند کدام فیلدها در پاسخ بازگردانده شوند.(محدود کردن در سطح ستون)
          • برای تعریف فیلدهای غیر لوکاپی، باید InternalName فیلدها را مستقیم درج کنید.(برای نمونه “select” = “Id, Title, Resolved, Description” )
          • برای فیلدهای لوکاپی و User and Group باید فیلد متناظر مورد نظر را با ساختار Parent/Child تعریف کنید. ( برای نمونه Province/Title)
        • $filter
          • برای فیلتر بر روی نتایج بر اساس یک یا چند شرط مشخص به کار می‌رود. ( جهت محدود کردن در سطح رکورد)
          • می‌توان برای ترکیب چند شرط از عملگر های and و or نیز استفاده کرد.
          • مهمترین عملگر هایی که در تعریف شرط در این قسمت می‌توانید استفاده کنید را می‌توانید در جداول زیر مشاهده کنید.

• • • • چند مثال از فیلتر بر روی ستون های مختلف:

"$filter" ="ID gt 1000 "
"$filter" ="City eq ’Tehran' "
"$filter" ="startwith(Title, 'P')"
"$filter" ="Start_x0020_Date le '2016-03-26T09:59:32Z' "
"$filter" ="month(Modified) eq 5"
"$filter" ="(Modified le datetime'2016-03-26T09:59:32Z') and (City eq  'Tehran')

• • • $orderby:
      • برای مرتب سازی نتایج بازگشتی بر اساس یک یا چند ستون مشخص به کار می‌رود.

نحوه اتصال PowerBI به SharePoint از طریق API

برای نمونه فرض کنید می‌خواهیم به لیست شیرپوینتی که در اول این بخش تعریف کردیم وصل شویم.

در اینجا نحوه تعریف ساختار نهایی کد SharePoint API و نحوه تعریف آن در Advanced Editor بیان شده است. اینکه این ساختار از کجا آمده می‌توانید از این مرجع مشاهده کنید.

برای اتصال به لیست فوق مراحل زیر را باید انجام داد:

1. از Get Data یک Blank Query ایجاد کنید.
2. بر روی Advanced Editor کلیک کنید. بر اساس آنچه گفته شد،SharePoint API لیست بالا ساختاری به شکل زیر دارد. این کد را در پنجره Advanced Editor ، وارد و بر روی دکمه Done کلیک کنید.

   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" = "Province/Title eq 'البرز'" 
               ], 
               Headers=[Accept="application/json;odata=verbose"]
           ]
       )
   )
       
   in 
   Source
   

   در این کد:

   • Json.Document : این دستور داده های JSON را به یک ساختار داده قابل فهم برای PowerQuery تبدیل می کند (مانند جدول، لیست یا رکورد)
   • Web.Contents : برای API به شیرپوینت از روش بیان شده، باید از مدل اتصال به منابع وب استفاده کنید. در داخل این تابع باید ساختار کد SharePoint API را وارد کنید که ممکن است برای هر لیست شیرپوینتی متفاوت باشد. برای مثال ما:
     • APIURL: در این مثال APIURL لیست به صوت زیر است:
       “https://…../BPMS/_api/web/lists/GetByID(‘{6F168A61-2C7E-4568-93E9-B6FD65D3B06B}’)/items”
     • Query:
       • top: با توجه به تعداد رکوردهای لیست تعریف می شود. در اینجا ۱۰۰۰۰ لحاظ شده است.
       • Expand: ما در لیستمان دو ستون لوکاپی Province و City و یک ستون user and group با نام user داشتیم که در صورت نیاز به واکشی دیتای آن باید در اینجا نیز حتما تعریف شود.
       • Selected: در اینجا باید بر اساس آنچه قبلا بیان شد، نام (Internal Name) ستون های غیر لوکاپی به صورت عادی و ستون های لوکاپی و user and group که از لیست دیگر دیتا واکشی می‌کنند، به صورت Parent/Child تعریف شود. دقت کنید که در هر دو قسمت Child و Parent باید Internal Name  فیلدها تعریف شود.
       • Filter: همانطور که در تعریف این پارامتر مشاهده می‌کنید، در اینجا قصد داشتیم تنها دیتای مربوط به استان “البرز” را واکشی کنیم. با توجه به اینکه این فیلد لوکاپی بود فیلد به صورت Province/Title تعریف شده است.
     • Headers: قالب مربوط به برگرداندن فرمت Json تعریف شده است.

   اگر پارامترها را درست تنظیم کرده باشید، خروجی یک Record خواهد بود که در تصویر زیر مشاهده می کنید.

۳.برای تبدیل رکورد مرحله قبل به جدول استاندارد مورد نظر باید مطابق تصویر زیر، مراحل زیر را انجام دهید:

1. 1. بر روی Record کلیک کنید.
   2. سپس بر روی List کلیک کنید. خروجی ستونی با تعداد رکوردهای معادل با رکوردهای که با API خوانده شده خواهد بود.
   3. سپس بر روی list راست کلیک کنید و دکمه convert To Table را بزنید تا خروجی مرحله قبل به جدول تبدیل شود.
   4. حالا برای اینکه Recordهای بدست آمده را به یک جدول استاندارد که عناوین فیلدها در سرستون قرار بگیرد تبدیل کنیم، Expand کنید.
   5. ستون‌های مورد نظر را انتخاب کنید و بر روی OK کلیک کنید. (معمولا برای ID دو ستون بر می‌گردد که می‌توانید تیک یکی از انها را غیر فعال کنید. همچنین می‌توانید فیلد _metadata را واکشی نکنید)
   6. در صورتیکه ستون لوکاپی یا user and group ای را واکشی کردید باید مجدد نسبت به Expand ستون مربوطه اقدام کنید.

4. می‌توانید حالا در صورت نیاز، پروسه ETL را بر روی داده ادامه دهید.

جمع بندی و چند نکته مهم

• در این روش توانستیم قبل از Import لیست؛ بر روی ستون‌ها و رکوردهای شیرپوینت فیلتر انجام دهیم که منجر به کاهش زمان لود و بالا بردن Performance سیستم می‌شود.
• ساختار SharPoint API به صورت قالب زیر است. می‌توانید هر جا نیاز به API داشتید این قالب را در Advanced Editor یک  Blank Query، کپی و Paste کنید و بنا به نیاز نسبت به تعریف Url و GUID و پارامترهای Query اقدام کنید. هر پارامتری را هم که نیاز نداشتید می توانید با “//” کامنت کنید.

let
    Source = 
    Json.Document(
        Web.Contents(
            "https://.../_api/web/lists/GetByID('{...}')/items", 
            [
                Query=[
                    #"$top" = "...", 
                    #"$expand" = "...",
                    #"$select" = "...",
                    #"$filter" = "..." 
                ], 
                Headers=[Accept="application/json;odata=verbose"]
            ]
        )
    )
    
in 
    Source

در ادامه به بیان چند نکته مهم و کاربردی خواهیم پرداخت:

نکته ۱: یکی از راه های تشخیص InternalName یک فیلد این است که در لیست مربوطه رکوردها را بر اساس آن فیلد Sort کنید. در قسمتی از URL نام داخلی آن درج شده است.

https://…SortField%3DTechSiteID_x003a_Cabinet_x0020_I-SortDir…

نکته ۲: در لیست های شیرپوینتی هنگامی که نام  Internal-Name یک فیلدی شامل کاراکترهای خاص،space  یا حروف غیر لاتین مانند فارسی باشد معمولا شیرپوینت اسم آن را به شکل کدگذاری شده ذخیره می‌کند. برای نمونه اگر نام داخلی فیلدی مستقیم “تاریخ” و نه یک عبارت لاتین تعریف شده باشد، در شیرپوینت نام آن را بصورت “_x062a__x0627__x0631__x06cc__x06”   ذخیره می‌کند.

در این حالت و در هنگام صدا زدن این فیلدها از طریق API باید آنها را با پروتکل OData در پارامترهای مختلف تعریف کنید. در اینصورت باید در ابتدای نام فیلد از OData_ استفاده کنید (OData__x062a__x0627__x0631__x06cc__x06 ).

این قانون هم برای فیلدهای لوکاپی و هم فیلدهای غیرلوکاپی صدق می‌کند و اگر ستونی که از لیست دیگر می‌خواهیم واکشی کنیم، نام داخلی فارسی داشت باید در قسمت دوم ساختار Parent/Child از ساختار OData استفاده کنیم.  برای مثال:

let
    Source = 
    Json.Document(
        Web.Contents(
            "https://.../_api/web/lists/GetByID('{...}')/items", 
            [
                Query=[
                    #"$top" = "100000", 
                    #"$expand" = "Author,Viewers",
                    #"$select" = "Id,Title,Viewers/Title,Author/Title,OData__x062a__x0627__x0631__x06cc__x06,OData__x0622__x063a__x0627__x0632_,OData__x067e__x0627__x06cc__x0627__x06",
                    #"$filter" = "OData__x062a__x0627__x0631__x06cc__x06 ge '2024-08-01T00:00:00Z'" 
                ], 
                Headers=[Accept="application/json;odata=verbose"]
            ]
        )
    )
    
in 
    Source

نکته ۳: ساختار گفته شده جهت واکشی دیتای فیلدهای لوکاپی از طریق تعریف expand و select به صورت “Parent/Child” تنها در زمانی می‌توان استفاده کرد که دو لیست در یک سایت یا زیرسایت قرار داشته باشند. برای مثال اگر لیست‌های پایه شما در سایت و لیست‌های دیگرتان که به این لیست لوکاپ می‌زنند در زیرسایت قرار داشته باشند از این طریق نمی‌توانید نسبت به API اقدام کنید.

معمولا شما می‌توانید با قرار دادن عبارت Id در انتهای InternalName فیلدهای لوکاپی و تعریف آن به مانند فیلد غیرلوکاپی و تنها در پارامتر  select، شماره رکورد آن فیلد را در پورتال که از آن لوکاپ کرده‌اید استخراج کنید. در این حالت باید شما هر دو لیست را جداگانه واکشی و در محیط PowerQuery نسبت به ETL و برای مثال Merge جداول اقدام کنید.

برای نمونه اگر در مثال فوق جدول اصلی در یک سایت/زیرسایت و جداول استان و شهر در یک سایت دیگر قرار داشت، برای نمونه در این حالت نحوه استخراج و تعریف کد به صورت زیر می‌باشد:

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

مقایسه روش SharePointAPI با روش SharePoint list

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

در مقاله بعدی به بیان نحوه اتصال به SharePoint از طریق API بهمراه استفاده از تکنیک Incremental Load خواهیم پرداخت.

کارگاه آموزشی فارسی سازی سرور Power BI