Hi everypeeps
So we have a large reporting sutie, running to hundreds, maybe even a thousand pages overall of reports. The data is brought in from a variety of sources from SQL to OData to Snowflake to Excel files stored on SharePoint. We often have to combine different datasources for a report, and most of the reports still run off their own semantic model (maybe shared with 3-4 other reports, maybe not)
We were getting a lot of inconsistency between reports for the same metrics, so we tried consolidating the semantic models into a single, large semantic model. This has proved to be quite unweildy to use, and can force the computers most of us use to write/edit the reports and do ETL on the data to massively bottleneck the process (it can take a couple of minutes on one of our "standard" machines between hitting the "New Measure" button and the bar becoming available to type DAX into.
Our next plan is to move all the semantic modelling into SQL, and just have a single source of truth database. Fabric is not an option as my COO refuses due to the variable costs and not easily being able to come up with solid numbers on the kind of processing power we'll need until we actually start loading loads of data into it. Our budgets are very small (UK education sector - really feeling the squeeze right now)
Trouble is this will still leave measures that have to be done in Power BI itself, SQL could give us a student's attendance YTD or for a given week or half term or whatever as an aggregated single figure, but people want custom date ranges or groups of students and I don't see any way SQL can do that kind of calculation and still give us remotely friendly data to work with.
I have thought about having a single sematic model by area, such as attendance, assessment, rewards, etc. But we have a number of reports that combine these things, such as attendance and behaviour, or interventions and assessment. And when you combine semantic models in a single report you can't do edits very easily on either. I'm coming more and more to the conclusion this is the only way we're going to get consistency in the reports though. Also looking at splitting the reports into different "topics" as best we can and using Power BI apps to deliver them out to the organisation.
Is there a better way to approach this? I know it's a wide open question so even recommendations for blogs, books, youtube channels, etc to go have a look through would be appreciated. I've come from an environment where not only did we have a single database to extract the data from, but we also had much less complexity, into an organisation where there's a vast and complex report suite already in place (which I am looking to simplify) but the constant complaint right now is "this report says X, that report says Y for the same metric" (sometimes one of those reports is something we haven't even touched for weeks or months) and it's not only damaging our reputation among the consumers of the reports but it's so g'dd'mn frustrating!
