A Pharmaceutical company has partnered with BrightStar Partners to complete an assessment of their current Cognos deployment and to implement various improvements based on the results of the assessment. During the review, we highlighted the areas that they excelled in as well as identified various opportunities for improvements.

 

During our assessment, we learned that many of the Framework Manager data sources were relational in nature, and we saw the detrimental effect this had on the FM models, report performance and ease of use by the business users.

 

We proposed a proof of concept data mart with a dimensional structure (star schema) to allow the Framework Manager models to use a physical dimensional model instead of relational data sources, to demonstrate the advantages this approach will deliver. This approach:

 

 

BSP continued to design and implement a dimensional model for a specific business reporting area. We completed the data load into the physical dimensional model, validated the data and documented all issues and known differences. A Framework Manager business model was created, and benchmark reports were developed to demonstrate the results,which included:

 

 

Per our proven practices, we delivered a detail set of documentation (reporting design and technical user guides) to aid in maintenance, troubleshooting, and future development, and completed knowledge sharing sessions with the client's team. We also laid out a roadmap to build upon the delivered data mart, to add additional data marts to the dimensional model, and for more robust reporting.

 

Many times we have the need for connecting to multiple source systems, without having to change the connection information and recompile the jobs.  As DataStage saves the connection information with the jobs, we accomplish the above using Project Specific Environment Variables.  The Project Specific Environment Variables (PSEV's going forward) enable us to pass connection info (DSN, username, password, etc.) to the jobs, for each of the different source systems we need to connect to, without having to change and recompile the job every time.  Although PSEV's can be applied to many uses, this article specifically caters to the need for handling DSNs.

 
For setting up PSEV's, we have to add 'User Defined' Environment Variables via DataStage Administrator.  We define three variables named 'data_source','user' and 'password'.  We then add these environment variables as parameters of each job in DataStage Designer.  In Designer, the default value of each of these parameters will have to be set to $PROJDEF.  By doing so, Datastage will use the last known value for the parameter.

Once we have the PSEV's set up and the jobs are modified to use these PSEV's, we can then set the values of these parameters in a script that runs the DSJOB command.

By doing this, we avoid the need to recompile the jobs repeatedly for each data source.

For detailed version of the article with screen shots, click here 

This process has been tested in DataStage v8.0.1.

These steps assume you have the appropriate user rights for your project, as well as having the required DSNs added to your project.  The example below is specific to the ODBC Enterprise Connector, connecting to a SQL Server 2005 Database.