Archive for March, 2012
Depending on your Oracle database configuration, you may find that your Oracle schema names vary across your environments (DEV/QA/PROD). This can be a problem for Cognos Framework Manager, which assumes a single standard schema per data source connection. What’s worse is that your user name and password can be changed easily from one environment to another (since these elements are defined in the Cognos portal data source connections) but not the schema (which is defined in Framework Manager and then published). There are a few different approaches that can be taken to handle this. The first is Oracle synonyms. These can easily resolve your schema name challenge at the database level, but can create headaches for your DBAs. Depending on the DBA policies of your organization, this may or may not be an option.
A second option, although not elegant, is to simply publish your model for each environment, changing the schema in the data source connection as you go. Although this will work, it feels more like a work-around than a solution.
You can also choose to manage them by using Parameter Maps in Cognos Framework Manager. If you choose to manage them in FM, the first step is to determine your environment. This can be done by creating a query subject that will determine your database connection or database server. A very useful Oracle function for this purpose is SYS_CONTEXT, which can tell you the server name, the database name or a variety of other database environment identifiers. It is also a widely available function, which means you should be able to call it no matter what Oracle account you are using.
You can then set up a Parameter Map to read your Environment Query. Base your Parameter Map on your defined Environment Query, using the SCHEMA column as the key and the SCHEMA_VALUE as the value.
Once you’ve defined your parameter map, you need to make two final changes. First, remove any Schema definition in your Data Source properties. Do not attempt to call your Parameter Map schema value here. If you try, you will get a recursive call error as this code will attempt to run for your Environment Query, which needs to run to get your schema value in the first place.
Next, add your parameter value to each Query Subject, except for your Environment Query:
Now you should be able to import and export your data model at will across your database environments without making schema changes at each turn.
While researching my learning plans for this year, I came across an interesting educational opportunity in the realm of Business Intelligence. It is the Master’s of Business Intelligence program at Saint Jospeh’s University in Philadelphia. It is offered part-time and online, making it a possibility for working professionals. A quick check of my LinkedIn network showed me that of 6 registered alumni of Saint Joseph’s Haub School of Business, one has this degree – and he now works as head of Business Intelligence at the Walt Disney Company.
The program appears to have exacting but flexible admission requirements. The GMAT requirement can be waived if you hold one of a list of professional designations. It takes 20 months to complete the entire program part-time, and total tuition costs are currently $25,980 (no additional fees for foreign students, at least for the online delivery of the course). You can expect to spend between 10 and 15 hours a week on a part-time basis. A thesis is required.
From what I can gather, this program appears best suited for those intending to climb the Business Intelligence ladder all the way to the top. Think of this as a BI equivalent to an Executive MBA. It has the potential to break you into senior management. If you have the time, energy and ambition, this could be a good fit for you.
The article that alerted me to this program was a blog entry by Richard Herschel, the Chair of the Department of Decision & Systems Sciences at Saint Jospeh’s University. He has an Expert Channel on the BeyeNetwork.
Cognos Framework Manager is a powerful data modelling tool, but like most powerful tools it can be used correctly and incorrectly. Many users love to see the SQL interface and start coding in SQL the first chance they get. This is usually a mistake. Hard-coding SQL in Cognos Framework Manager can cause unnecessary metadata callbacks which can seriously degrade a data model’s performance, and it can also make a data model more complex to modify, maintain and understand.
Now to the question of a union operator. The first and obvious way to do this is to simply put a union directly in your SQL query. This will give you the desired result, but it is not a best practice. For the sake of simplicity, let’s call this the wrong way.
A better way to do this, though less obvious to novice users, is to create a Query Set. Simply select the two tables you wish to union, then click on Actions and Define Query Set. You can do Union, Intersect and Except operations in a Query Set. You are limited to two tables at a time, but you will end up with a cleaner model if you take this approach. If you need to join multiple tables, you may be better off taking your desired operation back to the database in a database view or a merged table with ETL. Any time you find yourself coding heavily complex SQL code in Cognos Framework Manager, ask yourself if an ETL solution could serve your FM data model better.
So, as usual, there is more than one way to complete a particular task in Cognos. It’s just some ways are better than others.