Upgrade Azure Data Warehouse with Cloud OLAP Analytics

Microsoft Azure has seen explosive growth in the last couple years. In business intelligence, analytics, and data science areas, Azure provides a rich set of services that enable data scientists and analysts to work on large and complex data sets to deliver business value.

Microsoft partners from around the world gathered at last month’s Microsoft Inspire conference to get a first look at Microsoft’s latest technology. This year, Azure migration was a major focus. Migrating to Azure not only helps control operations and infrastructure costs, it also opens the door to additional analytics use cases.

Azure SQL Data Warehouse

Azure SQL Data Warehouse is Microsoft’s cloud data warehouse offering. It seamlessly integrates with Azure Active Directory, Azure Data Factory, Azure Data Lake Storage, Azure Databricks, and Microsoft Power BI. It also works well with other integration and business intelligence tools on the market. Some key features of Azure SQL Data Warehouse include elastic scaling, unlimited storage, automated administration, and advanced workload management.

The following is a diagram of an Azure Modern Data Warehouse solution:

1. Data (structured or unstructured) is ingested into Azure Data Lake Storage (ADLS) using Azure Data Factory (or any other ETL product).
2. Raw data stored in ADLS is then transformed by Azure Databricks jobs.
3. The result of the ETL processes. The cleansed, standardized, de-duplicated, and re-formatted data is stored in Azure SQL Data Warehouse. 
4. Azure Analytics Service provides aggregation services on top of the Data Warehouse to PowerBI dashboards for fast responses.
5. PowerBI can also query Azure Databricks tables directly.

SSAS – Query Acceleration Layer, but Only Available on Premises

SQL Server Analytic Service (SSAS) is a query acceleration layer that sits on top of data warehouses. It reads data from star or snowflake schemas in the data warehouses and calculates the aggregations. The results of these calculations are stored in a data structure called a cube.

This type of analysis is referred to as multidimensional analysis, and the related software is called MOLAP (Multidimensional OnLine Analytical Processing).

With multidimensional analytics, aggregation queries are answered by simple lookups into the cubes. This ensures fast, predictable, and guaranteed query response times. Since SSAS isn’t built on a distributed architecture, server configuration limits the total data and concurrent users it can support. In other words, you’d have to scale up to process larger workloads.

Unfortunately, SSAS is not available in the cloud. The closest service to SSAS in diagram 1 is Azure Analytics Service. Azure Analytics Service, while providing a unified semantic layer to the BI tools, does not have multidimensional analytics capabilities. Once migrated to the cloud, customers who have been running enterprise data warehouses and cubes on premises will find out that they are not able to click their dashboards interactively (as they are used to doing on premises).

Kyligence Cloud – Multidimensional Analysis in the Cloud

Fortunately, Kyligence can fill in this gap in the Azure Analytics environment. Kyligence Cloud Big Data analytics platform offers a managed augmented OLAP analytics service in the cloud. It leverages cloud-native computing and storage infrastructure. This enables fast, elastic and cost-effective analytics innovation, with any data lake and at any scale.

On the Azure platform, Kyligence reads data from Azure SQL Data Warehouse and generates pre-calculated aggregations. It then stores the result in Azure Data Lake Storage. It exposes ANSI SQL interfaces to Power BI or other BI tools.

Kyligence Cloud also provides cluster deployment and management, account management, and online diagnosis capabilities. It can also serve as the semantic layer for the BI tools.

Built on a distributed architecture, Kyligence Cloud can easily scale out to support a large amount of data and concurrent users. Recently, during a test for a major financial services company, we built aggregations for 350 billion rows of data on Azure. Enterprises use Kyligence’s OLAP engine to build cubes for billions of records to serve 1000s of concurrent users.

Other Benefits of Running Kyligence Cloud in Your Azure Environment

Kyligence goes beyond simply addressing the limitations of a SSAS-based approach. Enterprises adopting Kyligence as a solution for Big Data analytics on the cloud can also realize these additional benefits:null

Do More with Less

Once the cube is created, users can slice and dice data any way they want. It is very easy to support new requirements that need different queries and aggregations. End users can create new charts in the dashboard and see the results instantaneously.

Kyligence’s intelligent modeling and optimization capabilities automatically analyze query performance and cube consumption statistics in the background. This allows it to make necessary adjustments without requiring user intervention.

Lower Cloud Costs

Because aggregation is pre-calculated, there many fewer run time aggregations happening in the query. This reduces compute cost in the cloud. Even with increased storage costs (to store the pre-calculations), enterprises still see dramatic cloud cost savings.

Serve All Query Requirements

In the architecture diagram above (Diagram 4), Azure SQL Data Warehouse stores historical transactions and Kyligence stores aggregated results. Kyligence can also serve as the unified query entry point for both aggregated and detailed queries.

If a BI tool issues an aggregated query, Kyligence will simply look up the cube and get the result. If the BI tool asks for a specific transaction, Kyligence can route the query to the data warehouse. Azure SQL Data Warehouse will execute the query, fetch the transaction, and sends the result back to the BI tool.

See How Kyligence Cloud Enhances Azure Data Warehouse

If you're curious about what Kyligence Cloud looks like in action, this demo provides a helpful overview:

Get Started Upgrading Azure Data Warehouse

There has never been a better time to start migrating your analytics to the cloud. Technological advancements in cloud computing platforms (like Azure) have made transitioning a breeze. While the path forward may appear more streamlined, selecting the right cloud software and partners to help support that migration is key for success in the cloud.

If you’re ready to take your next steps towards migration, or wish to improve BI performance on the cloud platform you’re already using, check out our cloud big data platform and other extreme OLAP BI solutions.

References:

(1) https://azure.microsoft.com/en-us/solutions/architecture/modern-data-warehouse/

(2) https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-cube-objects/cube-cells-analysis-services-multidimensional-data?view=sql-server-2017