Architecture of Report Server

[F0RCE]

Hello,

My organisation is about to implement Power BI Report Server.

We would like to make some managerial dashboards with business data, as currently we provide a lot of data through Excel's to out management.

Data sources:

Database Informix

Excel worksheets, that are made and filled by employees.

Solution:

Reports that shows data from both Infromix and Excel in a stylish design.

Excel worksheets works as a supplement to the database information, because this databate is not perfect, and changing it was impossible - so excels are kinda over workaround.

What would be most cost and functionally efficient to do in our situation?

I thought about:

1. creating SQL database that would store data from both Informix and Excel. Excels could be stored on a network drive and taken from there by a process (or FTP?). This database with stored infromation would be a fundamental data source for Report Server. With this data source creating report would be easy

2. connecting excel data and informix within reports without making a new data source (is it possible)? - just ETL process?

Users want to be able to download pdf. If I recall correctly .pbix report cannot do that, so we should use reporting services reports?

The problem is that excel does not have a lot of validations, could misfunction - it would be great to have a front for data providing, but my organisation rejected Power Apps, in which I could create a simple app, and I have no alternative since my skill level is quiet low.

I could try to learn Visual Basic, but it will take a lot of time, and also it might not achieve what this organisation request now.

[F0RCE]

@lbendlin 

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.

@josef78 

**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?

[josef78 · Accepted solution]

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.

[josef78]

There is more possible ways,

you want some quick win solution, you can import data from both, informix and excel files into Power BI Desktop, and mashup them. Then you can upload to Power BI Report Server and you can automate refresh data. You can create PDF files from PBI Desktop, and users can see or analyze dashboard on portal. It's really quick, but probably you will soon found some limitations.

If you want robust enterprise solution, is better way, load excel files into DB (e.g. MSSQL) using an ETL (e.g. SSIS), after build dedicated model in SSAS (you can choose between more robust OLAP or more easy Tabular mode (same as in Power BI)), and use Power BI Desktop + Report Server only as visualization layer. To this dedicated model you can connect using Paginated reports (for pixel perfect design for PDF, and also for data driven subscriptions). And also to this model you can connect with additional tools, including Excel.

As option, you can load excel files directly to SSAS using PowerQuery, instead of using MSSQL and SSIS.

And additional tip, Excel sources are really not good idea, try decide if MDS (Master Data Services included in SQL Server) with Excel Add-in is not option, or also SharePoint lists can be good way.

[lbendlin]

Power BI Desktop has an Informix connector (albeit marked as beta) and it can export to PDF. Maybe you don't need Report server or SQL databases?