Section 3.4. Working with Your Business Data


3.4. Working with Your Business Data

Using the MapPoint 2004 APIs, you can import external data (i.e. your business data) and use it to create data maps. MapPoint APIs support variety of data sources for importing data. The data sources supported by MapPoint include, but are not limited to:

  • Text (.TXT, .CSV, .TAB)

  • Microsoft Excel spreadsheets

  • Microsoft Access database

  • Any relational database, such as SQL Server or Oracle Server

You can also import Outlook contacts. In this section, I will show how to import data from these data sources and display data maps using the imported data. Then I will show how to integrate your business data with demographic data. You will be able to display data maps rich with both business information and statistical data.

3.4.1. Importing External Data

Importing external data is generally simple using the MapPoint 2004 APIs. You use the DataSets.ImportData method to import external data. This method returns reference to a valid DataSet object upon successfully importing data . The returned DataSet object is also added to the current active map's DataSets collection so that you can use the dataset later; however, once the map is closed, the imported dataset is lost.

3.4.1.1. Understanding the DataSets.ImportData method

Like the DisplayDataMap method, the ImportData method is also exposed on the DataSets collection, mainly due to the fact that the imported data will be stored as a data set in MapPoint and made available from the DataSets collection for later use. The signature for the ImportData method is as follows:

     DataSets.ImportData(DataSourceMoniker, ArrayOfFields, Country, Delimiter, ImportFlags)

Let's discuss each argument in detail.

To import external data into a MapPoint 2004 DataSet, indicate the data source specifics, such as the path to the data source for a text file or an Outlook contacts file moniker, and pass this as the DataSourceMoniker argument. Even though this argument is an Object type, you can pass a string value. However, since there is no direct interface available in MapPoint 2004 to work with relational data bases such as SQL Server, you have to write code to connect to the SQL server and read the data to be imported into a DataSet (which we will see later in this chapter).

The next argument is the ArrayOfFields. This argument is used to import specific fields from the data source. This field is a two-dimensional array of objects. One dimension holds the data field names form the data source, and the other holds the field type specification. The field style specification is defined by using the GeoFieldType enumeration, which provides values such as geoFieldSkipped, geoFieldRegion1, geoFieldData, and so on. Each of these values is used to specify how the data field from the source should be treated in the MapPoint DataSet. The following code snippet shows how to define this argument and cite the field specifications. Say you have a text file that has the following tab-delimited fields:

     ID  State     Country   Our Sales($) Competitor A Sales($) Competitor B Sales($)     1   Alabama   US        531          2859                  2810     2   Arizona   US        4872         328                   193

You can specify the field specification:

     //Define field specification     object[,] fieldSpecifications = null;     fieldSpecifications = new object[6,2];     //Specify what fields are geographic and what fields are not     fieldSpecifications[0,0] = "ID";     fieldSpecifications[0,1] = MapPoint.GeoFieldType.geoFieldSkipped;     fieldSpecifications[1,0] = "State";     fieldSpecifications[1,1] = MapPoint.GeoFieldType.geoFieldRegion1;     fieldSpecifications[2,0] = "Country";     fieldSpecifications[2,1] = MapPoint.GeoFieldType.geoFieldCountry;     fieldSpecifications[3,0] = "Our Sales($)";     fieldSpecifications[3,1] = MapPoint.GeoFieldType.geoFieldData;     fieldSpecifications[4,0] = "Competitor A Sales($)";     fieldSpecifications[4,1] = MapPoint.GeoFieldType.geoFieldData;     fieldSpecifications[5,0] = "Competitor B Sales($)";     fieldSpecifications[5,1] = MapPoint.GeoFieldType.geoFieldData;

The next argument is the country. You have to specify the country enumeration to indicate the data for the country with which you are working. This argument's type is GeoCountry, an enumeration.

The other two arguments are the flags that aid the import process to understand how the data is delimited and data source-specific interfacing. I will discuss these flags later in this section.

Building on this introduction, let's see how to import data from different data sources.

3.4.1.2. Importing data from a text file

The DataSets.ImportData method can import data from text files with tab, semicolon, and comma delimitation. The following sample code shows how to import a tab-delimited text file with default field settings:

     //Using the MapPoint Sample Sales data from txt file     //This data file can be found on the companion CD in     //the Chapter 03 directory.     string filePath = @"D:\MapPoint\03\Sample Data\Sales.txt";     //Define field specification     //Since we want to import all fields, we can just     //go with default import     object missing = System.Reflection.Missing.Value;     //Import data and create a dataset     MapPoint.DataSet dataset =         map.DataSets.ImportData(filePath, missing,         MapPoint.GeoCountry.geoCountryUnitedStates,         MapPoint.GeoDelimiter.geoDelimiterTab,         MapPoint.GeoImportFlags.geoImportFirstRowIsHeadings);

When you run this code, each row from the text file is imported into a newly created DataSet, and a pushpin is placed on the map to indicate the location of the record. The new DataSet created with this method is also called a PushpinDataset. You can also call the DataSet.DisplayPushpinMap method to display the contents of a dataset. This method places a pushpin for each record in the corresponding location.

Also, as you might have noticed already, the Delimiter argument in this case is set to geoDelimiterTab, since the source data file has records with tab delimited columns; you can also use the geoDelimiterDefault value. However, in that case MapPoint determines the delimitation based on the filename. By design, MapPoint assumes that the delimitation is a tab for .txt, .asc, and .tab files, while delimitation is a comma for .csv files. Finally, The ImportFlags argument is of GeoImportFlags type, an enumeration whose value is set to GeoImportFlags.geoImportFirstRowIsHeadings to indicate that the first row is a header.

After importing the data, you can access the fields in this newly created pushpin dataset as you would access fields in the MapPoint demographic dataset. The following code shows how to access the fields from the imported dataset to display a data map. Say, for example, you have just imported a text file in the following format:

     ID  State     Country   Our Sales($) Competitor A Sales($) Competitor B Sales($)     1   Alabama   US        531          2859                  2810     2   Arizona   US        4872         328                   193

Now you can access the data and display a sized pie chart data map to compare your company sales with the other two competing companies. Pie chart maps are great for comparing data side-by-side:

     //Define data map columns     //These indexes come in the same order as     //the fields in the text file     object oursalesIndex = 4; //Fourth field from the text file     object compAsalesIndex = 5; //Fifth field from the text file     object compBsalesIndex = 6; //Sixth field from the text file     //Now get fields     object[] datafields = new object[3];     datafields[0] = dataset.Fields.get_Item(ref oursalesIndex);     datafields[1] = dataset.Fields.get_Item(ref compAsalesIndex);     datafields[2] = dataset.Fields.get_Item(ref compBsalesIndex);     //Now display datamap     MapPoint.DataMap datamap =         dataset.DisplayDataMap(MapPoint.GeoDataMapType.geoDataMapTypeSizedPie,                             datafields, MapPoint.GeoShowDataBy.geoShowByRegion1,                               MapPoint.GeoCombineDataBy.geoCombineByNone,                               MapPoint.GeoDataRangeType.geoRangeTypeDefault,                               MapPoint.GeoDataRangeOrder.geoRangeOrderDefault,                               6, 3, missing, missing, missing, missing, missing);     //Set the legend title     datamap.LegendTitle = "Sales by State";     //Set the legend pane     axMappointControl1.PaneState = MapPoint.GeoPaneState.geoPaneLegend;

The previous code displays a data map that compares your sales with your competitor company's sales, as shown in Figure 3-16.

Figure 3-16. Sized pie chart for sales comparison (Color Plate 6)


The map in Figure 3-16 contains a significant amount of information, and there are some immediate conclusions you can draw from it. I have listed a few of them:

  • Sales are strong on the West Coast; however, the sales magnitude is less in the West Coast compared to rest of the country.

  • Competitor A dominates the market on the East Coast, where the sales magnitude is also large.

  • The Midwest seems to be the battle ground among the three companies. They seem to have equal market share. Also, note that the Midwest has the biggest overall market size.

The list of conclusions that can be drawn can go on and on once you get into the details of each market segment, your share of the market segment, your competitor's share, and so on. To get there, though, you can easily import your business data and process that data using location to help you make intelligent and informed business decisions.

You may be wondering, "How does MapPoint understand how each record is linked to a location?" You are right that we haven't specified anything of that nature to tell MapPoint which column is a location column from the text file, neither in the import code nor in the data map display code. So how does it understand? It's actually pretty simple. MapPoint understands keywords like "state," "country," "Zip Code," etc. and maps them to the appropriate geographic region. In this example, MapPoint automatically associated each record with the state location.

So, what happens if you have location data in an obscurely named column, say, "LocX?" Can MapPoint interpret it as a location and map the record to that location? No. That's when you need to help MapPoint understand your data using the ArrayOfFields specification argument.

3.4.1.3. Helping MapPoint understand your text data

When no location column is clearly named or when you have 200 columns of which you want only 10 columns for your data map, you need to specify the field specifications to help MapPoint understand your data. Say, for example, you have a text file as a data source with the following data:

     ID  LocX     Land   Our Sales($) Competitor A Sales($) Competitor B Sales($)     1   Alabama   US        531          2859                  2810     2   Arizona   US        4872         328                   193

Imagine that the requirement is only to import the state field (named LocX) and your sales field (named "Our Sales ($)"), ignoring the rest of the fields. You have to define field specifications:

     //Define field specification     object[,] fieldSpecifications = null;     //Declare field specifications to indicate about     //each and every field - so we need a total of     //6 fields since we have 6 fields in the txt file     fieldSpecifications = new object[6,2];     //Specify the field name     fieldSpecifications[0,0] = "ID";     //Indicate MapPoint not to import it     fieldSpecifications[0,1] = MapPoint.GeoFieldType.geoFieldSkipped;     //Specify the field name     fieldSpecifications[1,0] = "LocX";     //Indicate MapPoint that this is a State field     fieldSpecifications[1,1] = MapPoint.GeoFieldType.geoFieldRegion1;     fieldSpecifications[2,0] = "Land";     fieldSpecifications[2,1] = MapPoint.GeoFieldType.geoFieldCountry;     //Specify the field name     fieldSpecifications[3,0] = "Our Sales($)";     //Indicate MapPoint that this is one of the required     //data fields so that MapPoint imports it     fieldSpecifications[3,1] = MapPoint.GeoFieldType.geoFieldData;     //Skip all other fields     . . .     //Now import the data and create a dataset     MapPoint.DataSet dataset =         map.DataSets.ImportData(filePath, fieldSpecifications,         MapPoint.GeoCountry.geoCountryUnitedStates,         MapPoint.GeoDelimiter.geoDelimiterTab,         MapPoint.GeoImportFlags.geoImportFirstRowIsHeadings);

The GeoFieldType enumeration value is used to inform MapPoint about any given field from the data source.

Once you import your data into a dataset, using either a default import or a customized import using the custom field specifications, you can integrate your data with the demographic data and display data map using both datasets.

For full list of GeoFieldType enumeration values, see the GeoFieldType values topic from the help documentation: mk:@MSITStore:C:\Program%20Files\Microsoft%20MapPoint\MapPoint.chm::/BIZOMVGeoFieldType.htm.


3.4.1.4. Displaying Data Maps using your data and demographic data

MapPoint 2004 allows you to draw more than one data map on any given ActiveMap instance meaning that you can display an Annual Average Spending data map using the demographic data set, and then display a data map of your sales on top of the existing data map. The resulting data map contains data from both datasets and gives you an idea of how to make use of demographic data in conjunction with your business data to make intelligent business decisions.

Now that you know how to import data from a text source and how to customize the import using field specifications, let's look at importing data from other data sources.

3.4.1.5. Importing Data from Microsoft Excel

Importing data from a Microsoft Excel file is no different from importing data from a text file. To use an Excel data source with the MapPoint 2004 API, you need to know how to define an Excel data source moniker. Say you have an Excel file with sales data in the C:\MapPoint\Data directory. Also, assume that the actual sales data is in Sheet2 of the Excel book. The data source moniker would be:

     string filePath = @"C:\MapPoint\Data\Sales.xls!sheet2";

Once you have the proper data source moniker ready, importing the data is easy. It is similar to what you have seen in case of a text file:

     //Define the missing field     object missing = System.Reflection.Missing.Value;     //Import data and create a dataset     MapPoint.DataSet dataset =         map.DataSets.ImportData(filePath, missing,         MapPoint.GeoCountry.geoCountryUnitedStates,         MapPoint.GeoDelimiter.geoDelimiterDefault,         MapPoint.GeoImportFlags.geoImportExcelSheet);

However, if you are importing data from an Excel source, and you want to limit your data to a particular row and column range, the moniker and the data import flag will be different. Table 3-7 shows different data import options, corresponding monikers, and the appropriate data import flags.

Table 3-7. Excel data source monikers and import flag options

Import type

Moniker example

Import flag value

Excel File

C:\MapPointData\Sales.xls

geoImportExcelSheet

Excel Sheet

C:\MapPointData\Sales.xls!Sheet1

geoImportExcelSheet

A1 - Range

C:\MapPointData\Sales.xls! Sheet1!A1:D10

geoImportExcelA1

R1C1 - Range

C:\MapPointData\Sales.xls! Sheet1!R1C1:R10C3

geoImportExcelR1C1

Excel Named Range

C:\MapPointData\Sales.xls!myNamedRange

geoImportExcelNamedRange


The A1 and R1C1 ranges are used to select a particular set of rows and columns from the source Excel sheet.

3.4.1.6. Importing data from Microsoft Access

Support for importing data from a Microsoft Access database is built into MapPoint 2004 APIs. An import from an Access database differs from any other data source imports that we've seen so far only in terms of defining the data moniker and specifying an appropriate import flag.

For example, if you have a sample sales Access database in the C:\MapPointData\Sales.mdb directory, and if it has a table called SalesTable that contains sales information based on location, you can define a moniker as follows:

     string filePath = @"C:\MapPointData\Sales.mdb!SalesTable";

Once you have the right moniker, it is pretty simple to import the data:

     //Define missing argument for optional argument     object missing = System.Reflection.Missing.Value;     //Import data and create a dataset     MapPoint.DataSet dataset =             map.DataSets.ImportData(filePath, missing,             MapPoint.GeoCountry.geoCountryUnitedStates,             MapPoint.GeoDelimiter.geoDelimiterDefault,             MapPoint.GeoImportFlags.geoImportAccessTable);

Microsoft Access also supports data source monikers in the query form. If you have a query, namely SalesQuery in your Access database, you can import data from it using the following moniker and import flag arguments:

        string filePath = @"C:\MapPointData\Sales.mdb!salesQuery";     //Define missing argument for optional argument     object missing = System.Reflection.Missing.Value;     //Import data and create a dataset     MapPoint.DataSet dataset =             map.DataSets.ImportData(filePath, missing,             MapPoint.GeoCountry.geoCountryUnitedStates,             MapPoint.GeoDelimiter.geoDelimiterDefault,             MapPoint.GeoImportFlags.geoImportAccessQuery);

Next, let's look at how to import data from a SQL database.

3.4.1.7. Importing data from SQL Server 2000

Unlike the data imports that we have seen so far, MapPoint 2004 does not have any built-in support for importing data from relational databases such as SQL 2000. Using the .NET Framework's ADO.NET base classes , however, you can easily import data. So, in this section, I will show how to import data from SQL into MapPoint. In the following example, I import customer order information from the Orders table in the standard NorthWind database.

Since there is no ImportData method that both supports a SQL data source and creates a resulting DataSet, you need to create a data set yourself in which to import data. The first step is to create a new DataSet instance in the DataSets collection:

     //See whether there is an existing datasetwith the same name     object datasetName = "NorthWind Orders";     if(map.DataSets.Count > 0 && map.DataSets.get_Item(ref datasetName) != null)         //If so, delete it         map.DataSets.get_Item(ref datasetName).Delete( );     //Now create a new dataset     MapPoint.DataSet dataset = map.DataSets.AddPushpinSet("NorthWind Orders");

I check for an existing DataSet with the name NorthWind Orders and delete it if it already exists. We need to perform this check since you cannot create a dataset with the same name twice in one map session; MapPoint throws an exception when you create a duplicate.

Once you have the DataSet ready to import the data, connect to the SQL Server using the ADO.NET SqlConnection class:

     //Define a connection string     string sqlConnectionString =        "server=localhost;database=northwind;User Id=sqluser;Password=password";     //Define a connection object     System.Data.SqlClient.SqlConnection connection         = new System.Data.SqlClient.SqlConnection(sqlConnectionString);

You can also configure your App.Config file to read the connection from SQL Server; in either case, make sure to keep your SQL Server user ID and password safe and secure.


Now that your application is ready to connect to SQL Server, the next step is to create a SQL query using the ADO.NET SqlCommand class to get the purchase order records from the orders database:

     //Define a SQL query     string sqlQueryString = "SELECT CustomerID as ContactName, ShipName as CompanyName, ShipAddress as Address, ShipCity as City, ShipRegion As Region, ShipPostalCode as PostalCode, ShipCountry as Country FROM Orders WHERE ShipCountry = 'USA'";     //Define a command object     System.Data.SqlClient.SqlCommand command =             new System.Data.SqlClient.SqlCommand(sqlQueryString, connection);

Since we have the SQL command instance ready, we need to execute the command, read through the records, and import each one of them into the DataSet instance that we have created earlier.

     try        {           //Open the connection           connection.Open( );           //Get a sql reader from the query           System.Data.SqlClient.SqlDataReader sqlReader =                     command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);           if(sqlReader != null)            {               while(sqlReader.Read( ))                {                   //Get the name of the customer                   string customername  = sqlReader["ContactName"] as String;                   //Get the company name                   string companyname  = sqlReader["CompanyName"] as String;                   //Get the address string                   string address = sqlReader["Address"] + ", " + sqlReader["City"] + ",                                  " + sqlReader["Region"] + ", " +                                   sqlReader["PostalCode"] + " " + sqlReader["Country"];                  //Find location                  MapPoint.Location location = null;                  try                    {                       //Find the address                       MapPoint.FindResults findrs = map.FindResults(address);                       //If no results found, skip to next record                       if(findrs == null || findrs.Count <= 0)                           throw new Exception(address);                       //Get the location                       location = findrs.get_Item(ref index) as MapPoint.Location;                       //Create a pushpin                       if(location != null)                        {                          MapPoint.Pushpin pushpin =                               map.AddPushpin(location, companyname);                          //Assign the contact name                          pushpin.Note = "Contact : " + customername;                             //Move to the pushpin dataset                          pushpin.MoveTo(dataset);                        }                    }               catch                    {                       //Do some logging                    }             }             //Close the reader; this will automatically close the connection             //due to the command behavior setting during the ExecuteReader method             sqlReader.Close( );           }         }     catch         {           //Do clean up         }

This code loops through the records from the SqlDataReader instance, finding the corresponding location for each ship address in the orders table, adding a pushpin to the map for that location, and, most importantly, at the end, moving that pushpin to the dataset created previously using the Pushpin.MoveTo method.

Upon executing this code, we now have a pushpin DataSet that can be treated like any imported DataSet that we have seen so far in this chapter.

Finally, to use the MapPoint 2004 Import Data Wizard, call the DataSets.ShowImportWizard method.

You can also import data from a SQL database using a simple UDL file.


So far, we have used MapPoint DataSets to display data maps. There are more powerful business intelligence applications you can build with MapPoint 2004.

A MapPoint DataSet is very versatileyou can build business applications to assist in making intelligent business decisions (similar to the one you have seen at the beginning of this section with pie charts), but now let's take a simple scenario that does not include a data map but still relates to business intelligence and decision making. The DataSet that I will use for this example is the NorthWind Orders dataset that we just created.

Let's tackle a simple form of a supply-chain optimization problem: your company has warehouses on the West Coast where all of the West Coast shipments come from. Considering that the U.S. West Coast is very long, your CEO wants to know whether there is a way to cut transportation costs and boost your company's bottom-line. One simple way to answer his question is by investigating all shipped orders and finding out how many orders are transported from any given warehouse for more than, say, 100 miles. Next, investigate whether there is a possibility of opening another warehouse in a place closer to the customers in that area. Then you could cut down the transportation for the shipments. Can MapPoint 2004 do this? Using the DataSet querying capabilities, absolutely! Let's see how.




Programming MapPoint in  .NET
Programming MapPoint in .NET
ISBN: 0596009062
EAN: 2147483647
Year: 2005
Pages: 136
Authors: Chandu Thota

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net