AtScale + Excel: Working with Blended Data Sets

AtScale’s semantic layer weaves together multiple data sources into a single view of analysis-ready data that can be easily accessed and reliably used by analysts and data scientists for any number of use cases. AtScale’s no-code data modeling utility allows users to quickly spin up semantic models with customized metrics, and structured dimensions and hierarchies that let data consumers quickly grab the specific cuts of data they are looking for. The AtScale platform manages query traffic to underlying cloud data and leverages intelligent aggregation logic to optimize analytics performance and cloud resource consumption.

This is the last blog in a series highlighting this powerful combination.  In prior posts, we have reviewed the basics AtScale + Excel in our How a Semantic Layer Turns Excel into a Sophisticated BI Platform blog; we discussed how AtScale can speak the native query language of Excel: MDX; and we talked about the power of using Pivot tables connected to AtScale for ad hoc analysis. This post will dive into the very common example of building a sophisticated financial model in Excel linked to underlying cloud data sources.

Needing to access multiple data sources within a complex model is common – particularly in financial analysis use cases. AtScale allows users to create a semantic model using multiple data sources and create a single analysis-ready data set for analysts. When combined, these blended data sets may comprise billions of rows of data. Querying a data set of this size for specific data slices would be slow – even using the most powerful cloud query engines.  AtScale leverages knowledge of the data structure (generated in the semantic model) and the use of autonomous aggregate logic to create speed of thought performance for analysts. 

Let’s walk through an example of how AtScale is able to blend together multiple financial data sources from a Snowflake data warehouse and allow an analyst to generate a rich, security trading analysis model using Excel.

The Semantic Model

First, let’s use AtScale to blend multiple data sources into a single semantic layer, with trade, trader, and position data. For an in-depth tutorial on creating a semantic model, make sure to check out our blog post: Scaling Self-Serve BI Program on Snowflake With a Semantic Layer.

The image below depicts the resulting data model as it appears in AtScale Design Center

Once we have created and defined our semantic model in AtScale, we are able to access the same measures, dimensions, and hierarchies using a variety of analysis tools. In this example, we will be using Microsoft Excel as our analysis tool of choice to analyze large cloud data sets.

To access our newly created semantic model in Excel , simply navigate to “Get External Data” and “From Other Sources” menus in Excel and complete the Data Connection Wizard. Once we have established a connection to our AtScale semantic model, we can leverage the flexibility and power of Excel in our financial analysis. First, we are able to connect the billions of rows of data from our model to an Excel pivot table, as you would create any pivot table in Excel. 

Below we have created a pivot table that looks at all trades by Exchange, Action, and Date. AtScale allows the financial analyst to query through billions of rows of financial data in a matter of seconds. We can also see how the semantic model we defined in the AtScale Design Center provides analysts with a clear and organized view of their trading data. 

Excel pivot tables are very useful as they provide great flexibility for analysts to explore and model the data. Using the Excel GETPIVOTDATA or CUBEVALUE functions, we can use the pivot table’s data to create sophisticated models creating more possibilities for analysis.

But how is AtScale able to create a pivot table from billions of rows of data in a matter of seconds? 

Query at Scale

Through the use of aggregates and autonomous aggregate creation, an analyst can query billions of rows of data in a matter of seconds. By querying data at the speed of thought, less time is spent waiting for Excel to query the data and more time is spent in analyzing the data and creating models.

Let’s now take a look behind the scenes to see how AtScale aggregates improve query time. In AtScale Design Center’s AGGREGATES tab, we can see how billions of rows were aggregated automatically down to 99 rows, allowing for sub-second query performance. The query initiated from our pivot table above was optimized by AtScale with the autonomous creation of an aggregate.

Summary:

In this post, we covered the importance of a semantic layer in creating a financial model in excel based on multiple data sets. We showed how AtScale can help to:

1. Seamlessly combine multiple datasets for sophisticated analysis.
2. Increase query performance through the use of aggregates.
3. Accessing the semantic model through Excel.

Not only is creating a semantic model with multiple data sources easy and intuitive with AtScale,  accessing these semantic models using a flexible tool like Excel means everyone has access to valuable analysis-ready data sets, managed on shared cloud infrastructure, with high performance queries.