PROBLEM: Output in Excel 2003 with Cognos 8.1 the window just disappears

PROBLEM: Output in Excel 2003 with Cognos 8.1 the window just disappears

SOLUTION: Alter/set the following conditions in Internet Explorer

1. Open Internet Explorer

2. Click 'Tools' -> 'Internet Options..'

3. Click the 'Security' tab

4. Click on 'Internet'

5. Click 'Custom Level...'

6. Ensure the 'Automatic prompting for file downloads' and 'File download' are enabled.

7. Click 'OK'

8. Repeat for 'Intranet' and 'Trusted Sites'

Dynamic Sorting Based on Selection in Cognos Report Studio

Dynamic Sorting Based on Selection in Cognos Report Studio

Requirement: User should be given a choice to select the columns on which the output should be sorted. Ex: A List has Month and Quantity.  If the user selects Quantity, output should be sorted based on Quantity in ascending order.

Solution: Create a ‘Sort’ data item in the same query subject and perform ‘Advanced Sorting’ based on the ‘Sort’ data item.

Detailed Steps:

1.     Create a List report with Month and Quantity columns

2.     Create a ‘sort’ prompt with static choices as 1-Month and 2-Quantity

3.     Create a Data Item ‘Sort’ in the query subject as
     case
when ?sort? = 1 then [Month]
when ?sort? = 2 then [Quantity]
end

4.      Select 'Month' column in Report Page, select ‘Advanced Sorting’ and  add ‘Sort’ under ‘Detail Sort List’

                 5.  Repeat the above step for “Quantity” also.

Limitations: The above solution sorts both columns in the same order. i.e either by Asc or by Desc. This works only when both columns are of same data type

Scope Relationship

Scope Relationship

Scope Relationship: which is exist between measure dimension and regular dimension to define the level at which the measures are available for reports- This is available in DMR model.

What are summary filters? Grouped filters?

Summary filter: If the filter applied after aggregation then aggregated records are filtered.

Group filter: A filter which is applied to the group is known as group filter

a	10
c	12
c	8
d	6	Details	> 10	2 records
d	7	Summery	> 10	3 records
b	6
b	15

Cognos improve report performance in model

·         Default aggregation properties

·         Uses properties

·         Joins

·         Limited local

·         Remove loop's

·         Proper determinants

·         Removing non supported function in model

·         Cardinality

·         Try to use cube

·         Make sure all the "Joins" and "Join strategy" are correct.

·         Check for the cardinality.

·         Create summary tables (one of the best approach in increasing the performance)

·         Use Power play cubes as data sources.

·         You can remove loop's at that time you can improve performance at model level

·         use indexes you can improve performance

·         Change the property of query processing to Limited local or database only.

Static Choices

Requirment:

I have 4 countries in my database as USA, UK, INDIA, CHINA. In value prompts prompt control list, I want WESTERN_COUNTRIES, ASIAN_COUNTRIES instead of those country names. When I click on WESTERN_COUNTRIES,

1. From Query Explorer, open up the query for which the filters should be applied on.
2) Drag a Filter object from the Toolbox tab to the Detail Filters pane.
3) In the Expression Definition, enter the following including the #s
                        #prompt ('<prompt parameter>', 'token', '<default filter>')#
                       example: #prompt ('Countries', 'token', '[Country] IN ('USA', 'UK', 'INDIA', 'CHANA')#
4) Add a Value Prompt to the report .
5) Select the Value Prompt and in its Properties in the bottom right corner, go to the Static Choices property.
5) For each possible filter that needs to be applied, add a new Static Choice.  Make the Use the expression of the filter that you should be applied and the Display is what is shown to users in the prompt's drop down list.:

Use: <filter expression>
Display: <what is displayed to users>

Example :
Use: [Country] IN ('INDIA','CHINA')
Display: Eastern Countries

Report performance tuning in report studio

If all reports are slow, the issue may be due to your environment or database. If all reports from a specific package are slow, the issue may due to the model design. If just one report is slow, the issue may be due to a specific report element.

1. How many queries does your report contain?
2. The number of queries on the report will proportionally affect the report execution time.
3. Does the report run slowly for everyone, or just for one user?
4. If the report runs slowly for just one user, the issue may be due to something in that user’s environment, such as virus scanning, page file size or location
5. Settings or their location on the network.
6. If many people are running the same report at the same time, you may need to scale your environment or consider using dispatcher routing rules to direct all requests for a specific package or group of users to a specific server or server group. For more information, see the IBM Cognos Business Intelligence Administration and Security Guide.
7. Do your queries require local processing?
8. The following report elements require local processing: crosstabs and charts, master relationships, unions or joins, multiple fact queries, bursting, and non-vendor specific functions. Local processing requires the IBM Cognos server to compute operations on the result set returned by the database, which can impact the SQL execution time.
9. Does your environment use a Custom Authentication Provider?
10. Using a Custom Authentication Provider could cause a memory leak if the code is not destroying objects correctly.
11. Have you reviewed the logs in the c10_location/logs directory and the audit logs?
12. They may help you identify the source of the problem. Monitoring your processes, such as the Java TM and Business Intelligence bus processes could also identify excessive memory use.
13. Is your environment tuned correctly?
14. For more information, see the Performance Tuning Settings for IBM Cognos 8 Business Intelligence and the IBM Cognos 8 Business Intelligence Performance Tuning Cheat Sheet documents available online at www.ibm.com.
15. Have you recently upgraded?
16. Ensure that any tuning settings that were applied to your previous installation are applied to the new environment. Ensure that your models have been verified, upgraded, and republished. Verify that the IBM Cognos Framework Manager governor that allows enhanced model portability at runtime is not enabled. Depending on your upgrade method, you may also need to open and save the reports again after upgrading.
17. Change the order in which items are queried from the database.
18. Create sections without creating master detail relationships.
19. Share queries between lists and crosstab.
20. Convert queries to SQL
21. Avoid using functions with limited support.
22. Be aware of the limitations of creating expressions with SAP BW data sources
23. Use Select & Search prompts instead of value prompts if your list of prompts is long
24. Provide your own prompt values in a prompt to avoid accessing the database.
25. Suppress null cells using filters.
26. Set page breaks without creating master detail relationships
27. Do not modify IBM Cognos Analysis Studio set definitions.
28. Do not put filters on non-identifiers.
29. Avoid combining large hierarchy levels and sets in a drill-through report in a way that creates large
30. Use database functions when possible.

Role - playing dimension

Role- playing dimension

Dimension having multiple valid relationship itself and other dimension as well call role playing dimension.

Dimensions are often recycled for multiple purposes within the same database. For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a “role-playing dimension”.

Basically, if the same dimension is used more than once with different names in the cube then it is called the role- playing dimension

Cub VS DMR

Sl No.	Cube	DMR
1	Cognos 7 technology on words	Cognos 8 technology on words
2	Performance high	Performance Low
3	offline data	online - life data
4	Attributes nested within dimensional values	not possible with cubes
5	Cube does require a separate model/IQD	DMR does not require a separate model/IQD
6	Calculated categories creation - YTD, MTD	No Calculated categories creation
7	supported multiple environments/formats	Not supported multiple environments/formats
8	Not supported HUGE volume data	Supports HUGE volume data sets
9	Security in custom view and access manager	Security in Cognos server or FM
10	Custom view	No custom view
11	MDX	SQL
12	Support up to 2 GB	No Size limit
13	Manually maintain data in categories for data correction	Data correction only possible in database level not model / package level

Single cognos report multiple data sources

Stpes

1.    Get ready with SQL query

2.    Open report studio with/without any package

3.    Go to query explorer

4.    Drag query1

5.    Drag SQL object from Toolbox to query explorer

6.    Click on SQL object

7.    Properties pan select data source (Which you should have to created data source before for given SQL)

8.    Double click on SQL object

9.    Copy query into SQL ojbect

10.  Validate SQL

11.  Click ok

12.  Now you can design report

13.  Same way you can develop multiple query with multiple data sources report

How cube is making join data source

How cube is making join in multiple data sources and building .mdc file and preparing package.

Cognos transformer has unique mechanism to gather multiple data sources (Reports, IQD's (Queries), model, packages) to build cube.

Question is how transformer doing magic
Are we define any join one IQD's to another IQD's ?
Are we define any join between two tables?
Are we define any join between two database?


After cube published .mdc file In power play studio we can do analysis using those all dimensions.
All dimensions made based on unlinked data sources, how this magic?

Answer : Cube machanism search in backgroud to join one table to anther by just finding common column, and same also doing in case for multiple databases. it will gather common column join to each other.


Thanks.

Conditional Bar Chat cognos report

Requirement : Cognos Bar chat report color with different contion.

1. Create sting variable with different values (Drag data item month in string values like 'R','B','G','Y'
2. Click on series
3. Click  conditional pallet
4. Select new conditional
5. Select data item (Series data iteam)
6. Select type = string
7. Select option Enter string criteria
8. Select contains from dropdown
9. Enter  R
10. Define the color 'Red'
11. Select option Enter string criteria
12. Select contains from dropdown
13. Enter  B
14. Define the color 'Blue'
15. Repeate this steps 7-10 upto you like (you can define multiple number of color)
16. ok
17. ok

Thanks.