Cognos Data Manager Variables in Dimension Builds

Cognos Data Manager Variables in Dimension Builds

How to use Variable in a Dimension Builds

Before starting to explain how to use variables in a Dimension Build, we assume you know about following topics if not we recomend to read it. 

Lets assume that you have a business requirement in which a Employee Dimension need to be created from Employee Source Table named tblEmployee with following columns.

  • EmployeeID
  • EmployeeName
  • EmployeeManagerID 
  • EmployeeRole
  • Region

But as per requirement you have to discard some employee based on specific region. Let say region equal to 1. How you are going to achieve this?

Following are the possible solutions
Solution 1: I think you know about this solution. Using Where Clause in SQL Query (Not Recommended Solution. Why? You will understand it Why. Please stay with me.)

When you fetching data from database table you can use where clause

SELECT * FROM tblEmployee WHERE region=1

Great you achieve the requirement. But what if the region is changing frequently. Hummm we have to change the SQL Query every time when region value is changed. Lets think about a better solution.

Solution 2: Why not use Cognos Data Manager Variables. (What Variables are going to create some magic. Yes it is.)

Modify your above solution. Use Cognos Data Manger Variable. Follow below step to modify your first solution

  • Define a variable named Region with data type Integer (assuming region is an integer column).
  • Set value of $Region dynamically before calling your Dimension Build.
  • Modify your SQL Query.
  • SELECT * FROM tblEmployee WHERE region={$Region}

(Wow my query is dynamic. Ok now i understand why to use a Variable instead of a static query. A variable can make my query more dynamic. It will help me to reduce my future efforts.)

I know you are thinking you can achieve the above requirement with any other solution. Yes you are correct you can. But in this article we are not focusing on the solution for above requirement. We are focusing on how to use a variable in a Dimension build.

Ok, if you got this. Make a best practice to use a variable to make your query dynamic. You can start reading the next article How to use a Variable in Fact Build.

Still facing any issue. No problem leave a comment below. We will respond ASAP.

Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first