Using IBM's Weather Data in Cognos Analytics

Using IBM's Weather Data in Cognos Analytics

The Weather Company

Back in 2016, IBM made a strategic acquisition of The Conditions Visitor, 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 concern value to all kinds of companies (turns out, the conditions has an impact on almost whatsoever 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 xi.1.4 The Weather condition Company was added as a data source, making the procedure simpler and native to Cognos.

Here, we'll explore how that connection works and await at a uncomplicated example of how to visualize Conditions company data.

Steps Outlined for Using IBM'due south Conditions Information in Cognos Analytics:

  • Establish data connectedness to "The Weather Company" & import metadata for schemas

  • Organize location information by zip codes

  • Recollect IBM Weather Information using Data Modules

  • Create Relationships betwixt Tables & Fields

  • Create a Weather Dashboard

Accessing IBM Weather Data

Equally function of each Cognos installation, starting with version eleven.ane.four, IBM has included a sample data connection that allows you to access atmospheric condition data and include it in your reports and dashboards. The information connexion is function of the Weather Company sample and an optional package that your Cognos administrator tin include at installation time.

If you practise non have the Weather Company sample, download the file The_Weather_Company.zip from IBM and relieve it to the deployment folder of your Cognos installation. Open the Administration console, switch to Content Administration, and use New Import to load the nix file.

Data Connection

To brainstorm, admission the Weather Company sample folder. Open the data connection "The Weather Company" and edit the service and gear up the authentication method to connect anonymously.

Cognos "The Weather Data" data connection

As role 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 fundamental serves as both the username and password. (If you don't have an API central, please reach out to united states. Access to weather data requires paid licensing from IBM, but we can assistance you get a free trial and ensure yous become the best pricing possible if you decide to purchase.)

Inserting API key to Cognos IBM Weather Company Connection

Test the connection: you should receive a success message. If non, click the information to examine the error message(due south).

Successful Connection to Weather Company Data

Upon a successful connection, you will need to import the metadata for the schemas. Y'all will not be able to import the V3/SYSTEM schema, and then ignore it.

Importing Weather metadata in Cognos

When y'all're done y'all should see 12 forecast tables, 1 geography table, and 12 historical tables.

Metadata successfully imported into Cognos

Using Specified Zip Codes

The Weather Company data returns data grouped on a concatenation of postal lawmaking and country lawmaking. For example, downtown Chicago's key is the combination of zip code and country code: "60601:US". If you want data for a specific expanse, you need to include key values.

Beneath I will demonstrate to you how I created a dashboard showing the boilerplate daily temperatures for all nothing 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 goose egg code only, while postalKeyCountry is the cipher lawmaking and state code as previously detailed:

Excel workbook named Chicago_zips.xlsx

I uploaded Chicago_zips.xlsx to Cognos into the aforementioned folder that holds the Atmospheric condition Company sample.

Similarly, I created some other Excel workbook named 2020-03.xlsx with the dates of the first 15 days of March 2020. These dates volition control the days for which weather data can be retrieved. I uploaded the 2020-03.xlsx and placed it in the same folder

Excel workbook named 2020-03.xlsx

Retrieving IBM Weather condition Information in Information Module(s)

To call up the weather data I created a new data module and chose the "Weather Visitor" data server.

New Data Module Created in Cognos


For this demonstration, I am only adding tables from the Geography and Historical schemas.

Adding Geography and Historical Schemas to Cognos Data Module

I accept selected the Locations table from the "Geography" schema and the Temperature tabular array from the Historical schema.

Selecting Tables for Cognos Data Module

The Locations table contains the fields state, postalKeyCountry, postalKey, latitude, and longitude for each country and postalKey.

Cognos Data Module with recently added tables

The Temperature tabular array 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 total 24 periods of each day.

Cognos Data Module with Temperature Table

I added the Excel workbooks as boosted sources and verified that the expected data is available.

Creating Relationships Betwixt Tables & Fields

Now I created relationships betwixt the Excel workbook tables and the Weather Company data fields. First, I linked the 2020-03 dates with the Temperature information.

Creating Relationship between Excel Workbook and Weather Company Data Fields in Cognos

The match between the tables is on the Date fields in both tables. I set the join type every bit an inner join, one-to-many with 2020-03 dates equally the one and Temperature equally the many. Notation that I likewise set the bring together to Unique Values: this will come in handy afterwards in the procedure.

Since at that place is a large amount of data in the Temperature table – there is no filter on the locations and then all locations for all dates in 2020-03 are available – and so the data service volition not return any data. This behavior is expected and does non bear on whether the bring together is valid.

Creating relationship between Dates and Temperature tables

Next, I joined the Chicago_zips table and the Temperature table to match on the postalKey field. Strangely, the Temperature tabular array uses the nix code plus state lawmaking 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 at present the sample data from the Match returns valid information. The date join to the 2020-03 table – with Unique Values set -- selects a specific 15 days of information, and the link to the Chicago zip codes further restricts the data returned.

Creating relationship between Chicago Zip Codes and Temperature tables

With the advisable tables now joined, I saved the data module equally "Weather DM" to the samples binder.

Saved Data Module as "Weather DM" to samples folder

Creating a Conditions Dashboard

Now, I created a dashboard that will allow me to plot the boilerplate daily temperatures for any and all dates in the 2020-03 tabular array. In the new dashboard, I dragged the Engagement field from 2020-03.xlsx and set it to display as a list.

Date Field displayed as a list on Cognos Dashboard

Then I dragged over the postalKey in the Chicago_zips table, which contains the zip code but, and the TemperatureLocalDayAverage field from the Temperature table. At this point, I now have a color-coded map of all the boilerplate temperatures for all aught codes in Chicago which tin be filtered on a appointment. When I move my cursor inside a zip lawmaking area I see a tooltip with the data.

I can as well filter on a range of dates. I ready the aggregation of the boilerplate temperature field to Average, so when I select multiple dates and hover over a goose egg lawmaking area I see the average temperature for those dates.

IBM Cognos Analytics Dashboard with The Weather Company Data

Discover that the tooltip shows an extraordinarily high temperature, in the high 200s for the serial of March days. The Temperature tabular array fields render values in Kelvin, which starts at absolute cipher. Absolute zero is -273.15 degrees on the Celsius scale and -459.67 degrees Fahrenheit.  To catechumen Kelvin to Celsius subtract 275.xv, or to catechumen Kelvin to Fahrenheit multiply by 9, divide by 5 so decrease 459.67.  Equally an example, 274.65 degrees Kelvin would exist 274.65 - 273.15 = 1.5 degrees Celsius, or 274.65 * 9 / 5 - 459.67 = 34.vii degrees Fahrenheit.

Conclusion

I hope this article gives you a taste of the possibilities when you employ Conditions Company data.  Give thanks you for your fourth dimension!

Next Steps

We hope you lot institute 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 y'all have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.

Featured

AUTHOR: Chris Chen

A 30 yr veteran of the tech industry, Christopher Chen has worked with organizations such as PepsiCo, PNC Bank, Andersen Consulting (now Accenture), Groupon, Northwestern Academy, and the United Methodist Church building. He joined PMsquare in 2019 as a BI/DW consultant, fording information streams and wrangling spreadsheets in search of concern insights. In his spare fourth dimension, Chris collects terrible Dad jokes, the punnier the amend.

LinkedIn