Friday, January 6, 2012

Cognos TM1 - Creating a new Data Server

Cognos TM1 - Creating a new Data Server: Watch this on You Tube

Steps:
1- Goto C:\Program Files\cognos\TM1\Custom\TM1Data\PlanSamp
2- Copy tm1s.cfg and tm1s.lic and paste to c:\LearningTM1\BDCS\Data
3- Create a folder for log files: c:\LearningTM1\BDCS\logs
4- Open tm1s.cfg in a text editor.
5- Indicate the right ServerName and DataBaseDirectory.
6- Change the following:
 a- ServerName from ServerName=Planning Sample to ServerName=BDCS.
 b- DataBaseDirectory path:- c:\LearningTM1\BDCS\Data.
 c- Leave AdminHost=ACER-700
 d- PortNumber=12345 to PortNumber=23456.
 e- LoggingDirectory=c:\LearningTM1\BDCS\logs
7- Save tm1s.cfg.

Creating a new data Server desktop shortcut:
1- Goto C:\Program Files\cognos\TM1\bin and create a shortcut of
tm1s.exe.
2- Rename tm1s.exe BDCS and right click properties.
3- In the target box:
"C:\Program Files\cognos\TM1\bin\tm1s.exe" -z
"c:\LearningTM1\BDCS\Data"

** -z indicates the location of the server configuration file
4- Double-click the BDCS shortcut on the desktop, the new TM1 server is started.
5- Open Server Explorer, and then from the File menu, click Refresh Available Servers.
The new BDCS appears

SO THATS IT.... 
Thanks and Regards,
Bharati DW Consultancy
cell:+91-7738300984
The secret in education lies in respecting the student! 

Sunday, January 1, 2012

Cognos 10 Advanced Report Studio - Dynamic Grouping

Advanced Report Studio - Dynamic Grouping
Excercise:
Here we have a Report --> Country, Year, City, Sales Amount, Sales Quantity.
We need to give an option to the user to Dynamically Change the Primary Group to Country or Year.
Steps:
1- Create a Report with   Country, Year, City, Sales Amount,Sales Quantity.        
2- Create a Static Prompt (?Choice?) on the Report Prompt Page.    
3- Specify the Static Choices: Country / Year
4- Specify a Default Choice --> Country
5- Goto Report Page
6- Create two Query Calculations
Group-1: if(?Choice? = 'Country') then ([Country]) else ([Year])
Group-2: if(?Choice? = 'Country') then ([Year]) else ([Country])  
7- Change the Header Text for Group-1 or Group-2.
8- Hide Country and Year Columns.  
9- Run the Report.
SO THATS IT.....


--
Thanks and Regards,
Bharati DW Consultancy
cell:+91-7738300984
The secret in education lies in respecting the student! 

Cognos 10 - Advanced Report Studio - Joins, Unions, Intersect

Cognos 10 Training - Advanced RS - Creating Unions - Intersect - Part 7 of 25 Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Join Query
Application: Join on the report is used when, there is no join defined at the Framework Manager Model level.

Steps for Creating a Join at report Level:
1- Create two queries in report studio
2- Query-1  Drag City, Year, Sales Quantity and Sales Amount
3- Query-2 Drag Country, City     
4- Goto Query Explorer: Drag and Drop Join from the left hand side.                                    
5- Drag Query-1 on the first Box in the Join Query.
6- Drag Query-2 on the Second Box in the Join Query.
7- Specify the Join condition
8- double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
9- Goto Page explorer  & Drag and Drop a list.
10- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
11- From Data Items Tab: Drag the columns from Query-3 in the new list
12- Run the report
SO THATS IT.....

********************************************************************************************
Advanced Report Level Joins - Unions - Intersect:
You can create Joins - Unions - Intersect at a report level.
These should be sparingly used, as they impact the report performance.
Excercise: On a Report, from two queries, create a Union Query
Steps for Creating a UNION at report Level:
1- Create two queries in report studio
2- Query-1  Drag Country, City, Sales Quantity and Sales Amount 
3- Apply filter for Country-USA,UK for Query-1
4- Query-2  Drag Country, City, Sales Quantity and Sales Amount
5- Apply filter for Country-IN,UK for Query-2
6- Please note that for UNION, the columns and data type across two queries should be same.
7- Goto Query Explorer: Drag and Drop UNION from the left hand side.     
8- Drag Query-1 on the first Box in the UNION Query.
9- Drag Query-2 on the Second Box in the UNION Query.
10- Double click the Query-3: Drag and Drop the columns as required from Q-1 and Q-2.
11- Goto Page explorer  & Drag and Drop a list.
12- Associate Query-3 to this list; Select the entire list and in the properties pane, Link Query-3
13- From Data Items Tab: Drag the columns from Query-3 in the new list
14- Run the report

Steps for Creating a intersect at report Level:
15- Goto Query Explorer: Drag and Drop INTERSECT from the left hand side.                                    16- Drag Query-1 on the first Box in the intersect Query.
17- Drag Query-2 on the Second Box in the intersect Query.
18- Double click the Query-4: Drag and Drop the columns as required from Q-1 and Q-2.
19- Goto Page explorer  & Drag and Drop a list.
20- Associate Query-4 to this list; Select the entire list and in the properties pane, Link Query-4
21- From Data Items Tab: Drag the columns from Query-4 in the new list
22- Run the report

SO THATS IT.....

Cognos 10 Cascading Prompts


Cascading Report Prompts:
Value displayed in a prompt is driven by the values selected in another prompt on the same report.

Excercise:
Lets create two prompts: One of Country and other on City
We need to display values in the city prompt, based on the value selected in Country.

Lets do it....

Cognos 10 Scheduling a Report


Cognos 10 Administrator - Scheduling a Report:
A report can be scheduled based on Daily, Monthly, Weekly, Yearly timeframe or can be event based.
We can also specify the formats in which the reports are to be scheduled.
Also, we can send these reports through email / save them / Print them.
       

Steps:
1- Click the Scheduling option available in front of every Report.
2- Specify the run criteria / output format / save method  
3- You can monitor these reports from the Administration screen.

Lets see....    

4- You can see the report which are currently running in the Current Activities
5- Future schedules in the upcoming activities.

6- Lets see the output of the report.


SO THATS IT....

Cognos 10 Master Detail Report


Master Detail Report:
There are more than one query in a single report.
On this report, first query(Master) drives the data in second query(detail).


Excercise: Get the cumulated sales data in a list and then display a yearly graph each for every country.

Steps for Creating a master detail report:
1- Create a list report.
2- Drag Country, Sales Amount and Sales Qty.
3- From the toolbox, drag a chart and drop it in the list.
4- Drag and drop Country, Year, Sales Amount and Sales Qty.
5- Click on the chart; from the Data menu, click on master detail relationship
6- Create the link between the Two queries on Country...


Lets do it......

Cognos 10 FM Creating RMR


Creating a Framework Manager Relational Model - RMR.

1- Open the Framework Manager.
2- Click on New Project. Give a name.                  
3- Please notice that a Folder same as Project Name is automatically.
The project file has an extension .cpf --> Cognos Project File.

4- Select the Language for FM --> English.
5- Select the type datasource.
6- Select a datasource --> DB from where you get the data for your reports.
7- Select the tables / views which are required for reporting.
8- Uncheck the options for identifying joins from DB.
9- Import the tables/views, Known as Query Subject in Cognos world.


10- Identify the usage property for each Columns (Query Items).
Identifier --> Ids/ Codes / Dates
Fact --> Number / Figures / Measures
Attribute --> Remaining Query Items :-)

11- Right click on the project, create 2 Namespaces:
Database Layer
Presentation Layer.

12- Drag and Drop all the Query Subjects in the Database Layer.
13- CLICK ON DIAGRAM:
Create Joins / relationships between the Query Subjects. Specify the correct Cardinality. From Dimension:Fact --> 1:n

14- Create Model Query Subjects used for Reporting in the Presentation Layer.
Right Click Presentation Layer --> Create Query Subjects.
14a- Select the Columns you want to expose for Reporting; Map it to the database Layer.
15- Give Business Names to the Objects in Presentation Layer.

16- Now, we need to create a Package: Right Click Packages--> Create Package.
17- Hide the Database Layer and check the Presentation Layer.
18- Select a location to publish the package.
19- Verify the model and publish the package.
20- Logon to the Cognos 10 Portal --> Open the package in Report Studio and start creating reports.

SO THATS IT....


Cognos 10 Drill Through Parameter based reports


Drill-thru Report:
Two separate reports are linked with each other on a common column.

Excercise:  The first report, lets call it as Parent Report shows the data at a summary level.
The second report, lets call it Child Report has the detail level data.
Parent Report--> Country, Sales Amount, Sales Qty.
Child Report --> Country, City, Customer, Sales Amount, Sales Qty.

Child Report will have a prompt on Country.
To create a Drill Thru link, between these two reports, a link is created on Country column in the Parent report, in such a way that a click on country in parent report would filter the child report.

Steps for Creating a master detail report:
1- Create a parent report.
2- Drag Country, Sales Amount and Sales Qty.
3- Save the Parent Report.
4- Create a Child Report. Country, City, Customer, Sales Amount, Sales Qty.
5- Create a Prompt on Country. Save the Child Report.
6- Click on the country column in the Parent Report.
7- Create a Drill thru link.
8- Select the Child Report and map country prompt to country column from parent.
Lets do it......


THANKS!!!


Cognos 10 Administration Deployment


DEPLOYMENT:
a- Taking Backup of Packages/Reports code
b- Migrating the Packages/Reports across environments: DEV-->UAT-->PROD
From source environment to target.


1- Logon to Cognos 10 Portal.
2- Goto IBM Cognos Administration.
3- Goto Configuration.
4- Click on the Content Administration on the left side.
5- From the source environment you need to create an export of the content.
6- In the target environment you need to import it back.  
7- Click on the new Export icon on the top right. (Below the launch menu)
8- Specify a name of the Export.
9- Select the Packages / Folders (Please note that you CAN'T select individual Reports.)              
10- Keep all the selection options as default.
11- Click Next --> save and run once --> Finish.                                      
12- A zip file with the same name as Export would be created under :
<Cognos Server Installation Location>/Deployment

For Deployment into Target Environment:

13- Copy this file from Source Environment and paste it under:
 <Cognos Server Installation Location>/Deployment
of the target environment.

14- Do the import in the Target Environment, as shown during Export.

SO THATS IT.......

Cognos 10 Security


Creating Security Groups
Applying Report / Package Level security:
Access rights is a very important aspect of Business Intelligence implementation, as it may contain sensitive information.

1- Logon to Cognos 10 Portal.                                
2- Goto IBM Cognos Administration.                
3- Goto Security
4- Create New Group --> India Users --> Add Member
5- Create Another Group --> US Users --> Add Member
6- Grant Access rights to India Users for Indian Report
Click on Report Properties --> Permission --> Add

Users must have at least traverse permissions for the parent entries of the entries they want to access. The parent entries include container objects such as folders, packages, groups, roles, and namespaces.

To ensure that a user or group can run reports from a package, but not open the package in an IBM Cognos studio, grant the user or group execute and traverse permissions on the package.

7- Similarly, Grant Access rights to US Users for US Report
Click on Report Properties --> Permission --> Add


SO THATS IT......


Cognos 10 Report Studio Prompts


Report Prompts:
Induces report inter-activity. Asks the users to select a value to filter the data on.

3- Ways of Creating Prompts:

1- Using variable name between two question marks (??)
     Create a filter and add a variable name between 2 question marks.

2- Using 'Build Prompt Page' Button.
     Select a column to apply filter. Click on 'Build Prompt Page' Button.


3- Creating a manual prompt page
    Goto Page explorer.
    Click on Prompt Pages.     Create a new page.
    goto Toolbox...Drag value prompt...... Create the prompt...
    and run the report.

Lets do it....

Cognos 10 Creating Dashboard in Report Studio


Creating Dashboard in Report Studio:
In information technology, a dashboard is a user interface that, somewhat resembling an automobile's dashboard, organizes and presents information in a way that is easy to read.

We will see how to create various Dashboards using a Simple Report Studio Principles.
                                 

1- Drag and Drop a Table - 2C x 2R. (C- Column / R-Row)
2- Drag a list in the table Top Left@ 1C-1R
3- Drag a Column-Chart in the table Top Right@ 2C-1R
4- Drag a Bar-Chart in the table Bottom Left@ 1C-2R
5- Drag a Line-Chart in the table Bottom Right@ 2C-2R
6- In the list Drag Year/Country/City/Sales Amount/Sales Quantity
7- Now, this list query can be used to feed in data for the various different charts.
8- Associate List Query-1 to all the charts. Select one chart at a time and in the properties pane, Link Query-1
9- From the data Items Tab, In the measure / axis section drag and drop various query items as shown.  Run the dashboard.
10- Now, lets make it more interactive - add two prompts - year/country at the dashboard page itself.
11- Make these prompts optional / enable auto-submit.
12- You may need to refer - P-2 Creating Prompts video for more information on prompts.
13- Run the dashboard.
14- Format the dashboard as per your creativity - add colors etc to make it look good.

SO THATS IT......


Cognos 10 Report Formating using Conditional Variable:


Report Formating using Conditional Variable:
Applying a formating, based on a data item value is conditional formating.
For this, we need to define a condition-variables

There are three types of variables:
Report Language: For achieving Multi-lingual
Boolean: Values either Yes or No.
String: Values based on Condition
   

In this excercise, we will see Boolean variable:
1- Create a list report with Country, Year, Sales Amount, Sales Quantity
2- Now, we want to highlight values under 15 for Sales Quantity in RED COLOUR
3- While, values over 15 for Sales Quantity in GREEN Colour.            
4- Go to Condition Explorer, create a Boolean Variable.
5- Define a condition: SalesQuantity < 16
6- Click Ok and Close the expression editor.
7- Notice the possible values on the right side of the condition-explorer page YES/NO
8- Go back to Page Explorer.
9- Select the Sales Quantity - column body
10- This is an important step: Being on the Report Page, take your mouse pointer over condition explorer
11- From the available values for the Boolean condition, we just created, select YES
12- Notice the bar colour change to green - this indicates: the report layout while condition is VALID
13- Select the Style variable to Boolean1- then Set back ground colour for Sales Quantity to RED
14- AGAIN: From the available values for the Boolean condition, we just created, select NO
15- Notice the bar colour change to green - this indicates: the report layout while condition is INVALID
16- Select the Style variable to Boolean1- then Set back ground colour for Sales Quantity to GREEN
17- Run the report and Notice the background colour.

SO THATS IT..........

************************************************************************************************
Report Formating using Conditional Variable:
Applying a formating, based on a data item value is conditional formating.
For this, we need to define a condition-variables

There are three types of variables:
Report Language: For achieving Multi-lingual
Boolean: Values either Yes or No.
String: Values based on Condition
   
In this excercise, we will see String variable:
1- Create a list report with Country, Year, Sales Amount, Sales Quantity
2- Now, we want to highlight values under 10 for Sales Quantity in RED COLOUR
3- While, values between 10-20 for Sales Quantity in Yellow Colour.            
    While, values over 20 for Sales Quantity in Green Colour.  
4- Go to Condition Explorer, create a String Variable.
5- Define a case condition: GT / LT / BT     (Greater Than / Less Than / Between)
case
when [Query1].[Sales Quantity] < 10 then('LT')
when [Query1].[Sales Quantity] > 20 then('GT')
else('BT')
end

6- Click Ok and Close the expression editor.
7- We need to create the possible values on the right side of the condition-explorer page
8- Create a set of possible values as defined in case condition. GT / LT / BT -- Without the Quotes
9- Go back to Page Explorer / Select the Sales Quantity - column body
10- This is an important step: Being on the Report Page, take your mouse pointer over condition explorer
11- From the available values for the String condition, we just created, select LT
12- Notice the bar colour change to green - this indicates: the report layout while condition is VALID
13- Select the Style variable to String1- then Set back ground colour for Sales Quantity to RED
14- AGAIN: From the available values set BT to yellow and GT to green
15- Run the report and Notice the background colour.
SO THATS IT.......... 
         

Creating Cognos Datsource Connection


Creating Cognos Datsource Connection

1- Create a System DSN: Please note that User DSN doesn't work.
2- Goto Start --> Control Panel --> Administrative Tools --> ODBC.          
3- Create a System DSN -> Select the Microsoft Driver for MDB.
4- Select the abcd.mdb file.
5- Save the connection.

6- Logon to Cognos 10 Portal.
7- Goto IBM Cognos Administration.  
8- Goto Configuration.
9- Data Source Connections--> Click on new Datasource
10- Specify a Datasource name.
11- Select datasource type as ODBC
12- Specify the Name of the System DSN.
You can specify the username and password for the database.
Leave it blank for this database as its not secured.
13- Test the connection. SUCCESS
14- Click Finish.

SO THATS IT.... .........