SQL Server Analysis Services (SSAS) is used to create high-level aggregated views of data, allowing users to quickly create dynamic reports and dashboards to centralize business measurable values like KPIs (KPIs).
In this article, we'll cover how a user continued their analytics in SSAS after transferring terabytes of data into a Snowflake data warehouse.
After migrating 150 TB of business data to Snowflake, a major global retailer wanted to keep using SSAS for analytical processing and data mining. Based on their old data model, their business teams created a set of OLAP cubes (see below) in SSAS. When their teams tried to make the identical cubes with Snowflake, they discovered that the native connectivity and open-source connectivity options were not able to work with SQL Server.
An OLAP cube, also known as a multidimensional cube or hypercube, is a data structure that stores aggregated data and allows for near-instantaneous data analysis due to precomputed value sets.
To re-build their OLAP cubes, their teams ultimately picked CData to integrate Snowflake with SSAS. CData offers a direct SQL interface for Snowflake, allowing its engineers to rapidly and efficiently connect the cubes their teams have previously constructed to their Snowflake data. Their business units were able to evaluate, analyze, and mine data once the cubes were installed.
Creating a data source and view in an SSAS project, building and deploying a cube from the data are all as simple as installing the CData ADO.NET Provider.
At this point, you have an OLAP cube for Snowflake, ready for analysis, reporting, data mining, and more.
Companies choose to work with the CData Snowflake Adapter because of its ability to allow real-time integration with SSAS as well as standards-based connectivity, regardless of where the data is kept. This way, users can continue to use their SSAS data cubes when they move the entirety of their business data into a new data warehouse.
If you are interested in trying it out for yourself in order to get a better understanding, you can download a free trial of the CData Snowflake Adapter from the CData website. Feel free to ask any questions and discuss further.