Looking for Answers?

Browse our articles to find the answers you need

Data Modeling

Historical Currency Conversion Using GoogleFinance

 Analytical Need I have historical transactional data starting from 2012. Some are recorded in USD, others in EUR. I need to convert these values to USD based on the exchange rate on the date of the transaction

Market Basket Analysis

 Analytical NeedMarket Basket Analysis is a data mining technique that is widely used to identify consumer patterns, such as which items are purchased together, or how the purchase of one item affects the likel

Not all dimensions relate to all facts

 Analytical NeedIn a dashboard, we wish to analyze how our entities (sales, inventory, tickets etc.) behave and break them by several categories. However, not all entities share the same categories. We wish to

Design a schema with more than one fact - Multi Fact Schema

 Analytical Need We have multiple sources for our measures and multiple sources to slice & dice these measures.  Modeling Challenge How do we build a schema that will enable us to perform our needed analysis?We

Multiple Dates in a fact table

 Analytical NeedFact tables may include multiple dates per record. For instance, a record can have an open date, a closed date, an updated date etc.  Modeling Challenge We need one time dimension since we don’t

Get measures from 2 facts without duplications - Fact Attribution

 Analytical Need Working with 2 fact tables which have a direct 1:M relationship, and fetching measures from both. A common example from the retail industry is Order Header and Order Line Items tables  Modeling

The Funnel Analysis

 Analytical Need‘Funnel analysis’ describes process flow, where in its beginning the number that we measure is relatively big, and in each step within the process this number is getting smaller along the way ti

Design a schema with one fact - Star Schema

 Analytical Need We have one central source for our measures and multiple sources to slice & dice these measures.  Modeling Challenge How do we build a schema that will enable us to perform our needed analysis?

Cumulative Count Distinct

Analytical Need In some cases, you would like to count the number of unique entities that did a certain action/transaction. For example, how many individuals entered my store last month and purchased one of my

Slowly Changing Dimension

 Analytical NeedThis dimension table keeps every change that has been made. The user wishes to see historical data per the dimension and not just the current state.Example: Transactions per organizational hiera

Calculating Business Days Difference Between Two Dates

IntroductionThis article demonstrates how to avoid counting weekends and holidays when calculating the difference between two dates. Example - Average Shipping Time in Business DaysSteps In order to ‘subtract’

Business Requirements Gathering

The Business requirements gathering step aims to identify the end-users' key business requirements and KPI's to build customized dashboards that meet their needs. Well-defined requirements are the foundation fo

Days, Hours, Minutes, and Seconds Drill Down

Introduction In this post we'll learn how to create a drill down hierarchy for Days-->Hours-->Minutes-->Seconds. A general quick solution, in case you need the drill mostly for pivot table visualizations, wou

Trend Analysis

Trend analysis enables users to compare actual totals against measures derived from the data. This will enable users to compare actual results against derived amounts, and even project those amounts into the fu

The BI Implementation Workflow - Overview

When facing a new BI project you may ask yourself "Where do I begin?" This article will introduce Sisense's approach to managing an agile BI project from raising the initial requirements to rolling out the dash

BI Solution: High Level Design

Based on the business requirements summary you've created in the previous step, you can now design the high-level overview of your BI Solution. This step aims to translate the business strategy into operational

UAT

What is UAT? After the dashboard is completely developed and has been approved in QA tests, the final stage, before releasing a dashboard, will be to request a key end-user to perform User Acceptance Test. The

Represent Time in Widgets

As Sisense currently supports daily data, in the following article we will demonstrate how to include time in Sisense Web widgets that is present in DateTime fields. In order to display time format along with

Calculate Running Sum

 Analytical Need  There are cases where you wish to know for a certain point in time, how much you have accumulated up until now. This is called Running Sum. Although it is possible to use this in the dashboard

Enabling Timezone by User

 Analytical Need  Often times, you would have the need to support user across the globe that are digesting data from your dashboards. At these scenarios, the timestamp that was recorded and stored in the DB cou

Forecasting using Macro Trend and Seasonality

Forecasting using a macro trend (moving average) and seasonality (seasonal index) is a common way to forecast data into the future. The two components, seasonal index and moving average, are based on prior hist

Time Calculations in Sequence of Events

 Analytical Need Calculate the time difference between two events in a fact table.In the example, we want to measure how many days it took to complete each task in a shipment process. Modeling Challenge Orders

Date Dimension File

 Analytical NeedAnalyzing information by date can be very useful, but not every source data set provides enough information to do this kind of analysis.  Modeling Challenge We will need to import a date table,

Data Relationships: Introduction to a Many-to-Many

A relationship between tables specifies the field/s which the 2 tables have in common.In Sisense, we create relationships between tables in the Elasticube. Poorly modeled data relationships can lead to one or m

Creating Timeline from Start and End Dates Columns

IntroductionThis article will demonstrate how to quantify on a date axis the availability \ unavailability of products given only the start and end date of each product's availability (See data format in "Produ

Flattening out Multi Level Parent-Child Hierarchy for Hierarchical filtering

 Analytical Need With a Parent – Child hierarchy that has 3 levels, there must be a series of dependent filters for all levels individually but, affects the other levels below it too. Business defined hierarch

Data Relationships: One-to-Many Relationship

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:  Data Relationships: Introduction to Many-to-Many Data Relationships: Check

Display values based on selected date for multiple time frames

 Analytical Need  You want to display several ‘to date’ calculations for multiple time frames (i.e. week to date, month to date, year to date) based on a date selected by dashboard viewer. You want it to be vis

Generating a Numeric Key

Introduction Surrogate Keys are mainly used for creating a single identifier for multiple dimensions, mainly when building a Key Table. A text key can be easily generated by concatenating the text and delimiter

Creating Cohort Analysis With Sisense

 Analytical NeedCohort is a group of items who share common characteristic over a certain period of time. The Cohort analysis helps us identify the relationship between their characteristic and their behavior a

Data Relationships: Many-to-Many Relationship Resolutions

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:  Data Relationships: Introduction to Many-to-Many Data Relationships: Check T

Analyzing Measures Between Two Dates

Analytical NeedIn many use-cases, there’s a need to measure entities throughout their life cycle which spans from their ‘Start’ date to ‘End’ date. For example insurance company wants to count how many active p

Advanced Analytics with Sisense

Advanced AnalyticsAdvanced analytics is a part of data science that uses high-level methods and tools to focus on projecting future trends, events, and behaviors. This gives organizations the ability to perform

The Invisible Many to Many

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:  Data Relationships: Introduction to Many-to-Many Data Relationships: Check T

Unit conversion between Metric and Imperial (US)

 Analytical Need You have an international user base, some who use the Metric system and others who use the Imperial (US) system. You want to display dashboard values and calculations in the units that the user

Convert Text Date to Date-time format

The Problem Date information from the source is represented as a text string, with the format YYYYMMDD. We need the information to be represented as a date-time data type in Sisense.   The Solution This will ut

Supporting Updates and Deletions in DB when building accumulate

Introduction When using the "accumulate by field" (see below), Sisense will only import "new" records based on the selected field.  The logic will be similar to adding a Where clause to the SQL Query:  "WHERE D

How to implement a MS SQL Stored Procedure with a Cross Apply

Sisense does not currently support the Cross Apply operator but below shows a viable alternative. The easiest way to implement this would be to import the data returned from a stored procedure directly into an

Generating Dimension Tables From Multiple Fact Tables With a Common Field

In multiple fact tables, that all contain a common field, when filtering on this field in a dashboard, you want the filter to be independent of all of the related fact tables.In order to accomplish this, you’ll

Creating Custom Time Buckets in Elasticube

Introduction This article explains how to create a custom field which rounds timestamps (Date-Time) up into time buckets.  The buckets outlined in this article include every hour on the hour (10:00 AM, 1:00 PM)

Accumulating Data from Third-Party Sources via a Linked SQL Server

This document describes how to use a linked SQL Server to execute commands to external data sources (in this case - Netsuite), and create accumulated Transactions Tables with SQL JOBS. Before you Begin Make

Date table with Comprehensive To Date Fields

This article outlines how to create a date table that flags whether or not a particular date is within a particular range, based off of the current date. For example, if today is the 100th day of the year, we m

Calculate Distance Between Two Points Using Latitude and Longitude

In this post, we show the formula to calculate the shortest distance between two points using Latitude and Longitude. This calculation can be useful when trying to determine the distance for logistical purposes

Splitting delimited strings into rows

When would I use this: In cases when you have a table containing an identifier field with a related field containing delimited text and you need to split into a new row per split subfield. Example: The Email

Data Relationships: Check Type of Relationship

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:  Data Relationships: Introduction to Many-to-Many Data Relationships: Check

Table Types

 Analytical Need  When you decide which KPIs you wish to measure and according to which criteria to slice & dice them, you must be aware of the following table types.  Modeling Challenge When you start to im

Calculate MTD, QTD and YTD Values

 Analytical Need  A common requirement in data analysis is to be able to view and compare slices of data from different time periods to current date. Business applications for this vary, from identifying trend

Analyze Period over Period - MoM / WoW / DoD

 Analytical NeedThere are cases where we want to analyze our measures as a comparison between periods of time. For instance, ee want to know how did our measures behave on January this year vs. previous year.Th

Modelling Different Granularity Levels

 Analytical NeedThere are many cases where our data comes in with different granularity levels. For instance, Actual vs. Target where the actuals are at the product level and the targets are per sub category (h

Many to Many by Design (Relationship Table)

 Analytical NeedThere are cases where we need to define an association between two entities. This association is comprised of the entities and not by each of them separately. For example, students & courses. We

Fiscal Year Adjustments

 Analytical Need Customer's fiscal year does not comply with the Georgian 12-month calendar and starts on a different date than January 1st.Example:Customer’s fiscal year is from April 1st to March 31. This mea

Build A Flattened Hierarchy From 2 Levels

 Analytical NeedIn many cases a hierarchy appears in the data structured as 2 levels - A parent and a child. The business user would like to see the entire hierarchy and analyze the data according to the differ

Data Transpose Guide

Source data that is flattened may require some or all of the columns of a source table to be transposed, in order to work seamlessly with SiSense’s functionality. This post will outline the cause and two possib

Modelling Aggregate Facts

 Analytical NeedThere are cases where our data arrives in aggregate form, like in Google Analytics (where the lowest granularity level is daily). If we wish to free up clutter in the dashboard and prevent the u

Key Table: Store data relationships between primary key values

 Analytical Need A key table is a central table that stores all the relationships between the primary key values.  Modeling Challenge The main advantages of using Key Table: It helps to tidy up a schema that h

Week of the Year- The ISO Way

If you want each date to “belong” to its “actual year” you can use the ISO way to calculate the week number.For example, if 1/1/2016 is on Wednesday and you want the 1th ,2nd and 3rd to belong to the first week

Creating a Currency Conversion Filter in the Dashboard

 Analytical Need  The data is stored in a certain currency and you would like to present it in the dashboard converted to different currencies.  Modeling Challenge As a designer, I would like to have a dashboa

Switch Between Metrics

 Analytical Need There are many cases when we will want to measure a metric by a different angle or definition. For example, in some cases you would like to allow your end users to analyze average duration of a

Running Sum on Distinct Values

Analytical NeedA common use case is to know the number of new users in the system. It could be that same user appear in various months but we want to count each unique user only once.If we will use a simplistic

"Filling" missing records in Fact table from Dim tables

Use Case:Sometimes, we would like to create calculations from the fact table and we need to have a continuous range of dates or in other cases, we need to count the number of employees (just as an example) that

How to connect tables with more than one field in common - Surrogate Key

 Analytical Need We may encounter a situation in which a table has several fields that together identify each record. Each of them may appear several times but their combination is unique.  Modeling Challenge I

Presenting Time-frames in a Scatter Chart

 Analytical Need For different use cases, it can be relevant to present a timeframe of different business entities and the relations between them.  For examples, such use cases can be: Present hotel vacancies b

Creating a Common Date Selection

When pulling together data from multiple sources, you’ll have a number of different dates. For instance, the marketing department has a Campaign Date, Sales department may have an Opportunity Date and Finance h

Calculating Time differences Flexibly

 Analytical Need  Certain business questions might require us to determine the time that has passed between two status in a flexible manner.Calculating differences between two events often will require using fu

Conditional Lookups

 Analytical Need  Using the lookup function to import data from one table to enhance another, based on specific conditions or filters. This might be required if we wish to import attributes from a table which i

Count days passed from one event to the next

This article demonstrates two methods to count the number of days that passed from one event to the other for a specific category (i.e number of days passed between one sale to another by sale code). The prefer

Data Accumulation Guide

Data accumulation is SiSense's method for building ElastiCube data without completely refreshing a table. This post describes the data accumulation functionality and gives a user the ability to customize and op

Convert Integer Date to Date-time format

The ProblemDate information from the source is represented as an integer, with the format YYYYMMDD. We need the information to be represented as a date-time data type in Sisense. The SolutionThis will utilize a

Add 'Week of' to Custom Hierarchy

IntroductionThis Post Explains how to create a widget hierarchy that includes ‘week of’  as a member of the hierarchy.  (Calculates first day of that week)Business CaseAs a business Analyst, I would like to dri

Numeric Representation of Date fields

Business CaseCreating a “Date” table which is identified by a numeric representation of a Date field instead of the Date field itself may improve the query performances. In addition, a numeric representation of

Service Level Analysis

 Analytical Need When we want to measure the service level we provide our customers we can do that using many different KPIs. These differentiate between industries and management decisions.This modeling use ca

Advanced Data Security - Organizational Hierarchy

 Analytical NeedData security is implemented when there is a need to prevent the users from seeing the entire set of data. Each user only needs to see a data set based on permissions that is defined in Sisense

Calculate QTD (Quarter to date) flag

This is the solution of how to calculate the QTD, in continuation of the article : Calculate MTD, QTD and YTD ValuesIf the dim date is a base table you'll need to add the following 3 invisible fields:1. FirstDa

How to perform Left Join in the Dashboard

**Please refer to the following article for extended functionality: Performing a Front End Left Join **This article presents a data modeling based solution that may allow you to create a simple Left join within

10 Step Data Validation Guide

What is Data Validation Testing?In addition to QA and UAT Testing, Data validation testing is a critical step to ensure you are going into production with dashboards containing accurate and complete information

Data Relationships: Check Type of Relationship

For general information on types of data relationships, potential complications and how to resolve them, see the following articles:  Data Relationships: Introduction to Many-to-Many Data Relationships: Check

Filter dashboard on latest year based on the Elasticube data

 Analytical NeedFact tables may include a wide date range. For instance, a table can have historical dates without any transaction of the current year,The Need : filter the dashboard based on the latest year in

5 Simple Rules for ElastiCube Design

Rules To Live By When Designing An ElasticubeA list of recommendations which should be considered when developing an elasticube.  Start SmallBuild your ElastiCube incrementally. Start with only two tables, crea

Calling A SQL Server Stored Procedure With Parameters

From the Desktop ElastiCube Manager you can call a SQL Server Stored Procedure (with or without parameters) Step One:Configure for ad hoc distributed queries on the SQL Server Instanceexec sp_configure 'show ad

Calculating Week Number Of Quarter

IntroductionThis Post Explains how to create a new column that will calculate the week number of a Quarter.Business CaseIn many cases Business Analyst would like to track trends over time aggregated by quarters

Many-To-Many / Multivalued Dimension

There are situations when your data needs to represent a many to many relationships such that your dimension members are at a lower grain than related facts; aka multivalued dimension.  In these cases, a single

Creating Records For Non Existent Transactions

IntroductionSometimes when your analyzing sales you may want to look at products that are not being sold. Or maybe for a service provider you may want to see territories where you have no subscriptions. Unfortu

Cheat Sheet - Detecting Incorrect Data

Data is a beautiful mess. As data heroes, we spot the mess and get rid of it to highlight the beauty. Here are a couple useful tricks to detect incorrect data. We will assume a table that describes the number

Count Daily Workload / Open Tickets Per Date

Overview:Many times, we'd like to analyze workload over time. The following article describes a modeling solution approach to an often asked question: "How many active items are there per day?" Analytical Need

Convert Quad Decimal IP Addresses To IP Integers

This expression will convert IP addresses in the quad decimal format of 'www.xxx.yyy.zzz' to integers that can be used for further analysis (eg. cross referenced to a geo-location).For example, it would convert

Automatically Sort Values Into Ranges Of Known Size

If you know the size your range/bucket will increment by, this quick method will help you automatically sort your values into their respective range.This is useful when you cannot create or maintain ranges usin

Cohort Analysis

Data PreparationOrange: Borrowed from another table via lookup() Blue: Custom columnIf the first visit date is available in the dimension: Lookup the user’s First Visit Date from the dimension table in a cust

Calculating Weighted Median (Median Of Data In Frequency Table) + Mean & Mode

Analytical NeedSisense comes with a function called Median() to calculate the median of a set of values. However, this function works with one assumption: we have all the values stored in one column and each ro

Comparing Daily Averages Of Current Month/Quarter With Previous Month/Quarter

You might want to compare MoM on a daily average, which means to take the daily average of the current month (even if the month isn’t over yet), and compare it with the previous month’s daily average. You might

Compare Personal Data Versus Team’s Benchmarks Under Security Restrictions

DescriptionA common visualization is a comparison between personal KPIs and the rest of the team KPI averages. The main challenge is when applying a security configuration, the other users’ data is filtered out

Using "Now" In A Dashboard Function Date Comparison With R

OverviewThis post offers food for thought in regards to how R can be used to retrieve the real-time datetime with the RDouble function. It shows how to run date comparisons using the R syntax. ChallengeNormall

Calculate Average Time Between Transactions

IntroductionCalculating transaction frequency for a given dimension is a common requirement across many verticals. e.g. Average Time Between Purchases by a Customer. Average Time between support tickets etc.Whi

Salesforce - Modelling Pipeline History And Pipeline Movement

Download: ecube, excelIntroduction:Salesforce provides an OpportunityHistory table which stores an audit trail of all the changes made to an opportunity. Using data modelling we can roll back and obtain the his

Calculating Values For The Most Recent Date

The point of this article is to provide a method to a value associated with the most recent data in a date selection.ImplementationStep 1: in the Elasticube, create a field transforming the date field into an i

Elasticube Modeling With Data Security: Best Practices

Adding Data Security to an existing Elasticube Model can have little to great effect on the performance of the system. It is important to already have planned the Elasticube Model with the intention to apply Da

Time Dimension

Analytical Need Many times we would like to deep dive into our data and view it in a more granular fashion to conclude from it. Certain business needs might require us to use date-time hierarchies to help us un