From spreadsheets to IBM Business Monitor dashboards in under an hour, Part 1: Using the free CSV tool to create a dashboard

In Part 1, you'll earn how to create custom IBM Business Monitor® dashboards in under an hour. Using only a spreadsheet tool, such as Microsoft Excel®, you can define the metrics, KPIs, and reports you desire, and fill in sample data that you want to see in a dashboard. Then learn how to customize that dashboard to more closely meet the visual requirements of your business users. This content is part of the IBM Business Process Management Journal.

John Alcorn (jalcorn@us.ibm.com), Senior Software Engineer, IBM

author photo

John Alcorn is the chief architect for the IBM Business Monitor product. He has worked as a software engineer with IBM for 20 years, with more than 15 years on WebSphere products, including roles in both product development and software services. John has been a technical leader with the Business Monitor product for eight years, and works closely with the wider IBM Business Process Manager (BPM), IBM Operational Decision Manager (ODM), and IBM BlueWorks Live teams.

John is IBM-certified in XML technologies, SOA technologies, and in multiple WebSphere products, and is Sun certified in Java programming. He currently is privileged to work with an excellent team of software engineers at IBM's Research Triangle Park lab in North Carolina.



04 December 2013

Also available in Chinese Russian

Overview

This article will teach you how to very quickly create and customize real-time dashboards in IBM Business Monitor. We used IBM Business Monitor V8.0.1.2 in this article, but the steps should work on V8.0.0.0 or later. In this example, we used Microsoft Windows™, but other operating systems, such as AIX® are also supported.

You'll learn how, without needing to do any programming in Java™, JavaScript™, or SQL™, or using any tools beyond Microsoft Excel and a web browser, you can go from a simple spreadsheet description of a desired dashboard to having that dashboard running live, updating in real-time as you send it sample data, in less than an hour, using a simple CSV tool available for free download.

Figure 1. The CSV tool
The CSV tool

In this article, we'll use an Order Processing scenario, where each order has various metrics (Figure 2), where we track various KPIs (Figure 3, and where we chart various measures, or reports (Figure 4), against dimensions for these orders. You'll learn how to create your own custom monitor model and dashboards for this scenario.

Figure 2. Metrics example: Spreadsheet and resulting dashboard
Metrics example: Spreadsheet and resulting dashboard

Click to see larger image

Figure 2. Metrics example: Spreadsheet and resulting dashboard

Metrics example: Spreadsheet and resulting dashboard
Figure 3. KPIs example: Spreadsheet and resulting dashboard
KPIs example: Spreadsheet and resulting dashboard

Click to see larger image

Figure 3. KPIs example: Spreadsheet and resulting dashboard

KPIs example: Spreadsheet and resulting dashboard
Figure 4. Reports example: Spreadsheet and resulting dashboard
Reports example: Spreadsheet and resulting dashboard

Click to see larger image

Figure 4. Reports example: Spreadsheet and resulting dashboard

Reports example: Spreadsheet and resulting dashboard

Historically, doing this kind of tracking and reporting has meant involving a person with programming skills using the Eclipse-based IBM Business Monitor toolkit. In this series, you'll learn an alternate approach that is more business-user friendly, in which you simply fill in spreadsheet templates in Microsoft™ Excel™, then use a simple web app to generate and deploy a monitor model and its dashboards.

Get the spreadsheets

First download and unzip the CSV tool. (If you don't already have a free account, sign up here first.) In addition to the ear file for the tool itself and a readme.txt, you'll see four sample spreadsheets, saved in .csv format, as shown in Figure 5. For the purposes of this article, we'll assume these are placed in the C:\CSV directory.

Figure 5. Sample CSV files
Sample CSV files

Define metrics

Note that you could also open the CSV files into Notepad®, since they are plain-text, comma-separated-values spreadsheets; but they are more readable in a tool like Excel or any other spreadsheet application that can work with files in the CSV format, such as IBM Lotus® Symphony.

Double-click Order Metrics.csv, as shown in Figure 5. This will launch whatever spreadsheet application you have associated with .csv files, such as Microsoft Excel (Figure 6). You may want to resize columns to see everything (the CSV file format does not store metadata such as the size of each column).

Note: Be careful not to choose Order Metrics 2.csv. We'll use that later, in part 3 of this series.

Figure 6. Sample metric spreadsheet
Sample metric spreadsheet

Click to see larger image

Figure 6. Sample metric spreadsheet

Sample metric spreadsheet

This spreadsheet has already been filled out for you, in this example with orders from the Apple™ web site. Following are some comments on the structure of this spreadsheet:

  • The first row contains the names of your desired metrics, one per column. You can have as many columns as you want. The first metric is intepreted to be the key, which uniquely identifies an instance of whatever is being monitored.
  • The second row contains the data types for each metric from the previous row. Valid values are String, Boolean, Integer, Decimal, Date, Time, DateTime, and Duration (case insensitive). Note that certain data types require data for that column in subsequent rows to be entered in a certain format, such as a Boolean being true or false, or an Integer consisting of just numerals, with no letters or punctuation.
  • The third row is blank (exists for readability).
  • Subsequent rows list sample data you want sent to this monitor model later on. You can have as many rows of sample data as you want.

You can edit this spreadsheet as desired, but note that the screenshots in this article are based on an unedited version, and invalid values will result in failures (valid values will be explained wherever you must choose from a list of acceptable values). You'll see that 13 metrics are defined, of various data types, and 7 order instances are defined. Later, we'll make use of a second metrics spreadsheet that lists the same metrics, but has additional instances defined.

Define KPIs

Now that we've defined the metrics we care about, let's define some KPIs, which perform aggregations on those metrics, and display those relative to a target and ranges that we will specify. Double-click on Order KPIs.csv in the C:\CSV folder.

Figure 7. Sample KPI spreadsheet
Sample KPI spreadsheet

Again, this spreadsheet has already been filled out for you. Following are some notes on the structure of this spreadsheet:

  • The first row is just column headers to help you understand what goes in subsequent rows.
  • Next come as many rows as you want, each defining a new KPI. Valid values for Aggregation Type are Minimum, Maximum, Count, Sum, Average, and Standard Deviation. Metric should be the name of a metric from the metrics spreadsheet. Target and Range Value must each be legal Integer or Decimal values. Valid values for Range Color are Black, Gray, White, Red, Blue, Green, Cyan, Magenta, and Yellow (or you can specify an RGB value, which is 6 hexadecimal digits, 2 each for red, green, then blue, such as ff7700 for full red, half green, and no blue).
  • If you want multiple ranges per KPI (and you generally do), create additional rows underneath a KPI, with the first four columns empty. You can have up to 10 ranges per KPI.

Again, you can edit this spreadsheet as desired. You'll see that four KPIs are defined, with a varying number of ranges with varying colors for each.

Define reports

Now that we've defined the metrics and KPIs we care about, let's define some reports, which chart a measure against a dimension. Double-click Order Reports.csv from the C:\CS folder.

Figure 8. Sample report spreadsheet
Sample report spreadsheet

Again, this spreadsheet has already been filled out for you. Following are some notes on the structure of this spreadsheet:

  • The first row is just column headers to help you understand what goes in subsequent rows.
  • Each subsequent row represents a Cognos Business Intelligence™ report in the resulting dashboard. Measure Metric and Dimension Metric should each be the name of a metric from the metrics spreadsheet. Valid values for Calculation are Minimum, Maximum, Count, Count Distinct, Sum, Average, Median, Variance, and Standard Deviation. Valid values for Chart Type are Bar, Pie, Line, Area, Scatter, and Gauge.
  • If you want a dimension to have multiple levels, so that you can drill down by one metric, and then further drill down by another metric, and so on, create additional rows underneath a report, with the all columns empty except for Dimension Metric. In that case, the Dimension Metric entry on the main report row is the name of the multi-level dimension, rather than a metric name, and the values in subsequent rows are the names of metrics. You can reuse that named multi-level dimension in later report rows without respecifying each level. You can have up to five levels per dimension.

Again, you can edit this spreadsheet as desired. You'll see that six reports are defined, using a variety of chart types. These will appear in a two-column layout in the resulting dashboard, so there will be three rows of two charts each. A multi-level Location dimension is defined, which allows you to drill down by Country, then by State, then by City, and this Location dimension is used in three of the reports. Note that metrics of type Date, such as Delivery Date in this example, automatically have three levels: year, month, and day.

Generate a monitor model using the CSV tool

Now let's generate a monitor model from these spreadsheets, and deploy it to the server. If you haven't already, install the csv.ear from the zip file to your IBM Business Monitor server. Then point your web browser to http://localhost:9080/csv (or whatever host and port apply in your case). Hover help is provided to explain each field and button in the CSV tool.

Enter a name for your new monitor model, such as Order Processing, and then click Browse beside each spreadsheet name and select the corresponding CSV file (in C:\CSV), as shown in Figure 9.

Figure 9. CSV tool - Spreadsheets tab
CSV tool - Spreadsheets tab

Click to see larger image

Figure 9. CSV tool - Spreadsheets tab

CSV tool - Spreadsheets tab

If your server uses credentials other than admin/admin, check Specify custom credentials and fill in your user ID and password. Click 1. Create monitor model, and in the span of about a second, a complete monitor model will be generated on the server, ready to be deployed. Dismiss the dialog, which will enable the subsequent button (which we'll use later) and populate the other tabs, showing the contents of each spreadsheet you imported, and the resulting monitor model XML. Let's look at each tab.

First, click the Metrics tab. You'll see what we looked at in Excel a few minutes ago:

Figure 10. CSV tool - Metrics tab
CSV tool - Metrics tab

Click to see larger image

Figure 10. CSV tool - Metrics tab

CSV tool - Metrics tab

No action needs to be taken on this tab; this is just for informational purposes.

Next, click the KPIs tab. Again, this will show what we saw earlier in Excel. No action needs to be taken on this tab.

Figure 11. CSV tool - KPIs tab
CSV tool - KPIs tab

Click to see larger image

Figure 11. CSV tool - KPIs tab

CSV tool - KPIs tab

Next, click the Reports tab, once again confirming what we saw in Excel. No action needs to be taken on this tab.

Figure 12. CSV tool - Reports tab
CSV tool - Reports tab

Click to see larger image

Figure 12. CSV tool - Reports tab

CSV tool - Reports tab

Finally, click the XML tab to display the generated monitor model XML file.

Figure 13. CSV tool - XML tab
CSV tool - XML tab

Click to see larger image

Figure 13. CSV tool - XML tab

CSV tool - XML tab

Don't worry about understanding everything shown in the XML tab. The point is that normally you'd have spent hours in Eclipse authoring such an XML file - defining event schemas, monitoring contexts, correlation predicates, map expressions, KPI contexts, cubes, and so on. Instead, you've accomplished the same thing, just by spending a little time in Excel and then clicking a button in a web app!

Deploy your monitor model

Now return to the Spreadsheets tab and click 2. Deploy monitor model. A Java EE application (an ear file) for your monitor model will be generated and deployed to your server.

Note: This may take several minutes, depending on CPU and disk speed. Please be patient.

Figure 14. CSV tool - Deploy
CSV tool - Deploy

Click to see larger image

Figure 14. CSV tool - Deploy

CSV tool - Deploy

Once this completes, dismiss the dialog to enable the subsequent button (which we'll use later). At this point, your monitor model is running on your server, and a dashboard has been created for you!

Note: Do not close this browser window. You will use it again soon to send data to your new monitor model.

Let's take a look at the new dashboard. Open a new browser window and point it at the IBM Business Monitor dashboards, for example, http://localhost:9080/BusinessSpace (or whatever host and port are applicable in your environment). Log in using admin/admin (or whatever user ID and password are applicable in your environment).

Figure 15. Log in to the dashboard
Dashboard login

Click to see larger image

Figure 15. Log in to the dashboard

Dashboard login

Click Go to Spaces, and you should see an Order Processing entry (or whatever you entered as the monitor model name in the CSV tool). Note that you'll see a different set of spaces than those listed in Figure 16. What matters is that the Order Processing entry has been added for your newly created monitor model.

Figure 16. Go to Spaces
Go to Spaces

Click to see larger image

Figure 16. Go to Spaces

Go to Spaces

Click the new entry. You'll see a dashboard with three tabs (one for each spreadsheet you used earlier), as shown in Figure 17.

Figure 17. Empty dashboard
Empty dashboard

Click to see larger image

Figure 17. Empty dashboard

Empty dashboard

Send events to the monitor model

At this point, we have a working dashboard, but no instance data is being shown. Return to the browser hosting the CSV tool and click 3. Send data to monitor model.

Figure 18. CSV tool - Events
CSV tool - Events

Click to see larger image

Figure 18. CSV tool - Events

CSV tool - Events

An event (XML message) was created and delivered to a JMS queue for each row in the spreadsheet, and your monitor model processed each event. Dismiss the dialog (which says how many events were sent), and return to the browser showing the new dashboard to see the results.

View the dashboards

We now see data in our dashboard, representing the metric values entered in the metrics spreadsheet, as shown in Figure 19.

Figure 19. Metric dashboard
Metric dashboard

Click to see larger image

Figure 19. Metric dashboard

Metric dashboard

We'll learn to sort and format what we see here in Part 2 of this series.

Next, click the Order Processing KPIs tab.

Figure 20. KPI dashboard
KPI dashboard

Click to see larger image

Figure 20. KPI dashboard

KPI dashboard

Hover over each KPI and you'll see that the number of orders is 7, the total of all orders is $2354.95, the average number of items per order is about 1.86, and the most expensive item ordered cost $999.99. There are currently no alerts. You'll learn how to configure alerts in Part 3.

Finally, click the Order Processing Reports tab to see the various Cognos BI reports. If this is the first Cognos BI report accessed since starting your server JVM, it may take a minute or so to load (subsequent reports will load much faster).

Figure 21. Report dashboard
Report dashboard

Click to see larger image

Figure 21. Report dashboard

Report dashboard

Conclusion

Congratulations! You now have a live, custom dashboard! Without any programming or use of Eclipse, you learned how to define the metrics, KPIs, and reports of interest to you by simply filling in spreadsheets in Microsoft Excel or a similar spreadsheet application.

Furthermore, without any need to use the WebSphere administrative console, you generated and deployed an application for your custom monitor model to your server.

Finally, without needing to write an event emitting application, you have fed sample data to your monitor model, and seen the dashboards update to reflect the results.

In Part 2, you'll learn how to customize the dashboards that were generated to better meet the needs of your business users. And in Part 3, you'll learn how to define alerts and see the results as you feed in further sample data, and how to customize the generated monitor model in Eclipse if desired.

Resources

Learn

Get products and technologies

  • Get the CSV tool used in this article. (If you don't already have a free account, sign up here first.)

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Business process management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business process management
ArticleID=954648
ArticleTitle=From spreadsheets to IBM Business Monitor dashboards in under an hour, Part 1: Using the free CSV tool to create a dashboard
publish-date=12042013