Multiple Fact Tables - Advanced Filtering

[intapiuser]

Download: JAQL Butler

Purpose/Benefits

When your data model contains multiple fact tables, it can add challenges regarding filters.  Consider the following data model.

This Elasticube contains data from several fact tables (coming from Google Adwords and Google Analytics), joined to a campaigns dimension.  Each fact table has a different set of fields that could be used as filters (such as Medium, Source, Keyword, etc in the Ecommerce Table).  If a user creates a widget that contains metrics from both fact tables and tries to add a filter based on a field from the Ecommerce fact table (2), it will also filter the metric from the Adwords fact table (1).  In some use cases, you may want a filter from the ECommerce fact table to only affect aggregations based on the same table.  If you want to accomplish this using data modelling, you can do so using a Key Table approach but it increases the size of the data stored in the Elasticube.  This article describes how to have filters from fact tables affect only measures within the same fact table.

Steps/Notes

The following steps will walk through the process of adding the new chart type and creating a sample funnel chart.

STEP 1 - ADD THE PLUGIN

Download the attachment and unzip the contents into your C:\Program Files\Sisense\PrismWeb\plugins\ folder. If you are using version 7.2 and higher unzip the contents into your C:\Program Files\Sisense\app\plugins\ folder. If the plugins folder doesn't exist, just create it. After those files have been unzipped there, you may also have to restart the web server. 

STEP 2 - CONFIGURE THE PLUGIN

Open up the config.js file, and edit the prism.jaqlButler object.  This jaqlButler object contains one or more Elasticubes to be affected by this plugin, and each Elasticube has a scopedFilters, swapFilters and segmentedFacts object.  For this use case, only the scopedFilters object is relevant.  This should contain each of the table names for your fact tables within this Elasticube, and just provide a value of true.  Setting it up in this manor will make sure any Elasticube query will look for filters coming from these fact tables, and adjust the query so that only the aggregations that belong to the same table will be affected by the filter selection.

// Define Elasticubes and Tables to add filters for
prism.jaqlButler = {
 "myElasticube1":{
  scopedFilters: {
   "FactTable1-name": true,
   "FactTable2-name": true
  },
  swapFilters:{},
  segmentedFacts:{}
 },
 "myElasticube2" : {
  scopedFilters: {
   "FactTableA-name": true,
   "FactTableB-name": true,
                        "FactTableC-name": true
  },
  swapFilters:{},
  segmentedFacts:{}
 }
};

STEP 3 - CREATE YOUR DASHBOARDS(S)

There are no extra steps needed when creating dashboards and/or filters, the query adjustments will happen automatically. 

Figure 1: Dashboard with no filters applied, Cost comes from Fact Table 1, Revenue comes from Fact Table 2, Margin = Revenue - Costs

Figure 2: Dashboard with a filter applied from the Revenue Fact table, only the Revenue and Margin KPIs are adjusted

References/Notes

This plugin is setup to only be applied when using the Elasticubes and Tables defined within the configuration file.  If you change the name of the Elasticube or Table(s), you will need to update the config.js file.

UPDATE June 12, 2017 - This plugin has been updated to work with dependent filters.

This plugin works by adjusting the JAQL query on the fly to add/remove filters from specific items.  If you observe the JAQL Query normally made for the Total Revenue KPI, you can see what gets changed by this plugin.  Instead of using a standard aggregation definition in the query, this is replaced with a Measured Value formula.  Once the filter is built into the formula, the scoping filter is removed the from query as well.  The result is that only the specific metric gets affected by the filter

Original JAQL

{
 "datasource": {
  "title": "GA_AW",
  "fullname": "LocalHost/GA_AW",
  "id": "aLOCALHOST_aGAXwAaAW",
  "address": "LocalHost",
  "database": "aGAXwAaAW"
 },
 "metadata": [{
  "jaql": {
   "table": "Ecommerce, Daily Report",
   "column": "TransactionRevenue",
   "dim": "[Ecommerce, Daily Report.TransactionRevenue]",
   "datatype": "numeric",
   "agg": "sum",
   "title": "Revenue"
  },
  "format": {
   "mask": {
    "abbreviations": {
     "t": true,
     "b": true,
     "m": true,
     "k": false
    },
    "decimals": "auto",
    "currency": {
     "symbol": "$",
     "position": "pre"
    }
   },
   "color": {
    "type": "color",
    "color": "#124c87"
   }
  },
  "source": "value"
 }, {
  "jaql": {
   "table": "Ecommerce, Daily Report",
   "column": "Source",
   "dim": "[Ecommerce, Daily Report.Source]",
   "datatype": "text",
   "merged": true,
   "title": "Source",
   "collapsed": false,
   "filter": {
    "explicit": true,
    "multiSelection": true,
    "members": ["(direct)"]
   }
  },
  "disabled": false,
  "isCascading": false,
  "panel": "scope"
 }, {
  "jaql": {
   "table": "My Dates",
   "column": "Date",
   "dim": "[My.Date1 (Calendar)]",
   "datatype": "datetime",
   "merged": true,
   "title": "Years in Date1",
   "level": "years",
   "collapsed": false,
   "filter": {
    "explicit": true,
    "multiSelection": false,
    "members": ["2015-01-01T00:00:00"]
   }
  },
  "disabled": false,
  "isCascading": false,
  "panel": "scope"
 }],
 "isMaskedResult": true,
 "format": "json",
 "widget": "57ab7c53adefdbf435000011;",
 "dashboard": "57ab7c17adefdbf43500000c;Google Analytics",
 "queryGuid": "47E47-11CD-697B-B028-C8C4-5B22-1F79-D5FE-8",
 "offset": 0,
 "count": 50000
}

Modified JAQL

{
 "datasource": {
  "title": "GA_AW",
  "fullname": "LocalHost/GA_AW",
  "id": "aLOCALHOST_aGAXwAaAW",
  "address": "LocalHost",
  "database": "aGAXwAaAW"
 },
 "metadata": [{
  "jaql": {
   "type": "measure",
   "formula": "([0A9E4-C4],[B45B2-D2])",
   "context": {
    "[0A9E4-C4]": {
     "table": "Ecommerce, Daily Report",
     "column": "TransactionRevenue",
     "dim": "[Ecommerce, Daily Report.TransactionRevenue]",
     "datatype": "numeric",
     "agg": "sum",
     "title": "Revenue"
    },
    "[B45B2-D2]": {
     "table": "Ecommerce, Daily Report",
     "column": "Source",
     "dim": "[Ecommerce, Daily Report.Source]",
     "datatype": "text",
     "merged": true,
     "title": "Source",
     "collapsed": false,
     "filter": {
      "explicit": true,
      "multiSelection": true,
      "members": ["(direct)"]
     }
    }
   },
   "title": "Revenue"
  },
  "format": {
   "mask": {
    "abbreviations": {
     "t": true,
     "b": true,
     "m": true,
     "k": false
    },
    "decimals": "auto",
    "currency": {
     "symbol": "$",
     "position": "pre"
    }
   },
   "color": {
    "type": "color",
    "color": "#124c87"
   }
  },
  "source": "value"
 }, {
  "jaql": {
   "table": "My Dates",
   "column": "Date",
   "dim": "[My.Date1 (Calendar)]",
   "datatype": "datetime",
   "merged": true,
   "title": "Years in Date1",
   "level": "years",
   "collapsed": false,
   "filter": {
    "explicit": true,
    "multiSelection": false,
    "members": ["2015-01-01T00:00:00"]
   }
  },
  "disabled": false,
  "isCascading": false,
  "panel": "scope"
 }],
 "isMaskedResult": true,
 "format": "json",
 "widget": "57ab7c53adefdbf435000011;",
 "dashboard": "57ab7c17adefdbf43500000c;Google Analytics",
 "queryGuid": "62D12-BD2D-CC24-453A-EE61-5150-3C14-A341-7",
 "offset": 0,
 "count": 50000
}