Publishing Package from a Cube

Using Framework Manager, we can publish a package which could either be sourced from a database or a cube. Here we will be discussing on how to publish a package in Framework Manager, with cube as a source. Below listed are the steps to be followed in the process:

1. Create a cube (.mdc file) and save it in any location in the server.

2. Open Framework Manager and click on “Create a New Project”.

3. Give the name and location for your project and click “OK”.

4. Select the language for your project and click “OK”.

5. Now, you will get the “Metadata Wizard” with options to “Select Metadata Source”.

6. Click on “Data Sources” and “Next” to create a new data source for connecting our cube.

7. Click on “New” for creating a new data source and click “Next”.

8. Specify a name for the new data source to be created and click on “Next”.

9. Now, select the “Type” for the new data source connection. Select “Cognos PowerCube” for type.

10. Now, specify the location of the cube (.mdc file) and click on “Test the connection”.

11. Click on “Finish” to complete the creation of the data source connection to the cube.

12. Select the created data source and click on “Next” to complete the import process and then click on “Finish”.

13. The “Create Package” window will be displayed. Enter the name for the package and click on “Finish”.

14. Now, you will get the “Publish Wizard”; specify the publish location. Click “Next” and then click on “Publish”.

Your cube is now published in the portal. Do note that you cannot make any changes to the metadata while publishing the cube.

Cognos PowerPlay Transformer - Online Exam

Take a practice test at the link below to check your knowledge on Cognos PowerPlay Transformer:

http://www.wiziq.com/online-tests/25991-test-cognos-transformer

 

Cognos PowerPlay Transformer

Cognos PowerPlay Transformer is a tool to create multidimensional database, Powercube. A PowerCube contains calculated, summarized data organized into dimensions and measures and can be viewed and analysed in the PowerPlay Web Explorer, PowerPlay client for Windows, PowerPlay for Excel or Cognos 8 BI. PowerCubes provide secure and fast data access.

The default Cognos PowerPlay transformer window is composed of the following windows:

1. Data Sources - a list of all connected data sources. Each source can be expanded and it lists all available columns. Users can browse the source data by using Data Source Viewer. Columns from a data source window can be dragged & dropped to the dimensions and measures windows accordingly.

2. Dimension Map - manages the dimensions and levels hierarchy in the data model. It allows creating new dimensions and modifying existing using an user-friendly graphical interface. Dimensions and underlaying Levels are created by moving columns from the Data Sources window to the corresponding dimension column.

3. Measures - drag & drop columns to manage number figures (facts) in a PowerPlay model.

4. PowerCubes - you can specify here a list of cubes created by the model.

5. User Classes - define the security.

6. Signons - datasource connection information.

Building a PowerPlay model involves designating columns in the source files as measures and dimensions.

Some of the problems faced during cube creations are:

Insufficient Memory

Increase the paging file size. However this requires re-start of the computer. So, before you build a cube on your local PC, make sure you have sufficient virtual memory (500-1000 MB).

The file system is full

Owing to the amount of data fetched from the database, the temporary space of the database where the data is temporarily stored becomes full. It is not possible for more cube processing to happen on the database and the cube is not created. The solution to this:

1. Check if the sqls fired are correct. Best option is before the iqds are included as data sources into the model, to fire the sqls separately, directly on the database and check if all the joins exist and are correct and if the right result is obtained.
2. Use query-optimization techniques to enhance the performance of the query through modifying the queries themselves or creating appropriate indexes on the tables used by the queries

The SQL explain facility is part of the SQL Compiler that can be used to capture information about the environment where the static or dynamic SQL statement is compiled. The information captured allows you to understand the structure and potential execution performance of SQL statements, including:

·         Sequence of operations to process the query

·         Cost information

·         Predicates and selectivity estimates

·         Statistics for all objects referenced in the SQL statement at the time of the Explain.

3. Ensure that the cube is run during off-peak hours. This is because, if other processes are running on the database, the primary and the secondary log files (into which queries are temporarily processed and stored) may become full and give the above error.
4. If conditions a, b and c are checked and still, the above error is obtained, then procure more temp space. Ask the DBA to either increase the number of secondary log files or increase their size.

Time-Consuming Build of Cube

If the cube has more than 8 measures, then new categories are searched for and created for each measure and then the records added. This is an extremely time consuming process and the cube can take upwards of 2 hours to completed build itself. If there is a database-resource intensive process that needs to run meanwhile, then this is not feasible.

These are the methods we can adopt to reduce the processing overhead

1. Incremental update of the cube - Instead of building the entire cube at a time, we can plan for the cube to ‘build incrementally’. However, this can be done only if there are no major structural changes to the old data i.e. the old hierarchical structure of the dimensions does not change and there are no updates on the measures. If this happens, then the cube has to be re-created again.  In incremental build of the cube, the old data in the cube remains unchanged and only the new records are added into the cube.

In PowerCube windowàPropertiesàProcessingàEnable the radio button ‘This cube is incrementally updated’

2. Use of Proper Optimization – There are 4 types of optimization methods which should be used depending on the way records are stored and updated in the database. The 4 types of settings are
1. Categories
2. Data Passes
3. Direct Create
4. Auto-Partition

The best way to arrive at the appropriate optimization technique is to limit the cube to only few records fetched at a time (excluding most of the categories, putting filters in the iqds). Using this, we can create a test cube in lesser time and then alternate between various optimization techniques to identify the best one.

More investigation should be done about excluding some data sources for measures at a time so that only limited and manageable amount of measures are processed at a time. One the cube is built, then the rest of the data sources are added. If incremental update of the cube is enabled, then the data sources present earlier shouldn’t be ideally processed and hence cube build should be faster.

Test - Cognos 8 BI Administrator

Take a practice test at the link below to check your knowledge on Cognos 8 BI administration, before attempting the cognos certification exam COG-122:

http://www.wiziq.com/online-tests/24969-cognos-8-bi-administrator

Best Practices – Cognos Report Studio

Listing out some best-practices which a developer should follow while developing reports in Cognos Report Studio. For more information, go through Report Studio User Guide.

Formatting:

1. Classes - Define classes for all objects in the report. It would help in maintaining consistency in formatting of all the objects throughout the report page.

2. Consistency – Maintain consistency for the formatting options (Font, Margin, Border, etc.) throughout the report. This will help in improving readability.

3. Report Expressions - Add Report expressions above the crosstab for the selection made in the prompts. This will help the user know the selections made in the report; It would also help in the scenario, where there is no data for the selections.

Filters & Prompts:

1. Ensure that the report contains filters for every dimension required. The rows fetched from the database will be less.

2. Use ‘=’ instead of ‘in’ for single value comparison in filters. This reduces the query run time.

3. Use CASE statement instead of IF…ELSE, for implementing conditional filter. This helps in improving the performance of the filter execution.

4. Delete filters which are not required instead of disabling them. This is required as unused Code hampers the performance of the report.

5. If a prompt is kept required; the corresponding filter in the query should also be kept required. This is done to maintain consistency between filter and prompt.

Calculations:

1. Use in-built calculations instead of calculating manually. This improves performance for the execution of report.

2. Assign proper solve order for the calculated data items. This helps in proper sequence of execution of the calculations.

3. Use explicit brackets for expression definitions. The calculations may give wrong answer if the Brackets are not used in the expressions or calculations of data items.

4. Avoid the usage of tuple function in Calculations unless necessary. The use of tuple function affects the performance as it goes to the detailed level of data, and does it for every record.

Query Items:

1. Remove all unnecessary objects from the report. This improves execution because when the report is executed, the data is fetched for all the data items in the report.

2. Use data items instead of “Calculated members” and “Calculated measures”. The calculations of members should be done in data items. It helps to reduce the run time of calculations.

3. Special Characters should be avoided in the Query names. This can result in the user being unable to generate the SQL/MDX query.

4. For the query items which are not involved in any type of calculations, set the Aggregate Function and Roll Up Aggregate to NONE. This improves execution of report.

Charts:

1. Show Tool Tip for the report items displayed on the chart. This helps the user to easily understand the chart values.

2. Define single Palette for all the charts in the report. This helps to maintain consistency for all the charts used.  3. When using grouping between the data items in the Series; if you want to hide the 2^nd data item, keep the source type for that data item to text and don’t put any text.

General:

1. Use brackets in ‘IF…ELSE’, ‘CASE.’ and other expression. Not using Brackets in the control structures or expressions may result in Parsing error and readability of the expression reduces.

2. Minimum use of Java Script. Usage of Java script might affect the performance of the report.

3. Use Render variable instead of Style variable to show or hide any report items. Style variable executes the item on the report page and then hides it but the render variable does not execute the item which is not to be displayed in the report page.

4. Use Boolean variables instead of String variables unless string variables are unavoidable. Boolean variables get executed faster as compared to String variables.

Cognos Test - Report Studio

Have created the below online-test which can be taken by anyone to check their knowledge on Cognos Report Studio before attempting the cognos certification exam COG-112:

http://www.wiziq.com/online-tests/24850-cognos-report-studio-basics

Cognos Report Studio - II

Q. What is Condition Explorer in Report Studio?

Ø  Condition Explorer allows you to manage the variables that are being used in the report.

Q. What is a Page set in Cognos Report Studio?

Ø  The set of pages to render according to a grouping structure.

Q. What are classes in Cognos Report Studio?

Ø  Classes provide a default style to be applied to the objects.

Q. How you do “Conditional Highlighting” on a report in Report Studio?

Ø  The steps to add conditional highlighting to a report are listed below:

a)     Select the list column body for which you want to set conditional highlighting.

b)    From the Style toolbar, click Conditional Styles.

c)     In the New Conditional Style list, select Advanced Conditional Style.

d)    Type a name for the style. Click on New Advanced Condition.

e)     In the Expression Definition pane, specify terms for the expression.

f)     Select a style from the Style list. Edit to define a custom style.

g)    Click Ok and run the report.

  Q. How do you convert a list to cross-tab in Cognos Report Studio?

Ø  To convert a list to a cross-tab, follow the listed steps:

            1. Click the columns that you want to appear as columns or nested columns in the cross-tab.

            2. From the structure menu, click “Pivot List to Crosstab”.

Q. How can you hide an object in Report Studio?

Ø  Set its “Box Type” property to “None”.

Q. How do you hide the rows that have null values?

Ø  You can hide the rows having null values by applying conditional formatting as when you find null values in rows make the box type none in the properties.

Q. How can you hide a column in Report Studio?

Ø  For Crosstab reports –

a)     Group on the column and create aggregation to have the column total in the bottom row.

b)    Click the Unlock button on Report studio toolbar.

c)     Now select the “text item” and delete it.

d)    Select the empty crosstab node after deleting the text. Change its padding to 0 pixels and font to 0.001 pt size.

Q. How can you Specify what appears when no data is available in a report?

Ø  The steps  are as listed below:

a)     Select the data container in the report.

b)    From the properties pane, set the “No Data Contents” property to Yes.

c)     No data contents tab is displayed, which can be used to drop “Text item” or “Layout Calculation” to be displayed in case of no data in report.

COGNOS TRAINING

Below is the detailed course outline of Cognos 8 training. For further information, contact guideoncognos@gmail.com.

1. Overview of Business Intelligence

                      i.        What is Business Intelligence

                     ii.        Importance & Uses of BI

                    iii.        Components of BI

                    iv.        Business Intelligence Roadmap

2. Overview of Data Warehousing

                      i.        What is Data Warehouse

                     ii.        OLTP and OLAP systems

                    iii.        Data Warehouse Architecture

                    iv.        Star Schema

3. Cognos 8 BI introduction

                      i.        Cognos Architecture

                     ii.        Installation of Cognos 8 BI

                    iii.        Configuring Cognos 8 BI

                    iv.        Different studios in Cognos 8 BI

4. Cognos 8 BI Administration

                      i.        Create and Modify Data Sources

                     ii.        Create Users, Roles and Groups

                    iii.        Define permission/security settings

5. Cognos Connection QuickTour

                      i.        Navigation - Public and Private Folder

                     ii.        Scheduling – Report, format options, job

                    iii.        Create URL, Portals, Pages, Report views

                    iv.        Cognos Configuration, Content Store

                     v.        Security setup and Deployment(import,export)

6. Cognos Query Studio

                      I.        Introduction to Query Studio

                     II.        Create Reports (List, Crosstab, Charts, Grouped List)

                    III.        Report Layout and formatting

                   IV.        Sorting, Grouping & Subtotals

                    V.        Applying Filters, Prompts & Calculations

                   VI.        Drill Through Reports

                  VII.        Run and Manage Reports

7. Cognos Analysis Studio

                      I.        Introduction to Analysis Studio

                     II.        Creating a basic Analysis

                    III.        Insert data, Nest data

                   IV.        Exploring data

                    V.        Change measure, Drill down

                   VI.        Insert a calculation, Sharing data

8. Cognos Report Studio

                      I.        Introduction to Report Studio

                     II.        Creating List, Crosstab, Chart, Repeater, Maps

                    III.        Formatting Reports

                   IV.        Creating prompts, filters, calculations

                    V.        Conditional formatting

                   VI.        Master detail and drill through reports

                  VII.        Report bursting

                 VIII.        Setting Variables

                   IX.        Set operators

                    X.        Report functions & Report validation

9. Cognos Framework Manager

                      I.        Introduction to Framework Manager

                 (User Interface, Navigation, Objects, Metadata modeling)

                     II.        Creating Model & Namespace (Layers)

                    III.        Importing Metadata

                   IV.        Creating Query Subjects & Query Items

                    V.        Creating Relationships & setting cardinalities

                   VI.        Verify model, publishing package

                  VII.        Add Business Rules & Set Governors

                 VIII.        Assigning package level security

                   IX.        Calculations & Filters

10. Cognos Event Studio

 

                      I.        Introduction to Event Studio

                     II.        Creating Agents

                    III.        Specify an Event condition

                   IV.        Scheduling Agent