Best practices on azure for a supply chain management company

Question

For a supply chain management firm, we need some advice on best (optimum) practices specific to azure.

The ultimate goal of the project is to monitor operational performance metrics in real time on power bi and keep all data in an environment where it can be analyzed and processed easily.

Facts, assumptions and expectations are as follows:
1- The company manages its operations via an in-house developed web application backed by a sql database running in a virtual environment.
2- This relational database is subject to a very heavy read/write operation 18/7 and is NOT considered to be migrated to Azure. Moreover, the size of daily transnational data is up to 5 GB.
3- It has been decided to use power bi as the final reporting/dash-boarding tool.
4- In the near feature, the company is willing to run some machine learning algorithms on top of this big data and support its mid-term business strategies.

The challenges:
1- Transferring (syncing) data from on-premise sql to azure environment to cater for real time monitoring requirements has limits. Consider the fact that we have a transaction and heavy load database and records are updated retrospectively, which makes the syncing much harder. What would be your suggestion to cater for real-time expectations.
2- Power bi premium includes azure analysis services (AAS) behind the scenes. However, we are under the assumption that this AAS is not running on the best hardware. Thus, this leads us to consider using a stand alone AAS and use it as the data source on Power Bi. What would be your suggestions on this matter?
3- The following pipeline and or tools seem to be the plan. on-premise sql -> (via data factory) -> azure sql on azure data lake -> AAS -> Power bi . Any suggestion on this?

Please feel free to ask if anything is unclear.
Best Regards,

Answer 1

@Anonymous Welcome to Microsoft Q&A forums.

For transferring high volume of transactional data in near real time, you will need an Express route connection from your On-premises network to Azure.
Please check our reference architecture - Extend an on-premises network using ExpressRoute

You can use transactional replication, ADF to transfer data from your on-premises sql server to Azure depending on the latency you can afford in your reporting system.

Here is another reference architecture that uses blob storage as an intermediate for transferring data, but you get the idea.
Enterprise business intelligence

We have lot of reference for various industry domains and use cases on our Architecture center.
Please browse through and let us know if you have specific questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.

Answer 2

Hello @Anonymous ,
Just to add to which @KalyanChanumolu-MSFT said .
Before I start the replying to the ask , I thank you for the great question , I really liked the way you called out the "Facts , assumption & expectation"

1- Transferring (syncing) data from on-premise sql to azure environment to cater for real time monitoring requirements has limits. Consider the fact that we have a transaction and heavy load database and records are updated retrospectively, which makes the syncing much harder. What would be your suggestion to cater for real-time expectations.

The data which is in premise and I am assuming is running hot , will use the CPU of the VM while running queries . You never mentioned which version of the in premise SQL are you using if you are using any latest version , you may use Always-On setup or if you are using old version may be log shipping . I know many enterprise do setup business continuity program ( BCP ) and they do setup one of these . The advantage of this setup is that you can read the data from the secondary node and use NO CPU cycles of the primary(main) server .

2- Power bi premium includes azure analysis services (AAS) behind the scenes. However, we are under the assumption that this AAS is not running on the best hardware. Thus, this leads us to consider using a stand alone AAS and use it as the data source on Power Bi. What would be your suggestions on this matter?

Well if you are using AAS , then you will have to do a data refresh to get the latest data on the PBi report . How are you planning to do the data refresh ? Does that data refresh not add to the delay to the report ? You mentioned this "The ultimate goal of the project is to monitor operational performance metrics in real time on powe...."

3- The following pipeline and or tools seem to be the plan. on-premise sql -> (via data factory) -> azure sql on azure data lake -> AAS -> Power bi . Any suggestion on this?

To ADF to pull data you will have to use the ADF triggers , In ADF we have two kind of trigger , scheduled and tumbling window trigger ( TWT ) . Scheduled trigger can be scheduled NO less then 15 mins ( as of today , which can add to the delay ) . TWT can be scheduled every min , I request you to less read about these more .

Please do let me know if you have any further questions .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members