Using IBM's Weather Data in Cognos Analytics
The Weather Company
Back in 2016, IBM made a strategic acquisition of The Weather Company, bringing a wealth of weather and climate-related data assets under the IBM umbrella. Since that time, IBM has been demonstrating how weather data can provide business value to all kinds of companies (turns out, the weather has an impact on almost any kind of business), and simultaneously working to integrate the technology into various IBM products. While previously it's been possible to bring weather data into Cognos Analytics with some coding via a set of APIs, in Cognos Analytics 11.1.4 The Weather Company was added as a data source, making the process simpler and native to Cognos.
Here, we'll explore how that connection works and look at a simple example of how to visualize Weather company data.
Steps Outlined for Using IBM’s Weather Data in Cognos Analytics:
Establish data connection to “The Weather Company” & import metadata for schemas
Organize location data by zip codes
Retrieve IBM Weather Data using Data Modules
Create Relationships between Tables & Fields
Create a Weather Dashboard
Accessing IBM Weather Data
As part of each Cognos installation, starting with version 11.1.4, IBM has included a sample data connection that allows you to access weather data and include it in your reports and dashboards. The data connection is part of the Weather Company sample and an optional package that your Cognos administrator can include at installation time.
If you do not have the Weather Company sample, download the file The_Weather_Company.zip from IBM and save it to the deployment folder of your Cognos installation. Open the Administration console, switch to Content Administration, and use New Import to load the zip file.
Data Connection
To begin, access the Weather Company sample folder. Open the data connection "The Weather Company" and edit the service and set the authentication method to connect anonymously.
As part of the sample installation, IBM includes a JDBC driver that connects to the Weather Company servers. Edit the data connection and insert your API key as the password. Leave the username blank as the API key serves as both the username and password. (If you don’t have an API key, please reach out to us. Access to weather data requires paid licensing from IBM, but we can help you get a free trial and ensure you get the best pricing possible if you decide to purchase.)
Test the connection: you should receive a success message. If not, click the information to examine the error message(s).
Upon a successful connection, you will need to import the metadata for the schemas. You will not be able to import the V3/SYSTEM schema, so ignore it.
When you're done you should see 12 forecast tables, one geography table, and 12 historical tables.
Using Specified Zip Codes
The Weather Company data returns data grouped on a concatenation of postal code and country code. For example, downtown Chicago's key is the combination of zip code and country code: "60601:US". If you want data for a specific area, you need to include key values.
Below I will demonstrate to you how I created a dashboard showing the average daily temperatures for all zip codes in the city of Chicago.
To select the location data I created the Excel workbook "Chicago_zips.xlsx" with four columns containing the country code, postalKey, postalKeyCountry, and city name. postalKey is the zip code only, while postalKeyCountry is the zip code and country code as previously detailed:
I uploaded Chicago_zips.xlsx to Cognos into the same folder that holds the Weather Company sample.
Similarly, I created another Excel workbook named 2020-03.xlsx with the dates of the first 15 days of March 2020. These dates will control the days for which weather data can be retrieved. I uploaded the 2020-03.xlsx and placed it in the same folder
Retrieving IBM Weather Data in Data Module(s)
To retrieve the weather data I created a new data module and chose the "Weather Company" data server.
For this demonstration, I am only adding tables from the Geography and Historical schemas.
I have selected the Locations table from the "Geography" schema and the Temperature table from the Historical schema.
The Locations table contains the fields country, postalKeyCountry, postalKey, latitude, and longitude for each country and postalKey.
The Temperature table contains multiple fields with variations on the local temperature readings for each location. I used the TemperatureLocalDayAvg field which is the average temperature for the full 24 periods of each day.
I added the Excel workbooks as additional sources and verified that the expected data is available.
Creating Relationships Between Tables & Fields
Now I created relationships between the Excel workbook tables and the Weather Company data fields. First, I linked the 2020-03 dates with the Temperature data.
The match between the tables is on the Date fields in both tables. I set the join type as an inner join, one-to-many with 2020-03 dates as the one and Temperature as the many. Note that I also set the join to Unique Values: this will come in handy later in the process.
Since there is a large amount of data in the Temperature table – there is no filter on the locations so all locations for all dates in 2020-03 are available – so the data service will not return any data. This behavior is expected and does not affect whether the join is valid.
Next, I joined the Chicago_zips table and the Temperature table to match on the postalKey field. Strangely, the Temperature table uses the zip code plus country code values for postalKey rather than postalKeyCountry in the Chicago_zips table. Thus, I joined the two fields with an inner join, a one (Chicago_zips) to many (Temperature) cardinality and set the Unique Values flag.
Notice that now the sample data from the Match returns valid data. The date join to the 2020-03 table – with Unique Values set -- selects a specific 15 days of data, and the link to the Chicago zip codes further restricts the data returned.
With the appropriate tables now joined, I saved the data module as "Weather DM" to the samples folder.
Creating a Weather Dashboard
Now, I created a dashboard that will allow me to plot the average daily temperatures for any and all dates in the 2020-03 table. In the new dashboard, I dragged the Date field from 2020-03.xlsx and set it to display as a list.
Then I dragged over the postalKey in the Chicago_zips table, which contains the zip code only, and the TemperatureLocalDayAverage field from the Temperature table. At this point, I now have a color-coded map of all the average temperatures for all zip codes in Chicago which can be filtered on a date. When I move my cursor within a zip code area I see a tooltip with the data.
I can also filter on a range of dates. I set the aggregation of the average temperature field to Average, so when I select multiple dates and hover over a zip code area I see the average temperature for those dates.
Notice that the tooltip shows an extraordinarily high temperature, in the high 200s for the series of March days. The Temperature table fields return values in Kelvin, which starts at absolute zero. Absolute zero is -273.15 degrees on the Celsius scale and -459.67 degrees Fahrenheit. To convert Kelvin to Celsius subtract 275.15, or to convert Kelvin to Fahrenheit multiply by 9, divide by 5 then subtract 459.67. As an example, 274.65 degrees Kelvin would be 274.65 - 273.15 = 1.5 degrees Celsius, or 274.65 * 9 / 5 - 459.67 = 34.7 degrees Fahrenheit.
Conclusion
I hope this article gives you a taste of the possibilities when you use Weather Company data. Thank you for your time!
Next Steps
We hope you found this article informative. Be sure to subscribe to our newsletter for data and analytics news, updates, and insights that are delivered directly to your inbox.
If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.