Using a Semantic Layer Solution to Replace SQL Server Analysis Services (SSAS)

AtScale’s semantic layer solution offers the capabilities of SSAS, but is much more open and business-friendly to the cloud data ecosystem. It can connect to any business intelligence (BI) tool, and it supports your data science teams through Jupyter Notebooks or various ML applications.

In this blog, we will discuss the differences between AtScale and SSAS, how the semantic layer platform provides a better solution across your enterprise, as well as how to get started with creating your first dimensional model (“Cube”) within the AtScale Design Center. 

AtScale vs. SSAS

How do these two solutions differ when they appear to look the same in terms of supporting different native protocols? 

First, AtScale connects to data where it lays. Whether it’s stored in a data lake like Hadoop, or a cloud data warehouse like Snowflake, the data stays where it is. There is no pre-compute, data aggregation, or extracts, which means you get full access to the full fidelity in your data warehouse. We support not only MDX and DAX queries, but also SQL to support tools like Tableau and Looker. This is a cloud-based, lightweight service line that has a live connection to your data, which will scale your data without data storage or memory to maintain. 

AtScale “Cube” Creation Walkthrough

Let’s start this demo off by creating our own virtual cube within the AtScale Design Center. To do this you can click “New Cube” in the top right corner, and give it a cube name as seen below. This will then take you to the canvas which will be used to build out your AtScale model. 

Below also shows the canvas where we can connect to different data sources using the Data Sources pane. In the example below we will be using Snowflake.

To build our model, it is as easy as dragging tables into the canvas like it shows below. Then we want to define those tables as dimensions and facts. The video below shows examples of what it looks like to turn sales amount and sales quantity into measures, to which can be saved to a folder (you can see AtScale will handle the default aggregation method as a SUM for the sales data).

Now that we have a couple metrics, the video ends with how to create a calculated column. We can also augment the fact table with data already present using a calculated column. This is useful for those situations in which changing the underlying data and/or structure will take too much time, resources, or both. For example, if we want to create a calculated column called “Sales Tax”, just click the hamburger button at the top right of the table, and then “Calculated Column” shown in this video:

Below demonstrates using the Library pane which allows you to store dimensions as conformed dimensions and share them across models within the same project. These dimensions can be dragged onto the canvas, and unlike SSAS we can create relationships just by hooking them up to our fact tables. 

For product information, you may have nested fields in modern data warehouses like Snowflake. However, with AtScale, you can create these mapped columns where in the example below we make keys called “Color” and “Style” that get turned into virtual or degenerate dimensions (they can be dragged over to the right to become dimensions). This will allow the BI user to use these as filters or slicers within their tool.

Once finished,  we can publish our AtScale model which allows us to connect to our various BI tools like Power BI as well as ML tools. Here we have Power BI using a live connection to our data. After creating this connection, we get excellent performance from the model we just created. Power BI will inherit the full model by communicating through DAX queries where you can see all of the dimensions without having to remodel. 

AtScale will automatically generate the right SQL queries against our Snowflake data warehouse at BI speeds. Below shows that same connection, as we have visualized our sales data. 

In summary, this can be done in Tableau, Excel, Looker, and any AI or ML tool. You will get the same answers from each tool as they use the same semantic layer creating consistency across your business. Whether it is key insights, ML features, or any kind of manipulated data, anyone in the organization can see the data and use it for their data science projects.