Transcription
BI OfficeMapping Framework & Installation GuideVersion 6Revised: July 4, 2016Copyright Pyramid Analytics 2010-2016
BI OfficeContentsI.II.About This Document . 3Overview of the Two Mapping Techniques . 3i.ii.Technique One (PAID Lookup) . 3Technique Two (Text Lookup) . 3III. Mapping Prerequisites . 3i.ii.iii.Space for GIS Database . 3Location Name Preparation. 3Licensing . 3IV. Installation & Updates . 4A.Updating Existing Databases . 4B.New Database Installations . 4i.ii.Connecting the Pyramid GIS Database to the Client . 6Field Details . 6V. Anatomy of the Pyramid Geospatial Database . 7VI. GIS Admin & Upload Mechanism . 8C.GIS Admin . 8D.Upload Mechanism . 9i.ii.Adding Shapefiles . 9Adding Addresses . 10VII. Technique One (PAID Lookup).11E.Overview . 11F.Mapping Diagram . 12G.Geospatial Mapping to Client Data . 13i.ii.H.Relational Database Integration . 13Cube Dimension Integration. 15Using the Geospatial Mapping in the Client . 18VIII. Technique Two (Text Lookup) .20I.Overview . 20J.Integrating the Two Mapping Techniques . 20i.ii.K.Setting Geospatial Attributes Settings . 20Custom Shapes Mapping . 21Data Model Setup . 22IX. Appendix .24L.Proxy Server Configurations . 24M.Database Changes in Version 6.0. 252Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeI.About This DocumentThis document covers general setup and installation of the BI Office geospatial database, and configuration in the adminconsole. The document also explains how to implement and use the two mapping techniques.The term PAID stands for Pyramid Analytics ID.II.Overview of the Two Mapping TechniquesBI Office 6 offers two distinct techniques for portraying map data.i.Technique One (PAID Lookup)Technique One provides rich functionality and fine control of mapping entities. When using Technique One, the lookup isdone via an index key to match between the text labels and the shapes. For example, the Australia.key would be matchedagainst the Pyramid GIS shape for the same key. When necessary, custom shapes and lookup tables can be added via theAdministrative Console. For all versions prior to BI Office V6, this was the only technique available.ii.Technique Two (Text Lookup)Technique Two has been introduced as of BI Office V6. When using Technique Two, the lookup is done via text labels (forexample “Australia”) that the BI Office engine uses to find the exact matching shape or data point and plot it onappropriately the map.III.Mapping PrerequisitesClients wishing to deploy the Pyramid Geospatial solution will need to consider the following considerations:i.Space for GIS DatabaseThe Pyramid GIS database is currently 3GB in size. Please take into consideration that the client requires space for eachshape to upload. A large amount of space could be needed, depending on the nature and size of the shape.ii.Location Name PreparationClient geographic data should be prepared for GIS geo-referencing. Where possible, Standard English naming conventions should be used for all geographic entities. ISO-standardabbreviations can be used for countries and states.Addresses provided should be constructed in a format that will allow the geocoding engines to successfully placethe location on a map. The general format for a complete address is a series of comma separated parameters:Street Number, Street Name, City, State/Province, Country, Postal Codeiii.LicensingThe Pyramid Mapping solution currently uses the Microsoft Bing service for image tiles and geocoding services.Depending on your type of BI Office licensing, some clients may be required to provide their own Bing account and licensekey in order to enable and activate the mapping component. For licensing go to px3Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeIV.Installation & UpdatesA. Updating Existing DatabasesIf your company has been using the BI Office mapping database in versions previous to 6, then all you need do is run thescript GisUpdate6.0.sql (downloadable from the portal).Open the script in SQL Server Management Studio and execute it against your existing BI Office database (“PyramidGis”).See the Appendix for details on the changes made.Figure 1: Update Existing DatabaseB. New Database Installations1)2)3)4)Download the Pyramid Geospatial database from the customer portal.Unzip the contents and copy “PyramidGis.Bak” to your local drive.Connect to the appropriate instance of the Microsoft SQL Server Database Engine.Right-click the database, point to Tasks, point to Restore, and then click Database, which opens the RestoreDatabase dialog box.Figure 2: Restore Database4Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Office5)6)In the Restore Database dialog, choose the Device option and select the PyramidGis.bak file.Press the OK button and wait for a minute for the process to finish.Figure 3: Restore Database Dialog7)SQL Server has now restored the database and you can now go to Pyramid Admin to point the PyramidApplication to the Pyramid GIS database.5Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Officei.Connecting the Pyramid GIS Database to the ClientOnce restored, enable Geospatial capabilities in BI Office:1)2)Inside the admin console, go to Settings tab and select the Enable Geo Spatial optionComplete the details as shown in the sample below.Figure 4: Enable Geo Spatialii. Field DetailsGIS Key: Copy/Paste the Bing key supplied to you by Pyramid.GIS Server: The server to which you have restored the DB.GIS Database: The name of the Pyramid GIS database.GIS User & Password: Your server credentials.Geocoding Limits: The Max amounts of Geocoding to perform per request.NOTE: Once you are finished, click refresh to see the list of tables currently in your database as shown below.Figure 5: GIS Layers6Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeV. Anatomy of the Pyramid Geospatial DatabaseBy default, the Pyramid GIS database is installed under the name “PyramidGis”. The database contains several lookuptables (contained in the standard dbo schema) and a separate shapes table (contained in the separate shapes schema).The current version of the Pyramid GIS database includes the following lookup tables: World ContinentsWorld CountriesWorld States/Provinces1World CitiesUS CountiesUS Zip CodesEach table has a variety of name fields which can be used by developers in the following matching process.Client developers use the lookup tables in the Pyramid GIS database to match up against their own geographic fields intheir databases, in order to map the shape geospatial lookup keys. These keys are GUID values in fields called PAID (shortfor Pyramid Analytics ID). These fields are used by the analytics engine to find the matching shapes in the geospatialshapes table.Often, geographic data is presented in hierarchies from country to state to county to city to zip/post code. As such, it isexpected that a given geographic table will contain multiple PAID keys that are used in the cube’s hierarchies.Where no matching shape file is available or appropriate, developers can also provide an “address” that will be geocodedby the Pyramid application as needed. When supplying an address, developers should try and provide the most completeaddress possible in the member’s address properties to ensure that geocoding is successful and accurate.These processes are described in more detail below.1States and Provinces as defined as “Level 1” administrative detail level by the UN.7Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeVI.GIS Admin & Upload MechanismC. GIS AdminThe GIS upload mechanism comes to replace the old standalone desktop tool.Use the uploader to load shape files or to load addresses that will be Geocoded and inserted into Pyramid GIS as pointgeometry (the benefit of doing this is that each time an address will be requested, no geocoding will be done).In order to facilitate the possibility to work with Tabular models we have added functionality to designate relationshipsbetween columns in the DB.Example: If we take the states table (‘WORLDstates’ in the Pyramid GIS database) and we look at the data contained in it,then it’s possible to associate the relationship based on the Name & Country columns. (Figure 6)Figure 6: Edit GIS LayerIn Figure 6 you can see that the first look up columns are Name & Varname 1. So each state name will be searched uponthese two columns. Also you may notice that we designated two First Relation columns (and secondary as well). As aresult, if your grid results contain the State “Montana” on the rows or the columns and the parent of Montana is theUnited States then the map will show Montana in the United States. If you omit the First Relationship and deselect theassociated checkboxes, then the query might return a different result since “Montana” is also a state which belongs tothe Country of Bulgaria.Figure 7 shows the data in the States table when the user selects a state called Montana and emphasizes the need for theFirst Layer relationship.Figure 7: Montana8Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeD. Upload Mechanismi.Adding ShapefilesThe Geo Spatial Admin section also encapsulates within the possibility to load shapefiles (WGS 84) & Addresses to thePyramid GIS. This capability is also available to end users in the main BI Office client mapping tab in Data Discovery.Working through an example will explain this feature best.If we want to upload the counties of Australia, we would first prepare our shapefile .ZIP file.Figure 8: Adding ShapefilesNote that a minimum of three files are required for the shape file uploader to succeed: shapefile.dbfshapefile.shpshapefile.shx.These three individual files must be contained within a single zip file. When adding them to the zip file, they must beadded as individual files (and NOT as a folder containing three files).Fill the required information for the layer name (logical name that will appear in the pyramid client), the description andthe target table name. Then press Next.Figure 9: Add GIS Layer (part 1)9Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeFigure 10: Add GIS Layer (part 2)Pressing the Process button will cause the shapefile to be uploaded and processed. (This process may take up to severalminutes).If for example, the user created a Data Model source from the table created, the user can then designate the countyname as a Geo Hierarchy (Figure 10), and choose a subset of the data that will result in the mapping to show thefollowing shapes.Figure 11: Australian Countiesii.Adding AddressesThe loader also supports the load up of addresses to the Pyramid GIS database through the use of a text file. Theseaddresses would be Geocoded through the Bing API and would be stored in the Pyramid GIS database as LatitudeLongitude points. Please note that the text file must contain the address column and that the file must be tab separated.10Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeVII. Technique One (PAID Lookup)E. OverviewWhen using the Technique One, the broad steps for deploying the Pyramid mapping component are as follows (see Figure12 below):1.2.3.4.5.2The client installs the Pyramid SQL Server 2008 geospatial database (“PyramidGis”). The database currentlyprovides the geographic reference points for world countries, states 2 and cities as well as US counties and US zipcodes.The client maps the geographic reference points in the Pyramid GIS database to their own dimensional data intheir own relational databases (where possible). Mapping of such data is completed through the use of variousISO standard naming keys or the plain English names of the entities.The client adds the new geographic reference keys into their SSAS cube designs. Keys, created in Step 2 above, areadded as attributes that will act as member properties for existing geographic items in their current dimension(s).Newly added attributes are flagged as geospatial attribute properties using the TYPE setting.Addresses and other geographic entities that are NOT reflected in the Pyramid GIS database are designated as“location-address” attribute types by the client. This will ensure that the Pyramid engine attempts to geocodesome data points and plot them on a map where possible.End users can now analyze their data geospatially in the Pyramid application. End users are able to browse thecube and slice & and dice their data as normal in the Pyramid Application. If the user queries a geospatiallyflagged hierarchy, the mapping component and map ribbon tab are enabled and data can be plotted on a map.a. In plotting data the application will attempt to either:i. Use the pre-coded geospatial reference keys (prepared in Step 3 above) to retrieve theappropriate geospatial objects from the Pyramid GIS database and display them in themapping component.1. Boundary reference points will be exhibited as color-coded 2-dimensional shapes ormulti-colored plotted pie charts depending on user preferences and the underlyingquery used.2. Users can elect to view plotted data on top of mapping tiles using graphic or satelliteimagery or against a plain skeletal world map.ii. Geocode the location addresses without a pre-coded geospatial reference key and plot them inthe mapping component.1. All successfully geocoded addresses will be shown as multi-colored push-pin piecharts2. Users can elect to view plotted data on top of mapping tiles using graphic or satelliteimagery or against a plain skeletal world map.b. All data points within the mapping component will provide context menus such as drill, dice, actionsand isolate/exclude transactions. Where possible and appropriate, tool-tips with entity data will also beprovided.States and Provinces as defined as “Level 1” administrative detail level by the UN.11Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeF. Mapping DiagramPyramidGISSQL Server 2008GeospatialDatabaseShape andpoint dataClient’sRelationalDatabaseViews mappingto PyramidLookup TablesUser browses cubeusing Pyramid and ageospatialdimensionClient AnalyticDatabaseClient’s SSAS CubesMap PA ID’s as propertyattributes to dimensionalelements in geospatialdimensionsSSAS CubeAmend all addresses to reflectfullest, and clearest possibleaddress syntaxSet attribute “type” togeography city/country/state/postal-code/countyOr location addressFigure 12: Pyramid Geospatial Flow12CloudPyramid ApplicationDerives geospatial data fromThe PyramidGIS databaseAnd/or geocoding facilitiesOn the internetRead in pre-geocoded:World CountriesWorld StatesWorld CitiesUS countiesUS zip codesMerge with client dimensionsGeocodedaddressesPyramid Analytics BI Office Mapping Framework & Installation Guide 6Bing“Map” tilesBingMaps
BI OfficeG. Geospatial Mapping to Client DataThe following steps are recommended for fusing the geospatial reference keys from the Pyramid GIS database into clientdimensional data.i.1.Relational Database IntegrationDevelopers create views in the target client relational database that simply read the lookup tables from the sourcePyramid GIS database. (If the Pyramid GIS database is installed on a separate database server, the Pyramid GISdatabase server should be linked first).An example of T-SQL is presented below for creating a view linked to the Pyramid GIS database. Here, the“UScounties” lookup table is being read into the client’s relational database as a view called “gisCounties”CREATE VIEW ounties2.Once the relevant lookup tables have been mapped into views on the client’s relational database, developers shouldthen endeavor to map the GIS foreign key(s) into their dimension tables. The foreign key field is always designated asthe PAID column of type “uniqueIdentifier”. Several standardized name and lookup fields are presented on thePyramid lookup tables to facilitate this cross-walking exercise.If a dimension needs to have multiple lookups (say for country, state/province, county, zip/postal code, city), theneach of the PAID foreign keys needs to appear on the dimension table. Each foreign key field should be given aunique name.A basic example of T-SQL code is presented below to map the PAID key from the gisCounties view to an example“DimCustomer” dimensional table as a field called “countyPAID”. Notice that the join used is an OUTER join (sorecords in the dimension that do NOT match the lookup are not dropped in the resulting view). Also in this specificcase, US counties often have the same name within different states. As such, two joins are required to ensure thatthe match against the dimensional table produces unique values – one for the county name and one for thestate/province.CREATE VIEW [dbo].[vDimCustomer]ASSELECTgislook.PAID AS countyPAID, cust.customKey,cust.FirstName, cust.LastName,cust.Address, cust.City, cust.County, cust.State,cust.ZIP, cust.Phone, cust.Email,cust.Country, cust.ISO, cust.State ProvinceFROMdbo.gisCounties AS gislookRIGHT OUTER JOINdbo.DimCustomer AS custON gislook.STATE cust.State ProvinceAND gislook.NAME cust.County13Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeA more advanced example SQL statement is provided below that maps multiple PAID foreign lookup keys from various GISlookup tables to a single dimensional table in a client’s relational database. Notice that some table joins require two orthree join keys and use different fields to make the match possible.CREATE VIEW [dbo].[vDimCustomer]ASSELECTcountry.PAID AS countryPAID,state.PAID AS statePAID,city.PAID AS cityPAID,county.PAID AS countyPAID,zip.PAID AS zipPAID,cust.*FROMdbo.gisCounties AS countyRIGHT OUTER JOINdbo.DimCustomer AS custON county.STATE cust.State ProvinceAND county.NAME cust.CountyLEFT OUTER JOINdbo.gisPostCodes AS zipON cust.Country zip.COUNTRYAND cust.ZIP zip.NAMELEFT OUTER JOINdbo.gisCities AS cityON cust.ISO city.ISOAND cust.City city.AccentCityAND cust.State Province city.STATE PROVINCELEFT OUTER JOINdbo.gisStates AS stateON cust.State Province state.NAMEAND cust.Country state.COUNTRYLEFT OUTER JOINdbo.gisCountries AS countryON cust.ISO country.ISO 2DIGIT14Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Officeii.3.Cube Dimension IntegrationOnce the client relational database has been setup and the PAID fields have been integrated into the dimensiontables, the new fields need to be mapped into the relevant cube dimension(s). Open up the cube in BusinessIntelligence Development Studio (BIDS). Then open up the relevant data source view and click the Refresh button toimport the new PAID fields into the schema. An example using Adventure Works is provided below.Figure 13: Cube Data Source View Refresh4.Next, open the relevant dimension and drag/add the new PAID fields to the list of attribute hierarchies on the left. (Inthe example below, three PAID fields are added to the Adventure Works Customer dimension: PostCodePAID,StatePAID, CountryPAID)Figure 14: Adding the PAID fields as Attribute Hierarchies15Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Office5.6.7.For each of the new GEOSPATIAL attribute hierarchies added, change their properties accordingly (as shown below):a) Set AttributeHierarchyEnabled to Falseb) Change the Type setting to GeoBoundaryPolygon type within the Geography sectionDevelopers can also provide a complete address attribute for geographic fields that cannot be matched to ageospatial key field in the Pyramid GIS database. Like the PAID keys, a separate “full address” field should be added asa new attribute to the dimension’s attribute hierarchy list, with the following property changes:a) Set the AttributeHierarchyEnabled to Falseb) Change the Type setting to the address type within the location section.After adding and amending the attribute hierarchies, it is CRITICAL that developers set the appropriate attributerelationships. For each attribute ensure that it is set as the related attribute to its counterpart source attribute. Asshown in Figures 15 - 17 below, we have made the Post Code PAID attribute related to the Postal Code sourceattribute. This step ensures that the new attributes are viewed as member properties in the resulting cube structurefor the selected members.Figure 16: Setting Attribute RelationshipsFigure 17: Attribute RelationshipsFigure 15: Attribute Hierarchy Properties16Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Office8.The dimension and the cube can then be processed and checked within a cube viewer application (either BIDSbrowser, Management Studio or Pyramid itself). When looking at a geographic element’s properties, the PAID uniqueidentifier values should be visible as a member property attached to each hierarchy member (see Figure 18 below).Figure 18: Viewing the PAID GUID values in the BIDS dimension browser17Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeH. Using the Geospatial Mapping in the ClientOnce the above steps have been completed successfully, the geospatial capabilities in the Pyramid Analytics client will beaccessible (with the appropriate licensing).The map report component and the map ribbon tab will be enabled and made visible if a user elects to include ageospatial hierarchy in their query (see Figure 19 below).Figure 19: Mapping Components in the Pyramid ClientAs the user drills down into the geospatial hierarchy, the client engine will attempt to retrieve the matching shapes to theelements selected in the query (using the selected PAID keys) and plot them on the map in the client browser. Shapes willbe changed to pie charts if multiple data points are presented for each location.Figure 20: Cube data plotted onto maps18Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeWhen no PAID key is found, the Pyramid Mapping engine will attempt to plot any members using member propertiesdesignated as “location/addresses” types. These addresses are geocoded and then plotted as push-pins onto a tiled map(see Figure 21 below). Geocoding of addresses is best completed when address fields are well structured and complete.As described early, the general format for an address should be a series of comma separated parameters:Street Number, Street Name, City, State/Province, Country, Postal CodeFigure 21: Plotting of Geo19Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeVIII.Technique Two (Text Lookup)I. OverviewTechnique Two is quicker and easier than the Technique One, but offers slightly less flexibility and accuracy in somesituations (depending on your data, data models and requirements).Technique Two involves the flagging of hierarchies (and levels) as geospatial content from inside the data discovery tool.In flagging the content, the user picks the geospatial data type of the target. The type is used to look up shapes in thedatabase for the visuals. If the geolocation details are missing, a sophisticated interpolation engine is used to determinewhich shape to retrieve.NOTE: When using the Technique Two, there is no need for the geospatial mapping described in the previous section.J. Integrating the Two Mapping TechniquesIf a user queries a geospatially flagged hierarchy and the data source does not contain the PAIDs integration, the BI Officemapping engine will perform a textual search on the data in order to fetch the appropriate geospatial entity from thedatabase (a polygon shape, a point of interest, etc.).i.Setting Geospatial Attributes SettingsIf a data model (OLAP or Tabular) was created without (geospatial) hierarchy types during its creation, users can still getthe mapping capabilities by setting hierarchy types in the data discovery client. Users need to right click on a hierarchy inthe hierarchy tree component and choose the appropriate geospatial data type to which to map. Doing so will set thenon-Geographical hierarchy to a Geo hierarchy as shown below.Figure 22: Setting Geospatial Attributes20Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI Officeii.Custom Shapes MappingIt is also possible to map Geographical hierarchies to custom geospatial types (tables) that have been uploaded into thePyramid GIS database via the Add GIS Layer dialog. To open the dialog, open the Administrative Console, click on GeoSpatial, and then click on the Add button. For details, see New Upload Mechanism.For example, if we look at a model that was uploaded via the New Data Model wizard that contains all Australiancounties, we can open the model and set the counties to “countyname” hierarchy and the BI Office mapping engine willcommit the textual search against the custom table Australia Counties that was created.Figure 23: Custom ShapesFigure 24: Custom Shape Mapping21Pyramid Analytics BI Office Mapping Framework & Installation Guide 6
BI OfficeIt is also possible to set hierarchy types in the Administrative Console in the Meta
Pyramid Analytics BI Office 7 Mapping Framework & Installation Guide 6 V. Anatomy of the Pyramid Geospatial Database By default, the Pyramid GIS database is installed under the name PyramidGis _. The database contains several lookup tables (contained in the standard dbo schema) and a separate shapes table (contained in the separate shapes schema
