Most people would simply move their data into PowerBI and use the PowerBI desktop to design their dashboards, and then publish them to the PowerBI service. That will be the easiest route and likely the best overall, as PowerBI is a robust platform with many options for dashboarding.
If PowerBI isn't an option, for reporting and analytics where the volume of data the user is interacting with can be millions of rows, the general advice is to query a data warehouse, or dimensionally modeled OLAP data source, that is optimized for aggregating data.
If a dimensional structure isn't available, then a tabular model that uses something like columnstore or other columnar optimized storage is preferred. The trend today is to use Parquet formatted files and a data engine that supports them. SQL Server 2022 can query Parquet files configured as external data sources.
Microsoft Fabric is the latest product for analytical data, and the underlying storage utilizes delta tables based on Parquet files. Google "Microsoft Fabric" to find out more. While it is a cloud product, you can utilize the underlying technology on-premises, but it's a lot of architectural work.
And lastly, if none of those options are feasible for your situation, then reporting against a read-only replica is preferred. How you replicate the data from the primary to the secondary replica has numerous options:
- log shipping
- SQL Server replication
- Change Data Capture with associated tasks to copy to a secondary replica
- Availability Groups, which require Enterprise Edition to read from secondaries
There are no generic answers on best practices. The factors for choosing these options depend on costs, tech budget, development time/delivery date, maintenance effort, and desired feature set. You haven't provided these. I've listed the options available in the order of preference I would use to achieve best performance.
I definitely would NOT try to build an application or GUI to do analytic dashboards, since there are other tools that do the job very well already. PowerBI certainly can, and many of its analytical features are available in Excel, just with fewer options for dashboarding.