It started simple. After finishing the analysis, table relationships, and DAX for one module out of six, I began building the report in Power BI Desktop – 11 tables, multiple relationships, and DAX measures, all sitting on a database with 3 years of history and 20M+ rows per table.
We had a bigger goal in mind: 11 dashboards across 6 modules, plus one master dashboard combining them all. To avoid duplicated DAX logic and future integrity issues, we decided to build a single centralized semantic model – a separate PBIX with no visuals, just the data model – that every report would pull from.
Then came the first wall: after finishing just one dashboard, the file was 1.5GB. Power BI Service caps publishing at 1GB.
I dug into optimization – used DAX Studio’s VertiPaq Analyzer, removed unused tables and audit columns, disabled the Auto Date/Time table. Got it down to ~700MB. Still too close for comfort, and it would only grow as more modules were added and data accumulated. A license upgrade looked like the only option left.
That’s when I stumbled on a term I’d never worked with before: PBIRS – Power BI Report Server. In simple terms, it’s the on-premise sibling of Power BI Service – same idea of hosting and viewing Power BI reports, but built for organizations that need to keep data and reports inside their own network instead of the cloud. A few things that set it apart from the Service I was used to:
→ Hosting: Power BI Service runs fully in Microsoft’s cloud; PBIRS is installed and run on your own on-premise server, behind your firewall
→ Licensing: PBIRS comes bundled with Power BI Premium or a SQL Server Enterprise + Software Assurance license, not the usual Pro/Premium subscription
→ Update cycle: Power BI Service ships new features almost weekly; PBIRS gets a “Power BI Desktop for Report Server” build only ~4 times a year, so it’s always a few versions behind (no R/Python visuals, no dataflows, no preview features)
→ Content types: Service supports dashboards, apps, dataflows, and workspaces; PBIRS is limited to Power BI reports, paginated reports, mobile reports, and KPIs – no dashboards
→ Sharing a model: this was the big one for us – Power BI Service lets many reports connect live to one published dataset. PBIRS doesn’t support that natively, so a single semantic model can’t simply power multiple PBIX reports the way it can in the cloud (more on how we solved this below)
- Setting it up also meant a separate “Power BI Desktop for Report Server” application and a server backed by two databases for PBIX files, data connections, and security config.
- Getting there needed a dbcreator role on our main database server, since PBIRS runs alongside it. Once we had that, we set up the PBIRS server.
- Then a curveball: I opened the regular Power BI Desktop file in PBIRS, and every card visual came back blank – tables worked fine, cards didn’t. Turns out card visuals behave differently between Power BI Desktop and PBIRS, so the whole report had to be rebuilt in the PBIRS-specific Desktop app.
Here’s where a mentor’s advice paid off – “document everything.” The first build (in the regular Desktop, with full documentation) took a day. Rebuilding the exact same report in PBIRS Desktop took only a few hours, purely because I had that documentation to fall back on.
Just when PBIRS was working, a bigger blocker surfaced: unlike Power BI Service, where one semantic model can feed multiple reports, PBIRS only allows a 1:1 mapping between a semantic model and a report. That broke our centralized-semantic-model plan entirely.
Cue a few hours of brainstorming with two mentors – sceptical minds, sharp humor, and very different strengths (one heavier on management, one deep in the technical weeds). Between us, we landed on SSAS Tabular Mode: a way to host the semantic layer in its own database, built and published through Visual Studio, with its own VertiPaq Analyzer for checking DAX and relationship integrity. This let us keep one centralized model that any report could connect to – solving the PBIRS limitation entirely.
Setting up SSAS needed Windows/Microsoft admin credentials, since it runs as a completely separate instance from PBIRS. Once we had access, the real work began: instead of the familiar Power BI Desktop/Service modeling experience, everything – semantic model, DAX, validation – now had to happen inside Visual Studio.
To move the model from PBIX into SSAS without redoing all the DAX from scratch, I used ALM Toolkit – a comparison tool normally built for standard Power BI reports, not PBIRS setups. It worked, with one catch: making sure the SQL Server data source entry was actually registered on the SSAS side, which took a few attempts to get right – worth documenting for next time.
Here’s the path that got us to a working deployment:
→ Created the central (empty) database via an XMLA query on the SSAS server → Built a Visual Studio project of type “Import from Tabular Server,” connected to the SSAS server and the SQL data source → Used ALM Toolkit to compare the original PBIX (source) against the SSAS server (target) and pushed updates across → Ran “Process All” in Visual Studio to sync the SSAS server → Built a fresh PBIX, connected it to the SSAS model using Live Connection, applied the theme, migrated the visuals from the old dashboard, and published it to the on-premise server
Two decisions worth explaining here:
Why “Import from Tabular Server” and not a blank project? Since I’d already created the empty central database on the SSAS server via the XMLA query, this project type let Visual Studio pull that exact database’s structure straight into my project – keeping the project permanently tied to that specific server database. A blank “Analysis Services Tabular Project” would’ve started disconnected from anything on the server, meaning extra setup just to point it at the right target. Importing straight from the server kept one clear source of truth from day one.
Why connect the new PBIX to SSAS with Live Connection instead of importing data? A quick technical note first: Live Connection and DirectQuery are related but not identical. DirectQuery generates queries (usually SQL) against a relational data source every time a visual is interacted with. Live Connection is specific to connecting a Power BI report to an Analysis Services (or SSAS) model – no data is copied in, but instead of Power BI writing the queries itself, the request is handed to the Analysis Services engine, which does the calculation and returns the result. Functionally the two feel similar to a report author (no cached data, always current), but Live Connection leans on the analytical engine of Analysis Services rather than pushing native SQL.
That’s exactly why it mattered here: the whole point of building a centralized model was to have every report – all 11 dashboards – pull from one single source instead of holding its own copy of the data. Live Connection meant no data ever gets cached inside the PBIX: the report file stayed tiny, always showed the latest data straight from SSAS, and needed no separate refresh schedule of its own. It also completely sidestepped the original problem that kicked off this whole journey – the 1GB publish-size limit – since a live-connected report has no data sitting inside it to bloat the file.
Demo dashboard: live. Blueprint for the remaining modules: update the model in Visual Studio → deploy to SSAS → build the report PBIX → validate DAX with the formula checker in Visual Studio and cross-verify output in DAX Studio.
Still a lot to explore in this space – but Power BI and PBIRS turned out to be a genuinely interesting rabbit hole. That said, my longer-term pull is toward Data Engineering, and this project only sharpened that interest.
