Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear experts,
I am looking for a solution to make use of SQL Server, SSAS Tabular Model, Power BI desktop, Power BI Report Server effectively.
The company I am working for use SAP datawarehouse system to store data. Recently we start introducing Power BI as a report tool for staffs and use Power BI server to spread the reports.
Some people argue that we have to use SSAS Tabular Model as the middle layer to import data from SQL server to manipulate data in Tabular Model using Visual Studio, then import data/ or use live connection from that to Power BI desktop, finally upload to Power BI Report Server. By doing that, they believe it is secure, and the data on Power BI report server is always automatically refreshed from SQL datawarehouse.
My questions are:
1) Is that compulsory to follow that procedure? Can we skip the SSAS Tabular Model step?
Personally I think it could be overlapped way to use both SSAS Tabular Model and Power BI model. I suppose the step to transform data in SSAS Tabular Model is helpful when the used dataset is big, for example: 20 mil rows of data AND/OR when we want to use one data model for different dashboards. However, if the dataset is small, is Tabular Model still needed?
2) At present I build data model in Power BI desktop. I can only upload *,pbix file from Power BI desktop to Power BI Server Report. I cannot use "Publish" button. If the data are stored in SQL server, and I set credentials to connect to Power BI Report Server, can the report on Power BI Report Server be refreshed automatically?
3) Is there any security setup on Power BI Report Server that is equivalent to Row Level security in SSAS Tabular Model? I read about Row Level sercurity in Power BI Desktop, here (https://docs.microsoft.com/en-us/power-bi/service-admin-rls), and I wonder is that equivalent to that in SSAS Tabular Model?
I look forward to learning from you. Any insight is well appreciated!
With best regards,
Lily
Hi @LilyLe_MAN,
1. Power BI report server October version does support SQL Server imported data model. See:Power BI report data sources in Power BI Report Server.
2. The "Publish" button is used for publishing report to Power BI service instead of Power BI report server. To publish report from desktop to Power BI report server, we need to use File-> Save as-> Power BI report server. Here is a feature request for Provide option to 'Publish to Report Server' instead of 'Save As' on Power BI Desktop for Report Ser... which you can vote it.
We can configure schedule refresh for SQL Server Import mode: How to configure Power BI report scheduled refresh.
3. You can see @jtarquino's reply in this same thread: RLS In PowerBi On Prem. Also you can vote for this idea:Dynamic row-level security for Power BI Report Server.
Best Regards,
Qiuyun Yu