Questions on first major project

[jpalaci1]

This is more of a strategy question to approach something.

I single-handedly created an Excel report for my company that reports all costs with 99% accuracy from our transactional SAP database. It’s truly revolutionary (my manager’s words and I work for a company with very poor data structures).

I currently Pull the data from SAP, use a combination of Excel and Power Query to transform the data (some things I’m still learning to do with DAX/PQ), warehouse my data in a flat/non-relational Access database, create connections into a new workbook that outputs/reports the data in needed fashion, and connect to the Access database to create PBI dashboards.

I’m thinking of doing this all in Access, creating a relational database, ETL using PQ or better yet Python (or maybe SQL, if possible or even smart to do), use PBI or use mathplotlib and seaborn to visualize, and then share data.

1. Is is best to do ETL then warehouse the data?
2. Is SQL at all used for any transformation or is that done outside with other tools? What tools are used?
3. Is the process of adding new data the same as when I first start? That is, new data comes, I perform ETL, warehouse it, then visualize/report out?
4. I see some SQL-like functionality in PQ (creating relationships, etc), is it best to do transformation in a tool like PQ or use a Python better then go into PQ?

[BA_Pete · Accepted solution]

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

[jpalaci1]

Thank you. I wasn't attempting to be broad but I totally understand your point. I was wondering what's the best solution but I will start and come back when I have specific design questions. I just wanted general direction as to what to do and your advice is great.

Thank you and sorry for being broad as this is my first time trying to venture into unknown territory.