Business intelligence platform definition

Copy the flat files to Azure Blob Storage (AzCopy).

Export the data from SQL Server to flat files (bcp utility).

The data pipeline has the following stages: This reference architecture uses the WorldWideImporters sample database as a data source. AuthenticationДЄzure Active Directory (Azure AD) authenticates users who connect to the Analysis Services server through Power BI. In this architecture, it queries the semantic model stored in Analysis Services. Power BI is a suite of business analytics tools to analyze data for business insights. For more information, see Comparing tabular and multidimensional solutions. If you require multidimensional models, use SQL Server Analysis Services (SSAS).

Tabular models use relational modeling constructs (tables and columns), whereas multidimensional models use OLAP modeling constructs (cubes, dimensions, and measures). It also supports elastic concurrency, by scaling out replicas for faster query processing.Д¬urrently, Azure Analysis Services supports tabular models but not multidimensional models. In this architecture, Analysis Services reads data from the data warehouse to process the semantic model, and efficiently serves dashboard queries. Analysis Services is especially useful in a BI dashboard scenario. Use Analysis Services to create a semantic model that users can query. Analysis Services is a fully managed service that provides data modeling capabilities. Analysis and reportingДЄzure Analysis Services. It supports massive parallel processing (MPP), which makes it suitable for running high-performance analytics. Azure Synapse is a distributed system designed to perform analytics on large data. Blob storage is used as a staging area to copy the data before loading it into Azure Synapse.ДЄzure Synapse. The Wide World Importers OLTP sample database is used as the source data. To simulate the on-premises environment, the deployment scripts for this architecture provision a VM in Azure with SQL Server installed. The source data is located in a SQL Server database on premises. The architecture consists of the following components. For a reference architecture that uses Data Factory, see Automated enterprise BI with Azure Synapse and Azure Data Factory. If you need to move data on a continuing basis (hourly or daily), we recommend using Azure Data Factory to define an automated workflow. This reference architecture is designed for one-time or on-demand jobs.

The organization wants to use Azure Synapse to perform analysis using Power BI. Scenario: An organization has a large OLTP data set stored in a SQL Server database on premises.

This reference architecture implements an extract, load, and transform (ELT) pipeline that moves data from an on-premises SQL Server database into Azure Synapse and transforms the data for analysis.ДЄ reference implementation for this architecture is available on GitHub.

Business intelligence platform definition