Report Server architecture and licensing question

[MartinB_Nbg]

Hi,

I'm familiar with the Power BI cloud service, but I'm new to Report Server on prem. I read this capacity planning whitepaper from Microsoft, but I'm still confused whether the SSAS shown there is part of Report Server or just an optional data source and which pieces of software need seprate licenses or share the same license.

Our szenario:
- Every piece of software that can be separated on a different machine shall be separated on a different machine so that we can scale specifically as needed and to avoid interference.

- With priority on the requirement above, minimum applicable licensing costs should be chosen.
- We only use on prem servers in the same own data center, so additional network latency from distributing software on different machines should not be an issue.

- All machines have 8 cores, but for the beginning, we only want to license as few cores as necessary to run the software on each machine. If a license applied to unlimited cores, of cause we would utilize all available cores on a machine.
- Users build reports using Power BI Desktop in import mode. (That we need a Pro license for the publisher is clear and out of scope of this question.)
- Data sources include Microsoft SQL-Server OLTP DB (already existing, already licensed for 8 cores with SQL-Server Enterprise 2019 with Software Assurance), Informix OLTP DB (accessed by Power BI through ODBC driver) and Excel files. Files are stored on a network share and are accessible for the Power BI servers.
- Report Server licensing shall come from SQL-Server license, not from Power BI Premium. No cloud services needed.

Specifically, I'm looking for answers to these questions:

Deployment of the OLAP server:
1. If Microsoft is talking about the Power BI Report Server Database, is this the report catalog database only, or the OLAP database only, or both mingled togehter?
2. Is the installation of the OLAP server that stores the data and executes the DAX queries from published PBIX files using import mode covered by running the installation of the report server (PowerBIReportServer.exe installation file) and is the OLAP server hosting the data and the in-memory DAX calculations always on the same machine as the web-server hosting the Report Server portal web-app?
3. If the OLAP server (the SSAS'ish piece of software) is installed on a different machine, how is publishing a PBIX file using import mode handled: Does the Report Server do the split, storing the data on the OLAP server and the layouts in the catalog database? Is this technically correct understanding that the report layouts are stored in the catalog database, or are they stored like in files on the Report Server?

4. If the OLAP server is installed from PowerBIReportServer.exe installation file, is there a way to configure how many cores and how much memory is assigned to the xVelocity engine for executing the DAX queries?
5. On which machine do ODBC drivers need to be installed that are needed for a scheduled refresh? Does the OLAP server or the portal server execute the Power Query scripts on scheduled refreshes?

Licensing requirements:
6. Are all necessary installations on each separate machine covered by our existing SQL-Server license or does each piece of software that is installed on a separate machine also require additional licenses?
7. If splitting OLAP server and portal web server is not possible with Report Server only, then we would consider installing Report Server on one machine and SSAS an a different machine and build live connection PBIX files. In this case, would the SQL-Server Enterprise 2019 with Software Assurance license for the SSAS machine also cover the Report Server on the other machine?

Paginated Reports
8. If we do not store our OLAP data in a seprate instance of SSAS but in the OLAP server that comes with Report Server, can we build paginated reports from the data models stored on Report Server?

Can anyone shed some light on my Report Server beginner questions or point me to a good documentation answering these questions, please?

Thank you very much!

Best regards,
Martin

[josef78 · Accepted solution]

[josef78]

1. Report Server Database is MSSQL (relational DB), and is only for Report Server Catalog.

2. For "Import Mode" PBI Report, SSAS server is covered by PowerBIReportServer.exe instalation, and hosting inmemory database and executing dax queries, is always on same server as web portal, and cannot be separated. Only procesing (scheduled refresh) of import mode reports can be separated to another machine (but is still covered by PowerBIReportServer.exe installation.

3. It is not possible for import mode, If you using import mode report, you do not need separated SSAS server. Techically SSAS integrated in PBIRS working in diskless mode, Report Server separate definition and data, but both is persisted stored in Catalog Database, and non-persist are also loaded to inmemory SSAS integrated database (on same machine as web portal).

4. Maybe yes, but it is not recommended. By default, all cores and all memory on machine is used for xVelocity engine. The reason is simple, there is nothing else (nothing important), Power BI Report Server is some portal web (but it not resource intensive), and SSAS instance (which is only resource important).

5. ODBC (or any refresh related part) for import mode must be installed on Power BI Report Server (covered PowerBIReportServer.exe), but you can scale out installation on mutiple servers, and is possible one machine use only for scheduled refresh.

My recomendation:
-use Scale out deployment of PBIRS, at least on three machines, two loadbalanced as frontend (web+xVelocity SSAS) and third for background (xVelocity SSAS+PQ+ODBC)
-do not limit on "import mode" reports, because there much more limitations in compare with PBI Service, for large model you will need dedicated SSAS server + PBI report with Live connection mode

[josef78 · Accepted solution]

[josef78]

6. You need licences for every core where you running any piece of SQL Server. You can licence virtual cores, on some virtualisation platform, or in case of EE with SA you can licence physical cores, independent how many virtual machines and how many virtual cores you have (on licenced physical server).

7. Right approach, but you need licences for both. If you install PBIRS on one machine you need licence for SQL Server (EE with SA), and also for second machine with SSAS you will need also SQL licences (posible Standard without SA, but in real case you will need Enterprise). In this case, from perfomance perspective, importat is SSAS server. But in real case, clear import-mode approach or clear live-connection approach is not ideal, in real case scenarion you will need mix both approaches, import-mode approach for simple, small and fast-delivery reports, and live-connection (with dedicated SSAS) for large and strong developed models.

8. Not possible (yet, officially) for import mode report. If you need use paginated report on your models, you need use dedicated tabular model on dedicated SSAS server, which will serve data to you PBI report and also for paginated reports.

[MartinB_Nbg]

Thank you @josef78 for your very, very clear explanation!

Two more questions, just to be sure:

Regarding licensing, from your answer to question 6. I assume the PBIRS checks the number of cores that are licensed and uses only the licensed number of cores for the xVelocity engine. Right?

You wrote that I'll probably face use case where I want to use the import mode even though I have a separtate SSAS. Nevertheless, would it possible for the administrator to forbid publishing import mode files so that using live connection to the SSAS is enforced? Then I could set up a large machine for SSAS and a small machine for PBIRS without running into users complaining about slow (import mode) reports. 

BR,
Martin

[josef78 · Accepted solution]

I'm not licensing expert, but I think no. 

-Even though technically and internaly is PBIRS composed of several components (Web Portal, SSRS, PowerBI and SSAS (in tabular diskless mode)) and SSAS is key performance component, from customer, installation and licencing point of view it is only one component = PBIRS. And all cores where PBIRS is running (regadless which internal componet uses which cores) must be licensed.

-By default PBIRS is running on all cores which is avaliable on machine. And you need licence for all cores where PBIRS can potentialy run.

-It is mean, if you have 8core machine, you need 8licences (4x 2pack). If you want limit number of cores, it must unavaliable on OS level (e.g. turning off in BIOS, or lower virtual cores on virtual machine).

-But with EE with SA you have two option how to licence. On virtual cores (for each core on virtual machine you need licence), or physical cores (for each physical core on physical machine you need licence, regardless number of virtual machines and total number virtual cores, which can be much more).

To second question, I'm not sure, but I think is not possible, but most importatntly it is not good way. Main difference between import mode and live connection mode is not technical backgroud (both is based on xVelocity engine), but developing cost and user learning path. For Import mode PBI, user need only Power BI Desktop RS, and average user, can produce nice PBI reports within ten minutes. But for Live connection mode, mean for design dedicated tabular model, which bring more opportunities (like much larger models, incremental update, multiple reports per model, and so on), you need advanced user, and need more developer tools (like visual studio or tabular editor) and much more SSAS experience. In my real practise, we have on PBIRS thousands Import mode small reports, but only tens dedicated SSAS models (but in total size much larger then all import mode reports. These are two way, for different approach, but both are correct. Yes, there is challenge how to balance performance, one option is to licence physical cores, and balance power between virtual machines using virtual cores, which you can change anytime.