Using Microsoft BI for Excel Report Distribution

rip73

New Member
We are looking at using Microsoft BI to give reporting capability that could be used on our company website.

We are also looking into having Microsoft BI to generate Excel reports that we distribute daily to replace our antiquated method of generating and sending out reports.

Has anyone been able to successfully set up Excel report creation and email using Microsoft BI?
 

rip73

New Member
Good morning,

Yes. Progress is the database that the reports would be generated from.

Currently we have a 3 step process to generate reports from our system that are emailed.

1. A Progress program generates the data file (.txt, .csv)
2. Windows Scheduler process that runs an Excel file that creates a spreadsheet.
3. Linux script emails the report to our customers and internal people.

The hope is that using a tool like Microsoft BI can give us a way to do this in 2, or maybe after we get some other things in place 1, step instead of the 3 we have now. Minimizing points of failure and having better options as far as creating more professional looking emails is a point of emphasis as well.

There are also reports that are on accessible by our customers through our website. BI has a very easy way of doing analytics that would be much easier to do generating them on our own and having to build the pages ourselves. All of the web reports are generated by users requesting data by parameters that they choose. So data ranges are not locked in so to speak. We are looking into how well Microsoft BI handles that aspect as well.

While I am doing research I am looking into if there are any horror stories in getting this to be report engine and if there are major flaws/pitfalls that Microsoft BI has in working with Progress in case I need to just break this avenue of research off and go in another direction.

If you could point me to some good resources that can give some insight that would be great. Also, if you feel there is a better product that could be used that information would be helpful as well.

Let me know if more information is needed to give an answer.
 

TomBascom

Curmudgeon
I imagine that Microsoft will want to talk to the OpenEdge database using a SQL-92 connection. If you do not already have that functioning then that sort of thing would be "in scope" for this forum. Things like setting up Excel and email using Microsfot BI are best be left to people who get their kicks using Microsoft's tooling (that is definitely not me).
 

Cringer

ProgressTalk.com Moderator
Staff member
Not had a massive amount of experience here, but one of the biggest issues could be performance. If you're reporting against your production database, then it's likely that production will either impact the performance of the BI tool, or the BI tool will impact the performance of production. Of course this can be tuned, but it's a consideration.
In order to mitigate this, the people I've worked with who do this often report against a replica of production. So, either a point in time replica, or a real time replica, depending on how up to date the reports need to be.
Point in time is relatively easy with backups and restores, or even using after imaging.
Real time will be more costly.
You can use OpenEdge Replication Plus which will have the added benefit of providing enhanced disaster recovery if you configure it correctly.
Alternatively you can use Pro2SQL. The big advantage of this is that you will be reporting against a SQL database, not a Progress one. That means you can structure your data in a method that suits the reporting tool, and enquire of it natively, rather than using an ODBC/JDBC conneciton or the like.
Obviously all these solutions carry costs.
 

rip73

New Member
I imagine that Microsoft will want to talk to the OpenEdge database using a SQL-92 connection. If you do not already have that functioning then that sort of thing would be "in scope" for this forum. Things like setting up Excel and email using Microsfot BI are best be left to people who get their kicks using Microsoft's tooling (that is definitely not me).

Hello,

We are having to re-tool a lot of things as us older guys are moving out and or moving on. We are trying to make our environment more accessible and quicker to pick up and be functional given what is out there, as far as programmers go, while keeping our Progress DB which we are happy with.
Agreed, using MS to augment things is not where I get my kicks but options are options and so the proverbial kicking of tires must be done.

Thank you for your responses!
 

rip73

New Member
Not had a massive amount of experience here, but one of the biggest issues could be performance. If you're reporting against your production database, then it's likely that production will either impact the performance of the BI tool, or the BI tool will impact the performance of production. Of course this can be tuned, but it's a consideration.
In order to mitigate this, the people I've worked with who do this often report against a replica of production. So, either a point in time replica, or a real time replica, depending on how up to date the reports need to be.
Point in time is relatively easy with backups and restores, or even using after imaging.
Real time will be more costly.
You can use OpenEdge Replication Plus which will have the added benefit of providing enhanced disaster recovery if you configure it correctly.
Alternatively you can use Pro2SQL. The big advantage of this is that you will be reporting against a SQL database, not a Progress one. That means you can structure your data in a method that suits the reporting tool, and enquire of it natively, rather than using an ODBC/JDBC conneciton or the like.
Obviously all these solutions carry costs.

Performance is an issue we are keeping in mind as well as costs. One of the reasons we have been looking at other methods to do this is that adding more Progess tools seems to be more costly than makes sense for us. The company overall is on the small side and the IT department has not been as much of a focus as it now. While we are getting more resources than before we still have a small pool to swim in financially speaking. Have a new director, new directions so a lot of change is coming. For the better though.
The data would need to be up to the second. We often have people go to their data, make changes and then immediately run reports to see how they are impacted by the change.
The examples that I have seen are reporting against an SQL database and looks to be the best way to go so I think that is the right way to look at this whether we go with MS BI or not.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The data would need to be up to the second. We often have people go to their data, make changes and then immediately run reports to see how they are impacted by the change.
Then your options are:
(a) access the data in production
(b) access the data in a read-only replica (OE Repl Plus license required)
(c) access the data in a SQL Server or OE replica, updated via Pro2 (Pro2 license required)

The examples that I have seen are reporting against an SQL database and looks to be the best way to go so I think that is the right way to look at this whether we go with MS BI or not.
As Tom indicated, your production DB can be "an SQL database", from your reporting client's perspective. If you run a SQL broker in production, your BI client can make an ODBC or JDBC connection and read the data directly, without needing to replicate anywhere. The only requirement is that the machine on which the BI client runs needs an OE driver. You have access to this driver via your RDBMS license. I can't say whether this approach is feasible for you. It depends on several factors, as Cringer said.
 

Cringer

ProgressTalk.com Moderator
Staff member
That depends on database activity Tom. It uses CDC to write changes to a staging table which are then processed across using worker processes. So there's going to be some delay, but it's usually pretty up to date. Although obviously not instantaneous.
 

rip73

New Member
Thank you everyone all for your information. This is not really going to be a one stop shop to address all our reporting needs but this process has helped us in taking a much more informed looked at how and what we need to look for in a BI tool.
 
Top