KB210396: How to use Data Import to prepare a dataset to plot United States Counties in an ESRI Map with areas in MicroStrategy Web

Community Admin

• Strategy

The Article details the process to correctly map county information to a geo attribute role through the data import feature of MicroStrategy Web.

Summary

Strategy Web 9.4.1 HF3 and above supports the option to plot United States County areas by using Visual Insight or Flash Report Services Documents. Counties cannot be uniquely identified by their name, therefore, several steps are needed to correctly identify and plot these areas in an ESRI Map.

Preparing the Data Source

Strategy Web 9.4.1 supports several Data Sources for Data Import. The minimum requisite is to have the following:

A column of type text that contains all the county names of the United States of America per ESRI name conventions. To verify what the ESRI name conventions refer to KB210826 How to determine the ESRI name conventions when plotting maps with areas in an ESRI Widget in MicroStrategy 9.4.1 HF6
A column of type text that contains the matching state name to the county column mentioned above.

An example of a table is shown below:

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Sample ProcedureUser ProcedureProvided out of the box.Created by users.Not editable.Can be edited.Can be used as a starting point when developing User Procedures.Can be based on Sample Procedures.BEFORE fixAFTER fix


select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"


select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

CountyStateLake of the WoodsMinnesotaCherokeeIowaFerryWashington

Selecting the appropiate georoles

When importing county and state information the following has to be performed:
a. Select the State georole for the State column as shown below:

b. Optional: Select the county georole for the County column. This step is not required since it is used only to automatically pick the Shape FIle. An example is shown below:

Save the cube and now the data is ready to be used in a Visual Insight Dashboard or Flash Document. Refer to KB210392 How to plot United States Counties in an ESRI Map with areas in a cloud configuration.
In 10.x
The County and state attribute get automatically recognized as geo attributes in the data import process.
KB210396

0 comments

Details

Knowledge Article

Published:

April 12, 2017

Last Updated:

November 23, 2018