Do you have any recommendations for getting started with this?
The analysis pattern is usually importing data and cleaning it in PowerQuery, then building relationships between tables in the data model view, and then analysing it via a ‘PowerPivot’ Table.
PowerPivot then has new functionalities compared to a regular pivot table (eg measures) that allow for automatically calculating columns based on context and some additional capabilities you don’t get in excel.
My typical quick turn-around process is: type SQL in text editor, test sql in database, create a view, connect to the view from excel, use native excel features to display whats needed.
Usually I create a summary page as well which uses sum-ifs and such on the query result for the high level detail rather than go through the SQL process for it separately
The alternative is to load the sqlite DB into Excel via PowerQuery and then share the file, which will maintain type safety of all columns through the excel data model.
This provides much better GUI data manipulation too, as you can define relationships between the data in the model e.t.c.
The problem is I would guess less than 1% of Excel users actually understand this functionality, but it is absolutely core to doing proper analysis in excel (not saying you don't use it - you probably do - but lots of users don't!).