Close

Request a Demo

How to Connect AtScale to Excel to Directly Analyze Large Cloud Data Sets

Last week, AtScale announced new Excel integrations and we blogged about the ability to use Excel as a powerful business intelligence platform.  In this post, we will cover the basics of using AtScale to connect Excel to a cloud data set, bypassing row limits, avoiding data extracts, and allowing analysts to leverage the flexibility of a spreadsheet based model while getting the full power of a dimensional analysis engine.

How to Connect AtScale to Excel 

This example is based on connecting an Excel model to an Amazon Redshift data set through Atscale

First, the user should navigate to the Data tab, from the “Get External Data” group open the “From Other Sources” dropdown list and select “From Analysis Services”.

Excel Data Tab to Get External Data to From Other Sources to From Analysis Services

After clicking “From Analysis Services” the user is able to input information for the live connection to AtScale and use data from the Amazon Redshift warehouse without being moved or extracted. The user is able to select the “Use Windows Authentication” option to connect to the active directory and leverage existing access control policies. 

Excel Data Connection Wizard: Connect to Database Wizard

The user is now able to view a list of available data models and select one to work with. The models in the list are those the user has access to within the limits of the enforcements of the security policies set forth by the admin.

Excel Data Connection Wizard: Select Database & Table

After which the user is able to click “Finish” and create a pivot table or chart that leverages the power of AtScale’s security, governance, and speed of thought queries and cloud data warehouses on Excel, using the measures and dimensions organized by AtScale.

Create Pivot Table in Excel

Finding Actionable Insights with Excel through AtScale

In another example based on a recent webinar with Fousquare, (How to Merge Places (POI) Data with Additional Dataset to make Smarter Decisions), we look at using point-of-interest data to analyze potential locations for a new retail outlet. POI data is merged with first-party sales data in AtScale, ensuring consistency and uniformity. This allows the data team to get the same data, regardless of what tool is being used (Excel or Power BI). 

Using Excel, we are able to create a pivot table and chart to analyze the total sales amount by location and product. Creating these pivot tables and charts is made easy by AtScale’s organization of measures and dimensions, shown on the PivotTable Fields list on the right of the Excel window. 

Pivot Chart Analysis

From our pivot chart analysis, we found that the majority of our sales are coming from California. It would be a good idea to look further into opening a new store somewhere in California.

Now that we know what state is generating the most income for our business, we next look at when would it be the best time to open up our new store. To answer this business question, we take a look at the total sales amount over time, using an Excel pivot chart/ table. We are able to see there is a positive trend of our total sales in the current/ 4th quarter of the year, an indication of a good time to open up our new store!

Excel Pivot Chart Analysis

To watch the full webinar, click here.

Summary

This has been a practical, step-by-step example of connecting an Excel PivotTable (or PivotChart) to an AtScale semantic layer on a cloud (Redshift) data set. We will continue our series on Excel and AtScale with a discussion of alternate approaches to connecting Excel to AtScale as well as an in depth look at Excel’s dimensional analytics capabilities.

More Articles

AtScale in Action: Scaling Self-Serve BI Program on Snowflake With a Semantic Layer

The ability to access data from Snowflake in a consistent manner is crucial for scaling analytics programs and building a self-service BI culture. In addition to providing control and analytics governance, the AtScale universal semantic layer accelerates BI query performance and helps control runaway compute costs. AtScale forms a single source of truth for important business metrics and analysis dimensions. In this tutorial, we’re going to use AtScale to build a semantic layer for Snowflake and consume data via several popular BI tools. We’ll also explain how AtScale can help you to scale self-service BI on Snowflake. Let’s get started.…

Read More

How To: Build Business Forecasts with Excel

In a recent webinar with Ryan Squire, Senior Data Scientist at SafeGraph, we tackled the challenges that aren’t uncommon to the Data Scientist, BI Analyst, and other Analytics Leaders. Excel was designed for creating sophisticated models, but data extracts, joins, splicing and data wrangling can be painful and risky. What can you do with Excel despite these challenges? In this blog post, I’ll share how to turn Excel into a crystal ball for your business forecasting by using Excel pivot tables against billions of data points for data exploration.  For this webinar, the AtScale team and I were excited to…

Read More