Sprinkle Docs
  • What is Sprinkle?
  • Quick Start
  • Analysing your data
    • 🔭Analytics Overview
    • 💠Data Models
      • *️Variables
      • 🌲Hierarchies
      • 🤿Column Mask
    • 🎉Switch to New Reports & Dashboards
    • 🆕Reports
      • Overview
      • Build Using Tables
        • Create a new Report
        • Layout and options
        • Build and Format - Overview
        • Apply Row Limits
        • Identify Date Columns
        • Filter your data
        • Visualizations
          • Table
          • Pivot
          • Line Chart
          • Bar Chart
          • Column Chart
          • Area Chart
          • Combo Chart
          • Scatter & Bubble Plot
          • Pie Chart
          • Funnel Chart
          • Stat Card
          • Point Map
          • Heat Map
          • Radial gauge chart
        • Advanced Features
          • Custom Analysis
          • Variables
          • Table & Quick Calculations
          • Drill - Hierarchical & Date
          • Break Out
          • RLS in Table reports
          • Scheduled Exports
          • Embedding Table Reports
      • Build Using Models
        • Create a new report
        • Layout and options
        • Visualizations
        • Advanced Features
      • Build SQL Reports
        • Create a new Report
        • Layout and options
        • Writing a SQL Code on Editor
        • Visualizations
        • Variables in SQL Reports
    • 🆕Dashboards
      • 🌀Filters
      • 👆Click Behaviour
      • ⏰Data Alerts
      • 🗓️Date Drill
      • 📤Scheduled Exports
      • 🔗Embed link
      • 🖥️Dashboard layout
      • 📱Mobile Dashboards
  • Transforming your data
    • 🔰SQL Transform
    • 📓Python Notebooks
  • Integrating your data
    • ☁️Destination Warehouses
      • AWS Athena
        • Manage storage of Flow tables
      • AWS Redshift
      • Azure Synapse
      • Databricks
      • Google BigQuery
      • MySQL
      • Postgres
      • Snowflake
      • SQL Server
      • K8 Setup
        • AWS EKS
        • Google GKE
        • Azure AKS
    • ⚙️Warehouse & Storage Setup
  • Ingesting your data
    • ☄️Data Imports
      • Databases
        • Azure Cosmos DB
        • Azure Table Storage
        • Google BigQuery
        • Mongo DB
        • MySQL DB
        • Oracle DB
        • Postgres DB
        • SQL Server DB
        • Features
          • Ingestion Modes
          • Add Multiple Datasets
          • CDC Setup
            • CDC setup in Mysql
            • CDC setup in Postgres
            • CDC setup in Mongo
            • CDC setup in SQL Server
          • Destination Create Table Clause
          • SSH Tunnel Setup
      • Files
        • AWS S3
        • AWS S3 External
        • Azure Blob
        • FTP
        • Google Cloud Storage
        • Google Sheet
        • SFTP
      • Applications
        • Apple Search Ads
        • Appsflyer
        • Branch
        • Clevertap
        • Facebook Ads
        • Freshdesk
        • Freshsales
        • Google Ads
        • Google Ads V2
        • Google Analytics
        • Google Analytics 4
        • Google Analytics MCF
        • Google Search Console
        • Hubspot
        • Impact Ads
        • Intercom
        • Klaviyo
        • Leadsquared
        • LinkedIn Ads
        • Magento
        • Mailchimp
        • Marketo
        • Mixpanel
        • MoEngage
        • Rocketlane
        • Salesforce
        • SAP S4
        • Shopify
        • Snapchat Marketing
        • TikTok Ads
        • WooCommerce
        • Zendesk Chat
        • Zendesk Support
        • Zoho Analytics
        • Zoho Books
        • Zoho CRM
        • Zoho Desk
        • Zoho Invoice
        • Zoho Subscription
      • Events
        • Apache Kafka
        • AWS Kinesis
        • Azure EventHub
    • 📤File Uploads
    • 🤖API Pulls
    • 🕸️Webhooks
  • Collaborating on data
    • 📤Sharing
    • 💬Comments
    • ⚡Activity
    • 🏷️Labels
  • Managing Schedules and Data Refreshes
    • ⏱️Schedules
    • 🔔Notifications
  • User Management
    • 🔑Access Management
    • 🧑‍🤝‍🧑Groups
    • 📂Folders
    • 🔄Syncing users, groups and RLS
    • 📧Azure AD Integration
  • Data Security & Privacy
    • 🔐Security at Sprinkle
    • 📄GDPR
    • 📄Privacy Policy
  • Release Notes
    • 📢Release Notes
      • 🗒️Release Notes - v12.1 (New)
      • 🗒️Release Notes - v12.0
      • 🗒️Release Notes - v11.0
      • 🗒️Release Notes - v10.8
      • 🗒️Release Notes - v10.7
      • 🗒️Release Notes - v10.6
      • 🗒️Release Notes - v10.5
      • 🗒️Release Notes - v10.4
      • 🗒️Release Notes - v10.3
      • 🗒️Release Notes - v10.2
      • 🗒️Release Notes - v10.1
      • 🗒️Release Notes - v10.0
      • 🗒️Release Notes - v9.31
      • 🗒️Release Notes - v9.30
      • 🗒️Release Notes - v9.29
      • 🗒️Release Notes - v9.28
      • 🗒️Release Notes - v9.27
      • 🗒️Release Notes - v9.25
      • 🗒️Release Notes - v9.24
      • 🗒️Release Notes - v9.23
      • 🗒️Release Notes - v9.22
      • 🗒️Release Notes - v9.21
      • 🗒️Release Notes - v9.20
      • 🗒️Release Notes - v9.19
      • 🗒️Release Notes - v9.18
      • 🗒️Release Notes - v9.17
      • 🗒️Release Notes - v9.16
      • 🗒️Release Notes - v9.14
      • 🗒️Release Notes - v9.13
      • 🗒️Release Notes - v9.12
      • 🗒️Release Notes -v9.8
      • 🗒️Release Notes - v9.7
      • 🗒️Release Notes - v9.6
      • 🗒️Release Notes - v9.5
      • 🗒️Release Notes - v9.4
      • 🗒️Release Notes - v9.3
      • 🗒️Release Notes - v9.2
      • 🗒️Release Notes - v9.1
      • 🗒️Release Notes - v9.0 (Major)
      • 🗒️Release Notes - v7.23
      • 🗒️Release Notes - v7.21
      • 🗒️Release Notes - v7.20
      • 🗒️Release Notes - v7.15
      • 🗒️Release Notes - v7.14
      • 🗒️Release Notes - v7.13
Powered by GitBook
On this page
  • Overview
  • Watch Video
  • Feature Walkthrough
  • 1. Creating a Model
  • 2. Columns
  • 3. Expressions
  • 4. Joins
  • 5. Hierarchies
  • 6. Row-level Security
  • 7. Column Masks
  1. Analysing your data

Data Models

Overview & Detailed Walkthrough of Model Features.

PreviousAnalytics OverviewNextVariables

Last updated 1 year ago

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 query. It is configured with a simple point and click user interface.

Watch Video

Primarily Models comprise date dimensions, dimensions, and measures.

The Model page consists of 5 tabs, namely, Table, Column, Expression, Joins, Hierarchies

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 the Measures and Dimensions fields.

Columns

In the column tab, the list of columns of a given table is catalogued. Here, the user selects dimensions, and 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

Hierarchies can be added to define a relation between fields, where values of one can be controlled by other in a nested way.

For eg, Country -> State -> city This can be a hierarchy where city value is dependent on state, state value is dependent on country.

1. Creating a Model

2. Columns

The measure can be chosen by selecting the required aggregate function like distinct, Average, Sum and Count.

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 for the order date column.

Then click on the 'Create' button to create the model.

Now you can Click on 'Create Report', to create a Report directly 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.

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, a Google Ads Model having City as one of the columns can be joined to the Sales fact table as shown in the example below.

In Model joins, the Joins type can be selected from a list, say, Left Outer, Right Outer, Inner, or Full Outer Join. In addition to that, multiple conditions can also be created at one instance to join two models

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 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. Row-level Security

Row-level security lets 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.

7. Column Masks

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 Reports or Dashboards.

Measures are Quantitative data like Amounts, Averages, counts, 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.

Hierarchies then allow you to use the drill feature on Reports & Dashboards allowing you to drill down and drill up on your data to perform interactive analysis, explore trends. ()

Feature Walkthrough

To create a model click on New. Give a name & description to the model and select the SQL Transform table or the DB ingested table and click on Next.

After creating the model all the attributes present in the table are listed. Sprinkle by default chooses the dimensions, measures and date dimensions. The user can choose dimensions by just clicking on the checkbox.

In the below case, the count is selected for the Client column.

Click on Validate to test the expression, before saving it. Click on Done to save it as a customised dimension or measure.

You can also use variables in your expressions. .

Refer to this document for .

.

💠
💡
💡
🚶
🖱️
🖱️
⬇️
🖱️
Read more about hierarchies here
SQL Transform
Learn more about Variables here
Read more about hierarchies here.
Learn more about column masks here
🖱️
📺
Models : Explanation & Feature Walkthrough
Create a Model
Measures
Date Dimensions
Creating a report
Expressions: Validate & Done
Creating Joins
steps to create and use row-level security