Azure Synapse Analytics
Client: USA-based engineering company providing services to other companies.
Our Expertise: Business Intelligence
Technology Used: Azure Synapse
To create financial analysis and operations analysis for their companies and locations using multiple databases for each company and different data source types
We created azure-based data architecture to provide a single source data for Power BI reports. Advantage is azure synapse has inbuilt deep integration with other Azure services such as Power BI.
Azure Synapse contains the same Data Integration engine and experiences as Azure Data Factory, allowing you to create rich at-scale ETL pipelines without leaving Azure Synapse Analytics.
- Ingest data from 90+ data sources.
- Code-Free ETL with Data flow activities
- Orchestrate notebooks, Spark jobs, stored procedures, SQL scripts, and more.
So, in the current scenario, users were entering the data into SharePoint, Microsoft Access DB and Pervasive Database, and other multiple data sources for different locations and different companies.
To analyze the data in the Power BI at high-level Azure synapse analytics helped to integrate all these data sources in SQL pool and then serving the required data from the warehouse into a model that can then be consumed as shown in the diagram.
Using the Tabular model, we replaced calculations in DAX with calculation groups in the model, which reduced overhead for Power BI and improved performance. A large part of this is to ensure that data can be compressed as much as possible so that we can store more data within the model so that we can present data in an efficient way.
Transformation at a single point while combining them all rather than doing at each data source level
Less number of measures and a consolidated data structure