{"id":33326,"date":"2017-08-18T11:32:43","date_gmt":"2017-08-18T10:32:43","guid":{"rendered":"https:\/\/help.instantatlas.com\/instantatlas-desktop\/?page_id=33326"},"modified":"2021-12-06T11:47:14","modified_gmt":"2021-12-06T11:47:14","slug":"access-data-manager-user-guide","status":"publish","type":"page","link":"https:\/\/help.instantatlas.com\/instantatlas-desktop\/en\/welcome\/support-articles\/access-data-manager-user-guide\/","title":{"rendered":"Access Data Manager User Guide"},"content":{"rendered":"

Introduction<\/h2>\n

This document describes how the InstantAtlas Access Data Manager is used to generate data files for InstantAtlas stand-alone dynamic reports.\u00a0 It is supplied in conjunction with an Access Data Manager that contains example data.\u00a0 The reader may find it useful to refer to the example data in the Access Data Manager.<\/p>\n

The Access Data Manager is aimed at users with basic Access skills.\u00a0 The advantage of the Access Data Manager over the Excel Data Manager is that you can store all the data in a relational database.\u00a0 You can then create different reports with sub selections of the stored data i.e. you can choose the base geography that you like and also decide on just specific themes and associate columns to include into your data file(s).<\/p>\n

Installation and Setup<\/h2>\n

The Access Data Manager is a Microsoft Access 2003 database.\u00a0 Users will require Microsoft Access in order to use the database and rights to open a database that contains executable code.<\/p>\n

You can download the InstantAtlas Access Data Manager here<\/a>. Please unpack the zip archive and ensure that the containing MS Access file is saved to a location accessible by the user.<\/p>\n

\"\"<\/a> Note that you may see a security warning below the Access ribbon after opening the Access file. You should click ‘Enable Content’ to be able to use the programming code contained in the Access Data Manager.<\/p>\n

\"\"<\/a><\/p>\n

Loading Data into the Access Data Manager <\/strong><\/h2>\n

Setting up the Geography <\/strong><\/h3>\n

Definitions of the different geographies in InstantAtlas are given below.<\/p>\n

Base geography:<\/strong> The set of geographic features that you wish to produce a thematic map for.\u00a0 For example, if you have crime statistics for post code sectors in Edinburgh and you wish to make a thematic map of these, the base geography for your InstantAtlas report will be Edinburgh post code sectors.<\/p>\n

Contextual geographies:<\/strong> These are optionally displayed in dynamic reports as overlays to the base geography to provide context for the map(s).\u00a0 They are not used for thematic mapping as this role is reserved for the base geography.\u00a0 If for example you publish a report with Edinburgh post code sectors as the base geography, you might include post code districts (a higher-level postal geography) as a contextual geography.<\/p>\n

Comparison geography:<\/strong> Any geography that you want to treat as a comparator in your dynamic reports.\u00a0 Comparator values are not shown in the map(s) of a dynamic report, but they are displayed in a separate comparison geographies table.<\/p>\n

Because both base geography and comparison geographies have data values attached to them, they must be entered into your Access Data Manager.\u00a0 They are defined using two tables:<\/p>\n

tblGeographyType<\/strong><\/p>\n

This table should hold an entry for each of the geographies you are holding indicator data for, whether they are intended for use as a base geography or comparison geography (or both).\u00a0 For each geography you should enter a \u2018GeographyTypeID\u2019, \u2018GeographyTypeName\u2019 and the type of the geography (either polygon, point or line).\u00a0 Each \u2018GeographyTypeID\u2019 must be unique.<\/p>\n

\"\"<\/a><\/p>\n

tblGeography <\/strong><\/p>\n

This table holds a list of all individual geographic features included in your base and comparison geographies.\u00a0 Every feature must have a \u2018GeographyID\u2019, a \u2018GeographyName\u2019 and \u2018GeographyTypeID\u2019 (i.e. the geography type to which the geographic feature belongs).\u00a0 The \u2018GeographyID\u2019 values must be unique.\u00a0 The \u2018GeographyTypeID\u2019 must be consistent with the \u2018GeographyTypeID\u2019 in \u2018tblGeographyType\u2019.\u00a0 The \u2018GeographyID\u2019 must be the same as that used in the digital map file (ESRI shapefile or MapInfo interchange file) used to supply the geographic features.\u00a0 Note that the \u2018GeographyID\u2019 will be treated as text.<\/p>\n

\"\"<\/a><\/p>\n

Optionally you can supply a \u2018GeographyLink\u2019 for each feature.\u00a0 This is the link to the file that should open when the Notes icon for a geographic feature is clicked in the data table of a dynamic report.\u00a0 Each link can be different and if you do not supply a link, the Notes icon will not appear in the data table of the dynamic report.\u00a0 In the example above, the Notes icon for each post code sector will link to a file called notes.htm located in the same folder as the dynamic report.\u00a0 For example, you could replace notes.htm by \u2018C:\\profile.pdf\u2019 or \u2018..\/..\/profile.pdf\u2019 or \u2018http:\/\/www.instantatlas.com\u2019 to link to a different resource.<\/p>\n

Setting up Filters <\/strong><\/h3>\n

If you wish to use filters in your InstantAtlas reports, you will need to create entries in the following two tables:<\/p>\n

tblFilter <\/strong><\/p>\n

This table simply lists the filters in the Access Data Manager. Supply a \u2018FilterID\u2019 and a \u2018FilterName\u2019 for each filter.\u00a0 The \u2018FilterID\u2019 values should be unique.<\/p>\n

\"\"<\/a><\/p>\n

tblFilterDetails <\/strong><\/p>\n

This table defines the filters in terms of the \u2018FilterID\u2019 (from \u2018tblFilter\u2019), a filter category or value, and the geographic features associated with the category.\u00a0 This is a list by feature (defined by \u2018GeographyTypeID\u2019 and \u2018GeographyID\u2019) with associated \u2018FilterID\u2019 and \u2018FilterCategory\u2019.<\/p>\n

\"\"<\/a><\/p>\n

Setting up Splits <\/strong><\/h3>\n

Split information in the Access Data Manager allows you to split the data creation process so that data files are created for multiple dynamic reports.\u00a0 For example, rather than having a dynamic report displaying all post code sectors in Edinburgh you might wish to publish a series of reports with each displaying the post code sectors in a different zone (e.g. East Edinburgh, West Edinburgh, etc).\u00a0 If you wish to split a geography, then you will need to create entries in the following two tables:<\/p>\n

tblSplit <\/strong><\/p>\n

This table simply lists any splits you wish to use.\u00a0 Supply a \u2018SplitID\u2019 and a \u2018SplitName\u2019.\u00a0 The \u2018SplitID\u2019 value should be unique.\u00a0 The split name is not displayed in dynamic reports.<\/p>\n

\"\"<\/a><\/p>\n

tblSplitDetails <\/strong><\/p>\n

This table defines how the geographic features in a geography are split.\u00a0 Splits are defined in terms of the \u2018SplitID\u2019 (from \u2018tblSplit\u2019), a split category or value, and the geographic features associated with the split category.\u00a0 This is a list by feature (defined by \u2018GeographyTypeID\u2019 and \u2018GeographyID\u2019) with associated \u2018SplitID\u2019 and \u2018SplitCategory\u2019.\u00a0 The \u2018SplitCategory\u2019 is used to name data files created by the Access Data Manager and therefore should not contain characters that cannot be used in a filename (e.g. a colon or a slash).<\/p>\n

\"\"<\/a><\/p>\n

Setting up the Data Structure (Themes \/ Indicators \/ Time Periods) <\/strong><\/h3>\n

The data in InstantAtlas reports must be organised using three levels: theme, indicator and instance\/date.\u00a0 There are three different tables in which you need to define the structure of your data:<\/p>\n

tblTheme <\/strong><\/p>\n

For each theme you should provide a \u2018ThemeID\u2019, \u2018ThemeName\u2019 and optionally a \u2018ThemeOrder\u2019.\u00a0 \u2018ThemeID\u2019 values must be unique. You can use \u2018ThemeOrder\u2019 to define the order in which themes will be listed in the Data drop down list of your InstantAtlas report.\u00a0 If you do not define any \u2018ThemeOrder\u2019 values, your themes will be listed alphabetically in your report.<\/p>\n

\"\"<\/a><\/p>\n

tblIndicator <\/strong><\/p>\n

For each indicator you should provide an \u2018IndicatorID\u2019, \u2018IndicatorName\u2019 and optionally an \u2018IndicatorOrder\u2019.\u00a0 You can use \u2018IndicatorOrder\u2019 to define the order in which indicators will be listed within the theme they belong to in the Data Explorer of your dynamic report.\u00a0 If you do not define any \u2018IndicatorOrder\u2019 values, your indicators will be listed alphabetically in your report.\u00a0 The theme to which the indicator relates must also be supplied \u2013 an indicator can only be related to a single theme.\u00a0 The indicator type must be supplied \u2013 either \u2018numeric\u2019 or \u2018categoric\u2019.<\/p>\n

\"\"<\/a><\/p>\n

If you wish to link to an external file containing supporting information for a particular indicator, the link to the file should be supplied in the \u2018IndicatorNotes\u2019 field.\u00a0 This link is used when the Notes icon in the Data Explorer of a dynamic report is clicked for a particular indicator.\u00a0 In the example below, the Notes icon for post code sectors will link to a file called notes.htm located in the same folder as the dynamic report.\u00a0 A different link can be provided for each indicator.\u00a0 For example, you could specify a link of \u2018C:\\profile.pdf\u2019 or \u2018..\/..\/profile.pdf\u2019 or \u2018http:\/\/www.instantatlas.com\u2019.<\/p>\n

For \u2018numeric\u2019 type indicators you can specify the number of decimal places that will be exported into the JSON files and the precision that you would like to see in the report for this indicator.\u00a0 These values can be different.\u00a0 If you do not specify a value in the DecimalPlaces column the JSON file(s) will contain all decimal places that you put into the tblData table.\u00a0 If these values are originally results of calculations, they might have many decimal places which will increase the size of your JSON file and can potentially affect the performance of your report.\u00a0 The precision setting simply defines the number of decimal places that are displayed in your report regardless of how many decimal places are exported into the JSON file.\u00a0 Both settings follow the rules of rounding.<\/p>\n

tblIndicatorDate<\/strong> (for indicators with time series data)<\/p>\n

For each indicator that has data for more than one time period you should enter every possible combination of \u2018IndicatorID\u2019 and \u2018Date\u2019, supplying a unique \u2018IndicatorDateID\u2019 value for each combination.\u00a0 Note that \u2018Date\u2019 is currently simply 50 text characters allowing you to enter dates as you see fit.\u00a0 You can optionally supply \u2018DateOrder\u2019 values to define the order in which time periods will be listed in the Data Explorer of your dynamic report.\u00a0 If you do not define any \u2018DateOrder\u2019 values, your time periods will be listed in ascending order in your report.\u00a0 Optionally you can also set the precision of the data values at a date level.\u00a0 These settings will overwrite the precision setting of the indicator. In a similar way you can specify a Notes link for an individual date in the ‘IndicatorDateNotes’ column. These settings will overwrite the ‘IndicatorNotes’ setting of the indicator.<\/p>\n

\"\"<\/a><\/p>\n

Loading the Data <\/strong><\/h3>\n

You can load the data into the Access Data Manager in all the usual ways (import, attach table, copy and paste from Excel, etc).\u00a0 It is assumed the user has experience of loading data into an Access database.<\/p>\n

tblData <\/strong><\/p>\n

This table stores all indicator data values and any associated values.\u00a0 The required fields are \u2018IndicatorID\u2019, \u2018GeographyID\u2019 and Date if you defined one or more in the tblIndicatorDate table.\u00a0 Together these three fields define an individual display record for your InstantAtlas report.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a> Note: You need to make sure that each Indicator with Dates is included in the tblIndicatorDate table and that the combinations that you define there, match with the data you put into the tblData. Data values are held in one or more of the following fields depending upon which associates you want to use in your report.<\/p>\n

\"\"<\/a><\/p>\n

The value field holds indicator values.\u00a0 Any other fields hold associate values, so called because they are associated to indicator values.\u00a0 Associate values are not used in thematic mapping in InstantAtlas reports but perform other roles.<\/strong>\u00a0 The inclusion of associate values is optional, but the default configuration of some templates assumes that you will be supplying certain associates.<\/p>\n\n\n\n\n\n\n\n\n
Field <\/strong><\/td>\nDescription <\/strong><\/td>\n<\/tr>\n
Value<\/td>\nThis field contains indicator values that will thematically mapped in a dynamic report.\u00a0 These are stored as text so that you can enter text values for categoric type indicators.<\/td>\n<\/tr>\n
Count<\/td>\nThis field is an example for an associate that you might want to include when you map rates.\u00a0 By default, you will not see these values in the report.\u00a0 You will need to include a Count column into the Data Table using the Designer.\u00a0 Please refer to the Designer section of the IA Desktop User Guide for information on how to do this.<\/p>\n

Note that if you are creating data files for loading into InstantAtlas Server you do not need to populate this field \u2013 you should be putting your counts into the numerator field instead.<\/td>\n<\/tr>\n

UpperLimit<\/td>\nThis field contains any upper confidence limits associated with your indicator values.\u00a0 You can calculate these limits using any method you wish and including limits is optional.\u00a0 If you do include limits, they will be displayed as error bars in the bar chart of a dynamic report.<\/td>\n<\/tr>\n
LowerLimit<\/td>\nThis field contains any lower confidence limits associated with your indicator values.\u00a0 You can calculate these limits using any method you wish and including limits is optional.\u00a0 If you do include limits, they will be displayed as error bars in the bar chart of a dynamic report.<\/td>\n<\/tr>\n
diff<\/td>\nThis associate is used by default to draw the horizontal bars in an Area Profile – Spine Chart<\/a> report.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Some templates or components may require other associates:<\/p>\n\n\n\n\n\n\n
Associate <\/strong><\/td>\nReason for including in your data JSON file(s) <\/strong><\/td>\n<\/tr>\n
largestObservation, upperQuartile, median, lowerQuartile, smallestObservation<\/td>\nThese associates need to be provided if you would like to use the box-and-whisker chart in your report.\u00a0 This chart can be included in the Single Map template using the Designer.<\/td>\n<\/tr>\n
number, significance, baseline, state, trend, target<\/td>\nThese associates perform various roles in the Area Profile template.\u00a0 For more information please refer to the Area Profile<\/a> section of the InstantAtlas Desktop User Guide.<\/td>\n<\/tr>\n
xValue, yValue, sizeValue, numerator, denominator<\/td>\nThese associates perform various roles in the Bubble Plot template.\u00a0 For more information please refer to the Scatter Plot<\/a> section of the InstantAtlas Desktop User Guide.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Please note that ‘numerator’ and ‘denominator’ are included in the Access Data Manager as associates. These cannot be deleted as they were required to export data for our legacy product InstantAtlas Server. You can either leave them empty or use them if you want to set up numerator and denominator associates (e.g. for the Bubble Plot template).<\/p>\n

Please refer to section \u2018Adding New Associates<\/span><\/a>\u2019 of this guide for information on how to include further associates into the Access Data Manager.<\/p>\n

tblAssociate <\/strong><\/p>\n

Associate columns by default get exported into the JSON file(s) as type categoric, regardless of what data type is defined for this column in the table tblData (which means the values will be sorted alphabetically and will appear left aligned in the data table).\u00a0 To change the type of an associate or to set the precision of associate values you need to define this in the table tblAssociate.<\/p>\n

\"\"<\/a><\/p>\n

tblIndicatorAssociate <\/strong><\/p>\n

If you need to define the data type and\/or precision for an associate where these settings need to be different depending on the selected indicator, you can use the table tblAssociate to enter these settings.<\/p>\n

Defining the Template for the JSON Export <\/strong><\/h3>\n

tblTemplate <\/strong><\/p>\n

The Access Data Manager enables you to create JSON files for dynamic reports by using only a subset of the data you store in the Access Database.\u00a0 To define which associates shall go into the JSON files you can set up templates in the table tblTemplate.<\/p>\n

\"\"<\/a><\/p>\n

By default, there are already two templates defined.\u00a0 The template called \u2018Single or Double Map\u2019 will include only the associates Count, UpperLimit and LowerLimit (which are combined to \u2018Limits\u2019 in the tblTemplates since they are not supposed to exist separate from each other).\u00a0 The template called \u2018Area Profile\u2019 has additionally the diff column included. If you are using the numerator and denominator associates you may want to tick the relevant boxes to include them in the template of your choice. The first column IAS can be ignored and left unticked – this setting was required when exporting data for our legacy product InstantAtlas Server.<\/p>\n

Adding New Associates <\/strong><\/h3>\n

You can add your own new associate values (text or numbers) if you wish.\u00a0 These associate values can be displayed in custom columns in the main data table of an InstantAtlas dynamic report.\u00a0 For example, you may have an indicator called \u2018Percentage of the population who are visually impaired\u2019.\u00a0 You might also have an average score in a vision test which you wish to associate to the indicator value.\u00a0 The score associate values could then be shown in the data table alongside the indicator values themselves.<\/p>\n

\"\"<\/a><\/p>\n

You can also use associates to populate the Advanced Pie Chart.<\/p>\n

To display new associate values in an InstantAtlas report, there are two distinct steps:<\/p>\n

    \n
  1. Creating a data file that contains the new associate values for one or more indicators<\/li>\n
  2. Configuring the dynamic report to implement the new associate values.<\/li>\n<\/ol>\n

    Step 2 is carried out using the InstantAtlas Designer and is therefore beyond the scope of this document.\u00a0 Please refer to the Designer section<\/a> of the InstantAtlas Desktop User Guide. Step 1, however, is carried out using your InstantAtlas Data Manager.<\/p>\n

    To add new associate values, you will need to create new table fields in the Access Data Manager and populate these with values.\u00a0 You need to complete the following steps:<\/p>\n

    Step 1: Update the structure of the \u2018tblData\u2019 table by adding the custom fields as required and fill them with data.<\/p>\n

    \"\"<\/a><\/p>\n

    If the new associate should not be categoric, include a new entry in the table tblAssociate to define type and precision.<\/p>\n

    Step 2: Update the \u2018Data\u2019 query to include the custom fields.<\/p>\n

    \"\"<\/a><\/p>\n

    Step 3: Update the structure of the \u2018tblTemplate\u2019 table by adding the same custom fields.<\/p>\n

    \"\"<\/a><\/p>\n

    Step 4: In the \u2018tblTemplate\u2019 table either tick the box of the new custom field in one or more of the existing template entries or create a new entry with the same default entries as the template you are enhancing and check the boxes for the new custom fields.<\/p>\n

    \"\"<\/a><\/p>\n

    Once you have completed these steps you may run the data file creation process<\/a> using your enhanced template.<\/p>\n

    As mentioned above, new associate values will not be displayed in InstantAtlas reports by default.<\/p>\n

    Adding Metadata <\/strong><\/h3>\n

    You can add properties to geographies, themes and indicators by populating the tblMetadata table.\u00a0 You will need to populate this table if you want to do the following:<\/p>\n

      \n
    1. Adding metadata that will be displayed in a text box<\/li>\n
    2. Setting custom legends<\/li>\n
    3. Setting the chart axis minimum and maximum<\/li>\n
    4. Create data files for a Performance Analysis, a Bubble Plot (Simple Configuration) or a Funnel Plot report. Please refer to the respective sections in the InstantAtlas Desktop User Guide<\/a> for the list of properties that must be supplied.<\/li>\n<\/ol>\n

      The tblMetadata table consists of 4 fields:<\/p>\n

      ElementType \u2013 this should be \u2018Theme\u2019, \u2018Indicator\u2019 or \u2018IndicatorDate\u2019<\/p>\n

      ElementID \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u2013 this is the respective \u00a0 \u2018ThemeID\u2019, \u2018IndicatorID\u2019 or \u2018IndicatorDateID\u2019 to which the metadata relates<\/p>\n

      PropertyName \u2013 the name of the property e.g. \u2018metatext\u2019, \u2018customColours\u2019, etc<\/p>\n

      PropertyValue \u2013 the value of the property e.g. \u2018This is an example text for Indicator 1\u2019<\/p>\n

      You may have as many properties as you like per theme, indicator or time period.<\/p>\n

      Adding Metatext <\/em><\/h4>\n

      You can define metadata that will be displayed in a text box for themes, indicators or time periods.\u00a0 The format for entering this is shown below.<\/p>\n

      \"\"<\/a><\/p>\n

      Column C must contain \u2018metatext\u2019.\u00a0 Column D should contain the text that you wish to appear in the report text box (or text boxes in the case of a report published using the Double Map template or Double Map Time Series template).\u00a0 The following image shows how this metatext would look in a text box in a report.\u00a0 Note that the text boxes are added to a report using the InstantAtlas Designer (usually they are in the report layout by default) and that their size and position is configurable.\u00a0 Note that HTML can be used to format text in this way.<\/p>\n

      \"\"<\/a><\/p>\n

      Setting Custom Legends <\/em><\/h4>\n

      It is also possible to set custom classifications for individual themes, indicators or time periods.\u00a0 You can also set custom colours and labels for the display of the custom classification within a dynamic report.\u00a0 Furthermore, you can define that a theme, indicator or time period appears with a specific legend classifier or legend palette by default.<\/p>\n

      The following image shows a screenshot of the tblMetadata table in an instance of the Access Data Manager.\u00a0 A custom classification is set up for the Indicator with ID 1 which is a numeric indicator and for the Indicator with ID 4 which contains categoric values.\u00a0 The default legend classifier for the Indicator with ID 2 is set to Equal Interval and the default legend palette for this indicator will be the one called \u2018Sequential Green Yellow\u2019 in the config.xml file.<\/p>\n

      \"\"<\/a><\/p>\n

      You can see from the screenshot that the custom classification for both indicators (ids 1 and 4) is made up of three separate custom properties: customColours; customBreaks and customLabels.<\/p>\n

      customColours <\/strong><\/p>\n

      Use this property to set the colours for displaying individual classes or to set a colour ramp.\u00a0 If the number of customColours matches the number of classes in the custom classification, then the colours will be displayed for each class in the same order in which you enter them in the worksheet.\u00a0 You can set just two colours and the dynamic report will create a colour ramp between them for as many classes as you have chosen.<\/p>\n

      customBreaks <\/strong><\/p>\n

      Use this property to set the breaks between different classes.\u00a0 You should set one more value than the number of classes that you require i.e. if you want 5 classes you need to enter 6 values for breaks.\u00a0 If you have categoric data, then you must use the exact text label used within the data.<\/p>\n

      Categoric breaks are case sensitive.<\/p>\n

      customLabels <\/strong><\/p>\n

      Use this property to set the labels for each class that is displayed in the legend.\u00a0 The labels are applied in the order in which you enter them in the worksheet.\u00a0 You need to make sure that the number of labels matches the number of classes you have set up.<\/p>\n

      customClassifier <\/strong><\/p>\n

      Use this property to set the default legend classifier for an indicator to one of the InstantAtlas standard classifiers.\u00a0 Valid values are \u2018equalInterval\u2019, \u2018quantile\u2019, \u2018natural\u2019, \u2018continuous\u2019, \u2018standardDeviation\u2019.<\/p>\n

      customPalette <\/strong><\/p>\n

      Use this property to set the default legend palette for an indicator to one of the legend palettes that are defined the config.xml file.\u00a0 Valid values are the names of the palettes in the configuration file.\u00a0 To find these, open the config.xml file of your report in the Designer and click in the menu bar on \u2018Style\u2019 and then \u2018Palettes\u2019.\u00a0 In the \u2018Palettes (numeric data)\u2019 tab you can find the palettes names for numeric indicators and the tab called \u2018Colour Schemes (textual data)\u2019 will show you the names of the categoric palettes.<\/p>\n

      Setting the Chart Axis Minimum and Maximum <\/em><\/h4>\n

      By default, the bar chart and time series chart adjust their minimum and maximum depending on the data the chart shows.\u00a0 This might not be suitable e.g. when using a time animation.\u00a0 To set the chart minimum and maximum to a fixed value for each theme or indicator you can use the metadata elements \u2018minChartValue\u2019 and \u2018maxChartValue\u2019.<\/p>\n

      \"\"<\/a><\/p>\n

      Creating the Data File(s) <\/strong><\/h2>\n

      Data files for a report are generated using the following queries:<\/p>\n