ETL process for XER data

[adarshthouti]

How to extract multiple XER datasets from multiple projects and store it in a database and connect that database to Power BI.

For example I have some projects like 

Project A, Project B and Project C

I want to store all the above projects in the data base or data warehouse and finally connect it with Power BI for reports.

[v-zhengdxu-msft]

Hi @adarshthouti 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[adarshthouti]

No, the solutions provided by users are manual, but I am seeking an automatic solution, such as a completely dynamic approach as mentioned in my previous replies.

Currently, I have created a parameter in Power BI to select the path where the XER files containing the project data are stored in Power Query. It then transforms the data based on the transformations made in Power Query.

I have also created multiple measures, calculated columns, and calculated tables in Power BI Desktop.

My objective is to make this entire process dynamic, allowing for the comparison of two or more projects. I have set up a dropdown box to select projects and compare them.

Therefore, I need a solution on how to achieve this dynamically, without manually copying data from Primavera into Excel and connecting it with Power BI Desktop.

One of the solution which I have known from an ETL expert is parsing the XER files using Python and storing all those in a data warehouse or database and creating the calculations in it and finally connecting it to the Power BI.

are there any alternative approaches to achieve it?

[PowerProject]

Please check the free Primavera Power Bi solution in the Power Bi appstore: https://appsource.microsoft.com/nl/product/power-bi/qcompany1615911334463.ppc1001?tab=overview

[amustafa]

It is possible if you can find an automated way to dump files in a Sharepoint folder (One Drive for Business) from P6. This will require some planning on your end how to store project specific files in a seperate folder then connect your Power Query with dynamic parmaters to select a specific folder.

[adarshthouti]

Thank you for your answer. But I can see that this is the static way of Extracting the data from P6, Transforming the data in Excel, and Loading the data by connecting it to the Power BI. 

But I don't have a single project, I have multiple Projects, I want to make this process more dynamic by connecting all the stored datasets to the single Power BI report. 

I have created a Power BI report with multiple DAX measures, M queries of Custom columns and Calculated columns. I want all these things come into one place. 

Is there a possibility to achieve this?

[amustafa]

Hi, See this guide on how to access Primavera P6 data for reporting in Power BI. You can use new Fabric Lakehouse or Datamarts to store data for reporting and build your data model from there on.

How to connect Primavera P6 to Power BI (Full Guide) - Planning Engineer Est.