What are HeatMaps?
A heatmap is a graphical representation of data where the values taken by a variable in a two-dimensional map are represented as colors.
Moya Watson explains the basic properties of a heatmap very well in this blog. (A Heatmap Paints a Thousand Words)</p>h4. Business Scenario
Any Manufacturing unit has machines, which draw electric energy while producing the requisite product. The client wants a report generated for this consumption, which will indicate the machines taking more than standard amount of energy as compared to the quantity of product generated.
The machines are connected to SCADA systems which monitor the machines and get the data about the energy consumption and store it in a flat file at regular interval.
We have created a MySql database from this data. Now they want the report in the form of a HeatMap. The HeatMap will help them in their analysis as any difference from the normal consumption will be highlighted.
The basic reason for using the HeatMap is because the size of data in the database is huge since the SCADA systems record consumption frequently(user settings). To display this data in a meaningful manner we need to use a HeatMap.
This blog gives a step by step implementation of the above scenario from creating a BI_JDBC system to creating a HeatMap from it.
This is done on NW2004s SP 13
Database MySQL or ORACLE or MAXDB installed.
Visual Composer 7.0
Adobe SVG view and MSXML parser on the client side where you want to use VC.
Steps to implement the business scenario.
Step 1:* Connecting to the external database*.
Configure the BI JDBC Connector in the Visual Administrator.
1. Start the Visual Administrator using
2. Log on as an administrator.
On the Cluster+ +tab, go to your server Node and select Services JDBC Connector.
4. On the +Runtime +tab, verify that a JDBC driver is configured for your data source.
If a JDBC driver for your data has not yet been uploaded to the Visual Administrator, proceed to the next step.
(If necessary,) To import a JDBC driver for your data source, select the Drivers+ node on the *Runtime *tab, and press the *Create New Driver or+ DataSource*button.
5. In the Add Driver+ +dialog box, enter a name for your driver and press OK.
For e g: mysql
6. Specify the location of the JDBC driver JARs provided to you by your RDBMS vendor.
Keep adding JARs until all the required libraries have been added.
7. On the +Cluster +tab, select Services Connector Container, and on the</p>Runtime+ tab: <p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap5.JPG|height=301|alt=|width=300|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap5.JPG|border=0!</p><p>8. Select the *Resource Adapter* +tab.
Press +Add and enter the name of the JDBC driver to add it to the loader references. This takes the following format:</p><p>library :<drivername>, where <drivername> is the name you entered in Step 6 above, for example:</p><p>library: mysql</p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap6.JPG|height=1|alt=|width=1|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap6.JPG|border=0!!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap6.JPG|height=160|alt=|width=348|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap6.JPG|border=0!</p><p> 9. On the *Cluster +tab, select +Services Deploy+.</p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap7.JPG|height=1|alt=|width=1|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap7.JPG|border=0!!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap7.JPG|height=214|alt=|width=237|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap7.JPG|border=0!</p><p> </p><p>10. On the +Runtime +tab, select the BI JDBC Connector -</p><p>sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc.</p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap8.JPG|height=182|alt=|width=384|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap8.JPG|border=0!</p><p>11. Press Stop Application.</p><p>12. Press Start Application.</p><p>13. (Optional) To test the connection:</p><p>a) Select Services Connector Container, and select</p><p>sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc/BI_JDBC_CONNECTOR.</p><p>b) On the +Managed Connection Factory +tab, select Properties.</p><p>c) Enter your connection properties, for example:</p><p>URL: jdbc:mysql://<server ip>:<port>/database</p><p>Normally port=3306</p><p>Driver: com.mysql.jdbc.Driver</p><p>d) Save the settings.</p><p>e) Run the following test servlet:</p><p>[http://<hostname>:<port>/TestJDBC_Web/TestJDBCPage.jsp | http://<hostname>:<port>/TestJDBC_Web/TestJDBCPage.jsp]</p><p>Where hostname=hostname of your portal.</p><p>If your connection is successfully configured, a list of tables appears.</p><p>The BI JDBC Connector is now ready for use. </p><p>IF you want to connect any other database</p><p>BI JDBC Connector to Oracle</p><p>[http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html | http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html]</p><p>Driver: oracle.jdbc.driver.OracleDriver</p><p>URL: jdbc:oracle:<drivertype>:@<database></p><p>jdbc:oracle:thin:@myhost:1521:orcl</p><p> </p><p>BI JDBC Connector to MaxDB</p><p>Driver: com.sap.dbtech.jdbc.DriverSapDB</p><p>URL: jdbc:sapdb://<server>/<database></p><p>e.g. jdbc:sapdb://ilgm76/mydb</p><p> </p><p>BI JDBC Connector to MSSql*h4. Step 2: Create the BI JDBC System in the SAP NetWeaver Portal
Using the BI JDBC Connector, you now create a BI system that Visual Composer can use in the SAP NetWeaver Portal.
Preparing your system in the portal consists of the following tasks:
Prepare the connection properties
Configure the user mapping
1) To create a new system in the Portal System Landscape:
Log on to the portal and select +System Administration ->System+ Configuration->System Landscape.
On the Browse+ tab, select a folder or subfolder in the *Portal Content* folder and right-click to access the context menu. </p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap9.JPG|height=182|alt=|width=468|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap9.JPG|border=0!</p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap10.JPG|height=215|alt=|width=198|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap10.JPG|border=0!</p><p>On the *General Properties* screen, configure the *System Name* and *System ID* +fields with the values of your choice for your data source, and press Next. tab, open the system for edit by choosing</li><li>Open the object for editing+, and press OK0.1.
From the Property Category+ dropdown list, select *Connection Properties*, and configure the connection properties as pictured to the right.</li><li>Enter your own values for *Connection URL* +and Driver Class Name</strong>+ according to your JDBC driver documentation, for example:</li></ul><p>URL: jdbc jdbc:mysql://<server ip>:<port>/database</p><p>Normally port=3306</p><p>Driver: com.mysql.jdbc.Driver</p><ul><li>Use the optional *Fixed Catalog* and *Fixed Schema* properties to restrict metadata access, if desired.</li><li>Press *Save* to save the values.</li></ul><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap12.JPG|height=248|alt=|width=324|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap12.JPG|border=0!</p><ul><li>From the *Property Category* dropdown list, select *User Management+*.0.1.
From the +User Mapping Type +dropdown list, select admin,user0.1. Press +Save +to save the value.
Create a new alias for this system. This is how Visual Composer will refer to the system:0.1.
Using the +Display drop-down list, select *System Aliases* +and add an alias to your system. In this example, we name the alias BI_JDBC.0.1. Press +Save +to save the value.
From the first-level Portal navigation, select +User Administration >Identity Management.</li><li>Get +your user name, and in the +Details +section, select the +User Mapping +tab.0.1. Map your Portal user to the user of the newly created system (in this case, the user of the JDBC system), also configuring your user name and password for your system:
Your BI JDBC system is now ready for use in Visual Composer. Look for it in Visual Composer under the alias.
Step 3: Configuring the IGS server
Heatmaps rely on the graphics capabilities of the Internet Graphics Server (IGS), so to use them you need to tell Visual Composer where to find it before you fire it up. You do this by configuring the vc.bi.igs property in the Visual Administrator.
Step 4: Creating the HeatMap:
The user should have VCRole assigned.
The software’s mentioned at the start of this blog must be installed on his system.
Start Visual Composer by typing the URL http://:/VC/default.jsp
Create a new Model,,create an iview and drilldown into it.Then go to BI Integration Wizard
From the BI menu, choose BI Integration Wizard.
On the Select a Query Template screen, select the Heatmap template.
a.On the Select Fields screen, choose the parameters that you want to use . In our heatmap template, we visualize exactly four fields in relation to each other, two of which must be numeric, so select appropriately.
In our case we selecting
This can be different for different scenarios. For our scenario we have created them earlier in the database and they are coming from that place.
a. We are bypassing the optional Configure Joins, Configure Filters, and Specify Sort Order screens.
b.Next you arrive at the Configure Heatmap Parameters screen. The field you select for primary grouping will construct the larger boxes in the heatmap, and the secondary grouping will fill smaller boxes, subsets inside of each primary box. Select a field “higher up” in the hierarchy for the primary grouping field. For example, in a hierarchy of products, select Category in the primary grouping field and Product in the secondary grouping field:
f. On the Review Output+ screen, we give you an idea of what’s going to appear in the heatmap, in tabular form. </p><p>g. Press *Finish* to dismiss this screen, and you’re basically all done!</p><p>Tweak the Layout<br />Once you return to Storyboard, you see that the BI Integration Wizard has not only created a data service configured with the heatmap query, but also attached a start point for you and an HTML view on the service’s URL port. Remember that heatmaps rely on the IGS for their graphic visualization, so we call a specially created URL in a container called an HTML view to reference the IGS and display the heatmap: </p><p> </p><p>!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap21.JPG|height=1|alt=|width=1|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap21.JPG|border=0!!https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap21.JPG|height=180|alt=|width=335|src=https://weblogs.sdn.sap.com/weblogs/images/251875820/HeatMap21.JPG|border=0!</p><p> </p><ol><li>If you go to the *Layout+ *tab, you’ll see that this HTML container is a little small by default. You can change its size using a little trick:
a.Back on the Design tab, double-click the URL port on the heatmap data service.
b.On the Configure Element task panel, in the Output Fields section, double-click the URL field to modify it. In the +Compute +field, change the width and height parameters in the URL as desired:
Now, make sure that the HTML view container and the heatmap container are the same size. To adjust the size of the HTML view, go to the Layout tab and grab its handles to resize it. Its width and height are displayed in the pane below:
a. Now Go to the deploy tab and click deploy.
Click on Run