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.
The Problem
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.
The Solution
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.
The Process
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.
Creating a Data Source for Snowflake
In your SSAS project, create a New Data Source, selecting the CData ADO.NET Provider and entering your Snowflake credentials.
Creating a Data Source View
After you create the data source, create a New Data Source View, selecting the newly created data source, choosing foreign key match patterns, and selecting tables to add.
Creating a Cube for Snowflake
Finally, build a New Cube, selecting the tables and measures you wish to include in the cube, as well as the dimensions to be generated.
At this point, you have an OLAP cube for Snowflake, ready for analysis, reporting, data mining, and more.
Gaining Actionable Insight from Business Data
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.
Top comments (0)