SAP Data Warehouse & SAP Analytics Cloud - New Geo...

IngoH · ‎04-21-2021

Please note, that the below is showing an upcoming feature of the SAP Data Warehouse Cloud integration with SAP Analytics Cloud. This feature is being planned for delivery towards end of next month.

Disclaimer:

This presentation, or any related document and SAP's strategy and possible future developments, products and or platforms directions and functionality are all subject to change and may be changed by SAP at any time for any reason without notice.

Today we are going to take a look at an new upcoming feature for the integration of SAP Analytics Cloud with SAP Data Warehouse cloud, which is the Geo Enrichment of data directly within SAP Data Warehouse Cloud.

Here is our overall scenario:

Uploading the Geo Information for the dimension

Create the Dimension View and configure the Geo Enrichment

Add the Dimension View to the Analytical Data Set

Leverage the Geo relevant information in SAP Analytics Cloud

For our sample scenario we are using a set of retail transaction which contain the City in the US as column and a second set of data with the US cities and the geo relevant information.

Uploading Geo Information

As first set of steps we will upload the geo information for the dimension into SAP Data Warehouse Cloud and create the table for it.

We login to SAP Data Warehouse Cloud.

Navigate to the Data Builder.

On the start screen of the Data Builder select the option to import a CSV File.

Select the CSV File with the Geo relevant information.

In our example the file has the following structure

City	State ID	State Name	lat	lng
New York	NY	New York	40.6943	-73.9249
Los Angeles	CA	California	34.1139	-118.4068
Chicago	IL	Illinois	41.8373	-87.6862
Miami	FL	Florida	25.7839	-80.2102
Dallas	TX	Texas	32.7936	-96.7662

Import the CSV file and ensure that the data type for the column for longitude and latitude is set to Number.

After you imported the file, open the table in the Data Builder.

Ensure the Data Type for Longitude and Latitude is set to Decimal.

Ensure the table is saved and deployed.

Creating the Dimension View

After we uploaded the information for all cities, we will now create the Dimension View and configure the Geo Enrichment based on the Longitude and Latitude values.

In SAP Data Warehouse Cloud navigate to the Data Builder.

Navigate to the Data Builder.

Select the option New Graphical View.

On the left hand side you can see your previously created table.

Drag and Drop the table from the Repository to the canvas.

Select the node View 1 on the canvas.

Navigate to the Details panel on the right-hand side.

Set the following Details:
- Business Name: US Cities Geo Dimension
- Technical Name: US_Cities_Geo_Dimension
- Semantic Usage Dimension

Select the node US Cities on the canvas.

Select the option to add a node for Calculated Columns.

Select the new node “fx” for the Calculated Columns on the Canvas.

Navigate to the Details on the right-hand side.

Click on the “+” sign and select the option Geo-Coordinates Column.

You can now map the columns from the source table to the configuration for Latitude and Longitude.

Map those columns.

Enter the following details:
- Business Name US City Location
- Technical Name US_City_Location

Please note, that this is the name of the Location Dimension in SAP Analytics Cloud.

Click on the term “Columns” in the header of the panel to navigate back.

Save the View

Deploy the View

Adding the Dimension View to the Analytical DataSet

We have the dimension with the Geo relevant information now and can now create our Analytical Data Set and add the Geo Dimension as an Association to our View.

Login to SAP Data Warehouse Cloud.

Navigate to the Data Builder.

On the start screen of the Data Builder select the option to import a CSV File.

Select the CSV File with the fact data.

Import the file and Deploy the table with the name Retail Sample Data.

Navigate to the Data Builder.

Select the option New Graphical View.

On the left hand side you can see your previously created table.

Drag and Drop the table Retail Sample Data from the Repository to the canvas.

Select the node View 1 on the canvas.

Navigate to the Details on the right hand side.

Enter the following Details:
- Business Name Retail Sample Analytical Data Set
- Technical Name Retail_Sample_Analytical_Data
- Semantic Usage Analytical Data Set
- Expose for Consumption On / Enabled

In the Details panel scroll down to the list of Attributes.

Navigate to the Attribute Sales.

Ensure that you have at least one Measure defined.

In the Details panel scroll down to the area Associations.

Use the “+” sign to create a new Association.

Select the Dimension View US Cities Geo Dimension which we created previously.

Click OK.

In the panel on the right hand side ensure that the Join is based on dimension City.

Save your changes.

Deploy the View.

SAP Analytics Cloud – Using the Geo Information

We now have the view in SAP Data Warehouse Cloud and we can leverage the information in SAP Analytics Cloud.

Please note, that the following steps require a Live Connection towards the SAP Data Warehouse Cloud tenant to be configured in SAP Analytics Cloud.

Login to SAP Analytics Cloud.

Select the option to Create a new Story.

Select the option to use a Canvas page.

In the top left of the toolbar, select the option Data.

Select the option to add Data from a Data Source.

Open the list of Live Data Connections.

Select the entry SAP Data Warehouse Cloud.

Select the Connection.

Click OK.

Select the Space from SAP Data Warehouse Cloud.

Click OK.

Select the Analytical Data Set.

Click OK.

Select the option to add a Geo Map.

Navigate to the Builder Panel on the right hand side.

Click Add Layer for the option Content Layers.

Click Add Location Dimension for the option Location Dimension.

Select the entry US City Location, which is what we created as part of our SAP Data Warehouse Cloud model.

Click Add Measure / Dimension for the Bubble color.

Select measure Profit.

Click Add Measure for the Bubble Size.

Select measure Sales.

Click OK (bottom right).

As you can see, with a few steps we were able to enrich our data in SAP Data Warehouse Cloud with the relevant Geo information, add the Geo information to our view, and then simply leverage the information in SAP Analytics Cloud.

With this first step on the Geo Integration, we do support the Bubble Layer, Heat Layer, and the Flow Layer. Support for the Choropleth Layer and the ability to leverage regional information (like country and region) instead of longitude and latitude, is planned as well and will come soon.

iamimran2093_0 · ‎04-22-2021

Awesome feature. Looking forward to use it. Thanks ingo.hilgefort for sharing this

jimgiffin · ‎04-28-2021

ingo.hilgefort I see the dropdown has SRID 4326. Will we be able to use other SRIDs?

IngoH · ‎04-29-2021

Hello James,

right now it lists those SRID that are available in the HC system. There is a plan to provide an option to deploy additional items.

Best Regards

Ingo Hilgefort, SAP

melanie_wobbe · ‎05-07-2021

Hey Ingo, great blog! I tested it in our Demo Sandbox system - it works fine 😉

Many thanks!

XaviPolo · ‎05-17-2021

thanks for the info. Will it be available when using the business layer?

IngoH · ‎05-17-2021

Hello Xavier

klausfreyburger · ‎09-07-2021

Hi Ingo,

great blog, thanks!

Support for the Choropleth Layer and the ability to leverage regional information (like country and >region) instead of longitude and latitude, is planned as well and will come soon.

Any news on this?

Thanks

Klaus

IngoH · ‎09-07-2021

Hello Klaus,

yes those items are planned as well

Ingo

wounky · ‎05-26-2022

Hi ingo.hilgefort

could you please tell me what could be the reason that SAC completely ignores the geo location column?

I can see it correctly calculated in DWC and visible both from the Business Builder / Perspective and I also tried to expose it directly from Data Builder / An. Data set.

When read in SAC, this column is completely not recognized/visible.

Kind regards,

Sebastian

IngoH · ‎05-30-2022

Hello Sebastian,

that is "works as designed". the column for the Geo Location will not be listed for the standard charts or the table, but when you create a Geo Map, then the Geo Location column should show up as shown in the blog on step 16 / 17.

best regards

ingo

wounky · ‎05-30-2022

thanks, Ingo,

the problem being, it does not show at all in either, I will write here the solution once known

IngoH · ‎05-30-2022

Hello Sebastian,

is the View in DWC configured as a "Dimension" View ?

How are you bringing in the Dimension into the final Analytical Data Set in DWC ? just as part of the model or as part of an Association ?

Thanks

ingo

wounky · ‎05-31-2022

Hi Ingo,

It's brought as a part of the association.
There is a sales org dimension associated with one of the columns of the Analytical Data Set. This Sales org dimension has an association with the Country dimension enhanced by geo location.

I tried to do the same though, from the level of data builder and direct association and it also did not work. We have a ticket open.

Thanks,
Sebas

IngoH · ‎05-31-2022

Hello Sebastian,

so just to confirm:

you have our analytical view that points to an association (dimension) for Sales Org

Inside the Sales Org dimension you have another association pointing towards Country Dimension ?

so basically nested associations ?

If so - then that is the issue.

Ingo

wounky · ‎06-01-2022

Hi Ingo,

Correct, I think I also tried without a nested association and it was the same but I will give it another try 🙂

The ticket was taken over to the development team as a reproducible example, so who knows, maybe it is supposed to work.

Sebastian

IngoH · ‎06-01-2022

also remember that any change you do in the dimension view / association that you then have to redeploy any view / model upwards as well for the changes to come through

ingo

wounky · ‎06-08-2022

It is fixed now, there was an issue in the SAC GUI that was not selecting the model for the bubble chart.

Nested associations are also supported

guejul · ‎09-11-2023

Hello,

is this feature available now?

Thanks!

Julian