Since nearly all businesses understand that weather influences their business, a frequent question arises: How can I load weather data into SAP Analytics Cloud and use it to better analyze my business data? The benefits of finding weather correlations with business data are obvious – intelligent inventory management, better logistics planning, timely advertising opportunities, and dynamic human resource scheduling just to name a few. However, the process of getting, cleaning, loading, and updating weather data in a business intelligence system has traditionally been daunting. In this article, we’ll show you the benefits of using weather analysis with business data and walk you step-by-step through the process of getting weather data blended with your business data.
Although it is possible to obtain and process weather data yourself using various governmental and commercial sources from around the world, using an integrated weather data API is a far easier, less risky, and less costly option. Weather data APIs represents the state of the art in weather data for business analysts in several ways. They provide a single, high-performance API to access both historical weather and weather forecasts globally. This allows you to use a single infrastructure and single API to access data for business locations both around the corner and on the other side of the world. Compiling both historical weather sources and worldwide weather forecasts is a difficult challenge. So most homegrown and lesser solutions don’t provide historical weather data at all. However, since historical weather is the key to finding patterns and correlations, a data source that fails to provide historical data is much less useful in business intelligence and analyst application. So, selecting a source such as Visual Crossing Weather that can be a "one stop shop" for all of your weather data needs, bring immediate value to any weather project.
In addition, other weather data sources can be difficult by requiring users to download and parse extremely large datasets in order to analyze a few specific locations. And even then, the data user is often responsible for finding the best weather station nearest to the desired business location. It is an open secret in the weather data world that weather reporting stations frequently go offline for periods while others report conflicting data. Full-function weather APIs such as Visual Crossing Weather, Weather Underground, and Open Weather Map cleans and filters the data and then uses interpolation algorithms to find the best weather match for any given location. Stations that don’t report in a given period are automatically filtered out. Stations reporting erroneous or internally conflicting data are automatically corrected or homogenized based on reasonable norms and by comparison with other local stations.
Depending upon your business having a variety of weather metrics is often important as well. Weather APIs such Weather provides not only traditional weather metrics such as temperature and rainfall, but also some that are less common but have high business value. Metrics such as heat index can often influence the actions of customers involved in physical activity. If your weather interests include more sky-dependent activities, then metrics such as cloud cover and precipitation coverage may be important. In short, having a large variant of weather metrics allows you analyze your business in relation to any of these metrics and intelligently determine which ones have the most impact for you.
The first step in using a weather API is to create a weather query. In this example, we will use the Visual Crossing Weather data to provide the weather data. You will need to identify the locations for which you need weather analysis and the time period that matches your business data. As an example, we’ll consider demo data built around the sales data of a worldwide ice cream vendor. This sample data contains sales information for twenty-three leading cities around the globe for the year 2018. You can download the example data at the end of this article and follow along as you read if you wish.
If you are following along using the Visual Crossing Weather query page you will first need to log in. If you don’t already have an account you can sign up for a free trial. Simply click on the “Sign up” link in the upper right. You can then enter the locations for your weather query. In this example we will enter the locations for our cities. Luckily, we only need to do this one time, since we can rerun the same query as many times as desired after we have defined it. Likewise, we will enter the date range that we wish to analyze. For our example data, the starting date is January 1, 2018 and the ending date December 31, 2018. For the other options, we can accept the defaults because we want to fetch historical results and have the data aggregated at the daily level. For a more sophisticated analysis we may want to consider setting specific business hours for filtering the weather records. However, since our later analysis below is going to be based primarily on the daily heat index, setting custom business hours isn’t necessary for our simple example.
Once we have formulated our query, we can test it by clicking on the “Generate data” button near the bottom of the page. This button will run the query and show a preview of the output data in a new grid at the bottom of the page. If the preview looks to be correct, we are ready to copy it for inclusion in SAC. To do so, simply click on the “Generate OData” option above the sample grid and in the popup window click “Copy to clipboard” to copy the query string. Our query is now ready to use directly in SAC.
The second step of the configuration process is to create a new Data Model that combines both the weather data and the business data into a single Data Model. It is worth noting here that there are various ways to set up and join data weather data with your business data in SAC. In this example, we will discuss the most robust option, and that option is creating a joined Data Model. A primary advantage of this approach is that once the joined model is created, it can be reused in any Story, Analytic Application, or Digital Boardroom application by any author. However, there are other configuration options that may be useful depending upon the application and desired use case. For example, you can choose to import the weather data as a stand-alone Data Model where this new Data Model contains no business data and only weather data. You can then use the data merging functionality in an SAC story to build the combined dataset. For the sake of this example, we will showcase using a single, merged Data Model option, but you may want to consider all of the options when designing a production application.
Begin this step by asking SAC to create a new Data Model base on the OData Connection that we created above. SAC will ask about the columns that you want to import in your query. For this example, you can select all of the columns for ease or filter down to only the Location and Heat Index columns since those are the ones that we will be using to create our example Story below. While you are still experimenting with weather data, I would recommend that you select all of the weather data columns. However, when you are making production applications, it is typically better to filter the number of columns to reduce the overhead of carrying around unused data.
Once in the SAC Model editor, you will see a preview of the weather data that was loaded from your query. This data will match the preview grid that you saw in the Visual Crossing query editor. In order to join in the sample ice sream sales business data, select the Combine Data option from the Actions toolbar. In the real world, we would already have business data available and modeled in SAC. So in that case we would select the Data Model containing the business data that wish to analyze. However, in this article we will use sample data. You can find a link to this data at the bottom of this article. If you are following along, download the file to your local computer. Then select the option to load the data from a local file and point SAC at that newly downloaded file.
SAC will now show the Combine Data settings page. This is the page where we tell SAC how to do the join between the business data and the weather data. There are two key columns to match in this example. The first is “Address”. Select that in both datasets and SAC will wire the join and signal its approval in the preview window on the right. The second matching column is “Date Time”. Select that in both datasets and SAC will again signal its approval.
If you look at the grid at the bottom of the Combine Data settings page, you will see that SAC has now created the useful dataset that we desire. That is, we have weather conditions and ice cream sales by date joined together on the same grid. The final step is to tell SAC how to aggregate the metrics on our combined dataset. This is required because SAC defaults to doing “sum” aggregations for all values. This works fine for values such as our ice cream sales metric that we want to add together when shown over multiple areas or times. However, sums do not make sense for certain weather metrics. For example, the sum of heat index over times or locations is meaningless. In this example, we want to show the average when aggregated. It is worth noting, however, that in some analysis use cases we may want to aggregate based on other functions such as “maximum” or “minimum”. So, in your production application, think carefully about how your users will want to see any given weather metric aggregated and make sure that you select the appropriate aggregation function.
To affect this change from “sum” to “average”, simple go into the metric lists for this Model. You will see all of the available metrics including both the weather metrics and the ice cream sales. In our example Story we will be working with Heat Index. So, select the Heat Index metric and set it to have an Exceptional Aggregation type of “Average”. Optionally, if you wish to explore this dataset beyond the example in this article, you can also modify the other appropriate weather metrics to aggregate to Average as well. Note also that you can go back and edit this Model and change the aggregation functions at any time.
Finish the process by hitting the Combine button, and SAC will return to the Model editor. And then finish this step by giving your new Model a name and saving it.
After laying the infrastructure, we are now ready to make a Story to show our business data combined with weather. Just like the creation of any other SAC Story, simply go into the Story creator and select as the data source the combined Data Model that you just created. First, we’ll make a line graph that shows how ice cream sales vary around the world based on the daily heat index. For this example we chose heat index based on the logic that people’s desire for ice cream is directly related to how hot the day feels to them. Thus, since heat index combines both temperature and humidity, it is a great metric to describe how hot people perceive the day to be.
To complete this example, add a line graph to your new Story. For the Left Y-axis select the Heat Index metric. For the Right Y-axis select the Ice Cream Sale metric. For the Dimension, select the Address. You may also want to change the color scheme to make the two lines more distinct. SAC will show a graph like the one below.
From this graph you can see a basic correlation between ice cream sales and the heat index in our sample data cities. If you would like to analyze this data further by individual date, you can add the Date Time Dimension to the graph. However, if you do that directly the X-axis of our graph will become unreadable due to the large number of dates in 2018. To fix this, simply add a location or a date filter to the Story. Of course, this is just the beginning of the Story options that we can consider when presenting this data. At this point you are limited only by the Story controls in SAC and your own imagination. You can make all manner of complex Stories and Applications.
I hope that this article is just the beginning of your weather exploration. If you want to explore the sample data more, you can start by experimenting with weather metrics other than heat index. Is there a relationship between ice cream sales and precipitation, for example? However, the real value begins when you use your own business data to find weather correlations. Doing this is just as easy as following the example above, and following this article should have given you to knowledge and confidence needed. Simply create a weather query, create an SAC Connection based on the Visual Crossing Weather URL, create a Data Model based on that connection, and then join in your business data. Follow this simple process and you will be analyzing your business data with weather metrics in a matter of minutes.
If you have questions or run into trouble with your weather project, I’ll be happy to help. Post your questions and comments below or send me an email. Whether you are using Visual Crossing weather or another service, I’m sure that adding weather data into SAP Analytics Cloud will bring significant value to your business analysis.
Link to the data used in this article http://www.visualcrossing.com/weather/SampleData/Worldwide_Cities_Daily_BI_Data.xlsx
State of the Art Weather Data
Although it is possible to obtain and process weather data yourself using various governmental and commercial sources from around the world, using an integrated weather data API is a far easier, less risky, and less costly option. Weather data APIs represents the state of the art in weather data for business analysts in several ways. They provide a single, high-performance API to access both historical weather and weather forecasts globally. This allows you to use a single infrastructure and single API to access data for business locations both around the corner and on the other side of the world. Compiling both historical weather sources and worldwide weather forecasts is a difficult challenge. So most homegrown and lesser solutions don’t provide historical weather data at all. However, since historical weather is the key to finding patterns and correlations, a data source that fails to provide historical data is much less useful in business intelligence and analyst application. So, selecting a source such as Visual Crossing Weather that can be a "one stop shop" for all of your weather data needs, bring immediate value to any weather project.
In addition, other weather data sources can be difficult by requiring users to download and parse extremely large datasets in order to analyze a few specific locations. And even then, the data user is often responsible for finding the best weather station nearest to the desired business location. It is an open secret in the weather data world that weather reporting stations frequently go offline for periods while others report conflicting data. Full-function weather APIs such as Visual Crossing Weather, Weather Underground, and Open Weather Map cleans and filters the data and then uses interpolation algorithms to find the best weather match for any given location. Stations that don’t report in a given period are automatically filtered out. Stations reporting erroneous or internally conflicting data are automatically corrected or homogenized based on reasonable norms and by comparison with other local stations.
Depending upon your business having a variety of weather metrics is often important as well. Weather APIs such Weather provides not only traditional weather metrics such as temperature and rainfall, but also some that are less common but have high business value. Metrics such as heat index can often influence the actions of customers involved in physical activity. If your weather interests include more sky-dependent activities, then metrics such as cloud cover and precipitation coverage may be important. In short, having a large variant of weather metrics allows you analyze your business in relation to any of these metrics and intelligently determine which ones have the most impact for you.
Implementation for SAP Analytics Cloud Users
The first step in using a weather API is to create a weather query. In this example, we will use the Visual Crossing Weather data to provide the weather data. You will need to identify the locations for which you need weather analysis and the time period that matches your business data. As an example, we’ll consider demo data built around the sales data of a worldwide ice cream vendor. This sample data contains sales information for twenty-three leading cities around the globe for the year 2018. You can download the example data at the end of this article and follow along as you read if you wish.
Weather API Query Page
If you are following along using the Visual Crossing Weather query page you will first need to log in. If you don’t already have an account you can sign up for a free trial. Simply click on the “Sign up” link in the upper right. You can then enter the locations for your weather query. In this example we will enter the locations for our cities. Luckily, we only need to do this one time, since we can rerun the same query as many times as desired after we have defined it. Likewise, we will enter the date range that we wish to analyze. For our example data, the starting date is January 1, 2018 and the ending date December 31, 2018. For the other options, we can accept the defaults because we want to fetch historical results and have the data aggregated at the daily level. For a more sophisticated analysis we may want to consider setting specific business hours for filtering the weather records. However, since our later analysis below is going to be based primarily on the daily heat index, setting custom business hours isn’t necessary for our simple example.
Once we have formulated our query, we can test it by clicking on the “Generate data” button near the bottom of the page. This button will run the query and show a preview of the output data in a new grid at the bottom of the page. If the preview looks to be correct, we are ready to copy it for inclusion in SAC. To do so, simply click on the “Generate OData” option above the sample grid and in the popup window click “Copy to clipboard” to copy the query string. Our query is now ready to use directly in SAC.
SAP Analytics Cloud OData Connection Configuration Page
The second step of the configuration process is to create a new Data Model that combines both the weather data and the business data into a single Data Model. It is worth noting here that there are various ways to set up and join data weather data with your business data in SAC. In this example, we will discuss the most robust option, and that option is creating a joined Data Model. A primary advantage of this approach is that once the joined model is created, it can be reused in any Story, Analytic Application, or Digital Boardroom application by any author. However, there are other configuration options that may be useful depending upon the application and desired use case. For example, you can choose to import the weather data as a stand-alone Data Model where this new Data Model contains no business data and only weather data. You can then use the data merging functionality in an SAC story to build the combined dataset. For the sake of this example, we will showcase using a single, merged Data Model option, but you may want to consider all of the options when designing a production application.
Begin this step by asking SAC to create a new Data Model base on the OData Connection that we created above. SAC will ask about the columns that you want to import in your query. For this example, you can select all of the columns for ease or filter down to only the Location and Heat Index columns since those are the ones that we will be using to create our example Story below. While you are still experimenting with weather data, I would recommend that you select all of the weather data columns. However, when you are making production applications, it is typically better to filter the number of columns to reduce the overhead of carrying around unused data.
Once in the SAC Model editor, you will see a preview of the weather data that was loaded from your query. This data will match the preview grid that you saw in the Visual Crossing query editor. In order to join in the sample ice sream sales business data, select the Combine Data option from the Actions toolbar. In the real world, we would already have business data available and modeled in SAC. So in that case we would select the Data Model containing the business data that wish to analyze. However, in this article we will use sample data. You can find a link to this data at the bottom of this article. If you are following along, download the file to your local computer. Then select the option to load the data from a local file and point SAC at that newly downloaded file.
SAC will now show the Combine Data settings page. This is the page where we tell SAC how to do the join between the business data and the weather data. There are two key columns to match in this example. The first is “Address”. Select that in both datasets and SAC will wire the join and signal its approval in the preview window on the right. The second matching column is “Date Time”. Select that in both datasets and SAC will again signal its approval.
SAC Combine Data Configuration Page
If you look at the grid at the bottom of the Combine Data settings page, you will see that SAC has now created the useful dataset that we desire. That is, we have weather conditions and ice cream sales by date joined together on the same grid. The final step is to tell SAC how to aggregate the metrics on our combined dataset. This is required because SAC defaults to doing “sum” aggregations for all values. This works fine for values such as our ice cream sales metric that we want to add together when shown over multiple areas or times. However, sums do not make sense for certain weather metrics. For example, the sum of heat index over times or locations is meaningless. In this example, we want to show the average when aggregated. It is worth noting, however, that in some analysis use cases we may want to aggregate based on other functions such as “maximum” or “minimum”. So, in your production application, think carefully about how your users will want to see any given weather metric aggregated and make sure that you select the appropriate aggregation function.
To affect this change from “sum” to “average”, simple go into the metric lists for this Model. You will see all of the available metrics including both the weather metrics and the ice cream sales. In our example Story we will be working with Heat Index. So, select the Heat Index metric and set it to have an Exceptional Aggregation type of “Average”. Optionally, if you wish to explore this dataset beyond the example in this article, you can also modify the other appropriate weather metrics to aggregate to Average as well. Note also that you can go back and edit this Model and change the aggregation functions at any time.
Finish the process by hitting the Combine button, and SAC will return to the Model editor. And then finish this step by giving your new Model a name and saving it.
Story Time
After laying the infrastructure, we are now ready to make a Story to show our business data combined with weather. Just like the creation of any other SAC Story, simply go into the Story creator and select as the data source the combined Data Model that you just created. First, we’ll make a line graph that shows how ice cream sales vary around the world based on the daily heat index. For this example we chose heat index based on the logic that people’s desire for ice cream is directly related to how hot the day feels to them. Thus, since heat index combines both temperature and humidity, it is a great metric to describe how hot people perceive the day to be.
To complete this example, add a line graph to your new Story. For the Left Y-axis select the Heat Index metric. For the Right Y-axis select the Ice Cream Sale metric. For the Dimension, select the Address. You may also want to change the color scheme to make the two lines more distinct. SAC will show a graph like the one below.
SAC Story with simple line graph
From this graph you can see a basic correlation between ice cream sales and the heat index in our sample data cities. If you would like to analyze this data further by individual date, you can add the Date Time Dimension to the graph. However, if you do that directly the X-axis of our graph will become unreadable due to the large number of dates in 2018. To fix this, simply add a location or a date filter to the Story. Of course, this is just the beginning of the Story options that we can consider when presenting this data. At this point you are limited only by the Story controls in SAC and your own imagination. You can make all manner of complex Stories and Applications.
Where Do You Go From Here?
I hope that this article is just the beginning of your weather exploration. If you want to explore the sample data more, you can start by experimenting with weather metrics other than heat index. Is there a relationship between ice cream sales and precipitation, for example? However, the real value begins when you use your own business data to find weather correlations. Doing this is just as easy as following the example above, and following this article should have given you to knowledge and confidence needed. Simply create a weather query, create an SAC Connection based on the Visual Crossing Weather URL, create a Data Model based on that connection, and then join in your business data. Follow this simple process and you will be analyzing your business data with weather metrics in a matter of minutes.
If you have questions or run into trouble with your weather project, I’ll be happy to help. Post your questions and comments below or send me an email. Whether you are using Visual Crossing weather or another service, I’m sure that adding weather data into SAP Analytics Cloud will bring significant value to your business analysis.
Link to the data used in this article http://www.visualcrossing.com/weather/SampleData/Worldwide_Cities_Daily_BI_Data.xlsx
- SAP Managed Tags:
6 Comments
