Project architecture recommendation and improvement

Question

Hi.

I am studying a company project that involved developing a very large tabular model in Visual Studio then deploying it to Azure Analysis Service. Power BI then connects to the Analysis Service endpoint/database. This was done because developing the model in Power BI directly would obviously be slow. SSMS is also used to preview the tables and the tabular models deployed. All of this is done on a large VM.

This was done a couple of years ago. I just wanted to know if it's still the recommended way to achieve this. The use case in summary, is we will have a large tabular model with very complex relationships, several tables, columns and measures, etc. All tables will come from Azure SQL or CosmosDB, and the data is updated/sometimes removed and repopulated frequently (either daily, weekly or monthly). There will also be workflows that run on a schedule that gets public data (twitter, stats, news websites, etc.), analyzes them (sentiment, etc.) and populates the SQL tables (also connected to Visual Studio).

Does the above architecture suffice? Although, loading in a large model in Visual Studio makes it unresponsive when trying to navigate, create measures, deploy the model, etc., and you have to install a few extensions for different services I believe.

How would you go about this? Is there a more convenient way?

Answer 1

Azure Analysis Service (AAS) continues to be a great place for the tabular model.

Azure Machine Learning/Azure Cognitive Services can help you perform analysis (sentiment, etc) using social networks as sources.

For all your complex ETL processses and workflows, Azure Data Factory pipelines can really help. Using Azure Data Factory or Synapse Data Factory, you can create and schedule data-driven workflows (called pipelines) that can ingest data from disparate data stores. You can build complex ETL processes that transform data visually with data flows or by using compute services such as Azure HDInsight Hadoop, Azure Databricks, and AzureSQL Database.

This article can help you on automate the AAS portion on Azure Data Factory.

Here you will also find ways to refresh Power BI data sets using Azure Data Factory.

You can send messages and custom emails from Azure Data Factory as explained here.