Pinning Spark Tables: What is it and how it can help

Software Engineer in Test II • Strategy

When working with Hadoop, we recognize that not all data can be stored in MicroStrategy memory. The following article highlights how customer's can quickly improve the performance against their Hadoop ecosystem - pinning tables to Spark memory. Once achieved, customers will effectively have 3 levels of data access. (MicroStrategy memory, Spark memory, and then direct data access) It is observed that pinning tables in memory improved performance by factor of 10x!

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
When working with Hadoop, we recognize that not all data can be stored in Strategy memory. The following article highlights how customer's can quickly improve the performance against their Hadoop ecosystem - pinning tables to Spark memory. Once achieved, customers will effectively have 3 levels of data access. (Strategy memory, Spark memory, and then direct data access) It is observed that pinning tables in memory improved performance by factor of 10x!
Notably the following two scenarios take advantage of the pinning tables feature:
Scenario #1:
Connect live / Direct data access data set: Pinning tables is particularly useful for those Data Import Datasets that are connected live. For example, a visual insight dossier could have two data sets, one connecting to an intelligent cube and a second one to Spark SQL directly. We can filter out the Spark SQL queries by selections made in the intelligent cube sourced panel:

Scenrio #2:
Drilling out of intelligent cubes: The scenario is business user analyzing information at intelligent cube report level and drilling outside intelligent cube scope for more details. Spark SQL tables can be pinned to improve performance of the drilling report.

Performance Impact

Below a comparison between drilling actions execution times when table is pinned in memory and when table is not pinned in memory.
The schema for this exercise is simple:
Intelligent cube in Strategy: Arrival city, Avg (Fact 1), Avg (Fact 2)
Flight Fact table (Spark: 250 MB) : Arrival city, Flight, Fact 1, Fact 2
Flight Fact table (Spark: 8.7 GB) : Arrival city, Flight, Flight way point, Fact 1, Fact 2

Drilling:

Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns
Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns
Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns	Average time(secs) Run intelligent cube subset report (Arrival city level)648311Drill out of the intelligent cube (Arrival city, flight level)1,000421Drill again to (Arrival city, flight, flight waypoint level)100560.24.4ActionNumber of resulting rowsNumber of resulting columns

Direct data access:

Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8
Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	Average time (secs) Filter connect live grid with a particular city (arrival city, flight level)1,000 (all pages)421Filter connect live grid with a particular flight (arrival city, flight , flight way point level)2,100 (all pages)540.8	4.5

Comment

0 comments

Details

Knowledge Article

Published:

May 22, 2017

Last Updated:

March 21, 2024

Performance Impact