Questions on first major project

Hi jpalaci1 ,

Quite difficult to give you clear guidance without understanding the fine detail around your entire setup to be honest. However, I think I've been in a similar situation before so I would recommend short-cutting all of your Excel/Power Query/Access DB process, and just building a virtual datawarehouse using Dataflows in the Power BI service.

You obviously already understand the SAP transactional DB structure and where/what you need to get from there, so use this knowledge to build ETL'd tables in Dataflows that can be automatically refreshed overnight and can be connected to by Power BI in a couple of clicks.

This will serve as a cloud-based DWH to which access can be shared, without having to worry about writing to Access and whether someone has deleted the Access file, or it's got too big, or it's end-of-life etc.

If you want actual answers to your questions, then my tuppence:

1) Yes. Always transform and structure your data as you want before warehousing.

2) This is a very broad question. Again, it's nearly impossible to give an answer beyond "yes, no, sometimes, it depends". SQL is not used per se for transformations within Power Query, it's M code, but some of these actions will be folded back to the server using SQL native queries. However, I don't doubt it's used extensively in other scenarios where Power Query is either not suitable or not available.

3) Yes, this looks like the correct process flow.

4) You don't create relationships in Power Query at all, this is done in the data model side of Power BI/Excel. Beyond that, once again, impossible to say what is the best solution for you and your scenario. There's so many variables to consider. This is why consultants exist: to find the best way(s) for you and your scenario. However, I would revert back to my recommendation to look into Dataflows and see whether you can consolidate most/all of your processing steps with a single tool/language.

Pete