by Jerod Johnson | May 19, 2021

Leveraging OLAP for Advanced Analytics on your Data Warehouse with SSAS

SQL Server Analysis Services (SSAS) is used to build high-level aggregated views of data, enabling users to rapidly build dynamic reports and dashboards to centralize business measures such as key performance indicators (KPIs).

At CData, we provide the connectivity needed for users to leverage the analytics capabilities of SSAS for their enterprise data, no matter where their data is stored.

In this post, we walk through how CData helped one customer seamlessly continue their analytics in SSAS after migrating terabytes of data into a Snowflake data warehouse

The Problem

When a major global retailer migrated 150 TB of their business data to Snowflake, they wanted to continue using SSAS for analytical processing and data mining. Their business teams had developed a series of OLAP cubes (see below) in SSAS based on their old data model. When their teams went to produce the same cubes from Snowflake, they found that the native connectivity and open-source connectivity options available fell short of their needs, lacking the functionality required to work with SQL Server.

The Solution

Ultimately, their teams chose CData to connect Snowflake with SSAS to re-build their OLAP cubes. CData provides a direct SQL interface for Snowflake that enabled their engineers to quickly and easily attach the cubes their teams had previously created directly to their data in Snowflake. Once the cubes were deployed, their business units were once again able to analyze, process, and mine the data.

The Process

Connecting to and creating cubes from business data is as simple as installing the CData ADO.NET Provider, creating a data source and view in an SSAS project, and creating and deploying a cube from the data. Below, we walk through the basics of the process, with a link to a full article below.

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, create a New Cube, selecting the tables and measures you want to include in the cube and selecting the dimensions to be created.

At this point, you have an OLAP cube for Snowflake, ready for analysis, reporting, data mining, and more.

For the full walkthrough, read our Knowledge Base article.

Gaining Actionable Insight from Business Data

Thanks to standards-based connectivity, CData customers can continue to use their preferred processes and platforms to work with their business data, no matter where that data is stored. In this case, our customer was able to continue using their SSAS data cubes when they had moved the entirety of their business data into a new data warehouse.

Read more about the CData ADO.NET Providers for Snowflake and download a free, 30-day trial to see the CData difference for yourself.