Archive for category Cognos
Have you ever added a header to a crosstab table, only to find it started to double your totals? This scenario may transpire if you have relational data that you are reporting on with hierarchy structures, as explained in my example. As long as you do not have a header, your totals should show up correctly. But if you add headers, your totals can easily be messed up.
Let’s say you have three levels of GL account codes you want to report on, with totals for each. Your report looks like this:
At this point, your report is very basic and it works as expected. But let’s say you want to show your hierarchies stacked in order to save space in the row headers (a common technique I use). You add headers and remove the descriptions on the first two columns so your report looks like this:
Now your totals don’t work anymore. Why?
In short, Set Aggregation, which is a property of your crosstab.
Set Aggregation is automatically set to “No” for relational data sources. So the crosstab is adding up your total (for your header), and then adding up your total again (for your footer), giving you the double effect. Change Set Aggregation to “Yes” on your crosstab and your totals will work properly again.
Set Aggregation defaults to “Yes” for dimensional data sources, so this problem only occurs for relational data sources.
(Special “The Colour and The Shape” edition)
In Part 1 of this series we introduced the Esri Maps for Cognos (EM4C) product, which enables us to tie together BI-type reporting with the rich capabilities of Esri’s mapping software. In Part 2 we demonstrated how easy it is to use this software to connect point-type
data in a Cognos report to a map. In essence, we can take points of data identified by lat/long values and connect them to a map, and then colour-code the points to represent different categories or types of data. In our example, we looked at crime data for San Francisco. The result enabled the user to make inferences from the geographic distribution and type of crime reports that would be difficult to make if the data were simply listed by address, or even grouped into neighbourhood categories.
In this installment, we will look at a slightly different way of displaying data within the context of geography – instead of displaying discrete points (which require lat/long values) we will categorize larger geographic areas, defined by shapes on the map.
Note that in this example we don’t have any “lat/long” type data here – instead, we have Retailer Province-State, which contains values representing the name of each state:
This time, instead of adding a Cognos X/Y Layer to our Esri map in the report, we will add a Cognos Shape Layer:
A Cognos Shape Layer acts similar to a XY layer, except that it binds data based on common descriptions between the report data to a map containing a “shape”, instead of lat/long points. In this case we set up the map associated with the “shape” layer to one containing shapes of States in the US. In the wizard provided we can match the shape names in the map we have selected (STATE_NAME) to the appropriate column (Retailer Province-State) in our query:
We select the measures we are interested in…
… and then configure the “shape join”, assigning colour-values to relative levels of each measure (in this case, Revenue):
We now have a map that lets us see, by quantile, how revenue compares by state:
For example, here is the map showing Gross Profit:
Note that the legend shows the quantile breakdowns for each colour. As well, hovering over each state brings up information on the state:
Users are not limited to a single shape layer – multiple layers can be combined on a single map, and then the layers activated/deactivated by the user to how different data by different “shape”.
Shapes are not limited to conventional maps, of course. Floor plans provide an ideal source of shapes. Retailers can use shapes to identify revenue by area of a store, or property managers can look at building usage, perhaps over time. All that is needed is a Esri map with shapes that correspond to the physical areas the user is interested in, and have an attribute that can be joined to a column in the report that contains values that match the values of the attribute.
(Special “If You’re Going To San Francisco” edition)
In part 1 of this series, we looked at how Esri Maps For Cognos – EM4C – allows us to embed a map from an Esri map server inside a Reoprt Studio report. But the map is pretty useless if it doesn’t allow us to connect to our data and perform some kind of analysis that can’t be done with a regular list report, or with some kind of graph.
From a mapping perspective there are a couple of concepts that we need to keep in mind if we are going to bind business data to a map: one is the idea of a point, the other the idea of a shape.
We’ll start with a point. A point is a lat/long value on a map: it is (strictly speaking) an entity with no area. It could be a point that represents a store location, a home address, whatever you like. The important thing to keep in mind is that even if a store (or your house) occupies area, from a mapping/point perspective it is simply a point on the map.
So what kind of data can we plot using points? Crime data is one example – a police call is typically to a particular address. If we can plot these locations on a map, by type, we might gain insights into what kinds of crimes are being reported not just by location, but by location relatively to each other – what kinds of crimes cluster together, geographically.
Crime data for San Francisco for March, 2012 is available on the web, and this data set comes with both category of crime and lat/long of the police report. This makes the data set ideal for plotting on a map.
First, I set up a quick Framework Manager model that retrieves the data from my database. Then, we need a query in Report Studio that retrieves the data:
Note that we have a Category, Description, and X and Y values representing Longitude and Latitude respectively.
I add a map placeholder (as we did in Part 1) and then save the report. (I could, of course, add any additional report items, queries etc to the report that I wish.) I then open the map placeholder in Esri Maps Designer, add a base map, and then add a new layer: the special Cognos X Y Layer. I rename it Crime_Locations:
A wizard enables me to select the query associated with the Crime_Locations layer, which will display points:
Note the inclusion of a Unique Field – this is the IncidentNum from the original data.
Further configuration allows me to then assign the Lat/Long from the data set, and identify each point by the Category of crime.
I now have a set of symbols – coloured squares – that correspond with the categories of my data. When I view my report, I can see the location of each crime, by colour-coded type, at each location it was reported at:
Even at this zoom level I can draw some conclusions about what areas have more crime – the north-east seems to have more reports that the south-east, for example. But by selection of specific crimes, and zooming in, interesting patterns begin to emerge.
The orange squares represent drug-related charges. The green and purple squares are assault and robbery charges respectively. The drug-related charges are more concentrated in one relatively small area, while the assault and robbery charges seem more spread out – but with a concentration of them in the area the drug charges are also being laid.
If we zoom in even closer, we can see that certain streets and corners have more calls than others in close proximity – that the crimes seem to cluster together:
But zooming out again, we see an interesting outlier – a rash of drug charges along one street, with what appears to be relatively few assaults or robberies:
Zooming in we see that this activity is almost completely confined to a 7-block stretch of Haight St., with virtually no activity in the surrounding area, and few robberies or assaults:
This kind of spatial relationship is extremely hard to discern from a list or chart, even a chart that breaks events like police calls down by geographic category of some kind. But using mapping, with a simple zoom we can go from an overall view of patterns of activity to a much higher degree of detail that begins to tell some kind of story, or at least warrant further investigation.
But wait, there’s more…
By hovering over an individual square, I can get additional category information from my underlying data, assuming I have included it in my query. In this case there is a sub-category of the call:
By adjusting the query I can re-categorize my data to yield results by, for example, day of the week, or sub-category. For example, here we can contrast Possession of Marijuana (green) with Possession of Base/Rock Cocaine (pink):
Marijuana possession seems more diffuse, although concentrated in a few areas. The cocaine charges are much more concentrated.
In our next entry in this series, we’ll take a look at allocating data to shapes, to colour-code areas to represent different levels of activity.
Cognos report writers have long been frustrated by the poor built-in support for GIS-type displays in Cognos reporting tools. True, there is a basic map tool included as part of Report Studio, but it is quite limited in functionality. It can be used to colour geographic areas, but lacks layering, zooming, sophisticated selection tools, and the kind of detail we’ve all become used to with the advent of Google Maps and the like.
There are a few map-related add-ons for Cognos reporting available. Recently I had the opportunity to take Esri’s offering in this space for a test drive with a 2-day training session at Esri Canada’s Ottawa office. I came away impressed with the power and ease-of-use offered by this product.
EM4C – Esri Maps For Cognos – came out of development by SpotOn Systems, formerly of Ottawa, Canada. SpotOn was acquired by Esri in 2011. The current version of the product is 4.3.2. The product acts as a kind of plug-in to the Cognos portal environment, enabling Report Studio developers to embed Esri maps, served up by an Esri server, in conventional Report Studio reports. From a report developer perspective EM4C extends Report Studio, and does so from within the Cognos environment. This is important: EM4C users don’t have to use additional tools outside the Cognos portal. From an architectural perspective things are a little more complex: the Cognos environment must be augmented with EM4C server, gateway and dispatcher components that exist alongside the existing Cognos components.
Then, of course, there are the maps themselves. Since this is a tool to enable the use of Esri maps, an Esri GIS server must be available to serve the maps up to the report developer and ultimately the user. For shops that are already Esri GIS enabled this is not a challenge, and indeed I can see many users of this product wanting to buy it because they have a requirement to extend already available mapping technolgy into their BI shops. However, if you don’t have an Esri map server, don’t despair – the product comes with out-of-the-box access to a cloud-based map server provided as part of the licence for the product. This is a limited solution that won’t satisfy users who have, for example, their own shape files for their own custom maps, but on the other hand if you have such a requirement you probably already have a map-server in-house. If you are new to the world of GIS this solution is more than enough to get started.
So where do we start with EM4C? First, you need a report that contains data that has some geographic aspect to it. This can be as sophisticated as lat/long encoded data, or as simple as something like state names.
When we open our report, we notice we have a new tool: the Esri Map tool:
As mentioned, the EM4C experience is designed to enable the report writer to do everything from within Cognos. Using this tool we can embed a new map within out report:
So now what? We have a map place-holder, but no map. So the next step is to configure our map.
This step is done using Esri Maps Designer. This tool is installed in the Cognos environment as part of the EM4C install, and enables us to configure our map – or maps, as we can have multiple maps within a single report.
Esri Maps Designer is where we select the map layers we wish to display in our report. When we open it we can navigate to any Report Studio reports in which we have embedded and Esri map :
In this case VANTAGE_ESRI_1 is the name of the map in my report; the red X indicates it has not been configured yet. Clicking Configure brings up our configuration. This is where we select a Base Map, and then link our Cognos data to a layer to overlay on the map.
As mentioned, out-of-the-box the EM4C product enables the user to use maps served from the Esri cloud. We will select one of these maps from Esri Cloud Services as the Base Map of our report:
When the base map is embedded, it becomes a zoom-able, high-detail object within the report:
Unfortunately, while the map looks great it bears no relationship to the report data. So now what?
In part 2 of this overview we will look at how to connect the report data points to the report map. It is the combination of the ease-of-use of BI tools (and the data they can typically access) with mapping that makes a tool like EM4C so powerful. We will symbolize data to created colour-coded map-points to reveal the geographic location and spatial relation data, potentially allowing users to draw conclusions they otherwise would not have been able to with list-type data.
If you have one Cognos Framework Manager model with multiple packages, what can you do to publish some packages using one data source and some using another? You want to avoid duplicating your current Framework Manager model because you want only one model to maintain. How can you set something up that will allow you to toggle between data sources?
Add all the data sources you want to toggle between to your Framework Manager model. Obviously these data sources will have to have the same underlying database structure or your model will not work. The best way to add them is through the Run Metadata Wizard, which will force you to add at least one table reference. You can remove that table reference when you have the data source added. You may need to set the Query Processing setting to “Limited Local” for your new data source to work properly.
Create a session parameter. This will become the global variable of which data source your model is currently pointing at. The value of this session parameter should be the value of the data source you want to reference for your current publish.
- Change all of your database queries to use the session parameter reference instead of the data source reference. So instead of this:
SELECT * FROM [My Data Source].MY_DB_TABLE
You should have:
SELECT * FROM #sb($MySessionParameter)#.MY_DB_TABLE
You should include the square bracket function in your session parameter call. You can alternatively add the square brackets to your session parameter value. Do not use square brackets outside of the function call – it will not work, at least not as I tested it.
Set your session parameter to the value you want for the Cognos package you wish to publish.
- Publish your Cognos package.
Repeat steps 4 and 5 as many times as you need to reset your data source and publish your packages. You can now easily toggle between your data sources and publish Cognos packages as needed.
If you are finding that you cannot get Cognos Framework Manager or Cognos Transformer to start on a particular machine, you may need to modify the system settings for Data Execution Prevention (DEP) in Windows. This problem may manifest itself as an intermittent problem or happen each time one of the Cognos tools is run, and can include messages like “bmtui2 has stopped working”. You can sometimes get around this problem by resetting the Cognos configuration encryption keys or running the programs as Administrator. But if you are running into this time and time again, the only permanent solution is to change the Data Execution Prevention settings.
This is a standard configuration setting under the Windows System settings under Advanced > Performance. Add the Cognos executable files to the DEP exception list and you should be all clear. The executable for Cognos Framework Manager is BMT.exe, and the executable for Cognos Transformer is cogtr.exe. These are in the C10 bin folder.
I recently had the opportunity to act as a facilitator in a Business Analytics Experience workshop. The Business Analytics Experience, or BAE, is a business simulation where executives and analysts from the real world get to try their hand at running a fictitious company for a few hours with a focus on business analytics. Through a Cognos 10 interface, they have access to the company financials, pricing and marketing strategy and various other parts of the enterprise. The simulation runs for 4 fiscal quarters, and in each the participants get to choose how they want to proceed with their corporate strategy. Each quarter the numbers are run and then they can review their decisions and see if they are meeting their targets or not.
It is a fun, interactive way to see business analytics in action. Even though the simulation itself is somewhat simplified compared to real life, it does offer surprising depth and complexity that can include additional modules as time permits. It shows how insights gleaned from business analytics tools can be applied to real decision making.
If anyone is interested in learning more about the Business Analytics Experience or would like to get information about booking their own BAE session, please contact me and I would be happy to see what we can do for you.
In a Cognos Report Studio crosstab, selecting an individual column by clicking on the fact cells gives you access to the crosstab intersection. You are able to format this crosstab intersection through the Cognos properties window and change the appearance of your column. This does not affect the appearance of your column footers, which can also be selected individually as crosstab intersections at the bottom of your crosstab. Formatting each individually will give you the ability to format the column as you see fit. To format these items together, you might select the column intersection first and then the footer intersection, giving you the appearance that you have selected the entire column.
But you haven’t, and this can give you trouble. For instance, if you have an unbalanced hierarchy, your selected format in your crosstab intersection may not be reflected in your processed report. In the following example, the third column has been formatted to show as a percentage using the method described above, but the second row is showing it as a raw number because of the blank hierarchy level highlighted.
So how can you get a whole column formatted? You need to select the whole column. This is done by right clicking on the column header, and then selecting “Select Member Fact cells”. You will notice that this automatically picks your column footers so you can format these items in sync. This will resolve the issue with the unbalanced hierarchy and will allow you to control column display across the whole crosstab.
There are some hurdles to jump in order to get your Cognos Express 10 install to talk to your Oracle 11g database. Although both of these programs are 64 bit, they communicate by 32 bit drivers. But installing the 32 bit Oracle drivers is not enough – Cognos also has to be able to find them.
If Cognos cannot find those 32 bit Oracle drivers, you can expect to see the following error: Unable to locate the gateway “cogudaor”. The “cogudaor” gateway is the Windows gateway that establishes connections to Oracle databases. This typically indicates an Oracle configuration issue, as I have written about before.
Once you have the 32 bit Oracle drivers installed, you need to add the location of that install to your system path directory, preferably at the start of the list. This way Cognos Express will be able to find it.
I attended a very interesting session at IBM Information on Demand on advanced Cognos reporting techniques. Its focus was not what I expected. Taking a page from Apple, the session speaker told us that our Cognos reports need to look good. They need to be aesthetically pleasing, interactive and easy to use. I don’t go out of my way to make ugly reports on purpose, but I do admit that I have a tendency to focus on the report numbers over the report’s look and feel. But the session speaker had a point – without reports that look and feel good, Cognos reports will not be adopted by users and will tend to not get used that much, if at all. The session went on to explain some interesting techniques for changing the look and function of reports, with active toolbars and layered graphic displays.
So try to bring a little bit of Apple to your next Cognos report. Make your reports look good, and in doing so bring some joy to your clients’ Cognos experience.