Sprinkle Docs
Search…
πŸ’ 
Data Models
Overview & Detailed Walkthrough of Model Features.

Overview

Models, a powerful feature in Sprinkle that helps in presenting a multi-dimensional view of the table. It can be created without writing a single line of SQL queries. It is configured with a simple point and
πŸ–±
clicks user interface.
Primarily Models comprises date dimension, dimensions, measures.
​
πŸ’‘
Measures are Quantitative data like Amounts, Averages, Count, Prices, quantity, etc. Measures can be aggregated and used to derive other measures.
​
πŸ’‘
Dimensions are Qualitative data like names, dates, geographical locations, etc. which are used to categorize or Segment your data. They are the attributes on which the measures are calculated or grouped, and date dimensions are used to construct the data within a timeframe.
The Model page consists of 6 tabs, namely, Table, Column, Expression, Joins, Hierarchies & Funnel.

Table

The table tab contains an overview of the model, the name of the schema and the table on which the user wants to create a model. For creating a model, a table has to be selected and its columns need to be mapped to Measures and Dimensions fields.

Columns

In the column tab, the list of columns of a given table is catalogued. Here, the user selects dimensions, measures at different aggregates like sum, average, count, distinct count, etc whereas, in date dimensions, the time frame can be set for yearly, quarterly, monthly, weekly, daily and hourly basis.

Expressions

Sprinkle allows users to create calculated columns at the model level. These calculated columns might be a dimension expression or measure expression on a given table. The tool also supports dynamic bucketing where users can give just the ranges and variable names and the buckets are created easily.

Joins

Joins is a feature that allows the user to join the fact table (from which the model is created) with other models. All the dimensions present in the joined Model can be used in this model. For example, an Order Model having customerId as one of the columns can be joined to the Customer table. Similarly, it can also be joined to the Product table.

Hierarchies

In order to define the relationship between the fields, Hierarchies are added to the model. In a nested fashion, one value is controlled by another. For example: Country -> State -> District -> City -> Taluk can be one hierarchy where taluk value depends on their predecessors. This way, the values are filtered naturally which shows just the relevant values.

Funnels

Select Fields that can be used in the funnel analysis. After the funnel events/ columns are added to the model, funnel analysis can be performed through the Funnel section in the Analytics Section.

Feature Walkthrough
🚢
​

​
1
Creating a Model

To create a model
πŸ–±
click on New. Give a name & description to the model and select the flow table or the DB ingested table and
πŸ–±
click on Next.
Create Model

​
2
Columns

After creating the model all the attributes present in the flow table is listed. Sprinkle by default chooses the dimensions, measures and date dimensions. The user can choose dimensions by just clicking on the checkbox.
Columns Listing
The measure can be chosen by selecting the required aggregate function like distinct, Average, Sum and Count.
In the below
⬇
case, the count is selected for the order numbers
Measures
Date dimensions are the time span on which a report is built. Sprinkle supports hourly, daily, weekly, monthly, quarterly and yearly reports. The below screenshot shows the date dimensions. Click on the Create button to create the model.
Date Dimensions
Click on Create Segment Report, to create a segment from the model page.

​
3
Expressions

The user can also add customized dimensions and measures on the Expression tab in models. Calculated columns are created using expressions to obtain the desired answer, in this case, the total sum of the line amount is created.
​
πŸ–±
Click on Validate to test the expression, before saving it. Click on Done to save it as a customised dimension or measure.
Expressions: Validate & Done

​
4
Joins

Joins is a feature that allows the user to join the fact table (from which the model is created) with other models. All the dimensions present in the joined Model can be used in this model.
For example, an Order Model having Customernumber as one of the columns can be joined to the Customer table. Similarly, it can also be joined to the Product table.
In Model joins, the Joins type can be selected from a list, say, Left Outer, Right Outer, Inner, Full Outer Join. In addition to that, multiple conditions can also be created at one instance to join two models.
Joins

​
5
Hierarchies

Browse to the Hierarchies Tab to Add details about the Hierarchies.
Here the Hierarchy levels need to be defined in the right sequence and saved and in the model, and model build jobs need to be run successfully at least once so that they can be used in the dashboard.

​
6
Funnels

To do a Funnel Analysis on the model, the funnel field & events field needs to be defined. View the detailed explanation of the Funnell Feature,
πŸ–±
click here.

​
7
User Attributes

User attributes let you manage the row-level access of the models. It lets you restrict users' access only to the relevant part of the table. For example, you can restrict users in "Karnataka" to access the sales data only relevant to "Karnataka" from the sales table.
Refer to this document for steps to create and use User Attributes.

​
8
Column Masks

What is Data Masking?

It can be described as a method of modifying confidential data so that the actual values cannot be accessed. The data masking technique used in this feature is Dynamic Data Masking. It means temporarily hiding the data from users when they access it.

Why is it required?

In order to Mask sensitive information like PII, PHI, and other confidential data from the specific groups of users who do not require it on the platform.

The feature

In a Model, a set of fields can be dynamically masked for a group of users using the "Column Masks" feature. Data in these fields are not visible when the restricted user accesses Segment Reports or Dashboards.

Steps involved in creating column masks

  1. 1.
    On a Model, browse to the Column Mask Section.
  2. 2.
    ​
    πŸ–±
    Click on the β€œ+ Add Mask” Button, to create a new mask.
  3. 3.
    The Masks you create are mapped to a group of users, who must be restricted from accessing the columns. So from the group name dropdown, select the group for which the mask is to be created.
  4. 4.
    Then from a list of columns in the model, select those that have to be masked.
  5. 5.
    By clicking the tick button, you can create the mask for the columns in the selected group.

So, what happens when a mask is created?

The masks are honoured when segment reports are generated from the model with masked columns. If users from the masked group access the report, they will see asterisks rather than actual data in those masked columns, while other columns normally show data.
Also when such Segment reports are embedded onto a dashboard the masks are honoured.
Good to Know
  • In Segment Reports, the masked columns are not available for filtering and sorting data.
  • When a restricted group of users downloads the report, the column masks are respected in the downloaded file.
  • Even when models are joined, the column masks remain intact.
​
Copy link
On this page
Overview
Feature Walkthrough
Creating a Model
Columns
Expressions
Joins
Hierarchies
Funnels
User Attributes
Column Masks