Support categories: Analytics

Wondered how to create a time bucket dropdown in icCube?

Here’s a step-by-step guide.

To go further, we’ll also show how a chart can change its axis depending on the time bucket. For example, a column chart that shows data by day if the time bucket is “Last week” or “Last month”, and data by month if “Last year”.

1. Foreword
2. Time buckets definition
3. Time bucket dimension
4. Time bucket function
5. Time bucket filter
6. Applying the time bucket filter
7. Axis dependent on the selected time bucket

0. Foreword

Before we start, remember to always be careful when working with dates. Make sure the calculations you are choosing are the ones expected. For example, if you’re looking to get the previous year week, what do you mean?

• Same week number as previous year (e.g. Week 23 2023 vs. Week 23 2022)
• Week of current day vs. Week of same day of previous year (careful, e.g. June 4th is on Week 23 in 2023 and in 2022 it’s on Week 22)
etc

The function to be used will depend on this choice, make sure to carefully check the time intelligence functions’ definitions.

1. Time buckets definition

On your schema, create a simple table with your buckets

2. Time bucket dimension

Create the time buckets dimension (on the Wizard, click on “Create with Dimension/Facts Links Not Mapped”)

3. Time bucket function

In the schema’s script, insert the function below. Basically, this function takes “bucket” as an argument, which will be the event of your dropdown filter in your dashboard. If you select Last week then this function will create the list of dates from Last week until today, etc.

CREATE FUNCTION BucketDates(bucket) as  
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last week],    dtMinusWeeks(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last month],   dtMinusMonths(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last year],    dtMinusYears(dtAsOfToday([Time].[Time].[Day]),1):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last 3 years], dtMinusYears(dtAsOfToday([Time].[Time].[Day]),3):dtAsOfToday([Time].[Time].[Day]),
                  iif(bucket is [Time Buckets].[Time Buckets].[Time Buckets].[Last 5 years], dtMinusYears(dtAsOfToday([Time].[Time].[Day]),5):dtAsOfToday([Time].[Time].[Day]),
                  [Time].[Time].[Day]
                  )  
                  )
                  )
                  )
                  )

Here’s the list of time intelligence functions. Note that you could also use the Lag() function.

4. Time bucket filter

In your dashboard, create your dropdown filter. You need to write the MDX as not mapped dimensions do not appear on the query builder tree.

SELECT
   [Time Buckets].[Time Buckets].[Time Buckets].Members ON "MdxBuilderFilterItems"
FROM [Sales]
CELL PROPERTIES CELL_ORDINAL

Name the dropdown bucket in Interaction > Selection > Publish Selection.

5. Applying the time bucket filter

Create a table widget such as:

SELECT
   [Measures].[Amount] on 0
   BucketDates(@{bucket!∅}) ON 1
FROM [Sales]

And voilà!

6. Axis dependent on the selected time bucket

To go further, imagine you need a column chart that shows data by day if the time bucket is “Last week” or “Last month”, and data by month otherwise. Create a Column Chart with the following query statement:

WITH
  SET myDateAxis as iif(@{bucket!∅} is [Time Buckets].[Time Buckets].[Time Buckets].[Last week]
                       or @{bucket!∅} is [Time Buckets].[Time Buckets].[Last month],
                        [Time].[Time].[Day],
                        [Time].[Time].[Month]
                    )

SELECT
   [Measures].[Amount] on 0
   myDateAxis on 1
   
FROM [Sales]
FILTERBY BucketDates(@{bucket!∅})

Here we’re saying that we take the day level if the time bucket (the dropdown filter event) is “Last week” or “Last month”, or else we take the months level. Don’t forget the FILTERBY with the BucketDates() function.

With this, you now select the buckets on your dropdown:

You have days in the axis if you select “Last week”:

and months if you select “Last year”:

Schema & dashboard files

Find the files here.

• Schema – drag & drop it into the icCube Builder
• Data files – upload it on Admin > Docs > File System Root.
• Dashboard – drag & drop it into the icCube Dashboard Console > Dashboard Editor > Import Dashboard

icCube now integrates native Java and R. This means that you can build Java and R scripts into your MDX statements. The advantage of having those two languages natively integrated into icCube is that you are able to expand your analytical capabilities (predictive analytics, time series, data mining algorithms, etc.) with two of the richest mathematical programming languages available today, and directly apply those methods on your production data, analysis and dashboards.

Find the detailed documentation here: Java integration and R integration.

In this article we’ll show the same example, once in R then in Java with icCube. The schema used is the “Sales” schema that is available when you download icCube’s trial version.

In order to build a Java or R function, you need to define functions as “natives” (not defined using MDX.) Native functions are declared by NATIVE keyword. Here is a simple R example of a weekly day count between two dates. The function will count all Mondays, Tuesdays, Wednesdays, Thursdays and Fridays, between the two dates evaluated.

 WITH
    NATIVE FUNCTION RweekdaysBetween(date1,date2) AS
        /* R
sum(!weekdays(seq(date1, date2, "days")) %in% c("Saturday", "Sunday"))
        */
     MEMBER days as RweekDaysBetween(
                        [Time].[Calendar].[Day].[1 Jan 2005].key,
                        [Time].[Calendar].[Day].[1 Jan 2006].key
                    )
SELECT
days on 0
FROM [Sales]

Let’s show this same example using Java.

In order to put the algorithms in production, just add your corresponding Java or R code into your schema’s Script. Remember you can create a function in the MDX IDE on an existing schema without the need to reload it (DROP and CREATE the functions in the MDX IDE.)

 create native function weekDaysBetween(d1, d2 /* exclusive */) as
/* JAVA
	import org.joda.time.*;
    final int WEEK_START = DateTimeConstants.MONDAY;
	final LocalDate start = toLocalDate( toWeekDay( toLocalDate( d1)));
    final LocalDate end   = toLocalDate( toWeekDay( toLocalDate( d2)));
    final int daysBetween = Days.daysBetween(start, end).getDays();
    final int weekendsBetween = Weeks.weeksBetween(start.withDayOfWeek(WEEK_START), end.withDayOfWeek(WEEK_START)).getWeeks();
    return daysBetween - (weekendsBetween * 2 /* DAYS_PER_WEEKEND */);
*/
create native function toWeekDay(d) as
/* JAVA
	import org.joda.time.*;
    final int WEEK_END = DateTimeConstants.FRIDAY;
    final LocalDate day = toLocalDate( d );
	if (day.getDayOfWeek() > WEEK_END)
    {
      return day.plusDays(DateTimeConstants.DAYS_PER_WEEK - day.getDayOfWeek() + 1);
    }
    return day;
*/

WITH
MEMBER days as weekDaysBetween(
                        [Time].[Calendar].[Day].[1 Jan 2005].key,
                        [Time].[Calendar].[Day].[1 Jan 2006].key
                    )
SELECT
days on 0
FROM [Sales]

(btw, results should both be 260 🙂 )

What language should I use, Java or R ?

From a performance point of view, Java is faster, and potentially quite a lot, as you are using the same code icCube is running. Once the Java code is compiled, it’s as fast as icCube’s code. But you need to have a Java JDK in one of your servers (no need in production as the bytecode is saved in the schema definition). Remember you can add your own libraries, jar files, in icCube and they will be visible for your Java code.

From a functionality point of view, R has an amazing set of existing libraries that do not exist in Java. Please note that icCube is using Renjin that has not a 100% of all R libraries (check here).

From my side, this is just the first step to allow users making amazing maths in icCube, so stay tuned for what will be coming!

I knew it from the start.

The moment I started with presenting general ledger data in icCube dashboards, the issue of intercompany elimination would pop-up, eventually. T I had an easy elimination solution at one of my clients, that worked for three years. During fall 2017, the business controllers of that specific client requested a full elimination solution that would eliminate intercompany deals on all levels of the organization; a functionality you see in specialized financial consolidation applications like Oracle HFM or SAP BFC.

In this post, I present you the solution for intercompany elimination when processing financial G/L data in icCube.

What is “intercompany elimination” and why would you care?

Intercompany elimination can be defined as the process for removal of transactions between companies included in a group in the preparation of consolidated accounts. The process of intercompany elimination is helpful in managing eliminations of operations among companies within a single group (see link).

Organizations do business with the outside world and as a result transactions flow into the general ledger. Suppose we have a company, ‘EasyDrone’, that sells and repairs drones and within this company an engineer doing the repairs, Monica.

Using our definition of Intercompany Elimination and for the sake of this example, EasyDrone would be the group with two companies (in our example the Sales – and Engineering department). Our interest will be to calculate the actual value of this two departments as separate entities.

Let’s start creating a few financial transactions :

Monica repairs a drone for one of EasyDrone clients. After the work is done, the hours are billed and the results are recorded in the general ledger as “revenue from repair” for the cost center “engineering agency”. The revenue rolls-up into the organizational structure as indicated in the figure below. This transaction with a real client is called a regular “external transaction” (action 1).

So far so good. Next, a second-hand drone and a training drone need to be repaired by Monica. In this case, the clients are not a “real” client, but two internal departments: training and sales. When the job is done, Monica records her hours worked and an internal invoice is sent to the training department (action 2) and the training department (action 3). The G/L will record these as “revenue from consulting” and “repair cost” transactions.

The transactions between organization structures like these are called “intercompany transactions”. The departments roll-up into the organization structure as indicated in the following figure:

Adding up the revenue and costs for these actions gives the following result:

Gross profit looks nice but what happens if we look at the ‘revenue from repair’ and ‘repair costs’ ?. Are you able to detect the issue with the intercompany transactions?

Let me give you a clue. What if the Engineering department and the Sales department were separate legal entities and you wanted to buy only the latter. What would be the “real” revenue for the “Engineering department”?

Think about it, for a moment.)

Would it be really € 190? I do not think so. As a buyer I would argue that the internal revenue to “training” is not a real revenue, therefore I would value the revenue at € 155 only, eliminating the internal transaction with training as it is not a real revenue.

This process of eliminating the internal transactions is called “intercompany elimination”.

Applied to the example above, the following additional eliminating transactions are required to give the eliminated view of the complete organization:

In the figure above, you can see the “real” revenue from repair for each level in the organization. The “engineering agency” can report € 190 revenue in the internal management reports, but as you zoom out to the higher levels the internal transactions are deducted, eventually resulting in just € 100 revenue for the Total Organization, which is the revenue of the external client.

Without having the “eliminated” view on the organization, the revenues and costs are reported too high due to intercompany transactions.

This example showed intercompany bookings for internal services (revenue & costs), but intercompany effects, also apply to internal debts and loans and internal stock ownership.

The icCube solution for intercompany elimination

In the following steps I will show you how you can build intercompany elimination into your financial icCube model. I will use the data in the example above to keep the explanation short and simple, but you can easily modify it to your needs.

To make it practical, a ready-to-use schema can be downloaded here. Unzip and then import into the icCube builder.

step 1 – model exploration

The model contains three in-memory tables:

• 1. data, the data for the revenue in the previous chapter
• 2. costcenter structure parent-child, the structure for the organization
• 2. elimination structure, the flat hierachy structure for the elimination dimension

The solution for intercompany can be found in the icCube “data views”: createPath and elimination. Both views are ETL procedures based on javascript.

dataview: createPath

This procedure generates a path string in which the parent-child hierarchy is stored. The hierarchy in this example is very simple, but in reality, organization structures tend to be very “ragged”, meaning that some parts might just contain a few levels of depth while other parts are very detailed. Typically, the organization structure ends with the cost centers. That is why I call the organization structure “Costcenter”.

Here is the Init. Code:

ctxt.map = [[], []];

Row Processing Code:

// Get current node data
var node = get("costcenter");
var parent = get("parent");

// Find the path of the parent 
var idx = ctxt.map[0].indexOf(parent);
var parent_path = ctxt.map[1][idx] || "";

// Create path of current node
var path = parent_path + "|" + node;
set("PATH", path);

// Add row to map array
ctxt.map[0].push(node);
ctxt.map[1].push(path);

fire();

Completion Code:

ctxt.map = [];

This is the result:

dataview: elimination

This procedure processes all rows and if the row is an intercompany booking, it will “calculate” the elimination booking that will counterpart this row.

The logic is as follows:

• 1. for each normal row, set the elimination type to 1 (= orginal). If the row does not have an intercompany value, set the intercompany value to “external”.
• 2. for each row that has an intercompany value, find the lowest common parent for the “costcenter” – “intercompany” combination using the commonPath matrix.
  When found, create an elimination booking with coordinates on the common parent, elimination type 2 (= elimination booking) and -1 x amount.

Here is the Init. Code:

	//
	// get first common parent of all combinations
	//
	var df = table("createPath");
	var n = df.count();
	
	// output matrix, n rows and 2 columns: 1st element, 2nd the path
	var nrow = 2;
	var ncol = n;
	
	ctxt.m = Array(nrow);
	
	for(var i=0; i<nrow; i++) {
	ctxt.m[i] = new Array(ncol);
	}
	
	for (var i=1; i<n; i++)
	{
	var idx = i;
	var element = df.row(i).get("costcenter");
	var path = df.row(i).get("PATH");
	
	ctxt.m[0][idx] = element;
	ctxt.m[1][idx] = path;
	
	}
	
	ctxt.row_nb = 0;
	

Row Processing Code:

	var costcenter = get("costcenter");
	var intercompany = get("intercompany");
	
	if(get("intercompany") == null)
	{  intercompany = "external";
	} 
	
	// 1st, show the regular input as output
	set("costcenter", costcenter);
	set("intercompany", intercompany);
	set("type", 1);
	set("amount", get("amount"));
	fire(ctxt.row_nb++);
	
	
	// 2nd, check if the row is an intercompany booking and if so
	// prepare the row
	if(intercompany != "external")  // intercompany <> null <> ""
		{
		
		// look up the paths in the predefined matrix for both the costcenter as the intercompany
		var cc_path = "";
		var ic_path = "";
		for (var k=0; k<ctxt.m[0].length; k++)
		{
		if (ctxt.m[0][k] == costcenter)
		{
		cc_path = ctxt.m[1][k].split("|");
		if (ic_path.length > 0 )
			{
			break;
			
			}
			}
			if (ctxt.m[0][k] == intercompany)
			{
			ic_path = ctxt.m[1][k].split("|");
			if (cc_path.length > 0 )
			{
			break;
			
			}  
			}
			}
			
			// now, compare the paths
			var common_parent = "";
			for (var i = Math.min(cc_path.length, ic_path.length); i > 0; i--)
			{
			if (cc_path[i-1] == ic_path[i-1])
			{
			common_parent = cc_path[i-1];
			break;
			}
			
			}
			
			
			set("costcenter", common_parent);
			set("intercompany", common_parent);
			set("type", 2);
			set("amount", -1*get("amount"));
			fire(ctxt.row_nb++);
			
			}
			

This is the result:

As you can see, the procedure has generated two elimination bookings, exactly at the right level.

step 2 – model generation

Deploy the schema and navigate to the MDX editor. And copy the following MDX statement:

select {[Measures].[amount] }*descendants([Elimination].[Elimination].[Eliminated]) on 0
				, descendants([CostCenter].[CostCenter].[Total CostCenter]) on 1
			from finance

This gives as result:

Exactly as we want it to be!

Feel free to explore this model into more detail. Although the Intercompany dimension is not strictly needed, I would advise you to keep it as is, since it provides very useful insights in the intercompany process for the financial controllers.

practical things to consider in real-life implementation

The code presented in the previous paragraph has been implemented at a client and is used on a cost center structure of about 1500 members and 1 mio fact rows. It has worked flawlessly and fast for the past few months and I assume it will continue to work like that for the years to come.

When implementing intercompany allocation, consider the following:

• If intercompany elimination is introduced in an existing solution, make agreements with the controllers how to proceed with the historical data. Most likely, they do not want it to be changed, so intercompany elimination should only be applied to the data starting with January 1st this year.
• Loading the elimination bookings on subtotal levels might be difficult for end-users to swallow. For example, have a look at the column “Eliminated” in Figure 4. If you only see the figures for this element you could get nervous that the figures “do not add up”. Also the € -90 does not help a lot as you can not see the eliminatin booking of € -55 seperately, but in stead you see the summarized result. A possible solution to this, is to add an additional input level for each subtotal to the Costcenter hierarchy. Obviously, this has also its drawbacks as it “pollutes” the structure.
• Regardless of what you choose to load the elimination bookings, ensure to create sufficient explanatory dashboards. Intercompany elimination is a difficult process so help your controllers and other end-users so they can review, analyse and explain the results they see. The most efficient solution is the solution that leaves no unanswered questions for your audience.

Good luck implementing this in your dashboard solutions!

about the author

Arthur van den Berg, www.inside-vision.com

It’s a kind of magic … to transform loads of data into insight giving dashboard products that show your audience what step to take next, based on your data.

I work closely together with icCube being a Dutch reseller. I love their product that allows me and my clients to launch meaningful dashboard products and to embed analytical insights into their software.

We’re working hard on the next version that will include some important performance improvements such as smart cache and pre-compilation.

In the meantime, there are already a couple of “tricks” you can use to improve performance. Because they are specific to icCube, MDX+, they are not yet well known by all icCube’s users. So, we’re going to use a customer request as an excuse to present several ways for improving a classical MDX expression:

Sum( {a Set}, [some value] ).

This started with a customer telling us that some icCube requests were too slow, taking 20 seconds. This is certainly a lot of time, so we decided to dive into the query. To simplify, we are going to use parts of the original query that was using several calculated measures. It looks like this:

SET [MTD] as Mtd( ClosingPeriod([Calendar].[day]) )
SET [YTD] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) )
member [Measures].[Sales MTD] as Sum( [MTD], [Measures].[sales] )
member [Measures].[Sales Last Year YTD] as Sum( [YTD], [Measures].[sales] )

We have a couple of measures that calculate sales for different periods, namely YTD and MTD. The hierarchy [Calendar] has been built so ClosingPeriod([Calendar].[day]) returns the last day with data. YTD and MTD will sum all days from start year and start month, respectively. For YTD we can have up to 365 days, so it’s time-consuming, especially if the query is done on several calculated measures, as was the case.

The first quick improvement is to use the CompactSet function, which transforms the input set into a smaller set. If all days of a month are in the set, they will be replaced by the month. Therefore, the size of a set decreases from hundreds of members to around 10–20 members.

This transformation is possible because the numerical expression used in the Sum function uses the sum-aggregation method. This would also be true for Min, Max, Count but not for Aggregations methods such as Distinct Count, Open Period, or End Period. It would also not be possible if the numerical expression had multiplication, as for example in a share of market.

The new MDX looks like this:

SET [MTD] as CompactSet( Mtd( ClosingPeriod([Calendar].[day]) ) )
SET [YTD] as CompactSet( Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) ) )
member [Measures].[Sales PTD] as Sum( [MTD], [Measures].[sales] )
member [Measures].[Sales Last Year YTD] as Sum( [YTD], [Measures].[sales] )

This new query is already dropping the response time to 2.3 seconds—and this is a major improvement.

However, CompactSet will not work in all scenarios. So let’s try an alternate solution using our latest Eval() MDX+ function. This function is similar to the Aggregate() function but uses a subcube. The Eval() function is a bit smarter and will reuse subcubes defined with the same set.

SET [MTD] as Mtd( ClosingPeriod([Calendar].[day]) )
SET [YTD] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) )
member [Measures].[Sales PTD] as Eval( [MTD], [Measures].[sales] )
member [Measures].[Sales Last Year YTD] as Eval( [YTD], [Measures].[sales] )

This query needs 3.2 seconds. It’s not as fast as the Compact version, but it is seven times faster than the initial version. If we combine it with CompactSet, the time drops to 1.7 seconds. This is our fastest version for now.

Finally, we are going to use Categories. Categories allow defining new members based on a set or a subcube, and is similar to the Eval function.

As [MTD] and [YTD] sets are defined at schema level, we can add them into the Advanced/Scripts panel:

CREATE STATIC SET [MTD Set] as Mtd( ClosingPeriod(([Calendar].[day]) )
CREATE STATIC SET [YTD Set] as Ytd( ParallelPeriod( [Calendar].[day], 1, ClosingPeriod([Calendar].[day]) ) )
CREATE CATEGORY MEMBER [Util].[Categories].[default].[MTD] as [MTD Set]
CREATE CATEGORY MEMBER [Util].[Categories].[default].[YTD] as [YTD Set]

To use them is even simpler:

member [Measures].[Sales PTD] as ([Util].[Categories].[MTD], [Measures].[sales] )
member [Measures].[Sales Last Year YTD] as ([Util].[Categories].[YTD], [Measures].[sales] )

This is the fastest version, taking 1.6 seconds. It’s also IMHO, the cleanest and easy to reuse.

I hope these few examples will help you writing more efficient and faster MDX statements.

In this post we’re going to explain one of the latest functions in icCube , sumOn / VectorK , and how it can be used.

This start with the use case in the healthcare sector that was tricky to solve. It’s simplification of the actual scenario but we have data about the stay in a hospital of a list of patients that look like :

We want to be able to calculate the cost of the whole stay using as filter Department and Traitment.

The standard solution in MDX would be to filter the Stay members and afterwards calculate the stay cost (it’s not the traitement cost), something like :

WITH
// we need to reset the filter so we have all the traitements for a stay
MEMBER [Stay Cost] as ( [Patient].defaultMember, [Department].defaultMember, [Traitement].defaultMember, [Measures].[Cost] )
SELECT
  [Measures].[Stay Cost] on 0,
  NonEmpty( [Stay].[Stay].[Stay - L] , [Measures].[Cost] ) on 1
FROM [Cube]
WHERE [Department].[Department].[Department].[Radiology]

This will work for small schemas and when the [Stay] dimension is not very large but it’s going to get worst when we have millons of rows and have an increasing number of stays.

One solution would be to create a fact table that uses many-to-many relations (check ‘GroupBy on Stay’ view in the schema enclosed at the end of this post).

If this is possible, go for it. In our case this was not possible as we wanted to be able to filter on multiple dimensions using an AND relation. For example, we want to get the stays that had a row with Cardiology AND Ex traitement. The many-to-many relation answers to the question Cardiology OR Ex, so it can not be used as you can see in the table this information was lost in the transformation. We could indeed had created a composed dimension ( Department + Treatment ) but this was not ideal as we wanted the end users to be able to create their own reports with easy filters.

So eventually, the solution is a mix of icCube features based around the Vector aggregation. As a summary of the solution: the new calculation is going to get a measure that returns a vector with unique stays and we will use this vector with the stays to calculate the cost. How ?

a) Create a Measure with ‘Vector Unique’ as aggregation method on the stayId column -> [V – Stay]
b) Create a Vector that will hold for each member id the Stay Cost at the position defined by the id of the member (that has to be an integer). For example, [Stay 23] member that has an id of 23 will put his cost at the position 23 of the vector. To be memory efficient there should be no holes in the stayId values and they should start with 0, so the vector would be as small as possible.

We can already check our ideas :

WITH
   // create a vector with the cost by stay for each stay 
   STATIC FUNCTION VStayCost() as VectorK( [Stay].[Stay].[Stay - L] , [Measures].[Cost] )
   // This will sum for each stay defined in the measure it's cost
   MEMBER [Stay Cost2] as SumOn( VStayCost() , [Measures].[V - Stay ] )
SELECT
   { [Measures].[Stay Cost2], [Measures].[Stay Cost] } on 0,
   NonEmpty( [Stay].[Stay].[Stay - L] , [Measures].[Cost] ) on 1
FROM [Cube]
WHERE [Department].[Department].[Department].[Radiology]

STATIC FUNCTION allows us to evaluate only once ignoring the current cell context: filters, subselects and where clause.

VectorK, is a function that evalautes the expression and sets the value in the position defined by the member key. SumOn allows to sum to vectors, the first one defined the values to sum, the second one the positions to sum.

As our final detail, we move the Function and the measure declaration to the schema script definition and it’s done. Our robust and amazingly fast new calculation is there.

You can download the self-contained schema from here.

Hope you enjoyed !

Shopping Cart Advanced Analytics with icCube and MongoDB

In part 1 and part 2 of our blog post about icCube and MongoDB, we introduced how to load shopping cart data stored in MongoDB and how to perform some SubCube functions. In this third part, we introduce two icCube MDX+ object-oriented extensions: using vectors and defining the same hierarchy on both axes. MDX+ are icCube specific extensions to the MDX standard.

We aim to answer the question, ‘How similar are two customers’ shopping carts?’

For this, we’re going to calculate a cart proximity number and present the result as a heat map between customers.

The shopping cart proximity is a loose concept, indeed. For simplicity’s sake, we can start by visualizing the shopping cart for two customers:

How to calculate proximity?

As we want to put in a single number the distance we need to find a way for calculating, Distance( Cust1, Cust2 ), let’s represent a customer as a vector. The members of this vector will represent the number of products bought by the customer:

Cust1 = ( Number of Plasma , Number LED … ) – (2,0,3 …) Cust2 = ( Number of Plasma , Number LED … ) – (1,3,12 …)

Using the number of products measure is arbitrary, and we could choose other measures as well as other dimensions. But let’s continue with this choice.

The first idea is using the classical Euclidian vector distance. However, the result does not match our visual intuition, so it’s not a valid option. Thus, we use a new distance definition based on a graphical concept. Let’s use a distance function based on the visual chart surface.

The distance would be the absolute value of the difference divided by the maximum value (all values are positive, so sign is not a concern). The distance equation would look like this:

Dist = Abs( Cust1 – Cust2 ) / Max( Cust1, Cust2 )

Translating this to icCube is easy thanks to the OO vector support. In order to build a vector object, we can write the following MDX statement:

Vector( [Products Set], [Measures].[Number Carts])

The Vector() function will return a vector object after iterating for each member of the first parameter. MDX+ object-oriented support allows us to perform some math directly on vectors (check here for more information):

If a and b are vectors for customers a and b:

FUNCTION distance(Value a, Value b) AS ( a->pDif(b)->sum() ) / ( a->pMax(b)->sum() )

The notation -> is the MDX+ operator calling the underlying Java function on an object (here vector). The functions used in this calculation are defined as:

In our case, taking 1 for the length, this matches the surface of the chart.

Putting all this together in an MDX (the cache is for improving performance):

WITH
  // calculates the distance our shopping carts ,
  FUNCTION distance(Value a, Value b) as ( a->pDif(b)->sum() ) / ( a->pMax(b)->sum() )
  // FUNCTION distance(Value a, Value b) as a->corr( b )
  // true if we're on the top triangle of our axis
  FUNCTION isAbove() as [Customer].[Customer].current(0).name > [Customer].[Customer].current(1).name
  // a little bit of MDX leveraging request cache so we don't calculate each vector N times
  MEMBER Vect as Vector( [Product].[Product].[Product L], [Measures].[Number Carts]), REQUEST_CACHED = true
  // basket for each customer, to leverage cache use only one hierarchy
  MEMBER Basket1 as ([Customer].[Customer].current(0), [Measures].[Vect])
  MEMBER Basket2 as ([Customer].[Customer].current(1), [Measures].[Vect])
  // MEMBER Basket2 as ([Customer 2].[Customer 2].defaultMember, LinkMember( [Customer 2].[Customer 2].currentmember,[Customer].[Customer]) , [Measures].[Vect])
  // a distance between two basket (1 if they are the same going somehow to 0 if they are fully different)
  MEMBER Dist as IIF( isAbove(), null, 1-distance([Basket1],[Basket2]) ), FORMAT_STRING="percent" //, BACK_COLOR=paletteOutliersLight( currentCellValue() )
  SET [Cust1] as nonempty( [Customer].[Customer].[Customer - L], [Measures].[Number Carts]).head(25)
SELECT
  [Cust1] on 0,
  [Cust1] on 1
FROM [Cube]
WHERE [Dist]

Note this MDX statement uses a 4.8 functionality that allows defining the same hierarchy on both axis-0 and axis-1. The Current(0) function is picking the first (on axis-0) member for the hierarchy.

Eventually, all this can be put together in a single dashboard for experimenting that you can check here.

Shopping Cart Advanced Analytics with icCube and MongoDB

In our previous post, we introduced how to load shopping cart data stored in MongoDB to icCube leveraging the array support. Here we introduce the MDX+ SubCube functions of icCube. MDX+ are icCube specific extensions to the MDX standard.

The kind of question we’re going to answer here looks like, ‘How to make stats/count all shopping carts with TVs that have no cheese sales?’

Even though this query might not look as the most thrilling one, we might easily extend this for other scenarios and businesses as surveys and other customer behaviour scenarios.

Coming back to our query, there is no straightforward way to translate the question into MDX language. There is no way to create a filter defined as ‘all shopping carts with TVs but without cheese’. Why?

A short introduction to standard MDX

The MDX language works nicely in a universe without many-to-many relations but less so when those relations exist. By many-to-many we mean a hierarchy where several members, e.g., [United Kingdom] and [USA], are pointing to the same data. By definition, those members are from the same hierarchy, [Geo], and level, [Country]. Without many-to-many, defining a filter like [UK] – [US] has no sense as it is empty by construction.

An approach to understanding what is going on in MDX is to see MDX tuples as a way of defining/filtering a set of fact rows. In standard MDX, and with tuples without calculated measures, we can have two ways combining rows: Union and Intersects.

The tuple ( [USA], [2010] ): will be an intersect, aka logical-and, of the fact rows defined by [USA] and [2010].

A set {[USA],[UK]}: will be a union, aka logical-or, of the fact rows defined by [USA] and [United Kingdom].

We can add sets and tuples to the ‘where’ clause and to the subquery, but they impact how tuples and calculated members are evaluated. Unfortunately, this is all we have in standard MDX as set operations and combining them is an exercise of technique, experience, and imagination. Some operations are just overly complicated and using calculated measures might be error prone, poorly stable, and/or time consuming. Let’s say there is no proper support in MDX for basic set operations: combining union, intersection, and complement functions (logical and, or & not).

How to overcome this limitation

Since icCube 4.7, we have the MDX+ SubCube functions family:

SubCubeUnion, SubCubeIntersect, SubCubeComplement, SubCubeMinus, and SubCubeSymDifference.

Minus and Symmetrical Difference are a shortcut for combinations of the first three basic ‘brick’ operations. SubCubeMinus( A, B ) is a shortcut for SubCubeIntersect( A, SubCubeComplement(B) ). You can define your own set functions using MDX+ functional support(link here).

How do these functions work?

For example, let’s take SubeCubeMinus( [USA] , [United Kingdom] ).

This will filter all rows defined by [USA] minus the rows defined by [United Kingdom]. Note that the parameters of SubCube functions do not need to have the same dimensionality; SubCubeMinus( [USA], ([UK],[2010] ) is a valid MDX statement.

Before entering into an MDX example, feel free to try the following reporting example:

This is a dynamic report embedded with icCube’s WordPress plugin

An example of MDX syntax looks like:

WITH
  CATEGORY HIERARCHY [Stats].[Filter]
  CATEGORY STATIC MEMBER [Stats].[Filter].[All-M].[filter] AS SubCubeMinus( [Product].[Product Family].[Product Family L].[TV], [Product Plain].[Product Plain].[Product L].[Crab] )
SELECT
  [Measures].members ON COLUMNS,
  NON EMPTY [Age Group].[Age Group - L] ON ROWS
FROM [Cube]
WHERE [Stats].[Filter].[All-M].[filter]

SubCube functions return a SubCube object and currently can only be attached to category members. On the above MDX, we’re attaching our SubCube to a filter member that we can use as a normal MDX member.

We can also use categories and SubCube functions for axis, as in this example:

WITH
  CATEGORY HIERARCHY [Stats].[Filter]
  CATEGORY MEMBER [Stats].[Filter].[All-M].[With TV] AS {[Product].[Product Family].[Product Family L].[TV]}
  CATEGORY MEMBER [Stats].[Filter].[All-M].[TV without Seefood] AS SubCubeMinus( [Product].[Product Family].[Product Family L].[TV], [Product].[Product Family].[Product Family L].[Seafood] )
  CATEGORY MEMBER [Stats].[Filter].[All-M].[TV without VideoGames] AS SubCubeMinus( [Product].[Product Family].[Product Family L].[TV], [Product].[Product Family].[Product Family L].[Video Games] )
  CATEGORY MEMBER [Stats].[Filter].[All-M].[TV without Cheese] AS SubCubeMinus( [Product].[Product Family].[Product Family L].[TV], [Product].[Product Family].[Product Family L].[Cheese] )
SELECT
  [Measures].members ON COLUMNS,
  [Stats].[Filter].[All-M].children ON ROWS
FROM [Cube]

In our next post, we’re going to use object-oriented extensions to make some calculations. See you in Chapter III, presenting object-oriented icCube extensions.