I’m pleased to announce that TM1 Tutorials.com has had a complete rebuild and today we have launched the new site, just in time for IBM Information On Demand (IOD). Please head on over to the new and improved TM1 Tutorials.com and see for yourself what all the fuss is about! You can also track our progress at IOD by following @TM1Tutorials on twitter.

The new TM1 Tutorials website is now focuses on the IBM Business Analytics solution stack consisting of:

IBM Cognos TM1

IBM Cognos BI

IBM Cognos Disclosure Management

IBM SPSS.

We have our data-scientist Vangelis working hard on some really impressive SPSS Predictive Analytics tutorials, the first of which has been added to the archives.

4. New installation wizard consistent with the other versions of Cognos.

5. IBM Cognos Dynamic Query Analyzer (DQA) is now available for Cognos Express.

6. IBM Cognos Analytic Server (ICAS) Operations Console. Or else TM1 Top Utility on steroids. The ICAS Operations Console is installed as part of the standard installation and is run from a web browser.

7. Cognos for MS Office add-in capable of retrieving content from Cognos reports, is now available

8. Support of new operating systems, Windows 8 32 and 64 bit and Windows 2012.

9. In Cognos Express 10.2.1 Advisor and Data Advisor are deprecated. This means that they are available in the current release, but will not be available in future releases.

Below is table that sheds some light on the three different versions of IBM Cognos.

Got a chance to play with maps in Cognos reports for a recent PoC and learned quite a lot in the process. Most notably, I integrated OpenStreetMap in Cognos report, so I’ll demonstrate how to do it. Looks like this:

Cognos and maps overview

So you’re up to doing some maps in Cognos reports? I was always convinced (and still mostly) am maps are rarely a good graphic medium apart from “wow”-effect, but it’s quite magical to look at the same data on a map, get’s even to me -)

And my general feeling is that if location data is really important to your organisation, you would definitely have a serious GIS system in-house and wouldn’t be looking for “just a map report” in Cognos -).

I’ve met some strikingly well used location analytics, for example one of my retail customers was using a dataset with car purchase history and using owners addresses & car price to colour suburbs for selecting retail outlet positions, that was very impressive feat. You should contact guys from ESRI if you want to hear a proper location analytics pitch, but I’m still thinking that it’s just a quick way to score some points with users, but then again, if it’s a quick way and an easy one, why avoid it?

There are quite a few options you can go with if you want to embed maps in your reports:

Serious solutions

Built-in MapInfo maps

ESRI

Javascript maps in reports, most notably:

Google Maps

Bing Maps

OpenStreetMap (OSM)

And since it takes time for these things to sink in: “Google Maps and Bing Maps are not free to use”. OSM maps are

Serious solutions

Let’s start with heavy-weights. Go this way only if you really have the itching mapping need (if so you probably have one of them in-house already) and a bit of a budget, both come with a price tag. And that’s the reason I don’t have any experience with them apart for demos -)

MapInfo

Cognos has a built-in mapping capability based on MapInfo maps ever since Visualizer days. It ships with a few default maps (world map, US states) and you can use MapInfo Professional to create gst files for additional maps or you can buy them from MapInfo directly.

ESRI

ESRI has a full-blown over-the-top Cognos Integration (you get ESRI maps in Report Studio Toolbox and in most other studios) with no code-writing required. Good client and easy to use once you’ve set it up, if you plan to do a lot with location-based reports, ask for a demo and a quote.Heaps more on their site

Javascript maps

More affordable (to a certain degree) solution is to use one of the web mapping service providers and integrate Cognos report data with a map on-flight using Javascript.
There are heaps of examples for both Google Maps and Bing (see links below), but I must repeat that both of them are not-free-to-use. You can check the legal clauses yourself (Bing and Google Maps), but the only way to qualify for free usage is to have Free and Public access under a specified quota of map calls. You need to contact Google or Microsoft for correct pricing, but judging by internet rumours it start with 10k$ per year.

While Google maps were free, a lot of Google Maps and Cognos examples got published, here are some of them:
With coordinates set in latitude and longitude:
http://www.ibm.com/developerworks/data/library/techarticle/dm-1005googlemapscognos/
With client-side GeoCoding:
http://www-01.ibm.com/support/docview.wss?uid=swg21367426
http://www.ironsidegroup.com/2011/10/17/embed-google-maps-in-ibm-cognos-reports/
With polygons
http://sebastianmai.blogspot.com.au/2010/01/google-maps-polygon-overlay-with-ibm.html
With embedded report inside by using CMS:
http://www-01.ibm.com/support/docview.wss?uid=swg21430941

OpenStreetMap

OpenStreetMap is a crowd-sourced map of the whole world, meaning that it’s updated by users all the time (I just saw a bypass in remote town show up on OSM before Google Maps were updated), which is free to use and licensed so that it will be free to use in the future as well. There’s nothing fully free in this world, so there are some caveats I’ll highlight along the way, but, in general, you can use OSM without paying a dime and you can even host your map server inside your network (making it available in case your users don’t have internet access / you want to limit web traffic / or are security paranoid).

Sadly, there’s no Cognos + OSM recipes out there yet, so I’ll try to correct this.

Before we proceed, we’ll need a bit of common map related-lingo.

Terminology
Geocoding

In the world of maps you operate with geographic coordinates (latitude and longitude) of a point, but in your data it’s usually street address or a postcode. Converting addresses to geographic coordinates is called geocoding. It’s absolutely vital to geocode all locations you want to show before you do any report development. You can do online geocoding, but it’ll be terribly ineffective (geocoding will be called by every client running the report), slow (image the time it takes to convert you 1000 stores to points) and wouldn’t work at all (most online geocoders limit the load, Google geocoder wouldn’t allow more than ~10 requests per second so geocoding 1000 points would be just impossible).

It’s best to do your geocoding during ETL and store results in the dimension so you can easily use them in map reports you require.
And another licensing warning, you can legally display google geocoder results only on google maps, so it’s best to use one Nominatim (unsurprisingly, based on OSM data) if you want to use the coordinates anywhere else later.

Tiles

When you see a map in a webpage, it’s actually composed of multiple small square images called tiles. Map servers know what set of tiles to show user based on level of detail and map position. Here’s a fantastic explanation from Bing team.

You can imagine the amount of traffic tile serving requires, so there are only a few “free” tile servers (most notably, MapQuest I’m using in this example). You can register for free at CloudMade and try out their tiles, some people like them better.

If you have really high usage (or are absolutely paranoid), you can build your own tile server from OSM absolutely for free. If you do this, outside connection for maps will no longer be required, your maps will render from this server. Then you set up weekly updates to get map changes and you’re absolutely set to go.

You can even make your own tiles using free Tilemill and draw your own region / country borders or anything you would like. Game developers use this to draw “zombie” haunted city, overlaying it with real addresses and streets.
And while we’re at it GADM looks like a fantastic collection of official region definitions, there’s definitely the most detailed (60 Mb of shape files) “official” map of Vietnam that I could find for free.

Markers

Markers are the points that you put on the map and it’s quite easy to use non-standard icons, making the map a lot more appealing. You can conditionally change marker icons / colours.Here’s a good collection of free markers.

OSM example

And, finally, the main dish.

I’ll base this example on “sales and marketing” cube from standard cognos samples and what we’ll do will resemble Ironside recipe, but without geocoding, we’ll use predefined coordinates.

We’ll build a list of countries and draw a marker for each of the countries capital. I’m using Leaflet.js for mapping, a really nice mapping library. OpenLayers would be another option, but leaflet looked easier )

define latitude and longitude with following expressions
#
/*
Calculated latitute of country capitals
*/
#
case (caption([Retailer country]))
when ('Australia') then (133)
when ('Austria') then (13.3333)
when ('Belgium') then (4)
when ('Brazil') then (-55)
when ('Canada') then (-95)
when ('China') then (105)
when ('Denmark') then (10)
when ('Finland') then (26)
when ('France') then (2)
when ('Germany') then (9)
when ('Italy') then (12.8333)
when ('Japan') then (138)
when ('Mexico') then (-102)
when ('Netherlands') then (5.75)
when ('Singapore') then (103.8)
when ('Spain') then (-4)
when ('Sweden') then (15)
when ('Switzerland') then (8)
when ('United Kingdom') then (-2)
when ('United States') then (-97)
else
(0)
end
#
/*
Calculated latitude of country capitals
*/
#
case (caption([Retailer country]))
when ('Australia') then (-27)
when ('Austria') then (47.3333)
when ('Belgium') then (50.8333)
when ('Brazil') then (-10)
when ('Canada') then (60)
when ('China') then (35)
when ('Denmark') then (56)
when ('Finland') then (64)
when ('France') then (46)
when ('Germany') then (51)
when ('Italy') then (42.8333)
when ('Japan') then (36)
when ('Mexico') then (23)
when ('Netherlands') then (52.5)
when ('Singapore') then (1.3667)
when ('Spain') then (40)
when ('Sweden') then (62)
when ('Switzerland') then (47)
when ('United Kingdom') then (54)
when ('United States') then (38)
else
(0)
end

IBM has just released an interim fix for TM1 10.2 and provides important corrections. According to IBM this fix should be applied to all installations.

It includes the following fixes:

Number DescriptionPM97736
PM does not respond after opening a view that was migrated in from a Cognos Planning application
PM97742
Default sandbox data does not appear on a node when two nodes are open and edits made in another node
PM65978
ISB: problem with date-format

A fix pack is a cumulative collection of fixes. Fix packs are made available periodically. Fix packs are typically available for all platforms.

Interim fixes

An interim fix is a cumulative collection of fixes that arise between fix packs. Interim fixes are typically supplied in response to specific customer reported problems and they are available for a subset of platforms.

Feature updates went largely unnoticed with BI 10.2.1 FP1 release. It’s highly unusual to have major feature updates in fix packs, they’re usually held up to releases. But in this case we’ve got Rapid Analytical Visualisation Engine (RAVE) available in all reports, not only Active Reports (as per core 10.2.1). And that’s big news worth spreading!
So now you have 3 different charting engines 10.2.1 BI:

the “old” / legacy Cognos 8 charting

the ”new” Cognos 10 charts

RAVE visualisations

I couldn’t resist and dropped them together in one report:
It’s now a good time to say that film title reference in post name is completely accidental, as non-native speaker I just can’t walk away from obvious word play.
But jokes aside, which one do you like best? I intentionally used default settings in all charts (didn’t have much choice with RAVE, though).

Palette in Cognos 10 charts is really misleading, blue bars on the left seem darker than on the right because they “blend” in with orange. It looks like there’s more than 2 colours in the bars. I understand the idea of trying to make palette softer, but it looks bad anyway. And box borders don’t help either.

RAVE — please just shoot me, it’s joke )

It’s all customisable, but defaults got way worse along the way, imho.

More about RAVE

RAVE is the graphical engine that powers ManyEyes, an online visualisation engine launched in about 2008. Back then visualisation + web 2.0 collaboration sounded like future, so there were a few projects that were aimed on providing online collaborative data exploration tools (anyone remembers Swivel?). As it turns out, most of the people just wanted a place to quickly build basic visualisations to embed in their posts/news and didn’t care so much about others (surprise), which lead to eventual demise of most such projects.

Looking back, the hardest and most frustrating part of this development exercise turned out to convince IBM legal to approve a public (!) website where people could upload any data they want (!) for anybody else to see (!).

ManyEyes eventually got stacked into IBM’s Business Analytic division and shelved somewhere, up until competition from “visualisation” vendors such as Tableau and Qlickview forced IBM to find some suitable answers. We’ve got Cognos Insight’s viz capabilities at first and then somebody just realised that they already had a viable viz engine just around the corner, so rendering engine from ManyEyes got dusted and repacked into Cognos stack. And that’s wonderful, because it’s easily extendable and customisable, so creating new visualisation types should be heaps easier than adding them to “core” charting engine. And it’s obviously scalable
Right now, though, RAVE customisations are severely limited, you can’t change a chart’s palette, label colour, anything, even your initial chart type options are quite limited, there are no combination charts / dual axises. Check the list of available visualisations at AnalyticsZone.

When I started this site as a freelancer I was happy to cater to the need for non biased technical resources which solve real world problems. Back in Jul 2012 I joined InfoCube Consulting (Australia) as Director and Principal Consultant with the intention of creating something bigger than myself. I’m happy to say that now we are a team of over 10 expert consultants and we are growing quickly. So…

If you ever feel the need for expert advise in the IBM Cognos space, Why not call in the consultants who have been pouring their expertise into sharing best practice development for years. If geographically possible I will be more than happy to attend our first meeting and discuss how we can solve your immediate needs.

In this article we are going to introduce to you the new Excel Client for TM1 called Cognos Analysis for Microsoft Excel (CAFÉ). For those of you that have been using IBM Cognos BI you might think that this is not new and that you might have been using it for years. But it is new for TM1 users because IBM Cognos Analysis for Microsoft Excel is now integrated with IBM Cognos TM1 data sources! And I might add that it looks good too!!

CAFÉ can be used as a perspectives replacement for end-users and possibly in the future it will replace perspectives! It is an excel add in, as perspectives but it looks and feels more modern and updated while perspectives has been static for a couple of years without any significant improvements. One big advantage that CAFÉ has over perspectives is performance. WAN performance has been significantly improved in CAFÉ. All the basic functionality that you expect from a TM1 excel client is there. Like sandboxes, write back and data spreading.

CAFÉ has to modes that you can work. Explorations and Flex Views

Explorations mode

The explorations mode is very intuitive and BI users will feel right at home because it feels like your are working with Business workspace advanced (former business insight advanced).

With the explorations mode you can:

Explore data or to create views

Drill down, drill up, drag and drop items from the analysis pane

Edit the sets that you include in the crosstab or list

Filter your data using the context pane

Use existing subsets to populate your crosstabs or create new subsets.

Create calculated fields

Create sets using mdx

Convert the exploration into a flex view

Contribute to a plan by directly writing back values or use excel formulas or spread values across the crosstab

Use Excel charts

Flex Views mode

The Flex Views feels closer to perspectives. You cannot use it to explore data but to create highly customisable reports. In the Flex Views mode you can:

Drag and drop multiple pre-made views from the analysis pane or use a flex view created from the explorations mode. You can also use views from different cubes in the same sheet.

Use the dimension tree in the analysis pane to drag and drop items into the columns or rows

If you know the member names you can directly type them in the rows or columns and café will get the values

In this mode you can use excel formulas to create calculations

Use excel charts

Have multiple views in one excel sheet and you can use excel references to connect them

Contribute to a plan by directly writing back values or use excel formulas or spread values across the crosstab

Finally, for experienced users, it comes with its own API that you can use to automate the refreshing or publishing of workbook, document, and presentation content. You can also create VBA macros or use a scheduling tool to automate things.

as of TM1 10.2 you no longer get tm1top available to you.On the bright side though there has been massive improvements to OperationsConsole.This of course gives you monitoring on your TM1 service but not the whole box.A really useful and not expensive option is to use a complete monitoring tool like http://www.mobilepcmonitor.com/This way you can even log onto the box via your iphone to restart any service.

Not many people realise that Cognos Workspace can be setup to see the TM1 Application folder and Cubeviews. This means that users can drag/drop websheets and cubeviews onto their workspace and combine them with BI reports.

Essentially this is an evolution of the tm1 portlets capability.

To set this up on your server you need to modify 2 files.

Tm1_contribution.atom = This is to add the Tm1 instance to workspaceTm1_en.properties = this is to give the instance proper name

Be aware the 10.2 requires you to edit some further files but its all explained in the documents. Cognos BI will also require a restart to kick in the new settings

I have been doing quite a bit with CDM (Cognos Disclosure Management) and integrating TM1 as a datasource.A key thing for people to be aware of is CDM requires an Alias set in TM1 called MEMBER_CAPTION.The easiest thing to do is create the alias and then use a rule to dynamically populate the value from an existing Alias

In this new version of TM1, 10.2, a new feature has been added to enable the change of workflow state of a node to trigger TI processes. A preset TI process can be triggered immediate before and immediate after the workflow action.

This feature can be quite useful in a lot of ways, for example, if you have a separate BI report cube setup, when a node is submitted, TI process can be triggered to update the BI report cube without extra actions. And once all the nodes are locked, then you can be sure that BI reporting cube is ready for reporting, without running extra processes.

Here are few simple steps:

In Application Design mode, double-click on an application that you wish to update.

In the Properties pane, scroll down to select Custom Processes, and click on the Ellipse … to bring up the Custom Processes Settings screen.

Here you can specify which workflow actions can be enabled to trigger a TI process, either immediately before or immediately after the action, as well as the process need to be triggered.

There you go, a very useful feature that requires just a few simple steps. This feature can potentially enhance and streamline the business planning processes.

Some examples of how this feature can be used:

Updating BI Reporting Cube: To ensure the BI reporting contains the latest planning data, TI process can be triggered immediately after a node is committed or submit. A process is triggered to update a separate reporting cube (it would not matter if it is on the same or different server instance) from the planning cubes. This would not require separate action to update BI reporting cube (or improve efficiency in case your reporting cube is linked by rules).

Import latest actuals from ERP: To ensure the user is seeing the latest and greatest actuals from your ERP, a TI process can be kicked off to import actual financial data from EPR just before user takes ownership.

Notify other users when a node is committed: When there are multiple contributors to an application node, can utilise a TI process to notify all relevant users that data is updated, and peer review may be required.

Trigger report distribution/printing: When a top consolidation node is locked/submitted against a business unit, it triggers report distribution to all managers for that particular business unit. If all managers have access to the reporting portal online, this TI process can act as a notification mechanism.

Sending reviewer or contributor an email on submit or reject of a node: triggers a process to notify all parties involved.

Process to lock/unlock a node: In some cases, the business may want to prevent contributors from updating a node when it has been taken offline. A process can be triggered to lock access on the server to that specific node immediately after it has been taken offline, and a process to unlock it just before it is brought back online again.

The list can go on and on, it really depends on your business and application, and how this feature may enhance your planning process.

If you have missed it — there’s a new edition of Stephen Few’s Information Dashboard Design book. A must have and first edition is by far most worn-out Few’s book in my collection. Almost at a point where I’d be embarrassed to ask him to sign it,just had to buy a new one ) Not that we’ll meet any time soon ( Anyways, fantastic read so far!

Stephen is always repeating that BI vendors should step up and start embedding best practices in their products, not just make proper visualisations theoretically possible, but actively enforce them.
As a Cognos BI example, let’s look at a couple of charts.
If you like the right one (it’s ”scientifically” better from data/junk ratio), there’s a setting to turn off those black box borders in bar charts in Report Studio right here.
But by default it’s on and just tell me, how many times did you switch it off?