SQL Based Reporting Online

(0) Share

Report

Posted on by LBlackburn

We are looking to potentially migrate from on premise to online. One of the pieces we need to figure out is how we would maintain our more complex, SQL based reporting. Fetch based reports cannot handle the reporting scenarios. I'm looking to see if anyone has had any experiences with this and how it went for them.

It sounds like we would most likely have to setup an Azure SQL Database and use the Data Export Service to start with. From there, my biggest concern is how users will consume the reports. I'm guessing we won't be able to upload or place them in Dynamics anymore. Some options I can think of are to upload them in Power BI or to have an on premise reporting server that can connect to the Azure SQL Database. I'm not sure if it's worth standing up an Azure VM just for SSRS.

Any thoughts on the best option and associated cost?

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

ba365guy 2,950 on at

Like (0)

Report

In most of the scenarios the SQL based reports are re-developed in fetch XML. But there are scenarios where fetch XML is not successful. In these scenarios following are some of my suggestions:

1. Power BI (online) : Develop the reports using power BI. Power BI is very flexible tool. If you are accessing data directly from D365, this will help you to maintain the data access privilege as defined in D365. Users can define complex reports by themselves and these reports can be accessed from D365 directly.

Power BI reports and dashboards can be accessed via mobile app too.

If data-models / cubes are designed once, further report designing can happen with low-code / no-code. Most of the times drag and drop with rich GUI

2. Using Data Export Service: You will have data in SQL. You can user SSRS, SSAS or any other BI tool in connection with SQL. The data access privileges are ignored while accessing reports (this is treated as raw data). Reports needs to be accessed from outside D365.

Mobility option depends on the third-party BI tool (if any).

If SSRS is chosen, any new / modification in any report will be driven by custom code.

Was this reply helpful? Yes No
LBlackburn 5 on at

Like (0)

Report

EDITED

Appreciate the response. It's probably best our team gets used to Power BI as that is the future it seems.

Could Power BI be used to show D365 data to users without a license?

Also, can it handle large volumes of data with the ability to export (like 300,000 records)?

Was this reply helpful? Yes No
Suggested answer

ba365guy 2,950 on at

Like (0)

Report

1. Power BI online license is required

2. You can also browse through multiple options like Power BI on Common Data Service

3. D365 Full user may not be required, but other licensing options are available I guess. I am no expert in this area of licensing. Please talk to a licensing partner

Was this reply helpful? Yes No
CU23071537-0 5 on at

Like (0)

Report

I have a few questions on the same lines. We are On-Prem and looking to move online. All else is clear but SQL based reports are an issue. We have many reports run from CRM native reporting area where we join data with an external data source (Enterprise data virtualization layer). This is done by using a linked server connection on the CRM SQL server.

In the cloud word, one option we have is to have data moved to an Azure SQL DB and then have it replicated to a local database for reporting and federation with the enterprise source. But is there a good way to surface those reports within the CRM interface?

I'm not considering the Power BI option here due to the need to join the CRM data with other enterprise data.

We also use some reports in plugins/js to export the output as PDF and attach them in emails/CRM records. How are such process transitioned to cloud?

Thanks,

Mayank

Was this reply helpful? Yes No