Tag Archives: SharePoint

ByHariharan Rajendran

Extract Data from SharePoint List using SSIS

Microsoft released a new task called “OData Source” which helps us to connect to online SharePoint and extract the lists.

Follow the below steps to configure and extract the data.

  1. Make sure to use the latest version of SQL Server Data Tools to work with SQL Server integration Projects.
  2. Use the Data Flow Task
  3. You can see “OData Source” task. Drag and drop into the data flow canvas.
  4. This OData Source task required a connection manager. Click New on the OData Connection Manager.
  5. You need to pass the below information to successfully connect with SharePoint Online.
    1. Service document location – https://<yoursharepointsitename>/_vti_bin/listdata.svc
    2. Authentication Type: Microsoft Online Services
    3. UserName: Your email address to connect SharePoint Online
    4. Password: your password.
  6. Test the connection.
  7. On the OData Source Editor, Choose the SharePoint list on the collection section.
  8. Check the columns and use any destination where you want to store the data.

Screenshots.

ByHariharan Rajendran

SharePoint List to Power BI & Extending Power BI to SharePoint

This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.

I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.

Flow,

Pre-Requisites:

  1. SharePoint List URL
  2. SharePoint Credentials (On-premises or O365)
  3. Power BI Account
  4. Power BI Desktop

Follow the below steps,

Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.

Step 2: Get the SharePoint URL where the list is created.

https://xxxxx.sharepoint.com/Training

Step 3: Open Power BI Desktop and click Get Data.

Step 4: Choose SharePoint Online List, if you use O365. Click Connect

Step 5: Pass the O365 SharePoint URL and credentials.

Step 6: Choose the list and click Load.

Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.

Step 8: Save and publish the report to Power BI online services

Step 9: Login to app.powerbi.com with your credentials.

Step 10: Open your report from your workspace where you published.

Embed the Report in SharePoint

So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.

Follow the below steps.

Step 1: Open the report in app.powerbi.com

Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.

Step 3: It will ask you to publish.

Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.

Step 5: Go to SharePoint page and edit where you want to embed this report.

Step 6: Click Insert and choose the Embed Code

Step 7: Copy and paste the iframe

Step 8: Save the page.

Share your comments below.

Related Post:

Embed Power BI Report in Web page

1