BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools, Coding & Data

3 Killer Tips for Coding INFOMAPS for Your BI Dashboard

Submitted by on 2013-09-08 – 7:47 PM

In  Using the INFOMAPS Procedure Part 1 post, I showed you how to code a simple SAS information map with a filter for use with BI Dashboard. In this post, I’ll show you some additional tricks for using PROC INFOMAPS that are intended to work with a dashboards. 

Tip #1: Careful when Using An Information Map with BI Dashboard

When you use an information map as the source for the dashboard, it is sensitive to any changes that are made.  When you have code – you probably just rerun the code to recreate the map instead of opening Info Map Studio to make the change.  If you make a change to a data item, most likely, you’ll see a query error for the indicator data similar to the following figure:

sas bi dashboard query error for information map indicator

You will have to re-add the information map that has the changes.  The easiest trick I discovered was just selecting a different data source (such as data table) and re-selecting the information map choice.  Then I was able to load the information map with the changes.   

Obviously you cannot plan everything but just be aware of this limitation and ensure the information map is extremely close to what you want.  

Tip #2: Updating a Data Item Format for the Dashboard

In the earlier example, we created an information by adding all of the data items at once. However what I noticed is  when you use information maps as a source, it uses the format associated with the information map.  So make sure you set the format you want before you create the indicator.  It just makes it easier.

Things that Went Wrong

Here’s an example.  For the dashboard I had two indicators at the top that used the same information map, which I called General Statistics.  This figure shows the planning on the top and the actual result on the bottom.  Notice that the indicators are very similar, the only difference being that one table uses a comma format (Value) and the other table uses a percent format (Value (%)).

sas dashboard planning

Creating a New Variable

The first time I created the data table for the General Statistics information map – I only had 4 columns: Timestamp, Metric, Value, and Range.  My plan was simply to change the format in the dashboard indicator so that one indicator used comma and the other used a percentage. However, I didn’t realize the dashboard would not let me change the format in the table.  

The indicator wants to use the format from the information map. So, I had to add a new column with the values as percentage, shown in the following figure.  This table then had 5 columns: timestamp, metrics, value, value_pct, and range.  

sas information map raw data

Updating the Information Map Code

I updated my information map code so it would have the format I wanted.  As you may recall in my original code, I use the _ALL_ option on the INSERT DATASOURCE statement to have all of the data items in the dataset used in the information map.

Since I want to change the default, I used the UPDATE DATAITEM statement to set the format.  Since these are numbers, they are classified as measures by default – but I added the CLASSIFICATION option for demonstration purposes.

To update the item, you have to use the name from the information map, not the data table.  So in this instance, note that VAL_PCT is the variable name in the table but the information map called it VAL PCT (no underscore).  

 

/*Create the Information Map */
proc infomaps ;
new infomap "Dashboard All Metrics"
mappath="/Shared Data/Maps"
auto_replace=yes;
/* Add all variables to the info map */
insert datasource sasserver="SASApp"
table="Blog"."DB_MAIN_STATS" _all_
id="T1";
/*Update Measures to desired format */
UPDATE DATAITEM
"Val Pct" /*Use data item as it appears in info map not dataset*/

classification=measure
format="percentn6.2";
UPDATE DATAITEM 
"Value"

classification=measure
format="comma6.";
/*OPTIONAL: Insert other code here*/

/*Saves to the location in mappath*/

save;
run;

 

Now when I load my information map, you can see the values are properly formatted with comma or percentages.

sas bi dashboard result

Here’s another tip – if you use SAS Enterprise Guide 5.1+ it will help you with the PROC INFOMAPS code.  When you start typing, it will suggest some of the items you want.  If you don’t have SAS Enterprise Guide 5.1, then just check the Infomaps procedure user documentation.

Tip #3: Insert a Multi-Item Filter with Ease

I used two tables for the General Statistics because I knew the formatting (comma vs percentage) would be a display issue.  I added two filters to the map to ensure I could have the variables with the comma in one table and the variables without in the second table.

The following code was inserted before the SAVE statement in the above code.  To ensure I was able to get two values at once I had to use an In operator with the values in quotes.  This was not obvious from the examples in the documentation.  So I just wanted to show it here.

 

/* Create a filter for the each metric. */
insert filter
name="Filter: No Percent"
condition="<<T1.Metric>> in ('Visitors','Actions') ";
insert filter
name="Filter: With Percent"
condition="<<T1.Metric>> in ('Conversions','Bounce Rate') ";

I had to create two different indicators, once that allowed had the values with the commas (Visitors and Actions) and the other for the percentages (Conversions and Bounce Rate).  Here's the result.

sas bi dashboard result

Other Tricks

The SAS user documentation shows you how to use the PROC INFOMAPS to join tables, aggregate values, and other tricks that you might want to use for your information map. 

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , ,