This article goes through the high level considerations with the various options, it is intended to promote discussion so all readers and participants may benefit from their collective experience/knowledge.
Although Power BI can connect to SAP Data Warehouse Cloud / S/4HANA, the connectivity has a few limitations, this has been the case for a long time, including with SAP BW, this results in Power BI not being an effective live connection, which in turn means that you either not use Power BI with SAP directly or one end up loading data in Power BI datasets or data-flows, effectively turning Power BI into a data warehouse which is not recommended.
The data warehouse architecture should be guided by the organisation’s data and analytics strategy. We will be defining a number of categories of data and analytics data storage and consumption that should be taken into consideration for the design of the data warehouse. Later a Microsoft architecture and an SAP architecture is evaluated from a pro or cons perspective.
Data and Analytics consumption categories
The MS Data Warehouse has an SQL DW/Synapse used as data storage, Power BI used as reporting later. The pros for this could be existing investment, existing skills and familiar UX for business users (easy to use/learn) and cons are extraction of SAP/deltas complexity, modelling in S/4HANA (and potentially other SAP systems) will be required and all data required for reporting will need to be extracted to the DW (live connection to Power BI is limited).
Microsoft Data Warehouse Architecture
The Power BI could be connected to S4/HANA directly, but scenarios need to be tested and validated. It is not recommended for this to be the main pattern of consumption. SAC to be used for operational reporting within S/4HANA as well as planning and ETL can be BTP data intelligence of Synapse (depending on current toolset). Using both is not recommended due to cost and skills.
SAP Data Waterhouse use S/4HANA for operational dw modelling, SAP Data Warehouse Cloud for data storage, SAC used for reporting. With this the pros would be a seamless UX across systems, SAC can consume data from S/4 (& other sap systems) without replication, easy connection & extraction to DWC and SAP strategic direction. Cons would be a new skillset is required, SAC UX not as mature/intuitive as Power BI and existing data from MS DW will need to be reloaded /remodelled in DWC.
SAP Data Waterhouse Architecture
The historical data can be loaded directly to SAP Data Warehouse Cloud , HANA cloud can also be used to store data and consumed via SAP Data Warehouse Cloud without replication (benefit being it is a db so modelling is SQL as opposed to SAP Data Warehouse Cloud proprietary) and existing Power BI reports can be continued to be used (fed from SQL DW, not in the picture) and transition to SAP progressively.
There are some items to consider like data volume or cost, usage of data lake components (both in MS as well as SAP), to reduce cost, ETL toolkit, both for extraction as well as data transformation and extraction strategy from SAP systems, especially S/4HANA (tables vs CDS views vs. APIs or Extractors). Dependency on S/4HANA cloud version (Multi vs. Single tenant) as well as capability.
It is important to understand the business context, strategies, current state (including pain points) and different personas (tech savvy vs tech illiterate users), including citizen developers, data ingestion patterns and requirements will play a key part on the final architecture, footprint of SAP vs other systems needs to be taken into consideration (if 90% SAP, it makes sense to alight to SAP, where reasonable and possible) and existing investment & skills also need to be considered.
There have been a few super blog posts on connectivity between the components. You may find this useful: https://developers.sap.com/tutorials/data-warehouse-cloud-bi7-connect-powerbi.html