Looking for Answers?

Browse our articles to find the answers you need

Elasticube Data Management

LOOKUP WITH TWO CONDITIONS

Question: How to do a lookup using two conditions (i.e., pulling project number on a unique equipment for a specific day)?Solution: You can only use one field as the key field in Lookup function. So, it is bett

Supporting Updates And Deletions In DB When Building Accumulate

Question: When using "Accumulate By" builds (see below), Sisense will only import "new" records based on a selected INT or DATETIME index field - if values in the source DB exceed the maximum value of that inde

Split A Delimited Field Into Rows

Question: A data source in your ElastiCube contains a field with multiple values in it delimited by a particular character (i.e - comma, semicolon, space, etc.). You need to parse that field and create a table

Star Schema Modeling Challenge Along With Security Implementation

Question: We have multiple clients and to maintain this we modeled the dim_client table and we implemented the data security rules on this table.We have 2 fact tables (fact_sales and fact_budget) to have the sa

Calculating YTD With Variable Start And End Dates Of The Year (Academic Calendar)

Question: For a specific industry, like Education, even though the number of days is roughly the same every year, each academic year can start and end on different dates. The usual way of calculating YTD wouldn

LAG / LEAD Functions

Question: LAG & LEAD Functions are functions that give access to multiple rows within a table, without the need for a self-join. The LAG function is used to access data from a previous row, while the LEAD funct

Filtering On Multiple Tags In A Single Row

Question: A relatively common database design strategy is to have a tag column with an arbitrary number of tagsHere is an example in which we look at numbers between 1 and 10 and tag them as even, odd, perfect,

Removing Leading Zeros

Question: What is the best way to remove leading zeros?  I have a field that is alphanumeric values (JONESBJ001, SMITH KC02, 0000012345, 0000555213.Solution: If all of your data with leading zeros will always b

Enforcing Security On Consortium Data By Masking Sensitive Values

Question: Some organizations provide Sisense to their clients as part of their business offering. Some of these organizations would like also to provide clients with the consortium data derived from Sisense - a

Flattening JSON Objects In SQL Server

Question: In instances where tables within SQL Server contain JSON objects within a column you can use the import sql query editor feature in Sisense to flatten these objects into individual fields.For example:

Using SQL Coalesce To Handle Null Values

Question:SQL NULL is not technically a value, but rather a state. Fields are not equal or not equal to null, they are or are not null.  As such, null fields can get a little tricky when attempting different kin

Get Elasticube from Process ID

Question:I would like to be able to track memory consumption of my Elasticubes, but I can't find an easy way to link the process ID of  my Elasticube to the cube name.Solution:If you open the Task Manager, go t

Accumulation Build For Millisec (Ms) By Using Unix Timestamp Transformation

Question:This post we will talk about accumulation via millisec by using a unix timestamp transformationIn this use case, We will be working with a CData Elasticsearch ODBC driver and client need a way to accum

Elasticube Data Folders Get Created Only From Web Version Of Elasticube Manager

Question:When an elasticube is created in desktop version of elasticube manager, only a .ecube file gets created, where as when an elasticube is created in web version of elasticube manager, a folder with the e

Any Logging On Schedule Synchronization Function

Question:Is there any logging on changes made to the schedule sync?We have an instance where one schedule build was disabled, and we would like to find out why.Solution:The log that shows these actions in the E

Connecting To The Elasticube From Microsoft Excel

Question: The purpose of this post is to demonstrate how to connect to a Sisense Elasticube from Microsoft Excel.Solution:Begin by installing the Sisense ODBC driver.https://documentation.sisense.com/latest/odb

List Of Date Format Conversions

Question:A date stored in a source system is not a datetime that Sisense recognizes. Converting it to a Sisense datetime allows powerful front end capabilities (such as drill down, quick functions and more)Solu

Editing An Existing SQL Custom Import Query

Question:I want to edit existing Custom Import Query, how can I do so?Solution:From the context menu open on the table with custom import query Change connection settings for table:From there you will be able t

How To Filter For The Last Two Records Using SQL In Ecube

Question: How to find out if there exists the top 2 records for the same ClientID in a table. I know that the MAX gives the Top record and that SQL has the LIMIT 2 but I cannot use these two keywords in eCube.S

How to Split Row On New Line

Question: Currently have a one column custom SQL table that has multiple entries per row, separated by a new line.  I would like to be able to split or break apart the multiple values so we are left with one no

How To Concate Three Strings In Elasticube

Question: I have requirement to combine and show the dimension. There are two dimensions Product and Product_Family. I want to display it as single dimension in charts like "Product 1 | Product Family 1", "Prod

Recommendations For A Book/Tutorial On SQL For Sisense

Question: For a novice starting out with Sisense and a very basic knowledge of SQL, would someone in this community be able to recommend a book and/or tutorial that could help expedite learning the SQL syntax u

Does A Dashboard Try To Execute All Views At Once Upon Loading?

Question: is there any way to get the views to execute sequentially instead of simultaneously? How does the dashboard in Sisense execute the queries?Solution: The Sisense client application does attempt to send

Reason For Adding A Custom Table

Question: What is the reason of adding a custom tables in the Elasticube? As I understand all the customizations and calculations should be done in the Dashboard itself,  so why do we need to create custom tabl

Numeric Representation Of Dates And Times

Question: I have to do a MAX(<ColumnName><of DateTimeFormat>) in the dashboard.The main thing here is that I have to have the last second entry for that specific fieldSolution: We suggest you would use a float 

Rolling Trailing 12 Months

Question: I'm looking for how to create a chart that shows how my business is performing for the last 12 months at each month-end for a set period. For example, the period ending March '20 would sum up Apr '19

How To Read Dates Before 1970

Question: I have to display a field with dates such as 06/05/1851, 07/07/1891 and things like that.  I tried to workaround this by using CreateDate() but the data displayed is 01/01/1970.  Do you know how I can