DEV Community

Jerod Johnson
Jerod Johnson

Posted on

Leveraging OLAP for Advanced Analytics on your Data Warehouse with SSAS

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.
Alt Text

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.
Alt Text

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.
Alt Text

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.

Discussion (0)