🟡Cube Builder

Introduction Cube Builder

Presight Cube Builder empowers users without SQL knowledge to efficiently create new tables with specified conditions and filters directly from users' database.

You can build a table via the side bar from the left of the doc or when you breakdown a metric by a dimension:

• Via side bar:

• Via dimension list when breaking-down:

A new blank table will appear and you can start adding columns and using Excel-like formulas in the table to create new dimensions with more complicated filters and conditions for your workspace.

How to use Cube Builder

The following example will help you to understand how to create a cube/ a table and use it in the workspace.

For instance, you need to know if the customers buying goods in the given month also had orders in the last month, to analyze the continuity in buying behaviors of the customers. So, you need to classify the current buying customers in two groups: "Buy in 2 consecutive months" and "Only buy this month".

Follow the steps to create a table with the given condition:

• Create a new cube from side bar or from the breakdown menu; name the table "Continuity Status"

• Add the following columns into the table. If the inputs are just taken from raw table data, drag them from the side list and drop them to the table. If the inputs need to be calculated, use formulas (formula list is in the next section).

  • Column 1: Customer ID from Invoices table - this is raw data, just drag and drop; name the Column "Customer ID"

  • Column 2: the analysis requires monthly status so Invoice month should be shown by using formula: MONTH(Invoice Date) - choose Invoice Date from table Invoices; name the Column "Invoice Month"

  • Column 3: count Invoice in the given month, using formula: COUNT(Invoice ID, Invoice Date) - choose Invoice ID and Invoice Date from table Invoices; name the Column "Invoice Count this month"

  • Column 4: check if there is any invoice in the previous month of the given month, using formula: PREVIOUS(Invoice Count this month, Invoice Month, Customer ID) - Invoice Count this month, Invoice Month and Customer ID are the columns that have been created from table Continuity Status; name the Column "Invoice Count previous month"

  • Column 5: name the status, using formula: IF(Invoice Count previous month<>0&&Invoice Count previous month<>NULL, "Buy in 2 months", "Only buy this month"); name the Column "Continuity Status"

Here below is the result:

• Then, Save Table

• Before saving, you can check and change Table name, Description and choose to show or hide any of the columns of the newly created table.

• After saving the cube, you can find the cube and its dimensions in the dimension list when breaking-down:

• You can breakdown any metrics that have relationship with the table(s) you use in the cube. In the example, we can breakdown Sales, Buying Customer Count, Order Count and others:

Supporting Formulas in Cube Builder

Presight Cube Builder supports many formulas to help with more complicated business needs:

Formulas	Syntax	Examples
XIF (COUNTIF, SUMIF)	SUMIF(dimension,condition)	SUMIF(SalesAmount, Product Category="Bikes"&&Region="Europe")
IF	IF(condition,"value1","value2")
RANK	RANK(dimension used to compare for rank,"ORDER",dimension to partition by) *Order can be DESC or ASC	RANK(Monthly Invoice Sales,""DESC"",Date Month)
PREVIOUS	PREVIOUS(Dimension to get the previous value of, dimension to order by, dimension to partition by)	"PREVIOUS(This Month,Date Month, Customer ID) === Take "This Month" at previous "Date Month" for each Customer ID, the result will show the "Previous Month" of "This Month" at each Customer ID row.
FIRST	FIRST(Date needed)	FIRST(Order Date) === If there is Customer ID column in the table, using the formula FIRST(Order Date) will help to show the first order date of each Customer ID line.
LAST	LAST(Date needed)	LAST(Order Date) === If there is Customer ID column in the table, using the formula LAST(Order Date) will help to show the last order date of each Customer ID line.