Create A Custom Table Report using Helical Insight (Dynamically Picking the Columns Names and Data)

Create A Custom Table Report using HI (Dynamically Picking the Columns Names and Data)

If you have already had a Hands-On experience on the Helical Insight Tool [HI tool] then this blog would be helpful

For a creating a report there are 4 files required
1. EFW

the Report Layout lies on the HTML page, the SQL queries lies within the EFWD file and the visualization lies in the EFWVF File.
Hence once the Query is fired it comes to the visualization file to create a table as that’s our goal. With the help of the following code below it can be created with ease.

The below code here is a template of our EFWVF looks like
<Chart id=”1″>

//Your Visualization Code Goes Here


Chart ID here is 1 which is unique
Type is of custom
DataSource 1 is the Unique ID defined in the EFWVF File. ie (<DataMap id=”1″ )
Now within the <script> </script>
we Paste the following:

//The If Block Does Return A Message NO Data when there is No Data
if(data.length == 0)
$(‘#chart_1′).html(“<div ><h4 style=’text-align:CENTER;color:black; padding-top:60px;’>No Data Available For Current Selection</h4></div>”);
//The Else Block Returns The Table if there is a Table
//Here the funtion Tabluate Returns the Data in Tabular Form
function tabulate(elem, data, columns)
//Function Start
var table =“table”)
.attr(“class”,” table display compact width:100%;cellspacing:1 “)
thead = table.append(“thead”),
tbody = table.append(“tbody”);

//Append the header row
.text(function(column) { return column; })
.attr(‘class’, function(d, i){ return “colH_” + i; })
.style(‘@media print’,’display:none’)

// create a row for each object in the data
var rows = tbody.selectAll(“tr”)

// create a cell in each row for each column
var cells = rows.selectAll(“td”)
.data(function(row) {
return {
return {column: column, value: row[column]};
.text(function(d) { return d.value; })
.attr(‘class’, function(d, i){ return “col_” + i; })
.attr(‘align’, ‘left’)
return table;
//Function END

//Render the table
//Object.keys(data[0]) is the Data to fetch the Column Header
//data has the data of the Table
var subjectTable = tabulate( ‘#chart_1’, data, Object.keys(data[0]));
Save it in the same Directory with rest of the file

Run your report on HI and you get a table.

Keep in mind you can change the query and still get the new columns from the new query.

Sohail Izebhijie

Pentaho Analysis: Workflow

OLAP Analysis: Workflow


To prepare data for use with the Pentaho/Jaspersoft OLAP Analysis (and Reporting, to a certain extent) client tools, you should follow this basic workflow :

Design a Star or Snowflake Schema

The entire process starts with a data warehouse.The end result should be data model in the star or snowflake schema pattern. You don’t have to worry too much about getting the model exactly right on your first try. Just cover all of your anticipated business needs; part of the process is coming back to the data warehouse design step and making changes to your initial data model after you’ve discovered what your operational needs are.

Populate the Star/Snowflake Schema

Once your data model is designed, the next step is to populate it with actual data, thereby creating your data warehouse. We can use  Pentaho Data Integration or Talend for ETL.

Build a Mondrian Schema

Now that your initial data warehouse project is complete, you must build a Mondrian schema to organize and describe it in terms that Pentaho/Jasper Analysis can understand. This is also accomplished through Pentaho Data Integration by using the Agile BI plugin. Just connect to your data warehouse and auto-populate your schema with the Modeler graphical interface. Alternatively, you can use Schema Workbench to create an analysis schema through a manual process.

Initial Testing

At this point you should have a multi-dimensional data structure with an appropriate metadata layer. You can now start using Pentaho Analyzer, Jasper Ad-hoc and JPivot to drill down into your data and see if your first attempt at data modeling was successful. In all likelihood, it will need some adjustment, so take note of all of the schema limitations that you’re unhappy with during this initial testing phase.

Do not be concerned with performance issues at this time – just concentrate on the completeness and comprehensiveness of the data model.

Adjust and Repeat Until Satisfied

Use the notes you took during the testing phase to redesign your data warehouse and Mondrian schema appropriately. Adjust hierarchies and relational measure aggregation methods. Create virtual cubes for analyzing multiple fact tables by conforming dimensions. Re-test the new implementation and continue to refine the data model until it matches your business needs perfectly.

Test for Performance

Once you’re satisfied with the design and implementation of your data model, you should try to find performance problems and address them by tuning your data warehouse database, and by creating aggregation tables. The testing can only be reasonably done by hand, using Pentaho Analyzer, Jaspersoft Ad-hoc or JPivot. Take note of all of the measures that take an unreasonably long time to calculate. Also, enable SQL logging and locate slow-performing queries, and build indexes for optimizing query performance.

Create Aggregation Tables

Using your notes as a guide, create aggregation tables using Pentaho Aggregation Designer or talend to store frequently computed analysis views. Re-test and create new aggregation tables as necessary.

If you are working with a relatively small data warehouse or a limited number of dimensions, you may not have a real need for aggregation tables. However, be aware of the possibility that performance issues may come up in the future.

Check in with your users occasionally to see if they have any particular concerns about the speed of their BI content.

Deploy to Production

Your data warehouse and Mondrian schema have been created, tested, and refined. You’re now ready to put it all into production. OLAP reports help users to do multi-dimensional and cross functional analysis, slice and dice, drill-up and drill-through the data. It gives power in the hands of business users to generate ad-hoc reports and get better insight of their business and and make better decisions based on OLAP analysis.