Wouldn’t it be nice if you could let non-technical users query useful information and create reports from your data without getting your technical people involved? A big part of the appeal of business intelligence tools like IBM Cognos is the drag-and-drop interface that seems to offer a tool so easy to use, you could just hand it to your business analysts with no fuss. No examining schema diagrams, no writing sql- users just drag entities (tables) and their attributes onto a report page, click run, and all the hard work is done for you.
So your company buys Cognos Business Intelligence and the documentation says something about OLAP, star schemas, blah, blah, blah. Let’s light this candle! Open up Framework Manager, run the import wizard, and try out some queries. What could go wrong? I must say, Cognos is very clear about the preparation and work that goes into supporting that magical ui. But dreams die hard. Just seeing that smooth, shiny interface makes some managers assume that no extra work is needed. What, really, can go wrong?
Here is one thing that can go wrong when you point a business intelligence tool like Cognos at a typical normalized OLTP database. Say you have customer and sales records that each refer to product and region records (a customer can only have one product? it’s just a quick example, don’t be too harsh).
Your business analysts go into Report Studio and decide they want a report on products and regions. They drag over the product_id and region_id into the report, run it, and get output like this:
Five rows (with no details like names to make this example simpler). The user doesn’t know or care about the physical product and region tables in the database. In fact, from Report Studio, they couldn’t find out more even if they wanted. Database internals are encapsulated and sealed away from report users by design. The whole point is that users can run this tool without knowing about stuff like foreign keys and constraints
Now let’s say, in a somewhat contrived example, that the name of the t1_customer entity has to be changed to v1_customer. The report above doesn’t even query the customer table so it shouldn’t matter to it at all, right?
Here are the results after rerunning the report after the customer entity has been renamed:
Whoa, only three rows. These reports are supposed to be reliable. What happened?
When you hand-write a sql query, you explicitly declare how to join the tables together. A Cognos user can’t do that- they pick entities without knowing how they’re connected. The relationship between them must have been defined in the entity metadata beforehand. If you just imported info from your regular database into Cognos, the entities will reflect the same kind of normalized pattern. In our case, region and product are not directly related; they can be joined on either customer (product to customer to region) or sales (product to sales to region). When a user runs a report on region and product, how does Cognos know which path to take?
If Cognos has two equally valid join paths between two entities, it will choose (I kid you not) the one with entities whose names sort first alphabetically. When we changed the name of t1_customer to v1_customer and reran the report, Cognos generated new sql that joined on t1_sales. I’m sure that’s what you wanted.
The solution here is that a dba or developer needs to do some work before any report gets run. One way to fix this problem would be to create separate region entities, one joined to sales and one joined to customers.
Note that the circular relationship between the entities is now gone. Now this solution might be totally inappropriate for your needs. You might rather denormalize region into the sales and customer entities. Or maybe get rid of the customer entity if you’re just interested in sales. Your solution will depend on the kinds of queries your customers will want to run, but regardless, you need to remove the possibility of ambiguous join paths. You’ll also need to look at the cardinality of your entities and whether your entities contain mixtures at dimensions or facts and…well, all the things that separate an operational database design from a reporting one.
Cognos is a great tool. If you’ve ever torn out your hair trying to supply reports to non-technical users with ever-changing requirements, you should check it out. With good report entities and relationships supporting it, the drag and drop interface is awesome, or at least far better and more useful than what most database developers could whip up. But it is, unfortunately, not magic, as some managers hope. Unless you already have a star schema data warehouse (in which case you’re already beyond all this), a lot of work needs to be done to map your database tables to report entities so that your clueless business users can use them safely.