Close

Request a Demo

AtScale Modeling – Beyond the Basics

AtScale Modeling - Beyond the Basics

This blog series will delve deeper into AtScale’s unique no-code approach to building business-oriented data models on top of live cloud data sets with specific discussion around:

  • Designing business-oriented data models (aka. Semantic models)
  • Mapping common or shared dimensions
  • Setting up dimensions that may relate to specific facts 
  • Unrelated dimension handling on measures
  • Calculated measures to “put together” measures from multiple facts
  • Query Data Sets (what they are, when you need them, and how to use them)
  • Quick and easy data manipulation
  • Uncommon measure types (standard deviation and semi-additive)
  • Hierarchies (special sorting and hiding levels)
  • Bridged Dimension

Introduction

Breaking complex models down to a simpler form can be great for getting a point across and even a must for demonstrations; however, data in the real world is never really “simple”. This is the case because you may find the need to include data from multiple fact tables in your model, or you might even have data coming from multiple systems that need to be merged for reporting. Below shows a model we are building out that has two fact tables. We will first discuss how we actually deal with shared dimensions between multiple facts, any dimensions that may be specific to different fact tables, as well as some of the issues that may pop up when creating multi-fact models. 

 Multi-Fact Models

Multi-Fact Demo

We start our demo with some store sales sample data taken from Snowflake with a number of dimensions. 

Note: To follow along with this data you can click on the “Data Sources” button highlighted in red, then search for the “SNOWFLAKE_DATA_SAMPLE” drop down button. We are working with the TPCFD_SF10TCL data. 

When you click that drop down button, towards the bottom, you will see “WEB_RETURNS”, a.k.a. web sales data. Let’s add this data into our model as well like so…

Next we want to start joining our web sales data to the common dimensions that will be shared amongst the different fact tables. To start, we can just search for the first dimension we need which will be “WS_PROMO_SK”, and drag it to the Promotion’s dimension then click save (as seen below). 

Web_Sales Table

Now just repeat this step joining “WS_BILL_CDEMO_SK” with both the Customer Demographc’s dimension: “CD_DEMO_SK”, as well as the Household demographic’s dimension:  “HD_DEMO_SK”. Do this again by joining “WS_SHIP_CUSTOMER_SK” with the Customer’s dimension “C_CUSTOMER_SK”.  Lastly, we want to join “WS_SOLD_DATE_SK” with the Time dimension “D_DATE_SK”, BUT before you click save, label the Relationship Settings as “Sold”, and repeat this for “WS_SHIP_DATE_SK”, but label it as “Ship”. We want to label these differently because the two play different roles within different contexts. AtScale will automatically replicate these attributes for the two labeled Date Attributes.  That is it for joining these common dimensions! Below shows a zoomed out of what we just did:

Web_Sales Table

Just like there are specific dimensions for the store sales data, there are also going to be relevant things for the website sales as well. For example, if you look through the “WEB_SALES” table, you will notice a key for warehouses. Let’s see this information presented in a geography based on where these warehouses are. To do this, we show the steps in the video below where we go back to the same drop down button that brought us to our sample data from the Data Sources button, look for a table labeled “WAREHOUSE”, and we’re going to drag that table over to our dimensions on the right side. Next we want to name our new dimension (i.e. “Warehouse Dimension”), set our KEY COLUMN to the warehouse SK (W_WAREHOUSE_SK), and our VALUE COLUMN to something like the warehouse name or in our case just W_WAREHOUSE_SK again. Now that you have created that, zoom out to find where it got placed and drag it up with the rest of the tables. 

Now we want to add some details to our new dimension. Double click the warehouse dimension and change the bottom level name to “Warehouse Number”. We want to build out our geography data within this dimension. As you can see, within our warehouse dimension table we can scroll down to see the geography data. The video will show where we drag “W_City” just above “Warehouse Number”, followed by “W_County”, “W_State”, and “W_Country”. You can change each name to include “Warehouse” instead of just the “W_”. 

NOTE: You might notice where the Warehouse City flag is in red. If you click on that it tells us “A child’s key value points to multiple parent key values”. This is essentially a unique key issue where some states may have the same city name. Nonetheless, to fix this, we can add County as an additional KEY COLUMN. 

Now that we have our warehouse dimension built out you can hook it up to the Web_Sales fact table. To do this, just drag “WS_WAREHOUSE_SK” from the Web_Sales table to the Warehouse Number field. We now have our warehouse dimension connected to our sales data! Final table output looks like this:

Warehouse Dimension Connected to Sales Data

You probably are beginning to notice that we can create measures as well as calculated fields all within AtScale. This functionality allows us to save our end users a few steps  when using this model in a BI tool (i.e. Tableau) for reporting and analytical purposes. For this example we are only simply taking the total of different measures; however, in some use cases you might have much more complex calculations, so this creates a convenient and time saving feature. In addition, it also provides governance, because now you know everyone would be using those calculations the exact same way, creating consistency. To test this, let’s say we created two measures and saved them to a particular folder (AtScale remembers it if you just type something in), one for web sales data and one for quantity sold. You can then create a calculated field to combine store sales data with web sales, (total sales data) and store quantity data with web quantity (total quantity) with simple MDX syntax like seen in the two videos below:

Creating Measures

Creating Calculated Measures

Creating Measures View

Let’s work with a model that is much more complex than the examples we have been working with (i.e. more measures, dimensions). 

NOTE: You don’t need to implement these into your data, just follow along

Before we get into this from a BI’s point of view, we need to address a particular problem with our geography data. There aren’t any joins between our geography data and the web sales fact, so if we were to connect to Tableau to make a particular data viz involving geography, it’s going to give us an error stating that our dimensions are unrelated. To fix this, we need to go back to our web extended sales price measure, and tell AtScale how we want to deal with unrelated dimensions. As shown below:

Note: This needs to be done for all measures 

We can show what this all looks like when connecting to Tableau below. As you can see there is no error when we drag the state data over.

SUMMARY

AtScale enables data, features and relationships through multi-fact models which allows for infinite data modeling and data manipulation possibilities. Today, we explored mapping common or shared dimensions, calculated measures as well as best practices for data modeling. Click here to see how we work with uncommon feature types, hierarchy tips, and more!

More Articles

A Capitalist’s Approach to Analytics & Performance

One of the most important concepts any business must consider when investing in technology infrastructure or applications is ROI (return on investment). Capital is one of the most precious commodities an organization has – and often one of the most scarce – next to (or right behind) people. At AtScale, our primary mission is to help organizations around the globe modernize their analytics infrastructure and applications; essentially, making Cloud OLAP a reality for the Fortune 2000.   The vast majority of our prospects and customers are on a multi-phased journey to modernize their analytics stack, an endeavor that is expensive, both…

Read More

The Future of Data Warehousing

Surprise! You never would have guessed the future is in the cloud. We should not be overly concerned with “the future” of data warehousing. The past five years have brought fundamental new capabilities at the platform level and most organizations are wrestling with adopting these new technologies. We should be focused on “the now” of data warehousing and how to use the new technology as a platform for creating “the future” within your own organization. New approaches and technologies have effectively removed limitations and now the future is malleable – up for grabs to the most aggressive and creative companies.…

Read More