Archive for category Business Intelligence
Messing with Roles can be Dangerous Business
Posted by Scott Andrews in Business Intelligence, Cognos on November 5, 2011
In the Cognos 8/10 security environment, there are a number of pre-defined Cognos roles that under-pin the entire Cognos security infrastructure. Technically, you can move, rename or delete these roles but do so at your own peril! Cognos recommends not changing these security elements and I’ve had a recent experience that highlights exactly why.
A client of mine, in an attempt to clean up and manage their security environment, moved all of the predefined roles into another directory. On the face of it, this seemingly innocuous act appeared to be no big deal. But then they also installed Cognos Adaptive Analytics. The new Cognos product install did not see the predefined roles, and appeared to have recreated all of them in their original location. This of course caused duplication of all the predefined Cognos roles. The security environment became very confusing as it was very difficult to distinguish between the identically named roles.
Attempting to clean up the duplicates caused untold havoc with users rights and privileges. Some of the old Cognos roles defined certain rights and some of the new ones defined others. Deleting the copied Cognos roles removed most rights under the Capabilities tab and Public Folder rights. All told, a lot of clean up and reconfiguration was required.
So if you get the notion to change your pre-defined Cognos roles, take Cognos’ advice and just don’t do it!
Can’t anybody here play this game?
Posted by Peter Beck in Business Intelligence, Client Relations, Cognos, Information Technology, Tech Trends on October 6, 2011
Can’t anybody here play this game? – Casey Stengel
IBM has a new paper out on that age-old question – why do BI projects fail, and what can be done about it? The paper is entitled “Bridge The Gap Between BI Best Practices and Successful Real World Solutions“. The first few pages are the usual marketing fluff, and they generally contradict the “meat” of the paper, which begins a little further in. That is, once again, we see a particular technical/product solution proposed to solve what is not a technical problem. This is accomplished by simply asserting that this particular technical solution maps neatly over the business problems Gartner has uncovered. If you are brave enough to hack your way through to where the Gartner material begins there are some interesting discoveries to be made. By “interesting” I mean “depressing”. Taken as a whole the paper can be thought of as a fine example of what the Gartner research itself reveals.
The paper begins with a set of now-common observations: that BI programs need a business sponsor, that IT ends up “selling” BI to the business (and doing it badly), that BI tends to get “stuck in reporting”, and that “Technology is rarely the culprit if the BI project is considered a failure”. All well and good. And then at page 2 we read, in bold, all-caps:
IBM COGNOS EXPRESS: THE KEY TO A WINNING BI STRATEGY
I see. IBM’s technology will be the “key”. That’s a relief. Gap closed! Close the document and move along.
But if I keep reading, I discover that the folks at Gartner have done some research on the practice of BI programs, most of which are not particularly related to technology (on the contrary.) The results aren’t good. That doesn’t mean they are surprising, of course.
The Gartner section of the paper is called “The BI(G) Discrepancy: Theory and Practice of Business Intelligence”. They break out 9 aspects of BI implementation, and discuss what should be done in each aspect, versus what their research indicates is actually taking place in the real world. The results are a confirmation of what most of us “in the trenches” feel intuitively: there seems to be little correspondence between what should be done, and what actually is done. And technology isn’t going to change that.
The whole thing is worth a read, but the most eye-popping turns out to be the discussion of - BI strategy! That thing that the latest IBM product will provide a “key” for! Turns out only 2% of organizations informally surveyed in mature markets had anything called a BI strategy. That’s not a typo. 2%. And this is among Gartner clients. Let that sink in for a second, and then consider this quote from the paper:
“Nearly shocking results are obtained when reviewing the so-called BI strategy documents. Almost never would those qualify as strategy in Gartner’s opinion. Quite often a strategy is merely a statement like “We have a Microsoft BI strategy” or “Our BI strategy is SAP” indicating what products the organization is using or planning to implement. Other times the “strategy” is merely an architecture diagram… This is as if the Ferrari Formula 1 team described its racing strategy as “using Bridgestone tires, Shell fuel, a V8 engine and red paint.”
I like the use of “Nearly” to suggest seen-it-all unflappability on the part of the author.
The analyst goes on to describe the initial 2% number as “rather optimistic” (raw-ther, old sport!), blows some dust off the dictionary definition of “strategy”, and then (perhaps beginning to get a little exasperated, and reaching for the bottle) muses that:
“The question could be expanded to: Do executives even understand what constitutes a strategy?”
Yes! It does appears that the question could indeed be expanded to that!
Everyone, and I mean everyone, I have ever encountered in this industry who works above the level of writing reports struggles with the problems outlined in the Gartner material every day. And yet here we are, decades now into the world of BI, and it doesn’t appear to be getting any better. BI still seems to be mired in confusion as to what it is – what is its identity within the organization. The default position seems to be: it’s a technology. IBM et. al. seems ok with this, and I can’t blame them. As long as the discussion can be returned to “BI is a product (and our product is the best!)” they seem to be happy, as they have a tangible thing to sell. My own feeling (obviously) is whenever the real answer to this question is found, it won’t be “Cognos” or “Microsoft Analysis Services” or any other piece of software, and I say this as someone who spends his days with the products in front of him.
If executives don’t have a grasp of the rudiments of BI strategy (or perhaps strategy in general), it seems that the best anyone can do is try to keep pushing technology. At least that seems to be what IBM’s “strategy” is with this document – provide a high-level summary, name the product and map it to what is “supposed” to happen in an organization, and hope for the best – and that no-one keeps reading. Or what the Gartner analyst, in the section on what goes on in the real world when it comes to the business case for BI, characterizes as a “leap of faith”. I’m not kidding, they actually use those words to describe what Gartner clients are doing to justify their BI investments.
Check the paper out, it’s worth a read.
The Meaning of Data Quality
Posted by Scott Andrews in Business Intelligence, Data Quality on August 8, 2011
Some interesting discussion on my last post got me thinking on the meaning of data quality. What does quality data really mean? Is it free from errors and omissions? Are defined terms used consistently throughout the data set? Or is it something even larger than that – does the data accurately reflect the reality of a business?
Errors and Omissions
This is the most obvious category of data quality problems. Errors at source will never be accurately reflected in any business intelligence system, but these errors will appear plainly in the source system too. An order to the wrong customer or for the wrong quantity will simply show as it is. Worse are omissions – for these prevent data rollups from functioning as they should, and source system may not even necessarily be concerned about these data items. Is an omitted field a “zero” or “unknown”? This is a critical question because of the basic arithmetic behind it. 1 plus 0 equals 1. But 1 plus unknown equals unknown. Throw in a few thousand unknowns and your summarization is invalid. Proper handling of unknowns requires input from your business analysts.
If enough unknowns pollute your data set, you may have major data quality problems – to the point that your data set may not be useable. Early reports from Canada’s ongoing 2011 voluntary “census” indicate that this will be a problem, one that was widely anticipated by data professionals. Data profiling can help determine the extent of errors and omissions in a data set.
Defined Terms
In my line of work, this is the most common problem I see in data quality. This is where one group or department uses a term for something, and another group or department uses a similar or identical term for something else. When these groups get together to discuss data, they invariably accuse each other of having “bad” or “invalid” data. Differences can be attributable to simple terms or codes, dates (sales date, payment date, shipping date etc.), or even periods of time (fiscal dates, annual dates etc.) This is essentially a problem of communication, not the data itself. Thomas Redman writes extensively on this issue in his book Data Driven.
But what about Business Reality?
What managers are really looking for in their data is a picture of reality. Sometimes the data set does not capture this. A good example of this happened to me a few years ago when I was dealing with the inventory system of a large corporate client. They were taking daily snapshots of inventory from their warehouses, but one of their warehouses was off-line each and every weekend. In the case of the off-line warehouse, inventory changes were reported each Monday, but the Friday, Saturday and Sunday midnight snapshots were not correct. Even though their business was running on a state-of-the-art SAP system, their inventory data did not reflect reality. In order to solve this conundrum, we resorted to reading transactions from the General Ledger and inserting adjustments into the inventory snapshots. It was a round-about solution but it worked. Of course, one could have suggested that the warehouse be on-line 24/7 like all the others but often data processes have to accommodate the business, not the other way around.
My point on this subject is that data quality can be bigger than the data set itself, and sometimes a larger view needs to be taken to see the reality of a business situation. Even perfectly clean and correctly recorded data can be wrong if it doesn’t match the business’s reality or meet the business’s needs.
Cognos 10, Report Bursting and Saving Output to File
Posted by Peter Beck in Business Intelligence, Cognos on August 7, 2011
(The instructions below present setting up C10 for output to a file location on the network within the context of bursting reports, but there is no reason you can’t set up file output for the normal manual or scheduled execution of reports – PB)
Cognos 10 (like all versions of Cognos BI since ReportNet) has a fairly straightforward way of configuring a given ReportNet report for “burst” output – that is, for generating a set of reports from a specific report specification, where the only difference between the reports is some selected value. Consider a generic sales report, where we have 2 different sales reps.
We might want to “burst” the report across the sales rep identifier, so we would get one report for each sales rep. We could then distribute each report to the appropriate rep.
Setting a report up for bursting is performed in the Report Studio interface. Under File… Burst Options we set how the report will burst. We also have the option of selecting how the report will be distributed – either as an email or as a Cognos directory entry. The value for the both the burst specification and the distribution must come from a query in the report.
However, it is quite possible that we might want the output to go out to a file location instead. To set this up requires a little bit of configuration, but it is quite straightforward. In versions of Cognos BI prior to 8.3 this was a bit limiting – we essentially had only one destination we could output to. In even older versions controlling the name of the output report was a pain as well – we needed secondary scripting to re-name the report in the output file based on an associated XML file. This is no longer necessary.
Note about the instructions below: this is not limited to burst output – setting up C10 for file system output can be useful for saving any report you run to the file system – a manually run report, a scheduled report, or burst report.
First, we need to create a shared folder on our server. This can be any name, but should not be located in the installation directory. The user under which the C10 service runs must have full rights to the folder. In this case I’ve created a folder called CognosOutput.
Now I must start Cognos Configuration, and navigate to Actions… Edit Global Configuration:
Under General, I enter the value of my \\server\share combination, prefixed with file://
Click the Test button, and then OK.
Returning back to the main configuration screen, select Data Access… Content Manager, and set the Save Report Outputs… value to True
You are now set up for report output. IBM notes that it is very important that you not be running your Cognos installation as “localhost”, but rather under the name of the server the service is running on.
These steps have set up the top-level directory under which we can save report output. Within Cognos Connection we must now define what the actual destination output locations within this folder will be.
Open up IBM Cognos Administration from the Launch menu in Cognos Connection. Then navigate to the Configuration tab and select Dispatchers and Services, and in the upper right side of the screen select Define File System Locations:
Give the new location a name under the Name section, and (optionally) a description and screen tip. Finally, give it a location – this is where it will appear under the output file folder you set up above. You can use the “\” character to nest a folder beneath another folder. You do not declare the top level folder, so in this case NewOutput could be used as a location, but not CognosOutput\NewOutput.
Now you are ready to burst the report to the file system! Select Run with Options for the report in Cognos Connection, and under Delivery method select Save the Report. Then click Advanced Options and on the the next page, select Save To the Filesystem, and select Edit the Options
In this case I have selected “New Output”, which I have set up to output to NewOutput/NewOutput1 on my file system. I have also renamed the report to August_Sales_Reports
Select OK, and select Burst The Reports from the radio button on the lower left side. Then click Run.
The reports will now be burst to the CognosOutput/NewOutput/NewOutput1 folder:
A couple of quirks: Cognos will append the language setting to the name of the report. It will also append the value by which the report was burst (useful for organizing the reports). It will also output a second XML file that describes the report.
The Information Continuum
Posted by Scott Andrews in Business Intelligence, Data Quality on July 25, 2011
Recently an American citizen was turned away from a Canadian border crossing for having a 20 year old conviction related to a high school prank. An immigration lawyer commented that he was not surprised as “the databases are getting deeper and deeper”. The Globe and Mail complained that this case and other similar ones fail to accomplish the goal of keeping Canada safe from real criminals and terrorists. While this decision appears frivolous or even silly, I think it highlights the differences between data, information and business intelligence.
The Information Continuum
Data – A Fact or a piece of information, or a series thereof
Information – Knowledge discerned from data
Business Intelligence – Information Management pertaining to an organization’s policy or decision-making, particularly when tied to strategic or operational objectives
So when hazy, ill-formed or poorly constructed objectives are tied to simple data access, bad business intelligence can be the result. Let’s consider two strategic objectives:
“Keep Canada safe from real criminals and terrorists”
(as suggested by the Globe and Mail editorial)- This leads to questions like
- What is a real criminal or terrorist?
- How can they be identified?
- This might lead to
- Information sharing with FBI and Interpol
- Data sets might include
- FBI’s most wanted list
- Interpol’s wanted list
In this case, the Information Continuum is approaching the Business Intelligence level.
“Keep convicted criminals out of Canada”
(the apparent objective applied in this case)- This is a blanket statement that will catch everyone ever convicted of any crime (assuming the data is available), regardless of circumstances
- It invites no further questions
- You will have false positives (prior convictions with no future criminal intent) and false negatives (active criminals with no prior convictions)
- In this case, the Information Continuum does not move beyond the data level
Simple data access will not make better business intelligence – if your people don’t have the right resources and direction, it could make it worse. Your analysts should be able to distinguish between real information and noise, and they should always be striving towards your organization’s clear and thoughtful objectives.
SQL Server Analysis Services Cubes and Cognos PowerPlay
Posted by Peter Beck in Business Intelligence, Cognos, Information Technology, SQL Server on July 11, 2011
SQL Server Analysis Services is a popular OLAP product included with Microsoft SQL Server. Especially since SQL Server 2005 this product has been quite powerful and fairly easy to develop with. SQL Server provides the Business Intelligence Development Studio (BIDS), a Visual Studio –like product to aid the development of Analysis Services cubes.
For browsing and reporting on a cube, however, choices have been more limited. Excel provides a good choice, especially since Excel 2007, which contains enhancements that make creating cross-tab reports easier than previous versions.
If your users are committed to Cognos PowerPlay, you can use this tool as well. Setting up a MS Analysis Services cube for browsing with PowerPlay is a little more involved than a regular Cognos cube, but is still quite easy to do.
First, you need to access a tool called PowerPlay Connect. This can be found in the Tools folder of your Cognos installation:
The executeable is ppconnct.exe.
This tool is used to create a binary “pointer file” with a .MDC extension. This file, once created, will behave like a PowerPlay OLAP Cube, but the underlying cube will actually be (in this case) a Microsoft Analysis Services cube.
Start PowerPlay Connect, select File… New to create a new MDC file. For the database type, select MS SSOS (ODBO):
You have a couple of choices next. If you know the server name for your instance SQL Server Analysis Services you can enter it in the next line, under Server:[Port]. In this case I can enter “localhost”, as I am serving the cube from my local machine.
Alternatively, I can select the … button beside Database, and I will be presented with the Chose a Remote Cube dialog box. In this case I then select Microsoft SQL Server OLAP Server at the bottom, and then select a connection I have already created previously using the tool. In this case the connection is called local. I’m then presented with a list of databases available on the connection “local”.
I can then open SSAS_Adventure_Works and the cube that exists in this particular database. A database might have many cubes available in it.
Alternatively I could create a new connection, by clicking on Connections… and then clicking Add. I enter the name I want to give the connection, and then the name of the server, and select Microsoft SQL Server OLAP Server and MSOLAP as the provider:
Since I selected the cube SSAS_Adventure_Works, we see this in the details of the connection string:
I can now click File… Save and save this as an .MDC file:
The file appears as a normal MDC cube, but is really just a pointer file to the SSAS database server:
Using PowerPlay, I can now open the MDC file as if it were an ordinary cube. I can navigate it generally in the same way I would navigate a Cognos cube, althought some things such as Measure Groups that are part of the Microsoft approach to OLAP do not behave exactly the same way. Meaures appear as a single list, much as they do in Cognos cubes.
PowerPlay Connect MDC files can be put on the network, or shared as any other file, and will work as long as the user has access to the underlying Microsoft database.
Cognos Transformer and Category Uniqueness, Part 2
Posted by Peter Beck in Business Intelligence, Cognos on February 2, 2011
In Part 1 of this series we examined how Category Codes are generated. For many users the “uniqueness” of these codes is never a problem, but if they change errors can result in reports that use them.
For example, if the categories are deleted and re-generated, Transformer may not assign the same value to the Category Code. Here is the result after deleting the category California from our example in Part1, and regenerating it:
Instead of CA~8, we now have CA~9. This causes the category to disappear from our Reporter-mode report from Part 1:
You can imagine the impact on users depending on reports with large numbers of dimensions that suddenly disappear on them. As a developer, if you don’t understand the impact of category code generation you can spend a lot of time scratching your head about what is going on.
So what can we do? The surest method is to always ensure that the Category Code is unique in a dimension by assigning a completely predictable value to it that will never change, and that you can count on as being unique at least in the hierarchy, if not in the entire model. By taking control of this value we are ensuring that it is not assigned by Transformer. This is probably best done in the database, during the generation of the dimension at the database level (not the Transformer “dimension”.)
As a quick, alternative method you can ensure uniqueness by creating a calculated column in the dimension that you know will be unique for the level. In this case I have created a calculated column for All_States called State_Cat, and one for All_Countries called Country_Cat.
These are calculations based on concatenating Country_CD and Country (and State_CD and State). Since these calculations will be unique in our dimension we can use them as sources for the Catergory Code in each respective level (only All_States is shown, but the idea is the same for All_Countries):
Now when we generate categories, we see that the Category Code for California is CACalifornia:
Because we are controlling this value, and not Transformer, we can be sure that this value will not change, even if the category is removed and regenerated in the Transformer model. This will ensure that reports that use this Category Code will continue to work correctly.
Cognos Transformer and Category Uniqueness, Part 1
Posted by Peter Beck in Business Intelligence, Cognos on February 2, 2011
One tricky and poorly understood problem with the deployment of Cognos Transformer cubes relates to the concept of uniqueness within a dimension. Developers and users can proceed for a long time without having any problems with the uniqueness of categories, and then suddenly – usually as a result of deployment of an enhanced version of an existing cube – user reports may start to behave strangely. Selected categories may disappear from reports authored in PowerPlay, or drill-throughs in reports created in C8 may no longer function correctly. Managing these category codes (“Member Unique Name(s)”, or MUN(s)) requires a bit of forethought that may be skipped over in the development phase
These problems may be rooted in the fact that Transformer insists on a unique identfier across all categories in a dimension, regardless of the level of the dimension the categories exists at. This can cause some subtle and hard-to-diagnose problems.
Consider a trivial “sales” data set:
We have a denormalized “flat file” of data with a hierarchy of Country … State … City. If we create a set of dimensions in Transformer using this data set, we might create something like this:
At the level All_Countries, the level is defined as shown:
At the level All_States, the level is defined as shown:
Note that in both levels the Category Code is left undefined. This will be determined by Transformer, and Transformer will ensure that it will be unique for the dimension. It will base the code on the value in the Source for the level.
This uniqueness is key, because if we look back at our data, we note that we have the value CA for both a Country_CD (Canada) for level All_Countries and a State_CD (California) for level All_States. We have used Country_CD as the Source for each respective leve. By default, since we have not defined how to calculate the Category Code, Cognos will calculate it for us. It will do so based on the Source column for each level, using value CA in the case of the Canada category in All_Countries and the California category in All_States.
The potential problem arises because we must have unique values in the entire dimension for Category Code. When Transformer finds that it has a value CA as the Source in All_Countries, and the same value as a Source for All_States, it must calculate a unique value for Category Code for All_States for California. It does so by putting a tilde (~) sign and a number after the Source value. We can see this in the Categories in Transformer, after the categories for the cube have been generated. Note the value CA~8 for Category “California”:
To reiterate: the Category Code value must be unique in the dimension.
The problem arises if we use this Category in a report in PowerPlay, for example. Consider a PowerPlay report authored in Reporter mode, that includes California:
The key point here is that the report is using the Category Code CA~8 “under the covers”, not the value California, to identify the value to be returned.
For many users this is not a problem. As long as the calculated Category Code “CA~8” never changes the user’s report will be fine.
But what happens, if as part of a development process, for example, the Category Code gets changed? This can cause some real headaches. If the Category Code is changed for any reason, the users PowerPlay Reporter-mode report will break. One result could be the disappearance of the category that is in the report:
In Part 2 well take a closer look at what can happen when Category Codes change, and propose some solutions.
IBM Cognos C10 Dashboards
Posted by Peter Beck in Business Intelligence on January 11, 2011
IBM Cognos 10 arrived with much fanfare last fall, and the IBM Cognos community is starting to absorb what has changed from C8. For the most part this version seems to be a fairly modest, “evolutionary” change, and I suspect that for the most part environments that have C8.4 running well will not be in a huge hurry to upgrade. From an architectural perspective this is in the same vein as the Cognos BI that we’ve seen since ReportNet, although the product has matured considerably since then. This is not the giant leap made from Series 7 to the ReportNet/C8 “enterprise” product.
That said C10 does have some interesting new features, once of which is Business Insight, or as they are more commonly referred to, “dashboards”.
Cognos 8 has a nice feature of being able to create custom pages with embedded report – and other content – that could be set up as tabs available to users on their default screen. These are useful for setting up dashboard-like content. For example, users in Finance can have a few of their most common reports available as tabs, and by selecting the tab can run the without having to explicitly navigate to the appropriate report and run it.
Extending the idea to the executive level, a few key reports can be assembled into a dashboard providing certain key metrics about the business. But of course tabs are useful in lower-level situations – for example, reports that monitor the health of a data warehouse can be “tabbed” so support staff can have instant access to metrics around, for example, warehouse rebuild times.
Tabs have always been a little awkward to set up, and to address some of their shortcomings IBM Cognos 10 introduces Business Insight, an easier way to create and share dashboard-style collections of reports (and other content).
Creating a new dashboard is simple – simply launch Business Insight from the Launch menu:
The dashboard creation screen is intuitive, with a Content tab to easily locate the content you want to place on the dashboard:
Content is placed on the dashboard with a drag-and-drop approach that allows for easy positioning of many reports on a single dashboard:
Each item on the dashboard is a “widget” that can be managed individually. This allows for some interesting features. For example, a list report can be quickly transformed into a chart-type report by selecting the widget, and the selecting “Change Display Type”
In this case we’ve selected a Column Chart, and the widget is quickly transformed to a new display type:
The ability to format the chart is quite limited, but this could be useful in situations where the customer wanted to be able to see things several different ways, if the underlying list report is set up correctly.
Another interesting feature is the ability to comment on any widget. Selecting Add Comment brings up a comment box. Users can leave a trail of comments on each widget.
Widget content can actually be selected from the components of each report – you don’t have to bring in the whole report:
In the case above, we can see that Percentage Sales By Year has both a Pie Chart and a List – either one can be used as the source for a widget.
Finally, if Business Insight Advanced is activated, the users can alter or create content of their own. By selecting a widget and selecting Do More from the upper right of the widget a user can open a query window and create or alter the content:
An interesting feature of this tool is the ability to integrate “External Data” – the ability to import external data and map it to existing report structures. This could allow Finance users, for example, to bring in budget or forecast data, which is often held off-line in spreadsheets.
Dashboard content can also include URLS, RSS feeds etc. although access to these must be granted through the Cognos Application Firewall. This can be useful to provide real-time feeds along with content that does not change as quickly.
Dashboards appear as any other object in Cognos Connection, so instead of opening a report the users simply open the dashboard. As well, the dashboards can be easily emailed to users as a link.
Dashboards in C10 represent a clear improvement from what was available in C8, providing IBM Cognos users with another powerful presentation choice, particularly in environments with a high demand for consolidated views of data.
TDWI Ottawa Chapter Kick-Off
Posted by Peter Beck in Business Intelligence on October 11, 2010
Readers in the Ottawa, Canada area should take note of the formation of a local chapter of The Data Warehousing Institute (TDWI.) The inaugural meeting was held on October 7 at the offices of Coradix Systems (www.coradix.com) and featured a presentation by Guy Michaud, CIO of the City of Ottawa on the city’s Open Data initiative.
More details on the activities of the chapter can be found at the chapter website at TDWI: www.tdwichapters.org/blogs/ottawa/list/home.aspx. The chapter is aiming for quarterly meetings featuring subjects related to BI and Data Warehousing.
A LinkedIn group has also been created at www.tdwiottawa.org