This is a newbie question. Currently, I connect to an SSAS service from Excel and bring back data from multi-dimensional cubes. Some calculations (using cube data and one or two numbers are hardcoded in the excel) and what-if-analysis are performed and the data is filtered for a specific week of the calendar year (Week 2 - Jan 3, 2022 - Jan 9, 2022) and moved to another tab and that forms the basis of the Power BI report along with the original cube data.
Since this is a weekly report and someone has to open the excel, refresh data from the cube, perform what-if-analysis using goal seeker and then move it to another sheet, etc. before refreshing Power BI. This is the current setup and I want to simplify/automate this and yet not overload the Power BI report that it takes forever to refresh or load.
My question: If there are calculations to be done in between the multidimensional cube data and Power BI, where should it be placed? Should I complicate the Power BI report with all these calculations or move the calculations and logic elsewhere such as for example a Python program that will connect to SSAS (I am somewhat familiar with Python). I was told to consider Databricks to run the Python code by a colleague.
Options:
Perform all calculations in Power BI. Yet to test how well the report can handle this.
Do the calculations elsewhere, for example on Databricks. Don't have Databricks yet. I can start with local Jupyter notebooks. I am concerned if I will run out of memory.
What is the best/industry practice in such scenarios? There are concerns about complicating the presentation layer in Power BI and impacting user experience with heavy Powe BI reports.
In general, you want all logic in the cube and use Power BI for reporting. If you can't put the logic in the cube, I would prefer to do it in Power BI to eliminate other points of failure, manual steps, or timing issues.