Skip to Content

Introduction

This is the follow-up to the blog Design Studio Innovation Series – Topic 6: Geo Maps Part I – Feature Review.  In that blog I covered the main features of the new Geo Map component introduced in Design Studio 1.5.  In this blog I’ll cover an approach for zooming and drilling down through multiple regions down to a street-level location and back.

Scenario

The example involves the display of US Census population data loaded into BW, on a map with drill-down from State to County to Place (City or Town).  To demonstrate the presentation of supporting information, there is also a chart with population estimates as well as a table listing the data.  The table can also be used as an alternative method of drilling down through the map.


The end result can be viewed in the following video:

Approach

In a nutshell, the approach used here relies on a combination of:

(a)  Applying the centerMap() method of the GeoMap component;

(b)  Applying filters to the assigned data source;

(c)  Toggling the visibility of map layers.

Steps Covered

  1. GeoJSON File Preparation
  2. Data Preparation
  3. BW Modeling
  4. BEx Query Design
  5. Application Design and Configuration

1.  GeoJSON Map File Preparation

(a) Base Files

To start with we need GeoJSON files representing US states and counties.  I found suitable ones at the GEOJSON AND KML DATA FOR THE UNITED STATES website:

GeoJSON_SourceFiles.png

To keep the file size to a minimum I downloaded the lowest resolution versions (20m) as highlighted above, named as follows:

gz_2010_us_040_00_20m.json – States

gz_2010_us_050_00_20m.json – Counties

(b) Merge Map Files

Initially I thought I would need to create a separate map layer for state and county.  However, after reviewing the mapping properties I noticed that conveniently, both files shared a common GeoID property (more on this later) as a key for uniquely mapping to each polygon representing the states and counties, so I thought what if we merged the files in order to use just one map layer?  I chose to combine the two files using the geojson.io tool because my requirement was simple and the tool allowed this to be done without any installation/setup.  Initially you’ll see the following home page:

geojsonIO.png

The first task is to simply open or drag and drop the States GeoJSON file onto the map as shown below:

ImportStates.png

We see a confirmation of the imported features corresponding to the states.  Each feature includes the properties GEO_ID, SATE, NAME, LSAD and CENSUS AREA.  Clicking the Table tab gives us a columnar view of the properties, making it easier to validate what we’ve imported.

ImportStates_Table.png

Now we do the same to add the Counties GeoJSON file to the States file we just imported:

ImportCounties.png

By reviewing the Table tab we can see that the counties (with GEO_ID’s of prefix 050) have been added after the states (with GEO_ID’s of prefix 040):

ImportCounties_Table.png

Now we simply save the combine file in GeoJSON format:

SaveGeoJSON.png

This results in a file of size roughly 4 MB.  I thought I’d try to reduce this a little with the mapshaper tool:

MapShaper.png

Again, just drag and drop the GeoJSON file onto the browser:

GeoJSON_original.png

I chose to simplify down to 60%:

GeoJSON_simplified.png

After exporting again in GeoJSON format the resulting file is around 3 MB, saving us 1MB which is reasonable for performance.

For further reading about preparation of GeoJSON files I’d recommend the following blogs:

DS15 – Create your own Map by Dirk Mayrock, which provides additional tips for using geojson.io  and

Creating Custom GeoJSON maps in Design Studio 1.5 by Mike Howles, which describes QGIS, a more sophisticated tool for creating custom map files.

2.  Data Preparation

The US Census population data is provided summarised at State, County and Place levels so this is what I used to load into BW, with appropriate transformations applied to generate GEO_ID values to match the corresponding ones in the GeoJSON map file.  The raw CSV file looks like this:

CensusPopulationCSV.png

This was supplemented with geolocation information for cities and towns.

3.  BW Modeling

The BW infocube and characteristics were modelled as follows:

InfoCube.png

Data.png

GeoID.png

GeoID_Data.png

4.  BEx Query Design

I was able to get away with just two queries:

(a)  US Mainland Population:

Query01.png

Important to note here is that I use a standard BEx user-exit variable to automatically calculate and filter the data for the latest year.  I have also restricted the states to the mainland states (excluding Alaska and offshore territories) to more effectively centre the maps on the screen.

(b)  US Population Estimates:

Query02.png

Note here I’ve used a standard BEx user-exit variable to calculate and filter the population estimates for the latest 5 years.  Again, I have also restricted the states to the mainland states (excluding Alaska and offshore territories).



5.  Application Design and Configuration


(a) Application Theme


The application applies the Blue Crystal theme as recommended by Karol Kalisz in Why Use Blue Crystal Style in Design Studio Apps?



(b) Global Script Variables

GlobalScriptVariables.png



(c) Data Sources, Components and Global Script Functions

Outline.png


(d) Data Source Initial Views

DS1.png

DS2.png

DS3.png


(e) Map Layers

MapLayers.png

Now just a point to note here, for layer US_COUNTY_PLACE_BUBBLE, I did explicitly specify the

Latitude, Longitude and Measure properties but for some reason after saving they no longer displayed but still seemed to be retained by the application.  Perhaps this is a bug.

(f) Scripts

Script for “On Select” event of GEO_MAPP

var geoIDselection = me.getSelectedMember(“ZGEOID”);

var geoIDselectionKey = geoIDselection.internalKey;

var geoIDselectionText = geoIDselection.text;

GEO_FUNCTIONS.processDrilldown(geoIDselectionKey, geoIDselectionText);

Script for “On Select” event of CROSSTAB_1

var geoIDselection = me.getSelectedMember(“ZGEOID”);

var geoIDselectionKey = geoIDselection.internalKey;

var geoIDselectionText = geoIDselection.text;

GEO_FUNCTIONS.processDrilldown(geoIDselectionKey, geoIDselectionText);

Script for Global Script Function GEO_FUNCTIONS.processDrilldown

var geoIDselectionKey = pGeoIDkey;

var geoIDselectionText = pGeoIDtext;

var geoType = GEO_FUNCTIONS.getGeoType(geoIDselectionKey);

if (currentLevel < levelCounty) {

  if (geoType==stateGeoType) {

  currentLevel = currentLevel + 1;

  currentState = GEO_FUNCTIONS.getStateFIPS(geoIDselectionKey);

  currentStateGeoID = geoIDselectionKey;

  currentStateText = geoIDselectionText;

  mapTitle = currentStateText;

  DS_1.setFilter(“ZSUMLEV”, countyLevel);

  DS_1.setFilter(“ZSTATEFIP”, currentState);

  DS_3.clearFilter(“ZSUMLEV”);

  DS_3.setFilter(“ZGEOID”, currentStateGeoID);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  FIORIAPPHEADER.setShowNavButton(true);

  } else if ( geoType == countyGeoType )

  {

  currentLevel = currentLevel + 1;

  currentCounty = GEO_FUNCTIONS.getCountyFIPS(geoIDselectionKey);

  currentCountyGeoID = geoIDselectionKey;

  currentCountyText = geoIDselectionText;

  mapTitle = currentCountyText + “, “ + mapTitle;

  DS_1.setFilter(“ZGEOID”,currentCountyGeoID);

  DS_3.setFilter(“ZGEOID”,currentCountyGeoID);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_2.setFilter(“ZCNTYFIP”, currentCounty );

  GEO_MAP.setLayerVisible(countyPlaceBubbleLayerID, true);

  CROSSTAB_1.setDataSource(DS_2);

  CROSSTAB_1.resetAllColumnWidths();

  }

} else if (currentLevel == levelCounty) {

  currentLevel = currentLevel + 1;

  currentPlaceGeoID = geoIDselectionKey;

  currentPlaceText = geoIDselectionText;

  mapTitle = currentPlaceText + “, “ + mapTitle;

  DS_2.setFilter(“ZGEOID”, currentPlaceGeoID);

  DS_3.setFilter(“ZGEOID”,currentPlaceGeoID);

  GEO_MAP.setLayerVisible(stateCountyMapLayerID, false);

  GEO_MAP.centerMap(countyPlaceBubbleLayerID);

}

GEO_FUNCTIONS.setMapTitle(mapTitle, year);

Script for “On Back” event of FIORIAPPHEADER

Here we navigate back or drill up.

if (currentLevel == levelPlace) {

  currentLevel = currentLevel – 1;

  currentPlace = “”;

  currentPlaceGeoID = “”;

  mapTitle = currentCountyText + “, “ + currentStateText;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  GEO_MAP.setLayerVisible(stateCountyMapLayerID,true);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_2.clearFilter(“ZGEOID”);

  DS_3.setFilter(“ZGEOID”, currentCountyGeoID);

} else if (currentLevel == levelCounty) {

  currentLevel = currentLevel – 1;

  currentCounty = “”;

  currentCountyGeoID = “”;

  mapTitle = currentStateText;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  GEO_MAP.setLayerVisible(countyPlaceBubbleLayerID,false);

  DS_1.clearFilter(“ZGEOID”);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  CROSSTAB_1.setDataSource(DS_1);

  DS_3.setFilter(“ZGEOID”, currentStateGeoID);

} else if (currentLevel == levelState) {

  FIORIAPPHEADER.setShowNavButton(false);

  currentLevel = currentLevel – 1;

  currentState = “”;

  currentStateGeoID = “”;

  mapTitle = defaultMapTitle;

  GEO_FUNCTIONS.setMapTitle(mapTitle, year);

  DS_1.clearFilter(“ZSTATEFIP”);

  DS_1.setFilter(“ZSUMLEV”,stateLevel);

  GEO_MAP.centerMap(stateCountyMapLayerID);

  DS_3.clearFilter(“ZGEOID”);

  DS_3.setFilter(“ZSUMLEV”, “040”);

}

Script for “On Item Select” event of FIORIAPPHEADER

Here we select between the GeoMap and Analytic Map (no basemap)

var mapType = me.getItemClicked();

if (mapType == “GeoMap”) {

GEO_MAP.setMapUrl(basemapURL);

}

else {GEO_MAP.setMapUrl(“”);}

Conclusion

I hope the example presented here has given you a good idea of the creative possibilities with the GeoMap component.  There’s quite a lot more I would have liked to explain based on my experiences preparing this post but that’s probably best left to a lessons learned follow-up.

Comments and questions are most welcome as usual.

Blog Series Index:  Design Studio Innovation Series – Welcome

To report this post you need to login first.

32 Comments

You must be Logged on to comment or reply to a post.

    1. Mustafa Bensan Post author

      Thanks David.  It was an interesting exercise to come up with an effective zooming approach that minimised the number of required map layers and geoJSON files.  I like the global script functions feature in Design Studio which certainly helps in building modular applications and avoiding code repetition. 

      (0) 
  1. Mustafa Bensan Post author

    Hi Jean,

    It seems your question post has been removed so I will answer by responding here.

    In my original prototyping with the Geo Map component I had experimented with manipulating the zoom parameters of the Base Map URL but this doesn’t work as desired because of the way the Geo Map component interacts with the URL and also can result in script errors.


    For your scenario, my suggestion is to create a map layer which uses a geoJSON file of Brazil (including the states), assign a dimension for from your data source that corresponds to states and filter on Rio de Janeiro and then apply the centreMap() method to the layer.

    You can refer to Lauren Geraedts’ comment in the Part I of this Geo Map blog: Design Studio Innovation Series – Topic 6: Geo Maps Part I – Feature Review for a suggestion about how to obtain a Brazil state map geoJSON file.

    Regards,

    Mustafa.

    (0) 
  2. arunnesh thanapalan

    Hi Mustafa,

    We are trying to have the Map View across various Regional Views with each region showing 2 KPI’s which we got it achieved through the different Layers in GeoMap.

    1. The different Regions has been achieved through our Bex Query Datasource View with the Basemap URL not specified. All the different regions is been visible.

    The issue we are facing is with the Centering the entire Map for different Regions.

    All of your above steps has been followed and we did had the Custom GEOJSON File with the different layers

    We did try the GEO_MAP.centerMap(); also but no luck.

    Can you please  help us of any other way to have the entire Map Centered to be displayed?

    I have attached the view of US where in it is not getting centered as well as the entire US is not getting displayed so I have to manually drag and drop it in the center of the screen to get the entire view of US.US View.PNG

    Thanks,

    Arunnesh

    (0) 
    1. Mustafa Bensan Post author

      Hi Arunnesh,

      The GEO_MAP.centreMap() method should work.  You should check your CUSTOM GEOJSON files and make sure they don’t include any distant territories off the mainland as this will affect the centring, as is the case with the US.

      Regards,

      Mustafa.

      (0) 
      1. arunnesh thanapalan

        Hi Mustafa,

        Thanks for your response.

        We are not using any Basemap URL so that it is by default pointing to the SAP Maps by default and no Custom geoJSON file is also been used.

        We are controlling the display of our maps for various regions by filtering at the datasource level.

        We did try the Centermap() at the On startup event specifying the different layers that is being used for the US region but still it did not work.

        Do you recommend us to create Custom GeoJSON file that could fix this Centering issue rather than having the Basemap URL not specified?

        Basemap URl.PNG

        (0) 
  3. Ian Godfrey

    Hi Mustafa,

    I am following along with your blog and all looks good until I get to the Global Scripts section.  The global script object GEO_FUNCTIONS includes 5 functions:

    getCountyFIPS

    getGeoType

    getStateFIPS

    processDrilldown

    setMapTitle

    I see the script for processDrilldown, but not for the other functions.  Perhaps the script is obvious so was not included, but I’m new to this area of BI so wanted to ask if you could also display the script for those functions.

    Also, the processDrilldown includes an “if” statement referring to the levels:

    if (currentLevel < levelCounty)

    can you let me know where the values for currentLevel and levelCountry come from.

    Thanks very much for any help you can provide,

    Ian

    (0) 
    1. Mustafa Bensan Post author

      Hi Ian,

      I’ll update the post with the code for the other Global Script Functions soon.

      levelCounty is a constant defined as a Global Script Variable (see section 5 (b) above) used to filter the county aggregation level with the ZSUMLEV attribute of the ZGEOID characteristic.  currentLevel is simply another Global Script Variable which acts as a counter/index for the current drill level, with valid values of: levelAll, levelState, levelCounty and levelPlace.

      Regards,

      Mustafa.

      (0) 
  4. RAHUL KHANNA

    Hi Mustafa

    I am using 2 Geo Json files 1 for states & other for Counties & have used the below Script for  drilldown functionality. But I am not able to zoom in when drilled down on the next layer. Can you please help what I am missing which had caused you to get the Zoom Functionality implementedSCRIPT.PNG

    Regards

    Rahul

    (0) 
    1. Mustafa Bensan Post author

      Hi Rahul,

      My initial suggestions and questions are as follows:

      1) Change

      DS_STATE.setFilterExt(“Country”, GEO_MAP_1.getSelectedMember(“Country”));

      to

      DS_STATE.setFilter(“Country”, GEO_MAP_1.getSelectedMember(“Country”).internalKey);


      2) Change


      DS_COUNTY.setFilter(“States”, GEO_MAP_1.getSelectedMember(“States”));


      to


      DS_COUNTY.setFilter(“States”, GEO_MAP_1.getSelectedMember(“States”).internalKey);


      3)  Which event is your script coded in?


      4)  In future, to make it easier to update script code, you should directly copy and paste the script text into your post instead of pasting a screenshot image.  This makes it easier to provide edit suggestions.


      5)  Can you provide a screenshot of the Additional Properties sheet of the Geo Map to show how the layers have been configured?



      Regards,


      Mustafa.


      (0) 
      1. RAHUL KHANNA

        Hi Mustafa
        Thanx for your  suggestions regarding the future posts  & efforts  have attached the screenshots of all the 3 layers

        1)  changed both the filters to internal key

        2)  this even is scripted in the ‘onclick’country.PNG event of Geomap

        (0) 
          1. Mustafa Bensan Post author

            Hi Rahul,

            Here are my additional comments and questions:

            1)  It looks like you have not specified a GeoJSON Mapping Property for the COUNTY layer.  Can you confirm?

            2)  Can you also provide screenshots if the Initial View for the three data sources DS_COUNTRY, DS_STATE and DS_COUNTY?

            Thanks,

            Mustafa.

            (0) 
            1. RAHUL KHANNA

              Hi Mustafa

              I have not used a Geo Json for the Country layer as on the country layer design studio is capable to detect the countries based on their names so haven’t used the Geo Json.

              Correct me if I am wrongcounty.PNGstate.PNG

              country.PNG

              Regards

              Rahul

              (0) 
              1. Mustafa Bensan Post author

                Hi Rahul,

                My comment about the GeoJSON was referring to the COUNTY layer and not the COUNTRY layer.  It looks like you haven’t assigned a GeoJSON Mapping Property for the COUNTY layer.

                (0) 
  5. karthik kumar

    Hello Mustafa,

    Hope you are doing good  !!!!

    BW7.4,DS1.6

    My map doesn’t plot Great Britain data as the country layer.

    Map is able to pick up every country data except great britain.

    Maps contain United Kingdom has country assigned and from master data we are able to get Great Britain as country name.

    Any insights on this .

    Regards

    Karthik

    (0) 
  6. David Desmonds

     

    Dear Mustafa,

    Thanks for your blog.
    Would you share the source code related to the global function ?

    GEO_FUNCTIONS

    • getCountryFIPS
    • getGeoType
    • getStateFIPS
    • processDrilldown
    • setMapTitle

    Thanks & Regards
    Elie

    (0) 
  7. Harish Konatham

    Hi Mustafa,

    Thanks for sharing your article!

    I have a question to ask; can we do Hierarchical  display of the maps? so, the end result would automatically shows the area and metric values depending on the hierarchy level.

    your demo shows it would be possible by creating layers, but will it be possible to achieve the same via BEx query ?

    Thanks,
    Harish

    (0) 
    1. Mustafa Bensan Post author

      Hi Harish,

      My demo is already based on a BEx Query but you need a flattened hierarchy for driIldown purposes.  It is not possible to use a BW hierarchy for map region drilldown because there is no way to link the nodes of the hierarchy to the corresponding GeoJSON Mapping Property in the custom GeoJSON file.  You need a query result which uses a flattened hierarchy as per my example above.

      In your example, the lowest level is Store (0PLANT).  You would need to add the appropriate geographic attributes for the desired region levels to the 0PLANT dimension.

      Regards,

      Mustafa.

      (0) 
  8. Rudra Sonar

    Hello Mustafa,

     

    I must say the best-described post I have seen recently….

    I was trying out whatever you said but got a very peculiar problem – can you please tell me what is wrong with my implementation and why all the map is colored here…

    My Output is like this  —  just want to show the regions on the map ,,, thats it ,,,

     

     

     

    I have data from report / data source is –

     

     

     

     

     

     

     

     

    (0) 
    1. Mustafa Bensan Post author

      Hi Rudra,

      I’m glad you found the post helpful.  Regarding your issue, are you sure that the Region dimension values in in your data source, eg. “HUG/01” etc, correspond exactly to the REGION property values in your custom GeoJSON file?

      Mustafa.

      (0) 
      1. Rudra Sonar

        Hello Mustafa,

         

        Thanks for looking into the issue –

        yes, the region is compounding char. in BW so the value is exactly is same.

        My geoJson file looks like this  —

        If you see the property value is exactly as it’s in DataSource. Does it have to do anything with Base Map URL?

         

        Also, would like to highlight that the regions are also getting Printed, these I saw just after I zoomed in till the “Hungary” … 

        1. I am not able to click on every region except 2 with Highest measures
        2. Also initial zoom till Country is possible here? 

        I have seen on your youtube video that u have initial zoom directly to the US states.. how did u achieve it? 

         

        My output on browser is like – 

         

         

         

        (0) 
        1. Mustafa Bensan Post author

          Hi Rudra,

          Okay, I see what you mean.  The values seem to be consistent.  You could try removing the Basemap URL just to rule that out, as the basemap isn’t needed to display the map.

          To set the initial zoom you can apply the CentreMap() method.

          By the way, what does your custom GeoJSON file look like when tested at http://geojson.io/

          Regards,

          Mustafa.

          (1) 
          1. Rudra Sonar

            Hello Mustafa,

             

            After a day break and 2 coffee mugs, I figured out there was a problem with my gEoJson file …

            Its looking great now…

             

            Thank you very much for the help 🙂

            (0) 

Leave a Reply