Architecture to pull in Sharepoint lists into power BI

[DebbieE]

Just wanted to get a bit of advice.

My new projects data source is Sharepoint lists. There are a lot of them in OLTP format. 

Im looking at architecture to support this and I dont believe Sharepoint list to power BI Is the best option because I want to model the data from the OLTP structure as a star schema.

it will need some heavy duty transformations. i think outside of Power BI because Ive used Power Query Editor in the past and it can't cope with that level of work.

I dont know what I have to use yet. Azure, Synape, Fabric etc but what would you recommend as the approach?

[v-binbinyu-msft]

Hi @DebbieE ,

Please try to use Fabric, you could get and transform data by Data Factory or Data Engineering with Spark compute, then save the data to Lake House, finally you could visualize the data by Power BI with Direct Lake mode.

For more details, you could read related document: What is Microsoft Fabric - Microsoft Fabric | Microsoft Learn

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DebbieE]

they dont have Fabric. Its not an option. 

[v-binbinyu-msft · Accepted solution]

Hi @DebbieE ,

You could also try to use Azure Data Factory or Azure Synapse:

1. Azure Data Factory (ADF) for Data Integration:
Azure Data Factory is a cloud-based data integration service that allows you to create data-driven workflows for orchestrating and automating data movement and data transformation. ADF can be used to ingest data from SharePoint lists and perform the necessary transformations before loading it into a data warehouse. This approach provides the flexibility and power needed for heavy-duty transformations outside of Power BI.

Action Plan:
Use ADF to create a pipeline that connects to your SharePoint lists as a source.
Perform transformations within ADF to model your data according to the star schema.
Load the transformed data into a data warehouse for further use in Power BI.

2. Azure Synapse Analytics for Data Warehousing and Analysis:
Azure Synapse is an analytics service that brings together big data and data warehousing. It offers the capability to perform big data analytics on data stored in data lakes, and it provides a robust and scalable platform for running heavy-duty transformations.

Action Plan:
Use Azure Synapse to ingest data from SharePoint lists.
Leverage Synapse's data transformation capabilities to model your data as a star schema.
Utilize the direct integration between Azure Synapse and Power BI to create your reports and dashboards.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DebbieE]

No azure Data factory or Azure Synapse either and yes I agree with both of these