Working on an SMB Power BI architecture for around 12 months, I wanna share my thoughts, workarounds and proposals for a discussion. As the architecture is pretty new and I used to work with qlik architecture, the last 10 years, I would love to have some feedback from power bi seniors.
I have the following scenario/case which I need to cover for building a reliable warehouse for a small middle sized business.
- Jedox (OLAP based budgeting tool)
- Infor LN for production part of the company
- SAP B1 for sales invoicing
- MS CRM for customer management
- MS Sharepoint Online
- LUCA.NET for consolidation
additional some of the source systems got recently updated and the schema of tables has changed.
The whole m-script/power query script part is managed and version controlled within the ms devops environment.
When I started to work on the solution I was to much influenced by the options and possibilities from my old tool (qlik) which I needed to drop and develop a “power bi” way to solve issues. So after a 3 months journey I realized the best way (by having one power bi premium server) is to divide the ETL-process into a base layer by source system and a combination layer to combine the various dimensions and facts.
With the learnings from above I created the following workspace strucutre:
Within those workspaces I created a dataflow structure seperated by DEV and PROD seperated by facts and dimensions to follow the classical ETL process:
Additional you will find the suffix DEV within the dataflow structure. This dataflows represent the abstraction of the file system which looks like this: