Forum Discussion
Architecture of Report Server
As quick win simple solution:
All what you need, you can build in Power BI Desktop RS+Power BI Report Server
1) connect to Informix using ODBC or OLEDB connector (do not use native Informix beta connector, because it not work for scheduled refresh) and get data
2) connect to excel files (must be on network shared drive or in sharepoint library) and get data
3) transform, join and clean-up data in power query as you want
4) create data model from tables (no matter from which source), create relationships, calculation, and so on
5) create visualization
6) upload to power bi report server
7) setup scheduled refresh (your ODBC/OLEDB drivers for informix must be installed on server)
For first simple scenario it is all, you are connected to two different sources, and data are processed automatically. But first complication will be PDF exports, if is manually acceptable, you can open report from report server in power bi desktop, refresh, and export as PDF manually. If for PDF creation need automation, it not possible this way, and you need use additional workarounds.
For robust enterprise solution:
Steps 1)-4) are almost same, but in Visual Studio SSAS Tabular designer
5) deploy model to SSAS server
6) on SSAS server setup scheduled refresh
7) create visualization in Power BI Desktop (using live connection to SSAS model)
8.) save PBI report to Power BI Report Server
At this point you are on same functionality as is quick win way, and you can continue:
9) create static paginated report in Report Builder (connected to same SSAS model)
10) save paginated report to Power BI Report Server
11) schedule PDF subscription of paginated report as you want
Additional note: I always recommends start with quick win way, this work is reusable (or convertible) to enterprise solution. But if you are talking about architecture, you must look ahead, if you will require onpremise Power BI Report Server only, or cloud Power BI Service only, or onpremise Power BI Report Server, with additional enterprise components like SSAS, SSIS or MSSQL DB.
Won't it be problematic to use Excel data + data from this connector?
They want kinda automatic solution and we already have licences for Report Server.
Also with your method it is possible to download this data to PDF and then share with users, but it will not be possible to view this data on a website.
**bleep**. I am sure that this PDF export will be problematic from Power BI Desktop. And from my memory I do not recall .pbix report download option - we specially created reporting services reports and they could be downloaded directly from report server.
Ok I understand the part "mashup" data but how to automate data refresh?
josef78Memorable Member
As quick win simple solution:
All what you need, you can build in Power BI Desktop RS+Power BI Report Server
1) connect to Informix using ODBC or OLEDB connector (do not use native Informix beta connector, because it not work for scheduled refresh) and get data
2) connect to excel files (must be on network shared drive or in sharepoint library) and get data
3) transform, join and clean-up data in power query as you want
4) create data model from tables (no matter from which source), create relationships, calculation, and so on
5) create visualization
6) upload to power bi report server
7) setup scheduled refresh (your ODBC/OLEDB drivers for informix must be installed on server)
For first simple scenario it is all, you are connected to two different sources, and data are processed automatically. But first complication will be PDF exports, if is manually acceptable, you can open report from report server in power bi desktop, refresh, and export as PDF manually. If for PDF creation need automation, it not possible this way, and you need use additional workarounds.
For robust enterprise solution:
Steps 1)-4) are almost same, but in Visual Studio SSAS Tabular designer
5) deploy model to SSAS server
6) on SSAS server setup scheduled refresh
7) create visualization in Power BI Desktop (using live connection to SSAS model)
8.) save PBI report to Power BI Report Server
At this point you are on same functionality as is quick win way, and you can continue:
9) create static paginated report in Report Builder (connected to same SSAS model)
10) save paginated report to Power BI Report Server
11) schedule PDF subscription of paginated report as you want
Additional note: I always recommends start with quick win way, this work is reusable (or convertible) to enterprise solution. But if you are talking about architecture, you must look ahead, if you will require onpremise Power BI Report Server only, or cloud Power BI Service only, or onpremise Power BI Report Server, with additional enterprise components like SSAS, SSIS or MSSQL DB.