Cognos complete FAQ'S
1.Data-Warehouse Concepts
2.Introduction to Cognos 8 BI
3.Dimensional Modeling4.Framework Manager
5.Best Practices – Cognos Report Studio
6.Cognos Report Studio - II
7.Cognos filter & prompts
1.Data-Warehouse Concepts
Q. What is a Data Warehouse?
Ø A data warehouse is integrated information collected from multiple sources that becomes the foundation for decision support and data analysis.
Ø A data warehouse is a relational database that is designed for query and analysis rather than transaction processing. It usually contains historical data that is derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
Characteristics of Data Warehouse
1. Subject Oriented: A data warehouses is designed with the purpose of analyzing a particular area of business, e.g. Sales or Finance.
2. Integrated: Data warehouses have the data from disparate sources put into a consistent format.
3. Static/Non volatile: As the data is made for analysis it is said to be static orNonvolatile means that the data should not change once entered into the warehouse.
4. Time Variant: Historical data has to be maintained to analyze the business or market trends.
Q. What is Data Mart?
Ø Data Marts can be said to be a subset of data warehouse or can be a small data warehouse itself. It is logical grouping of the data warehouse dimensions and the related fact, created to meet a specific group of users or requirements. It is a repository of data gathered from operational data and other sources that is designed to serve a particular community of knowledge workers. E.g. costing Data Mart, sales Data Mart. A Data Mart tends to be tactical and aimed at meeting an immediate need or meeting the specific demands of a particular group of knowledge users in terms of analysis, content, presentation, and ease-of-use. Users of a Data Mart can expect to have data presented in terms that are familiar or specific to a group of users. Data marts can be part of an Enterprise Wide Data Warehouse.
Q. What are the different types of data-mart?
Ø Data-marts are classified based on the data-sources used to build the data-mart.
The different types of data-mart are:
a) Dependent data-mart: built using data-warehouse as a source.
b) Independent data-mart: built using the operational data source.
c) Hybrid/Federated data-mart: using both data-warehouse and operational data source.
Q. What is metadata?
Ø Metadata is the information about the data. This is the layer of the data warehouse, which stores the information about the various aspects of data warehouse, like the source data, transformed data, date and time of data extraction, target databases, date and time of data loading, how the structures and calculation rules are stored, plus, possibly, additional information on data sources, definitions, quality, transformations, date of last update, user access privileges, etc. For ex in BO, the repository is the Metadata. In an ETL tool, the metadata contains information about the source tables, target tables, transformations, mappings etc. An Ideal situation is when we can control everything in a Data Warehouse through a Useful Metadata; that means data loading, data cleansing, transformations, reporting, admin activities, security, etc.
Q. What is DataWarehousing? Why is it useful and important?
Ø Data Warehousing is a way to convert huge volume of data into useful information, which can be used for making business decisions.
It is useful in the below situations:
a) Complex Analysis
b) What if Analysis?
c) Past & present trend analysis
d) Moving averages
e) Multidimensional Analysis
f) Slice and Dice of data
g) Drill Down and Drill Up to different levels of data
Q. What are the differences between OLTP and OLAP (Data-Warehouse)?
Ø The differences between OLAP and OLTP can be listed as below:
OLAP
|
OLTP
| |
Definition
|
On Line Analytical Processing
|
On Line Transactional Processing
|
Example
|
Data Warehouse
|
ERP, Legacy system
|
Data
|
Static. A time frame is decided to load the data into data warehouse, so data remains stagnant over a certain period.
|
Dynamic. As the updates, deletions and modifications are online the data is continuously changing thus not helpful for analysis or decision making
|
History
|
Historical data is stored thus making it difficult to study the trend of the business over the past to help in analysis.
|
Old data is purged or archived
|
Data Atomicity
|
Data is aggregated or summarized and stored at the higher level.
|
Data is stored at microscopic level
|
Normalization
|
Denormalized database are used to maintain detailed information in a row of record
|
Normalized databases
|
Joins in queries
|
Lesser and easier joins as the tables are Denormalized
|
More and complex joins as the tables are normalized
|
User
|
Senior management or Sales and marketing head to analyze the business trends and make decisions
|
Operational staff adding, modifying or deleting day to day transactions
|
Performance
|
Faster and better and ease of use as non technical people can create ad-hoc reports
|
Complex
|
Read-write
|
Read only data
|
Data can be updated, modified or deleted
|
Update Frequency
|
Updated at a fixed interval of time
|
Continuously updated
|
Example
|
Data Warehouse
|
ERP, Legacy system
|
Q. What are the major stages/steps in a Data Warehousing project?
Ø The steps in a data warehousing project can be listed as:
a) Understanding the business
b) Understanding the present and future needs
c) Strategic Planning
d) Design of Data Warehouse
e) Extraction of Data from different sources to a common staging area
f) Cleaning of data
g) Transformation of Data
h) Transportation of Data
i) Analysis of data (OLAP)
Q. What are the different tools used in data-warehousing projects?
Ø The tools used in data-warehousing projects can be categorized into three technical streams:
a) Database (Data warehouse, Data Mart).
b) ETL (Extraction, Transformation, Load).
c) Reporting (OLAP Tools).
Some tools and products available from different vendors are listed below:
2.Introduction to Cognos 8 BI
Ø Cognos 8 Business Intelligence is an integrated enterprise solution. It is Enterprise business intelligence software with integrated reporting, score-carding, analysis and event management capabilities. Cognos8 can perform Business Intelligence across multiple data sources and Applications.
Q. What are the new features of Cognos 8 BI?
Ø The new features of Cognos 8 BI can be listed as below:
1. Power-Play Web is replaced by Analysis Studio.
2. Metrics Manager is replaced by Metric Studio.
3. Decision-Stream is replaced by Data Manager.
4. Event Studio is added to provide event lifecycle management.
5. Open data access.
6. Dimensional Modeling of Relational Data Sources in Framework Manager.
Q. What are the different Cognos products?
Ø Some Cognos products can be listed as below:
1. Cognos Impromptu
2. Cognos Powerplay
3. Cognos Decision Stream
4. Cognos Data Manager
5. Cognos Reportnet 1.1
6. Cognos 8BI
7. Cognos Enterprise Planning
Q. Give overview of Cognos 8BI architecture?
Ø Cognos 8BI architecture can be separated into three tiers:
1. Web Server
2. Applications
3. Data
These tiers are based on business function, and are typically separated by firewalls. User interfaces sit above the tiers.
Q. What are the different interfaces and components available in Cognos 8BI?
Ø The user interfaces available in Cognos 8BI can be divided into:
1. Web based (accessed through internet explorer)
2. Windows based
Ø The components in Cognos 8BI are:
1. Web based – Query Studio; Report Studio; Event Studio; Metric Studio; Analysis Studio; Cognos Connection
2. Windows based – Framework Manager; Cognos Powerplay; Map Manager; Metric Designer
Q. Describe the different components available in Cognos 8BI?
Ø The components of Cognos 8BI are listed below:
1. Query Studio - used to create simple queries and reports. Basic templates and formatting can be applied.
2. Analysis Studio - used for data analysis through ranking, complex filters for large volumes of data.
3. Metric Studio - used to create score carding and dash board reports, assign tasks for poorly performing metrics and linking to reports containing related information.
4. Report Studio - used to create professional standard reports including dashboards, maps, charts etc., fully automated drill through is available.
5. Event Studio - has agents that check the data. When an agent detects a particular condition, Event studio takes action by generating emails, running reports etc.
6. Cognos Connection - used for managing content and administration of Cognos server.
7. Framework Manager - used to create packages based on relational, dimensional cubes. Packages are published to Cognos connection which will be used to create reports in various studios.
8. Powerplay transformer - Used to create powerplay cubes.
9. Map Manager - Used to create user defined maps.
10. Metric Designer - Used to create metrics which are used to create scorecards in metric studio.
Q. What are the different models used for report development in Cognos 8BI?
Ø The different models used for development in Cognos 8BI can be as listed:
1. Relations Models – based on relational databases like Oracle, db2.
2. Dimensionally Modelled Relational database (DMR) – used to provide dimensional approach to a relational database.
3. OLAP Sources – publishing packages based on cubes.
Ø Cognos 8 Business Intelligence is an integrated enterprise solution. It is Enterprise business intelligence software with integrated reporting, score-carding, analysis and event management capabilities. Cognos8 can perform Business Intelligence across multiple data sources and Applications.
Q. What are the new features of Cognos 8 BI?
Ø The new features of Cognos 8 BI can be listed as below:
1. Power-Play Web is replaced by Analysis Studio.
2. Metrics Manager is replaced by Metric Studio.
3. Decision-Stream is replaced by Data Manager.
4. Event Studio is added to provide event lifecycle management.
5. Open data access.
6. Dimensional Modeling of Relational Data Sources in Framework Manager.
Q. What are the different Cognos products?
Ø Some Cognos products can be listed as below:
1. Cognos Impromptu
2. Cognos Powerplay
3. Cognos Decision Stream
4. Cognos Data Manager
5. Cognos Reportnet 1.1
6. Cognos 8BI
7. Cognos Enterprise Planning
Q. Give overview of Cognos 8BI architecture?
Ø Cognos 8BI architecture can be separated into three tiers:
1. Web Server
2. Applications
3. Data
These tiers are based on business function, and are typically separated by firewalls. User interfaces sit above the tiers.
Q. What are the different interfaces and components available in Cognos 8BI?
Ø The user interfaces available in Cognos 8BI can be divided into:
1. Web based (accessed through internet explorer)
2. Windows based
Ø The components in Cognos 8BI are:
1. Web based – Query Studio; Report Studio; Event Studio; Metric Studio; Analysis Studio; Cognos Connection
2. Windows based – Framework Manager; Cognos Powerplay; Map Manager; Metric Designer
Q. Describe the different components available in Cognos 8BI?
Ø The components of Cognos 8BI are listed below:
1. Query Studio - used to create simple queries and reports. Basic templates and formatting can be applied.
2. Analysis Studio - used for data analysis through ranking, complex filters for large volumes of data.
3. Metric Studio - used to create score carding and dash board reports, assign tasks for poorly performing metrics and linking to reports containing related information.
4. Report Studio - used to create professional standard reports including dashboards, maps, charts etc., fully automated drill through is available.
5. Event Studio - has agents that check the data. When an agent detects a particular condition, Event studio takes action by generating emails, running reports etc.
6. Cognos Connection - used for managing content and administration of Cognos server.
7. Framework Manager - used to create packages based on relational, dimensional cubes. Packages are published to Cognos connection which will be used to create reports in various studios.
8. Powerplay transformer - Used to create powerplay cubes.
9. Map Manager - Used to create user defined maps.
10. Metric Designer - Used to create metrics which are used to create scorecards in metric studio.
Q. What are the different models used for report development in Cognos 8BI?
Ø The different models used for development in Cognos 8BI can be as listed:
1. Relations Models – based on relational databases like Oracle, db2.
2. Dimensionally Modelled Relational database (DMR) – used to provide dimensional approach to a relational database.
3. OLAP Sources – publishing packages based on cubes.
3.Dimensional Modeling
Q. What is dimensional modeling?
Ø It is a logical design technique used for building data-warehouses. It uses the concept of facts and dimensions. Dimensional Modeling is intended to support end-user queries in a data-warehouse.
Dimensional modeling visualizes the data in terms of Cubes. For example, if we want to measure Sales by Products, Customers, Location, And Time of a company. We can visualize that the x, y a z-axis of the cube depicts products, customers, location and time, every point in the cube depict the sales. This is a very simple way of representing the business.
The Dimensional modeling is also known as Star Schema. The reason is that in Dimensional modeling we have a large central table with many dimension tables surrounding it.
Q. What is dimensional modeling?
Ø It is a logical design technique used for building data-warehouses. It uses the concept of facts and dimensions. Dimensional Modeling is intended to support end-user queries in a data-warehouse.
Dimensional modeling visualizes the data in terms of Cubes. For example, if we want to measure Sales by Products, Customers, Location, And Time of a company. We can visualize that the x, y a z-axis of the cube depicts products, customers, location and time, every point in the cube depict the sales. This is a very simple way of representing the business.
The Dimensional modeling is also known as Star Schema. The reason is that in Dimensional modeling we have a large central table with many dimension tables surrounding it.
Q. What are the various available schemas for dimensional modeling?
Ø Star Schema
Ø Snowflake Schema
Ø Multistar Schema
Ø Aggregate schema
Ø Star Schema
Ø Snowflake Schema
Ø Multistar Schema
Ø Aggregate schema
Q. What is a Star Schema?
Ø The star schema is the simplest data warehouse schema. It is called a star schema because the diagram of a star schema resembles a star, with points radiating from a centre. The centre of the star consists of one or more fact tables and the points of the star are the dimension tables. The main advantage to a star schema is optimized performance. A star schema keeps queries simple and provides fast response time because all the information about each level is stored in one row.
Ø The star schema is the simplest data warehouse schema. It is called a star schema because the diagram of a star schema resembles a star, with points radiating from a centre. The centre of the star consists of one or more fact tables and the points of the star are the dimension tables. The main advantage to a star schema is optimized performance. A star schema keeps queries simple and provides fast response time because all the information about each level is stored in one row.
Q. What is a Snowflake Schema?
Ø The snowflake schema is a complex data warehouse schema. A snowflake schema is a set of tables comprised of a single, central fact table surrounded by normalized dimension hierarchies. Each dimension level is represented in a table. Snowflake schema implements dimensional data structures with fully normalized dimensions.
Q. What is MultiStar Schema?
Ø MultiStar Schema: It is various star schemas joined to create a Data Warehouse.
Q. What is MultiStar Schema?
Ø MultiStar Schema: It is various star schemas joined to create a Data Warehouse.
Q. What is a measure/fact?
Ø A performance indicator that is quantifiable and used to determine how well a business is operating. For example, measures can be Revenue, Revenue/Employee, Profit margin % etc.
Ø In relational modeling, this is also called a “fact”.
The different types of fact are:
Additive: Data that can be aggregated by using the sum function e.g. Sales
Semi additive: Data that cannot be aggregated directly over time. E.g. Inventory, account balances
Non-additive: Data that cannot be aggregated at all e.g. Time
Q. What are the different types of fact tables?
Ø The different types of fact table can be listed as below:
a) Cumulative Fact Table: This type of fact table describes what has happened over a period of time. For Example- This fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The above example given for “Additive Facts” is a Cumulative fact table.
b) Snapshot Fact Table: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. For Example - The table given above for Semi-additive fact is an example of Snapshot fact table.
Q. What is a Fact-less Fact table?
Ø A fact-less fact is a fact table in which only primary keys of the dimension tables present as foreign keys but no individual fact columns.
Q. What is a dimension?
Ø Dimensions are different perspectives through which a person can analyze the business measures.
Ø Dimensions contain descriptive data of a business.
Example – Geography, Product, Company, Time
Q. What are the types of dimensions?
Ø The different types of dimensions can be listed as below:
a. Conformed dimension - A dimension which can be shared by multiple fact tables is known as conformed dimension. It has exactly the same meaning and content when being referred from different fact tables.
b. Junk dimension - A junk dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. In simple terms, a randomly used dimension is junk dimension.
c. Degenerated dimension – It is a dimension which has only a single attribute. This dimension is typically represented as a single field in fact table. These dimensions are used when fact tables represent transactional data.
Q. What are Slowly Changing Dimensions?
Ø Slowly Changing Dimensions are the dimensions in which attribute values keep on changing.
Example – Product price of Products, Address/Phone No. of Employees/Customers
Q. What are the different types of SCD’s/Different methods to track changes in SCD?
Ø The different methods/types of SCD’s can be listed as below:
a) Type 1: Overwriting old value with new value.
In this type of SCD, the old value will be lost.
Example: The phone number of the customer “C1” is changed from “2341233” to 55210456”.
b) Type 2: Recording new values as new records.
In this type, the new values will be stored into new records. So, Type 2 SCD records both new as well as old values.
Example: The new Phone number for the customer “C1” is recorded again in a new row.
Issue in Type 2 Tracking: In the above example, The Customer ID column has duplicate values and it loses the primary key properties. Here, Surrogate Key comes into picture. Surrogate Key acts as the primary key for the dimension table.
c) Type 3: Old values are stored in new columns.
In this method, when the values are updated in dimension tables, a new column is created in which the old value gets stored.
Q. What is a Surrogate key? Where is it used? Explain with example?
Ø Surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table. The
only requirement for a surrogate primary key is that it is unique for each row in the table.
Data warehouses typically use a surrogate (also known as artificial or identity key) key for the dimension tables primary keys. It is useful because:
a) The natural primary key (i.e. Customer Number in Customer table) can change.
Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but not only can these change; indexing on a numerical value is probably better and you could consider creating a surrogate key called say AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.
b) Another benefit you can get from surrogate keys (SID) is, the Tracking of SCD - Slowly Changing Dimension.
Example: As described above for Type 2 SCD, the phone number of a Customer “C1” changes and it is recorded in a new row; thus making the primary key, “Customer Id” duplicate. Here, a surrogate key would be useful in tracking the change in dimension. Two new columns, Surrogate Key and Status would help in tracking the change with the status column indicating whether the phone no is current or expired.
Ø In relational modeling, this is also called a “fact”.
The different types of fact are:
Additive: Data that can be aggregated by using the sum function e.g. Sales
Semi additive: Data that cannot be aggregated directly over time. E.g. Inventory, account balances
Non-additive: Data that cannot be aggregated at all e.g. Time
Q. What are the different types of fact tables?
Ø The different types of fact table can be listed as below:
a) Cumulative Fact Table: This type of fact table describes what has happened over a period of time. For Example- This fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The above example given for “Additive Facts” is a Cumulative fact table.
b) Snapshot Fact Table: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. For Example - The table given above for Semi-additive fact is an example of Snapshot fact table.
Q. What is a Fact-less Fact table?
Ø A fact-less fact is a fact table in which only primary keys of the dimension tables present as foreign keys but no individual fact columns.
Q. What is a dimension?
Ø Dimensions are different perspectives through which a person can analyze the business measures.
Ø Dimensions contain descriptive data of a business.
Example – Geography, Product, Company, Time
Q. What are the types of dimensions?
Ø The different types of dimensions can be listed as below:
a. Conformed dimension - A dimension which can be shared by multiple fact tables is known as conformed dimension. It has exactly the same meaning and content when being referred from different fact tables.
b. Junk dimension - A junk dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. In simple terms, a randomly used dimension is junk dimension.
c. Degenerated dimension – It is a dimension which has only a single attribute. This dimension is typically represented as a single field in fact table. These dimensions are used when fact tables represent transactional data.
Q. What are Slowly Changing Dimensions?
Ø Slowly Changing Dimensions are the dimensions in which attribute values keep on changing.
Example – Product price of Products, Address/Phone No. of Employees/Customers
Q. What are the different types of SCD’s/Different methods to track changes in SCD?
Ø The different methods/types of SCD’s can be listed as below:
a) Type 1: Overwriting old value with new value.
In this type of SCD, the old value will be lost.
Example: The phone number of the customer “C1” is changed from “2341233” to 55210456”.
b) Type 2: Recording new values as new records.
In this type, the new values will be stored into new records. So, Type 2 SCD records both new as well as old values.
Example: The new Phone number for the customer “C1” is recorded again in a new row.
Issue in Type 2 Tracking: In the above example, The Customer ID column has duplicate values and it loses the primary key properties. Here, Surrogate Key comes into picture. Surrogate Key acts as the primary key for the dimension table.
c) Type 3: Old values are stored in new columns.
In this method, when the values are updated in dimension tables, a new column is created in which the old value gets stored.
Q. What is a Surrogate key? Where is it used? Explain with example?
Ø Surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table. The
only requirement for a surrogate primary key is that it is unique for each row in the table.
Data warehouses typically use a surrogate (also known as artificial or identity key) key for the dimension tables primary keys. It is useful because:
a) The natural primary key (i.e. Customer Number in Customer table) can change.
Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but not only can these change; indexing on a numerical value is probably better and you could consider creating a surrogate key called say AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.
Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but not only can these change; indexing on a numerical value is probably better and you could consider creating a surrogate key called say AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.
b) Another benefit you can get from surrogate keys (SID) is, the Tracking of SCD - Slowly Changing Dimension.
Example: As described above for Type 2 SCD, the phone number of a Customer “C1” changes and it is recorded in a new row; thus making the primary key, “Customer Id” duplicate. Here, a surrogate key would be useful in tracking the change in dimension. Two new columns, Surrogate Key and Status would help in tracking the change with the status column indicating whether the phone no is current or expired.
Example: As described above for Type 2 SCD, the phone number of a Customer “C1” changes and it is recorded in a new row; thus making the primary key, “Customer Id” duplicate. Here, a surrogate key would be useful in tracking the change in dimension. Two new columns, Surrogate Key and Status would help in tracking the change with the status column indicating whether the phone no is current or expired.
Q. What are OLAP, MOLAP, ROLAP and HOLAP?
Ø OLAP: On-Line Analytical Processing. A category of applications and technologies for collecting, managing, processing and presenting multidimensional data for analysis and management purposes.
Ø MOLAP: Generally when the OLAP is based on a Multi dimensional server than it is called as MOLAP. An example is Express objects of Oracle, Express Objects has a server, which contains the cubes, this server is then used for reporting, and the cubes contain the data.
· High performance, multidimensional data storage format.
· Data is stored on the OLAP server.
· Gives the best query performance, for small to medium-sized data sets
Ø ROLAP: With ROLAP data remains in the original relational tables. A separate set of relational tables is used to store and reference aggregation data. ROLAP is ideal for large databases or legacy data that is infrequently queried.
Example: Business Objects, the relations of the data Warehouse is stored in the Repository, which is actually a set of tables in a RDBMS.
Ø HOLAP: It’s the combination of ROLAP and MOLAP.A example is HOLOS.
Ø OLAP: On-Line Analytical Processing. A category of applications and technologies for collecting, managing, processing and presenting multidimensional data for analysis and management purposes.
Ø MOLAP: Generally when the OLAP is based on a Multi dimensional server than it is called as MOLAP. An example is Express objects of Oracle, Express Objects has a server, which contains the cubes, this server is then used for reporting, and the cubes contain the data.
· High performance, multidimensional data storage format.
· Data is stored on the OLAP server.
· Gives the best query performance, for small to medium-sized data sets
Ø ROLAP: With ROLAP data remains in the original relational tables. A separate set of relational tables is used to store and reference aggregation data. ROLAP is ideal for large databases or legacy data that is infrequently queried.
Example: Business Objects, the relations of the data Warehouse is stored in the Repository, which is actually a set of tables in a RDBMS.
Ø HOLAP: It’s the combination of ROLAP and MOLAP.A example is HOLOS.
Q. What is aggregation in a Data Warehouse? What is aggregate navigation?
Ø Usually in Data Warehouses, the facts store the data by a very low level grain, something like “I want to store the sales of each product by customer by day by store in the fact table”. If this is our grain statement, and suppose we have a base fact of 10 million records, then to calculate the sales of one customer for a period of 3 years would go through many records (actually all the records of that customer for that period). This whole process would be slow. To improve performance of such queries, we design special tables in the Data Warehouse which contain data at higher granularity, in our case we can design a table containing, the monthly sales of each customer by each product. So to calculate the total sales of a Customer for 3 years would become very easy if this table is accessed. Such tables are called aggregates, as data is aggregated.
The challenge comes in for the front-end tools to understand when to look in these tables and when to look in the basic fact table. This is known as aggregate navigation. When an OLAP tool has aggregate navigation feature, it can automatically select the right table depending upon the query fired by the User, without the user knowing anything about the background processes. For example, Business Objects handle aggregate navigation by something known as aggregate aware. This feature allows the Designer to refer to the aggregate table when a query of higher granularity than the base fact is fired.
Ø Usually in Data Warehouses, the facts store the data by a very low level grain, something like “I want to store the sales of each product by customer by day by store in the fact table”. If this is our grain statement, and suppose we have a base fact of 10 million records, then to calculate the sales of one customer for a period of 3 years would go through many records (actually all the records of that customer for that period). This whole process would be slow. To improve performance of such queries, we design special tables in the Data Warehouse which contain data at higher granularity, in our case we can design a table containing, the monthly sales of each customer by each product. So to calculate the total sales of a Customer for 3 years would become very easy if this table is accessed. Such tables are called aggregates, as data is aggregated.
The challenge comes in for the front-end tools to understand when to look in these tables and when to look in the basic fact table. This is known as aggregate navigation. When an OLAP tool has aggregate navigation feature, it can automatically select the right table depending upon the query fired by the User, without the user knowing anything about the background processes. For example, Business Objects handle aggregate navigation by something known as aggregate aware. This feature allows the Designer to refer to the aggregate table when a query of higher granularity than the base fact is fired.
Q. What are popular OLAP tools?
OLAP Tool
Company
Business objects
Business Objects
Powerplay
Cognos
SAS Software
SAS
Seagate Info
Crystal Decisions
Sagent Data Mart solutions
Sagent Technologies
Oracle Advanced Analytic Services
Oracle
MicroStrategy Intelligence Server
Microstrategy
Microsoft Analysis Services
Microsoft
Intelligent Decision Server
IBM
Hummingbird Pablo
Humming Bird
OLAP Tool
|
Company
|
Business objects
|
Business Objects
|
Powerplay
|
Cognos
|
SAS Software
|
SAS
|
Seagate Info
|
Crystal Decisions
|
Sagent Data Mart solutions
|
Sagent Technologies
|
Oracle Advanced Analytic Services
|
Oracle
|
MicroStrategy Intelligence Server
|
Microstrategy
|
Microsoft Analysis Services
|
Microsoft
|
Intelligent Decision Server
|
IBM
|
Hummingbird Pablo
|
Humming Bird
|
4.FRAMEWORK MANAGER
Q. What are the components/objects in a FM project?
Ø The components of a Framework Manager project can be listed as:
Project à Model à Namespace à Query Subject à Query Item
Q. What are the components of Framework Manager screen?
Ø The different components can be listed as below:
a) Project Viewer – shows project objects in a hierarchical view. Used to view, modify and create objects.
b) Explorer Tab – shows the contents of a project like any file system. Used to view, create and modify objects and relationships.
c) Diagram Tab – shows the relationships between objects in a project. Used to view, create and modify objects and relationships. Also used to change diagram settings, “Capture Screen” and “Set Focal Point”.
d) Dimension Map – Used to view, create and modify hierarchies and levels for the dimension selected in Project viewer.
e) Properties Pane – shows the properties of the object that is selected in the project viewer.
f) Tools Pane – The different tabs in tools pane are listed below:
a) Summary Tab
b) Search Tab
c) Dependencies Tab
Q. What is the extension of FM project file? What all files constitute a FM project?
Ø The extension of a FM project file is .cpf.
Below is a list of all files that constitute a project:
a) ProjectName.cpf – Project File (references the .xml files)
b) IDLog.xml – used to track objects for models
c) Log.xml – list of all modifications made to the model
d) Model.xml – actual model data
e) Preferences.xml –
f) Persistent.txt – Diagram information
Q. What are the different types of Query Subjects?
Ø The different types of query subjects can be listed as below:
a) Data Source Query Subject.
b) Model Query Subject.
c) Stored Procedure Query Subject.
Q. What are the different types of Stored Procedures in Framework Manager?
Ø Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.
The different types of Stored Procedures in Framework Manager are:
1. Data Query Stored Procedure – Issues a read-only transaction.
2. Data Modification Stored Procedure – Writes a record to the data source.
Q. How to build a model and publish a package?
Ø Steps to build a model and package:
1. Open Framework Manager and click on “Create a new Project”.
2. Select language and select data source.
3. Select the tables, views, procedures etc. to import.
4. Create relationship and click on import.
5. Right click on the package and select to create a package option.
6. Choose objects and select function sets.
7. Select location and click on publish.
Q. How do you create IQD in Framework Manager?
Ø We can create IQD by setting the externalize property and select property to IQD.
Steps to follow:
1. Select the Query subjects and go to Properties.
2. Select externalize method option.
3. Select property to IQD.
4. Save the model.
5. While publishing, specify the location for IQD files.
6. You can find your IQD’s in the specified location after publishing the package.
Q. What is loop in Framework Manager?
Ø Loop is a closed path (relation) that exists among 3 (or) more tables.
For example, if we have '3' tables T1, T2, T3 then, a loop exists among these tables only when we create joins in the following fashion:
Loop: T1 ---> T2 ---> T3 ---> T1
To resolve the above problem, we have to create a shortcut (or) Alias to the Table T1.
No Loop: T1 ---> T2 ---> T3 ---> Alias (or) Shortcut of T1
Loop: T1 ---> T2 ---> T3 ---> T1
To resolve the above problem, we have to create a shortcut (or) Alias to the Table T1.
No Loop: T1 ---> T2 ---> T3 ---> Alias (or) Shortcut of T1
Q. What is difference between Shortcut and Alias Shortcut?
Ø Shortcut is a pointer to an object.
While a regular shortcut is a simple reference to the target object; Alias shortcut behaves as a copy of the original object with completely independent behavior.
5.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 2nd 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.
Q. What are the different types of reports in report Studio?
Ø The different types of reports available in Report Studio are listed as:
1. List
2. Crosstab
3. Chart
4. Map
5. Repeater
Q. What are Prompts in Cognos Report Studio? List the different type of prompts?
Ø Prompts act as questions that help users to customize the information in a report.
Different type of prompts can be listed as:
a) Text Box Prompt – Retrieves data based on a value that user’s type.
b) Value Prompt – Retrieves data based on values that user’s select from a list.
c) Search & Select Prompt – Retrieves values based on search criteria that users specify.
d) Date Prompt – Retrieves data based on a date that users select.
e) Time Prompt – Retrieves data based on a time that users select.
f) Date & Time Prompt – Retrieves data based on a date & time that users select.
g) Interval Prompt – Retrieves data based on a time interval that users specify.
h) Tree Prompt – Retrieves data based on values that users select from a list (Returns a set of members from hierarchy). It is used when working with dimensional data.
i) Generated Prompt – Selects a prompt control based on the data type of the data item.
Q. What are Cascading prompts?
Ø Using cascading prompts, the values in one prompt are driven by what is selected in another prompt.
Q. What are the different types of filters available in Report Studio?
Ø Report Studio – Report studio provides three types of filtering:
a) Detail filter – Detail filters are used to filter detail level values within a report. They work at the lowest level of granularity in the selected objects.
Example: We can apply a detail filter on “Sales Month” to extract all transactions which occurred within the specified month.
b) Summary filter – Summary filters are used to filter summary level values within a report.
Example: We can apply a summary filter to get the highest sold product in a sales region.
We would need to group the “Region” and “Product” fields beforehand.
c) Slicers – Slicers are used with dimensional models. A slicer filter affects the cell value, not the row or column edges. Slicers are useful if you want some data in a report to be hidden and other data to be visible.
Example: We can apply a slicer if we want a report displaying all “Product” names but sales data only for a few and not all products.
Q. What are the different types of variables in Report Studio?
Ø The different variables are :
a) Boolean Variable
b) String Variable
c) Report Language Variable
Q. What are classes in Report Studio?
Ø Classes in Report Studio provide a default style to be applied to the objects.
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’s the difference between Drill-through report & Master-detail report?
Ø Drill-through Report – Links 2 separate reports containing related information. (Target & Source report). Steps to create are listed below:
a) Create a target report with detail filter that uses the parameter on which you want to filter when drilling through from the source report. Save the report.
b) Create a source report that contains the item you want to use for drill-through.
c) Select the item to be used as the drill-through and click on Drill-Through definitions button on the standard toolbar.
d) Click new drill through definition button. Select the target report and fill the required options.
e) Click Edit to display the parameters. Select method as “Pass Data item value”. Select value from the source report.
f) Click Ok and then run the report.
Ø Master Detail Report – Links information between two data containers (eg. List, crosstab, chart) within a report. Using master-detail, you can use a single report to display information from two reports. Steps to create are listed below:
a) Create a new report having two data containers E.g. Crosstab and Chart. Insert data items into both containers so that 2 queries are created E.g. Query1 and Query2.
b) The 2 queries should have at-least 1 Common data-item.
c) Click “Unlock” icon from Toolbar to unlock page objects. Select 2nd container object, and drag it into the 1st container where you want to place it.
d) Click embedded container object. Click Master Detail Relationships from Data menu. Create “New Link” between the common data-item.
e) Click Ok and run the report.
Q. How do you set security for reports?
Ø You can set security from the report properties in Cognos Connection. You can add users / user classes with read/write/execute/traverse/set policy level access.
Cognos Connection à Report Properties à Permissions
Q. What is a “Layout Component Reference” in Report Studio?
Ø “Layout Component Reference” is an object in Report Studio which is used to give a reference to another object in the report. It helps to reuse an object instead of creating it again.
Example – It can be used to include a Company Logo on all pages of a report.
Steps:
a) Create 2 page report; List on first page and Crosstab on second.
b) Add Company logo on first page and give it a unique name.
c) On second page, add “Layout Component Reference” object where you want the same logo to be displayed.
d) Check on “This Report” option and select the “Company Logo” object.
Q. What are the different types of reports in report Studio?
Ø The different types of reports available in Report Studio are listed as:
1. List
2. Crosstab
3. Chart
4. Map
5. Repeater
Q. What are Prompts in Cognos Report Studio? List the different type of prompts?
Ø Prompts act as questions that help users to customize the information in a report.
Different type of prompts can be listed as:
a) Text Box Prompt – Retrieves data based on a value that user’s type.
b) Value Prompt – Retrieves data based on values that user’s select from a list.
c) Search & Select Prompt – Retrieves values based on search criteria that users specify.
d) Date Prompt – Retrieves data based on a date that users select.
e) Time Prompt – Retrieves data based on a time that users select.
f) Date & Time Prompt – Retrieves data based on a date & time that users select.
g) Interval Prompt – Retrieves data based on a time interval that users specify.
h) Tree Prompt – Retrieves data based on values that users select from a list (Returns a set of members from hierarchy). It is used when working with dimensional data.
i) Generated Prompt – Selects a prompt control based on the data type of the data item.
Q. What are Cascading prompts?
Ø Using cascading prompts, the values in one prompt are driven by what is selected in another prompt.
Q. What are the different types of filters available in Report Studio?
Ø Report Studio – Report studio provides three types of filtering:
a) Detail filter – Detail filters are used to filter detail level values within a report. They work at the lowest level of granularity in the selected objects.
Example: We can apply a detail filter on “Sales Month” to extract all transactions which occurred within the specified month.
b) Summary filter – Summary filters are used to filter summary level values within a report.
Example: We can apply a summary filter to get the highest sold product in a sales region.
We would need to group the “Region” and “Product” fields beforehand.
c) Slicers – Slicers are used with dimensional models. A slicer filter affects the cell value, not the row or column edges. Slicers are useful if you want some data in a report to be hidden and other data to be visible.
Example: We can apply a slicer if we want a report displaying all “Product” names but sales data only for a few and not all products.
Q. What are the different types of variables in Report Studio?
Ø The different variables are :
a) Boolean Variable
b) String Variable
c) Report Language Variable
Q. What are classes in Report Studio?
Ø Classes in Report Studio provide a default style to be applied to the objects.
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’s the difference between Drill-through report & Master-detail report?
Ø Drill-through Report – Links 2 separate reports containing related information. (Target & Source report). Steps to create are listed below:
a) Create a target report with detail filter that uses the parameter on which you want to filter when drilling through from the source report. Save the report.
b) Create a source report that contains the item you want to use for drill-through.
c) Select the item to be used as the drill-through and click on Drill-Through definitions button on the standard toolbar.
d) Click new drill through definition button. Select the target report and fill the required options.
e) Click Edit to display the parameters. Select method as “Pass Data item value”. Select value from the source report.
f) Click Ok and then run the report.
Ø Master Detail Report – Links information between two data containers (eg. List, crosstab, chart) within a report. Using master-detail, you can use a single report to display information from two reports. Steps to create are listed below:
a) Create a new report having two data containers E.g. Crosstab and Chart. Insert data items into both containers so that 2 queries are created E.g. Query1 and Query2.
b) The 2 queries should have at-least 1 Common data-item.
c) Click “Unlock” icon from Toolbar to unlock page objects. Select 2nd container object, and drag it into the 1st container where you want to place it.
d) Click embedded container object. Click Master Detail Relationships from Data menu. Create “New Link” between the common data-item.
e) Click Ok and run the report.
Q. How do you set security for reports?
Ø You can set security from the report properties in Cognos Connection. You can add users / user classes with read/write/execute/traverse/set policy level access.
Cognos Connection à Report Properties à Permissions
Q. What is a “Layout Component Reference” in Report Studio?
Ø “Layout Component Reference” is an object in Report Studio which is used to give a reference to another object in the report. It helps to reuse an object instead of creating it again.
Example – It can be used to include a Company Logo on all pages of a report.
Steps:
a) Create 2 page report; List on first page and Crosstab on second.
b) Add Company logo on first page and give it a unique name.
c) On second page, add “Layout Component Reference” object where you want the same logo to be displayed.
d) Check on “This Report” option and select the “Company Logo” object.
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.
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.
6.Cognos Report Studio
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.
Ø 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.
7.Cognos filter & prompts ?
Filters:
Filters are used to remove unwanted data from the report.
Filters are two types 1) detailed filter 2) summary filter
Detail filter: The working of detail filter is based on APPLICATION
For Ex:
The report has the following columns
[year], [product name],[Quantity]
The filter I applied as follows….
If I select “Before auto aggregation”, what will happen?
The data will be filtered based on qty<500 at database level. Whatever the records are there which are less than 500 will be summed up for year, product line and result will be displayed.
If I select “After auto aggregation”, what will happen,
First the records are aggregated for year, productname then the filter qty<500 will be applied on those aggregated records, If there are any records statisfying qty<500 then we will get the output
Summary filter:
IF I want to use this summary filter, I should have groups and groups totals in the report.
While applying summary filter, we should set scope to specify particular subtotals to apply the filter weather filter to be applied on product type totals, or product line total or year totals…
PROMPTS:
All the filter are hardcoded. IF u run the report any number of times the same filter will be applied and the same output we will get.
Suppose If you want to select filter values at run time, then we use prompts..
There are several prompts that cognos supports
- Text box prompt
- Value prompt
- Select & search prompt
- Data prompt
- Time prompt
- Date & time prompt
- Tree prompt
- Interval prompt
- Generated prompt
There are several ways to get the prompts…
1) When we use parameter( for ex: ?x? ) in the filter then we will get prompt automatically..
When we run the report, Cognos will create a prompt automatically as follows...
The automatic generated prompt may not consistent…, suppose if you want prompt at center of the page, it is not possible here, suppose if I want select & search prompt, I cant change here..
2) Select the columns in the report page for which u want to create prompts, then go to tools menu, select Build prompt page option as follows…
Then the prompt page will be created automatically with prompts for all the three columns..
This method is the quickest way to create prompts…
3) Fully customized method. In this method we can create prompt page and prompts manually..
Point on Page explorer and click on “Prompt page” folder as follows..
Drag “page” from tool box to prompt page folder…
Doubl click on Prompt page1 to maximize prompt page..
Text box prompt
It does not display values in it, we have to imagine values
Value prompt
This will display values from database.
Drawbacks:
1) 1) Performance is slower (Every time u run the report, it has to connect to the database and bring the values..)
2) It can't display more than 5000 values
3) 3) We have to scroll down/up for searching the values..
Select & search prompt….
All the above drawback will be resolved in S&S prompt…
Filters:
Filters are used to remove unwanted data from the report.
Filters are two types 1) detailed filter 2) summary filter
Detail filter: The working of detail filter is based on APPLICATION
For Ex:
The report has the following columns
[year], [product name],[Quantity]
The filter I applied as follows….
If I select “Before auto aggregation”, what will happen?
The data will be filtered based on qty<500 at database level. Whatever the records are there which are less than 500 will be summed up for year, product line and result will be displayed.
If I select “After auto aggregation”, what will happen,
First the records are aggregated for year, productname then the filter qty<500 will be applied on those aggregated records, If there are any records statisfying qty<500 then we will get the output
Summary filter:
IF I want to use this summary filter, I should have groups and groups totals in the report.
While applying summary filter, we should set scope to specify particular subtotals to apply the filter weather filter to be applied on product type totals, or product line total or year totals…
PROMPTS:
All the filter are hardcoded. IF u run the report any number of times the same filter will be applied and the same output we will get.
Suppose If you want to select filter values at run time, then we use prompts..
There are several prompts that cognos supports
- Text box prompt
- Value prompt
- Select & search prompt
- Data prompt
- Time prompt
- Date & time prompt
- Tree prompt
- Interval prompt
- Generated prompt
There are several ways to get the prompts…
1) When we use parameter( for ex: ?x? ) in the filter then we will get prompt automatically..
When we run the report, Cognos will create a prompt automatically as follows...
The automatic generated prompt may not consistent…, suppose if you want prompt at center of the page, it is not possible here, suppose if I want select & search prompt, I cant change here..
2) Select the columns in the report page for which u want to create prompts, then go to tools menu, select Build prompt page option as follows…
Then the prompt page will be created automatically with prompts for all the three columns..
This method is the quickest way to create prompts…
3) Fully customized method. In this method we can create prompt page and prompts manually..
Point on Page explorer and click on “Prompt page” folder as follows..
Drag “page” from tool box to prompt page folder…
Doubl click on Prompt page1 to maximize prompt page..
Text box prompt
It does not display values in it, we have to imagine values
Value prompt
This will display values from database.
Drawbacks:
1) 1) Performance is slower (Every time u run the report, it has to connect to the database and bring the values..)
2) It can't display more than 5000 values
2) It can't display more than 5000 values
3) 3) We have to scroll down/up for searching the values..
Select & search prompt….
All the above drawback will be resolved in S&S prompt…
Thank you for sharing valuable information.
ReplyDeleteMicrostrategy Online Training