Author: Geetha Madhuri Bobbili
LinkedIn Profile: https://www.linkedin.com/in/geetha-madhuri-bobbili-971268184/
To create a location enabled model in SAP Analytics Cloud from a live HANA calculation view, Location Data must be prepared through SAP HANA Studio.
The Latitude and Longitude can be converted in to Location Dimension using Spatial data types like Geometry (ST_Geometry) and POINT (ST_Point).
These data types are used to to store spatial information in SAP HANA.
In the SAP HANA Studio console, run the following query:
CREATE SPATIAL REFERENCE SYSTEM "WGS 84 / Pseudo-Mercator" IDENTIFIED BY 3857 TYPE PLANAR
SNAP TO GRID 1e-4
TOLERANCE 1e-4
COORDINATE X BETWEEN -20037508.3427892447 AND 20037508.3427892447
COORDINATE Y BETWEEN -19929191.7668547928 AND 19929191.766854766
ORGANIZATION "EPSG" IDENTIFIED BY 3857
LINEAR UNIT OF MEASURE "metre"
ANGULAR UNIT OF MEASURE NULL
POLYGON FORMAT 'EvenOdd'
STORAGE FORMAT 'Internal'
DEFINITION 'PROJCS["Popular Visualisation CRS / Mercator",
GEOGCS["Popular Visualisation CRS",DATUM["Popular_Visualisation_Datum",
SPHEROID["Popular Visualisation Sphere",6378137,0,AUTHORITY["EPSG","7059"]],
TOWGS84[0,0,0,0,0,0,0],AUTHORITY[ "EPSG","6055"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree", 0.01745329251994328,
AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4055"]],
UNIT[ "metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Mercator_1SP"],PARAMETER["cen tral_meridian",0],
PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PA RAMETER["false_northing",0],
AUTHORITY["EPSG","3785"],AXIS["X",EAST],AXIS["Y", NORTH]]'
TRANSFORM DEFINITION '+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null
+wktext +no_defs'
1. Right click on the table that contains latitude and longitude data and select Data Preview.
2. Select a column that will be the Primary key or unique Location identifier for each location. In this example, the Location Identifier is Country. Set Country as a key column in the calculation view.
3. Create a Geo Location table. This table should contain two columns:
Once the table is created set the Location Data identifier column as a primary key.
4. Create a package (SAP_BOC_SPATIAL) to create your Calculation View containing location information. All the Geo-spatial HANA Views must be created in SAP_BOC_SPATIAL package.
5. Create a calculation view of type Dimension based on the Location Data. This view is referred to as Location Data view and should be created under Content > SAP_BOC_SPATIAL
6. Once the calculation view is created, drag the Location Data table to the Projection
7. Select the columns from the Location Data table in the Location Data view and set the Location Data Identifier column as a key column. In this example, it is Country_LD.
8. Select the Projection node, then double-click each column in the Details pane to make it display in the Output .
Select the Location Identifier column, then set the Key to True in the Properties pane.
9. Save and activate the calculation view.
10. Right click on the Calculation View and select Data Preview.
Now we have created Location Dimension in HANA. In the next blog series, we will see how to consume these dimensions in SAP Analytics Cloud.
Thank you for taking the time to read my blog and I hope you find it beneficial. Please like, share and comment 🙂
Stay safe!
LinkedIn Profile: https://www.linkedin.com/in/geetha-madhuri-bobbili-971268184/
Introduction:
To create a location enabled model in SAP Analytics Cloud from a live HANA calculation view, Location Data must be prepared through SAP HANA Studio.
The Latitude and Longitude can be converted in to Location Dimension using Spatial data types like Geometry (ST_Geometry) and POINT (ST_Point).
These data types are used to to store spatial information in SAP HANA.
Pre-requisites:
- The live HANA system must be at least on SPS11 on 112.07 or higher or SPS12 on 122.03 or higher.
- Your HANA system must be licensed for SAP HANA Spatial.
- The HANA system must be configured with a valid Spatial Reference Identifier (SRID) used by SAP Analytics Cloud.
Add Spatial Reference Identifier (SRID) 3857:
In the SAP HANA Studio console, run the following query:
CREATE SPATIAL REFERENCE SYSTEM "WGS 84 / Pseudo-Mercator" IDENTIFIED BY 3857 TYPE PLANAR
SNAP TO GRID 1e-4
TOLERANCE 1e-4
COORDINATE X BETWEEN -20037508.3427892447 AND 20037508.3427892447
COORDINATE Y BETWEEN -19929191.7668547928 AND 19929191.766854766
ORGANIZATION "EPSG" IDENTIFIED BY 3857
LINEAR UNIT OF MEASURE "metre"
ANGULAR UNIT OF MEASURE NULL
POLYGON FORMAT 'EvenOdd'
STORAGE FORMAT 'Internal'
DEFINITION 'PROJCS["Popular Visualisation CRS / Mercator",
GEOGCS["Popular Visualisation CRS",DATUM["Popular_Visualisation_Datum",
SPHEROID["Popular Visualisation Sphere",6378137,0,AUTHORITY["EPSG","7059"]],
TOWGS84[0,0,0,0,0,0,0],AUTHORITY[ "EPSG","6055"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree", 0.01745329251994328,
AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4055"]],
UNIT[ "metre",1,AUTHORITY["EPSG","9001"]],PROJECTION["Mercator_1SP"],PARAMETER["cen tral_meridian",0],
PARAMETER["scale_factor",1],PARAMETER["false_easting",0],PA RAMETER["false_northing",0],
AUTHORITY["EPSG","3785"],AXIS["X",EAST],AXIS["Y", NORTH]]'
TRANSFORM DEFINITION '+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null
+wktext +no_defs'
Prepare Location Data from HANA Studio:
1. Right click on the table that contains latitude and longitude data and select Data Preview.
2. Select a column that will be the Primary key or unique Location identifier for each location. In this example, the Location Identifier is Country. Set Country as a key column in the calculation view.
3. Create a Geo Location table. This table should contain two columns:
- Location Data identifier: ID column values from the Location Identifier (Ex: Country) column of your calculation view.
- Spatial data column: containing spatial data (for example ST_GEOMETRY) generated from your latitude and longitude data with SRID 3857.
Once the table is created set the Location Data identifier column as a primary key.
Create a Calculation View:
4. Create a package (SAP_BOC_SPATIAL) to create your Calculation View containing location information. All the Geo-spatial HANA Views must be created in SAP_BOC_SPATIAL package.
5. Create a calculation view of type Dimension based on the Location Data. This view is referred to as Location Data view and should be created under Content > SAP_BOC_SPATIAL
6. Once the calculation view is created, drag the Location Data table to the Projection
7. Select the columns from the Location Data table in the Location Data view and set the Location Data Identifier column as a key column. In this example, it is Country_LD.
8. Select the Projection node, then double-click each column in the Details pane to make it display in the Output .
Select the Location Identifier column, then set the Key to True in the Properties pane.
9. Save and activate the calculation view.
10. Right click on the Calculation View and select Data Preview.
Conclusion:
Now we have created Location Dimension in HANA. In the next blog series, we will see how to consume these dimensions in SAP Analytics Cloud.
Thank you for taking the time to read my blog and I hope you find it beneficial. Please like, share and comment 🙂
Stay safe!
- SAP Managed Tags:
7 Comments
