Cognos Architecture

<HEAD>
<meta name="last-review" content="12-10-2001">
<meta name="title" content="Model with Architect">
<meta name="product" content="Architect - BI">

Cognos
(R)
Architect
<meta name="version" content="7.0">
<meta name="prodversion" content="Architect - BI-7.0">
<meta name="area" content="support">
<meta name="classification" content="documentation">
</HEAD>
Model with Architect

Copyright
Architect version 7.0
This edition published 2001.
Copyright (C) 2001 Cognos Incorporated.
Portions Copyright (C) Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399 USA. All rights reserved.
Portions Copyright (C) 1989 to 1998 eXcelon Inc. All rights reserved.
ObjectStore, Object Design, the Object Design logo, LEADERSHIP BY DESIGN, and Object Exchange are registered trademarks of
eXcelon Inc. ObjectForms and Object Manager are trademarks of eXcelon Inc.
Sample product images with the pound symbol (#) in the lower right hand corner are copyright (C) 1998 PhotoDisc, Inc.
This software/documentation contains proprietary information of Cognos Incorporated. All rights are reserved. Reverse engineering of this
software is prohibited. No part of this software/documentation may be copied, photocopied, reproduced, stored in a retrieval system,
transmitted in any form or by any means, or translated into another language without the prior written consent of Cognos Incorporated.
Cognos, the Cognos logo, Axiant, COGNOSuite, Cognos Upfront, Cognos DecisionStream, Impromptu, NoticeCast, PowerCube,
PowerHouse, PowerPlay, Scenario and 4Thought are trademarks or registered trademarks of Cognos Incorporated in the United States
and/or other countries. All other names are trademarks or registered trademarks of their respective companies.
While every attempt has been made to ensure that the information in this document is accurate and complete, some typographical errors or
technical inaccuracies may exist. Cognos does not accept responsibility for any kind of loss resulting from the use of information contained in
this document.
This document shows the publication date. The information contained in this document is subject to change without notice. Any
improvements or changes to either the product or the document will be documented in subsequent editions.
U.S. Government Restricted Rights. The software and accompanying materials are provided with Restricted Rights. Use, duplication, or
disclosure by the Government is subject to the restrictions in subparagraph (C)(1)(ii) of the Rights in Technical Data and Computer Software
clause at DFARS 252.227-7013, or subparagraphs (C) (1) and (2) of the Commercial Computer Software - Restricted Rights at
48CFR52.227-19, as applicable. The Contractor is Cognos Corporation, 67 South Bedford Street, Burlington, MA 01803-5164.
Table of Contents
Chapter 1: Welcome 5
Chapter 2: Modeling Guidelines 7
The Architect Model 7
Verify the Contents of the Model 9
Import Metadata 9
Import Everything into the Model 10
Define Cross-Database Joins 10
Define Additional Keys 11
Add Business Information 11
Design Effective Entities 12
Normalize Entities 13
Combine Tables into One Entity 14
Define Effective Relationships 15
Filter Data 19
Prepare the Model for Reporting and Querying 20
Add Security to the Model 20
User Classes and Their Privileges 20
The Role of Security Administrator 21
Set Up Administrative Access 21
Set Up User Access 22
Chapter 3: Create Models for Cognos Query 25
Cognos Query and Architect 25
Objects in Packages and Foundation Queries 25
Import Metadata 25
Design Queries 26
Provide Calculated Information 27
Use Prompts to Filter Data 29
Categorize Data 30
Prepare the Model for Querying 31
Design Cognos Query Packages 32
Combine Information into One Query 32
Create Queries That Users Can Navigate Between 33
Create Summary Queries That Drill Down to Detailed Queries 34
Publish Packages to Upfront 36
Chapter 4: Create Models for Impromptu 37
Impromptu and Architect 37
Objects in Packages and Catalogs 37
Import Metadata 37
Import Catalogs into Architect 38
Define Relationships 39
Provide Calculated Information 41
Prepare the Model for Reporting 41
Import Security Information from a Catalog 42
Model with Architect iii

Export Packages to Impromptu 43
Use Existing Impromptu Reports with Exported Catalogs 45
Chapter 5: Create Models for PowerPlay Transformer 47
Transformer and Architect 47
Architect and PowerPlay Integration 47
How Architect Objects Correspond to Transformer Objects 47
Use An Architect Package as a Transformer Data Source 48
Set Attribute Usage 48
Define an Architect Attribute as a Transformer Measure 49
Architect Relationships and Transformer Dimensions 49
How the Number of Levels in a Dimension is Determined 50
Include a Date Dimension 50
Advanced Modeling Concepts 50
Normalize Entities 51
Create Alternate Drill-Down Paths 51
Design Filters 51
Define Calculated Attributes 52
Index 55
iv Architect (R)
Chapter 1: Welcome
Architect is a metadata management tool that provides a single point of administration for the
metadata that supports all your Cognos reporting and analytical products. You use Architect to
build a common set of business-oriented metadata so that your users can build queries, reports,
and cubes.
Architect is a custom component of the Impromptu Administrator and Cognos Query
installations. For information about installing each of these products, see the installation and
configuration guide for each product.
What Is in This Book

Model with Architect contains modeling guidelines that will help you make design decisions
when creating Architect models. This book also contains specific modeling guidelines for
creating models that are intended for Cognos Query and Impromptu users.
If you are new to Architect, we recommend you complete the tutorial lessons
Discovering Architect to get you started with the product.
What You Need to Know to Use This Product Effectively

To effectively use Architect, you must be familiar with the data and metadata that resides in your
databases. In addition, data modeling knowledge is an asset.
Other Architect Documentation

A list of other documentation is available from the Books for Printing command in the Architect
Help menu.
How to Order Extra Books

You can order extra copies of the printed documentation that is shipped with Architect. Please
contact your local Cognos office to order Discovering Architect or the Architect Quick Reference
Card. You can also print your own copies of the online versions of all online books in Adobe
Acrobat Reader.
Questions or Comments?
For the fastest response to questions about using Architect, contact customer support.
For information about customer support locations and programs, see Cognos on the Web on the
Help menu or visit the Cognos Support Web site (http://support.cognos.com).
Model with Architect 5

Chapter 1: Welcome
6 Architect ( R )
Chapter 2: Modeling Guidelines
To design and create an effective Architect model, there are general modeling guidelines that
you should follow. These guidelines apply to all types of Architect models, regardless of whether
they are designed for Cognos Query, Impromptu, or PowerPlay Transformer.
The Architect Model

Architect is a business intelligence (BI) metadata management tool. An Architect model
provides a central location for your data warehouse metadata as well as creating a reusable
environment for calculations, filters, prompts and display properties.
An Architect model can be used
• to generate a catalog for use by Impromptu
• as a data source by PowerPlay Transformer for use when defining a cube
• to publish queries to Upfront, Cognos' portal on the web, for use by Cognos Query.
The Architect model describes the metadata from the underlying sources (data access layer),
the business view of the corporate data (business layer), and specifies which parts of the
business layer will be used in reports, queries, or cubes (package layer).
For information about the general modeling process, see the Architect Quick Reference Card.
Data Access Layer

The data access layer contains the metadata that describes how to retrieve data from the
physical environment. Tables, columns, joins, and other database objects may be included, as
well as SQL statements and user-defined functions. The data may exist in multiple sources,
such as relational databases or Impromptu HotFiles, or may be retrieved by using stored
procedures, user-defined functions, or SQL statements.
Business Layer
The business layer describes the business view of the corporate data as well as the information
that your report authors and query designers require. It consists of the business model (entities,
attributes, and relationships), which you can view as an entity-relationship diagram, business
rules (calculations, filters, and prompts), and display rules (styles, and enumeration values).
Architect generates objects in the business layer based on the objects in the data access layer.
You can modify the business layer to reflect your users’ reporting and querying requirements.
Package Layer
The package layer contains a default package that references every object in the business layer.
You also create and store custom packages in the package layer. Custom packages usually
reference specific portions of the business layer. Each package may be targeted for one or more
Cognos products (PowerPlay Transformer, Impromptu, and Cognos Query), and designed for
specific users.
Objects in Each Layer

When you create or generate model objects (such as tables, entities, and relationships), each
object is associated with an object in another layer. In most cases
• objects in the data access layer are based on the metadata objects residing in the
databases that you import into the Architect model
• objects in the business layer are based on the objects in the data access layer
• objects in the package layer are based on the objects in the business layer

The following chart shows the most common associations between model objects. Not
applicable indicates that the layer does not contain an object that corresponds to an object in
another layer.
Metadata object in Object in data Object in business Object in

database access layer layer package layer
Schema Schema Not applicable Not applicable
Database catalog Database catalog Not applicable Not applicable
Table Table Entity Subject
Column Column Attribute Subject attribute
Join Join Association, Query path

container, or
reference
relationship
Alias or Synonym Synonym Subtype entity Subject
View View Entity Subject
Primary key Key Key Not applicable
Index Index Not applicable Not applicable
Stored procedure Stored procedure Entity Subject
Not applicable User-defined function Not applicable Not applicable
Not applicable SQL query Entity Subject
Not applicable Not applicable Filter Subject filter
Not applicable Not applicable Prompt Subject prompt
Not applicable Not applicable Style Not applicable
Not applicable Not applicable Enumeration value Not applicable
8 Architect ( R )
Verify the Contents of the Model

Verifying the contents of an Architect model at specific stages of its development is very
important. The verification process analyzes the model and ensures that it does not contain
logical design errors or inconsistencies across the data access, business, and packaging layers.
If you do encounter errors or warnings, the verification process usually suggests corrective
action.
If you do not have a valid and internally consistent model, you may not be able to export or
publish it, and the resulting reports or queries may not work at run time.
Ensure that you verify the model at these important stages of development:
• Before you build the business layer, verify the data access layer.
• Before you create packages, verify the business layer.
• Before you export the model to an Impromptu catalog or publish it to Upfront, verify
packages.
• After you import and set up user class access to portions of the model, verify security.
Verify the Data Access Layer

Before you build the business layer, ensure that the contents of the data access layer are valid.
If it contains a severe design problem, you cannot build the business layer until you correct the
problem. Or, if you are able to build the business layer and the data access layer contains slight
errors, you will create the same problems in the business model. More work is then required to
fix the problems in both layers, and it may be more difficult to find the source of each problem.
Verify the Business Layer

Before you create packages, verify the business layer. The same rationale applies to verifying
the business layer as verifying the data access layer. Before you proceed to build and design a
layer of the Architect model, verify the preceding layer or layers.
Verify Packages
Before you export the model to an Impromptu catalog or publish it to Upfront, verify packages.
Packages rely on the contents in each layer, and the design of each layer has an effect on the
run-time performance of queries and reports. Therefore, you should verify the contents of the
entire model, not just the package layer. If you verify only the package layer, the verification
process may indicate that the packages are acceptable. However, there may be an error in
another part of the model that may affect run-time performance.
Verify Security
After you import and set up user class privileges to portions of the model, verify security. You
can import user classes into an Architect model at any time during its development. However,
you will most likely set up access privileges after you create all the required packages. Then you
can test everyone’s access privileges before you deploy the model to the production
environment. At this stage of development, you should verify that the model does not contain
conflicting security settings, and that all user classes are defined properly.
After you deploy the model to your production environment, you should verify security again. If
the model contains security errors, users may not be able to run reports or queries, and access
privileges or restrictions to confidential information may fail.
Import Metadata
To create foundation queries for Cognos Query, dimension and level candidates for Transformer,
and catalogs for Impromptu, you must first import the required metadata sources into the
Architect model. If your users require information that resides in more than one metadata
source, you may need to import more than one source.

The imported metadata appears in the data access layer. Handle the contents of the data
access layer like an internal snapshot of the data source. If you modify an object in the data
access layer, you create an inconsistency between the object in the model and the
corresponding object in the data source. Inconsistencies may cause queries to fail at run time.
To properly design the data access layer, follow these guidelines:
• Do not modify the metadata properties of any object, such as the data type of a column.
However, you can add annotations that describe that object.
• Do not add, delete, or move columns between tables.
Instead, organize related objects in the business or package layers.
• Do not add, delete, or combine tables.
Instead, organize related objects in the business or package layers.
• Do not delete keys.
If you delete a key from a table, and a user requires information from that table in the
database, the query may not retrieve the correct information, or the query may not run at all.
• Do not delete joins in the data access layer.
Instead, modify or delete the corresponding relationships in the business layer. For more
information, see "Define Effective Relationships" (p. 15).
• Do not modify the cardinality of joins.
Instead, change the cardinality of the corresponding relationships in the business layer. For
more information, see "Include or Exclude NULL Values in a Report" (p. 15).
The only circumstances when you should modify the data access layer are when you must
• import additional metadata into the model
However, we recommend that you import everything into the model at the beginning of its
development.
• define SQL queries that will retrieve data explicitly through syntax developed by the modeler
For more information about defining SQL queries, see the Architect User Guide.
• define cross-database joins in order to use a single query to retrieve data that resides in
more than one data source
Import Everything into the Model

When you import metadata into an Architect model, you can import everything at once, or you
can import specific metadata objects. Even though users may not currently need access to all
the information in the data source, we recommend that you import all the metadata into the
model before you start working in the business layer or creating packages. Including everything
in the model makes the metadata readily accessible to you if a user has a new information
request, and creates a model that can evolve.
If you exclude metadata during the import process, you may need to import metadata each time
a user has a new information request. In addition, you would need to update each model layer
before you could make the information available to the user. In some situations, adding new
metadata can affect the existing design of the model. You may need to redesign existing objects
to accommodate the new objects.
Define Cross-Database Joins

Joins in the data access layer define the links between tables and other objects, such as views
and synonyms. When you import metadata, Architect gathers all available metadata about
tables and keys, and uses declared referential integrity to form table joins. Architect stores join
metadata in the data access layer and uses it to generate relationships in the business layer.
The only situation in which you should define joins in the data access layer is when a user wants
to retrieve information in one query, but the information resides in more than one data source.
You must create cross-database joins to meet this requirement. Import the required data
sources and define the required joins between them. Cross-database joins also facilitate the
creation of a cohesive business model in the business layer. If you do not want to add joins in
the data access layer, you can create cross-database relationships in the business layer
instead. Cross-database joins or relationships must exist in the Architect model for users to be
able to run cross-database queries.
10 Architect ( R )
We recommend that you individually define each required join. This approach ensures that each
join is accurately defined and is required in the model. You can automatically generate all
possible joins between tables, but you could produce unnecessary or invalid joins if unrelated
keys in the underlying data source have matching names. We recommend that you
automatically generate joins only if you are absolutely sure that the primary and corresponding
foreign keys in the data source have the same names. If you want to automatically generate
joins, try it in a test model first. If the results are satisfactory, you can repeat the process in the
production model.
If a user wants to retrieve information from two tables that reside in the same data source, but
the join does not exist, we recommend that you define these additional relationships in the
business layer. The information request represents a business requirement, and therefore
should be maintained in the business layer. The only joins you should maintain in the data
access layer are those you import from the data source, or those that indicate a logical
relationship between multiple data sources. For information about creating relationships, see
"Define Additional Relationships" (p. 15).
Define Additional Keys

Architect uses keys in the data access layer to generate keys in the business model. A key
uniquely identifies each instance of a record in a table. In the data source, a key that identifies a
unique instance is called the primary key, and a key that identifies how to retrieve a unique
instance from another table is called a foreign key.
Uniqueness of instances is important in business modeling. For example, you can identify each
customer by assigning them a unique customer number in the database. Then you can
associate purchase information with the exact customer, even if some customers have the same
name.
Uniqueness is also important in query construction and processing techniques. For example,
you can create joins between related tables and form summaries. You can create joins between
columns for which keys don’t exist. However, without a key definition, a query may have to
search an entire table before finding the required information.
Define additional keys only when you must create a join, and the required keys do not exist in
the data access layer. This situation occurs when foreign and primary key constraints are not
defined or complete in the original data source. We recommend that you individually define each
required key. This approach ensures that the key is accurately defined. To define keys
individually, you must create them in the business layer. For more information, see "Define
Unique Keys for Entities" (p. 13).
You can create additional keys in the data access layer. However, this approach automatically
generates all possible keys. To generate them, you can use either the Data Access Layer wizard
or the Refine Model wizard. You can automatically generate all possible keys if the table indexes
in the data source have the same names as the corresponding columns. If you want to
automatically generate keys, try it in a test model first. If the results are satisfactory, you can
repeat the process in the production model.
Add Business Information

Before you prepare a model for reporting and querying, you must know the type of information
your users want to retrieve, and how they want the information presented. After you know the
data requirements, you can add this information to the model. However, to provide your users
with accurate and meaningful information, you must know how to properly add business
information and rules to the Architect model. Otherwise, the model will not be effective.

Within the business layer you can

• rename entities and attributes
• refine relationships between the entities, such as creating a subtype relationship, or
changing a containment relationship to a reference relationship
• modify the cardinality between entities
• add new entities that combine information from multiple tables
• divide separate concepts from a single table into multiple entities
• add calculated attributes, create prompts, and apply filters
• add keys or change the definition of existing keys
Design Effective Entities

An entity is an object in the business layer that represents a category of information, usually a
significant concept of your business. For example, you have information about the vendors who
buy your products, such as names, addresses, cities, and postal zone. This is a specific
category of information, which you could include in a Customer entity. In addition, an entity can
contain attributes that reference one or more tables, synonyms, views, or SQL queries that
appear in the data access layer.
The main purpose of designing and using entities is to organize the data into distinct business
concepts that your users can understand. How you design entities directly affects how users
create reports and queries. Entity design also affects the actual performance of the queries at
run time. Therefore, as you design entities, it is important to consider both the information needs
of your users, and the types of queries that they will run.
To effectively design entities and attributes, data modeling knowledge is an asset.
Keep All Entities and Attributes in the Business Layer

During modeling activities in the business layer, you may decide that your users no longer need
information that is represented in the model, such as in an entity or attribute. Instead of deleting
objects that are not required, keep them in the business layer for possible future use, but do not
include them in a package. It is easier to keep the metadata readily available in the model than it
is to remodel it when a user has a new information request. The package layer is the most
efficient place to make this type of customization.
In addition, if you delete an object in the business layer, it may hinder the performance of reports
and queries at run time. For example, if you delete an attribute that represents a primary or
foreign key in the underlying database table, a user running a report or query against that table
will not get results. You can exclude subject attributes that represent keys from a package, such
as when users do not want code-type information in a report). However, ensure that the
corresponding key and join is defined in the data access layer.
Delete an object in the business layer if you did not design it correctly in the first place, or you
want to refine relationships between entities. For more information, see "Define Effective
Relationships" (p. 15).
Store Calculated Attributes in Related Entities

When you create calculated attributes, store the calculation in an entity that is logically related to
the information or concept that the calculation represents. Storing calculations in the appropriate
entities facilitates the performance of reports and queries at run time.
For example, this attribute calculates actual revenue:
Actual Revenue = [OrderDetail.Quantity] * [OrderDetail.UnitSalePrice]
The calculation refers to a Quantity attribute and a UnitSalePrice attribute, both of which reside
in the OrderDetail entity. Logically, you should also store the Actual Revenue attribute in the
OrderDetail entity.
Do not create an entity for the sole purpose of storing multiple, calculated attributes. Because
calculated attributes reference other attributes, rather than directly referencing columns, queries
would not be able to find the required tables at run time. According to proper design guidelines,
an entity that contains only calculations does not represent a distinct business concept.
12 Architect ( R )
Define Unique Keys for Entities

To optimize user queries, ensure that each entity you create has a unique key. When you build
the business layer, keys are automatically created based on the keys in the data access layer. If
you must define additional keys in the business layer, you can create them individually.
When you define a key, choose the fewest columns that you know will uniquely identify each
row. Do not define a calculated attribute as a key. It may not uniquely identify each record in a
table. In addition, do not modify or delete keys in the business layer. For more information, see
"Keep All Entities and Attributes in the Business Layer" (p. 12).
Normalize Entities
Normalization is the process of organizing tables or entities so that data is not duplicated, and
data modification is more efficient. Normalization usually involves dividing larger tables or
entities into smaller ones so that each column or attribute within a table or entity is distinct and
describes the overall concept that the table or entity represents. Normalizing tables or entities
conforms to the rules of Third Normal Form.
We recommend that you create and maintain normalized entities in the business layer.
Normalization is especially important if you are creating an Architect model that is used by more
than one Cognos product. If the entities are normalized, all the products can effectively use the
model. For example, normalized entities facilitate the creation of dimension, measure, and level
candidates in PowerPlay Transformer, and users can query a distinct portion of data in Cognos
Query or Impromptu.
If a user wants denormalized information in their reports or queries, create denormalized
subjects in the package layer. For example, denormalized subjects enable a Cognos Query user
to retrieve all required information in one query.
The only way to normalize entities in the business layer is to use the Normalize Entity wizard. Do
not attempt to manually create new entities. Using the wizard ensures that the relationships
between the new entities are set up properly.
When you normalize entities, you are not required to include all the attributes from the
denormalized entity in the new entities. However, we recommend that you include them all. If
you accidentally exclude an attribute that represents a key, the relationship is not defined
properly, and queries will fail at run time. If users do not need access to an attribute, exclude the
corresponding subject attribute from the package.
If the data source already contains normalized tables, you do not need to normalize the entities
in the business layer. It is already done for you.
Normalize entities when
• the data source contains merged or denormalized tables because the database
administrator wanted to optimize space allocations or schema organization.
This type of data source may contain a combination of normalized and denormalized tables.
If so, modify only the denormalized entities in the business layer.
• the data source is used as a data warehouse and the tables are organized into a star
schema.
You should modify every entity in the business layer so that they represent a snowflake
schema. If you are normalizing entities to use in PowerPlay Transformer, create an entity for
each level within each dimension. However, you may not need to normalize entities if your
Transformer modeler is familiar with the structure of the data warehouse and knows exactly
which dimensions and levels to create. Contact your Transformer modeler and ask them
whether they want to receive denormalized or normalized metadata.
Example
You want to normalize an entity that acts as a dimension table in a star schema. The entity is
called ProductDimension and it contains three business concepts: product line, product type,
and product. The entity also has a one-to-many relationship with an entity called SalesFact.

Using the Normalize Hierarchical Entity wizard, you divide the ProductDimension entity into
three new entities and allocate the attributes to the appropriate entity.
Entity Attributes
Product ProductNumber (key)

ProductCode (foreign key of SalesFact)
ProductName
ProductPicture
ProductURL
ProductType (foreign key of ProductType).
ProductType ProductType (key)

ProductLine (foreign key of ProductLine)
ProductLine ProductLine (key)
Then you arrange the entities so that their order reflects the one-to-many relationships they
have with each other. The order is ProductLine (which has a 1..1 to 1..n relationship with
ProductType), ProductType (which has a 1..1 to 1..n relationship with Product), and Product
(which has a 1..1 to 1..n relationship with the SalesFact entity).
Combine Tables into One Entity

You should combine tables into one entity only when two tables contain information that
represents one distinct business concept, such as vertically partitioned tables. The join between
the similar tables usually indicates that for each record in one table, there are zero or one
records (0..1 or 1..1) in the second table. The same rule applies to the records in the second
table.
Because the tables contain almost the same information, you are not really creating a
denormalized entity by combining them in the business layer. Instead, you are removing the
physical implementation of the data source from the business layer.
The only way to combine tables into one entity is to specify the option when you build the
business layer. Do not attempt to manually combine entities. For more information, see the
Architect User Guide.
Do not combine tables that have a one-to-many relationship into one entity. Instead, combine
the corresponding entities into one subject in the package layer. For more information, see
"Combine Information into One Query" (p. 32).
Combine Vertically Partitioned Tables

Vertically partitioned tables are multiple tables that share the same key, have a one-to-one or
zero-to-one join, and all have a different set of columns. For example, a Warehouse and a
WarehouseInventory table each contains a different set of columns, but the join between them
indicates that they have a one-to-one relationship.
To represent warehouse information as one distinct business concept, you can automatically
combine these two tables into one entity when you build the business layer. If you include the
combined entity in a package for Cognos Query, consider whether the query designer must
identify the tables that the subject attributes reference. If so, rename the columns so that they
convey that information.
14 Architect ( R )
Define Effective Relationships

By defining effective relationships in the business layer, you influence query path selection and
increase query performance at run time. Effective relationships also more accurately reflect the
reporting and information requirements of your users.
Define Additional Relationships

When a user wants to retrieve information in one query, but the information resides in more than
one table and the required join is not defined in the data source, you can define the relationship
in the business layer. If you are absolutely sure that information in each entity is logically related,
define the required relationship. If you define relationships between entities that are not logically
related, users will not retrieve accurate information in their queries or reports.
When you create the relationship, ensure that
• you properly define the cardinality
For more information, see "Include or Exclude NULL Values in a Report" (p. 15).
• you do not create a recursive relationship
If you want to indicate that two instances from the same entity are related, create a subtype
entity. For more information, see "Resolve Recursive Relationships" (p. 16).
Include or Exclude NULL Values in a Report

If users want to retrieve information that resides in more than one table, determine whether they
want to retrieve
• only those records that have corresponding data in both tables
Define mandatory relationships.
• all records, even if there is missing data (NULL values) in one of the tables
Define optional relationships.
To control the type of information that appears in a query or report, you may need to modify the
cardinality of the relationships in the business layer. By modifying the relationships in the
business layer rather than the joins in the data access layer, you are reflecting the information
needs of your users. If you modify the joins in the data access layer, you are not accurately
reflecting the join conditions in the underlying data source.
Cardinality is a property of a join or relationship that describes the association between two
tables or entities. Architect uses a form of Unified Modeling Language (UML) to express
cardinality:
• 0..1 (one optional)
• 1..1 (one required)
• 0..n (zero or more optional)
• 1..n (one or more required)
The first part of the notation specifies whether the association is optional (0) or mandatory (1).
The second part defines the association of records between the tables or entities (1=1,
n=many). To interpret cardinality, you must also consider the notation that appears at both ends
of a join or relationship.
Define Mandatory Relationships

Use mandatory relationships when users do not want to retrieve NULL values in their reports or
queries. Mandatory relationships have the same effect as inner joins. When you build the
business layer, any outer joins in the data access layer are automatically defined as mandatory
relationships in the business layer.

Mandatory relationships indicate that for one instance in an entity, there is at least one
corresponding instance in the second entity. These relationships also restrict the type of
information that a query can retrieve. The query can retrieve information only if it conforms to the
cardinality notation. Matching records must exist in two related tables. If one table does not
contain a record that matches a record in the other table, the data source inserts a NULL value
as a placeholder. Mandatory relationships do not retrieve information that contain NULL values.
To avoid NULL values in the data source, some administrators enter a record that contains a
zero (0) value. Because the data source considers zero values to be valid data, mandatory
relationships retrieve them.
For example, to indicate that each branch has one or more salespeople, and each salesperson
works only at one branch, you create an association relationship between the SalesBranch and
SalesStaff entities. Then you set the cardinality as 1..1 to 1..n. The relationship indicates that for
one instance in SalesBranch, there is at least one or more instances in SalesStaff (one or more
required). For one instance in SalesStaff, there is only one instance in SalesBranch (one
required).
Define Optional Relationships

Use optional relationships when users want to retrieve all information in their reports or queries,
even if there are NULL values. Optional relationships indicate that for one instance in an entity,
there are zero or more corresponding instances in the second entity. These relationships do not
restrict the information that a query can retrieve. If a record exists in a table, but a matching
record does not exist in the related table, the query retrieves it.
If you use optional relationships, we recommend that they represent left or right-outer joins. Do
not create full-outer joins unless your users absolutely need them. A full-outer join indicates that
the join criterion is optional for both tables. That is, the cardinality notation indicates that each
record in the first table has zero or more corresponding records in the second table. The same
rule applies for each record in the second table. If your users require full-outer joins, you should
warn them that they take a lot of processing time.
For example, managers want a sales report that lists every salesperson and shows the total
number of orders each person had for the year. They want all the salespeople to appear on the
report, even if they did not have any orders. In the business layer, you create an association
relationship between the SalesStaff and OrderHeader entities. Then you set the cardinality as
1..1 and 0..n. The relationship indicates that for one instance in SalesStaff, there are zero or
more instances in OrderHeader (zero or more optional). For one instance in OrderHeader, there
is only one instance in SalesStaff (one required). If the cardinality were 1..1 to 1..n, only
salespeople who had orders would be listed in the report.
Resolve Recursive Relationships

When you import metadata, you may import a table that has a self-join. A self-join, also known
as a recursive relationship, indicates that there is an association between two records that
reside in the same table. Because self-joins are not supported by Cognos reporting and query
products, you must resolve them. Do not modify self-joins in the data access layer. Otherwise,
the resulting queries may not retrieve data. Instead, modify the corresponding recursive
relationship in the business layer.
To resolve a recursive relationship, you can
• create a subtype entity of the entity that has the recursive relationship, delete the recursive
relationship, and define a regular relationship between the original entity and the subtype.
• delete any recursive relationships that are not required by your users.
You simplify the business layer when you reduce the number of entities and relationships,
and you do not expose the implementation details in the business layer. However, be careful
that you do not delete a relationship that you want to use for other purposes.
16 Architect ( R )
Example
The SalesStaff table contains information about salespeople and managers. There is a self-join
(0..1 to 0..n) that relates each salesperson to a manager. The join is between the StaffId and
ManagerId columns.
When you build the business layer, the self-join becomes a recursive relationship. The
cardinality is automatically defined as mandatory (1..1 to 1..n), which excludes NULL values in
the resulting query. To resolve the recursive relationship, you create a Sales Managers subtype
entity. Then you delete the recursive relationship, and create a new relationship between the
entity and the subtype entity. Now there is a subtype relationship between the two entities which
indicates inheritance, and an association relationship which represents the recursive
relationship.
Resolve Ambiguous Relationships

Relationships are ambiguous when two entities have more than one relationship between them,
and it is not clear which path the query should follow at run time. If you do not indicate which
path the query should follow, Architect analyzes each relationship and determines which path to
take. Analyzing path selection requires extra processing time. In addition, if each relationship
represents different data results, you should indicate under what circumstances the query
should use each relationship. Otherwise, users may not receive accurate data results.
To resolve ambiguous relationships, you can
• create a subtype entity and include the corresponding subject in the package
Use this approach if you want to create aliases for entities, or the ambiguous relationships
indicate that a significant number of users want to retrieve a subset of information from one
table. If so, the subset of information is a significant business concept, and therefore should
be represented in the business layer. For more information about subtype entities, see the
Architect User Guide.
• keep the ambiguous relationships in the business model, and only include the preferred
query path in the package
Use this approach if users have varied information requests, or if the resulting query path
references more than two tables. Customizing the query paths in each package is a flexible
approach to accommodating the different information needs of each user group.
• delete relationships that are not business-oriented
For example, an administrator may define extra joins in the data source to improve
performance or enforce referential integrity. If these joins are never required by your users,
you can simplify the business layer by removing them. We recommend that you do not
delete relationships unless you are absolutely sure they are not needed and do not affect
querying capabilities.

Example
There are two relationships between the SalesBranch entity and the SalesStaff entity. One
relationship (1..1 to 1..1) associates managers to a particular sales branch. The other
relationship (1..1 to 1..n) associates employees to a particular sales branch. Employees include
both salespeople and managers. Because your users must retrieve information about
salespeople as well as managers, you cannot simply remove one of the relationships.
To resolve ambiguity, you define a subtype entity to represent the managers of each sales
branch. Then you associate a filter with the subtype entity so that users retrieve information only
about managers when you include the corresponding subject in a package.
The other option is to keep the relationships in their original form and include only one query
path in a package. When users want to report on all employees, include the query path that
references the 1..1 to 1..n relationship. And when users want to report only on managers,
include the other query path.
Identify Preferred Query Paths

Sometimes you may want to give preference to a particular query path at run time. Use the
approach that best suits your needs.
For Cognos Query or PowerPlay Transformer, you can modify the relationship so that it
becomes a containment relationship. Use this approach if Cognos Query users want multiple
queries, or if you want a normalized business model that facilitates the creation of dimension
and level candidates in Transformer. For more information, see the Architect User Guide.
For Cognos Query, you can combine entities into one subject and specify which query path you
want to use. Use this approach if users want all the information in one query. For more
information, see "Combine Information into One Query" (p. 32).
For Impromptu, you can reduce the weights of the tables in the data access layer. For more
information, see the Architect User Guide.
Example
An OrderHeader entity contains information such as the date of the order, who the customer is,
who the salesperson is, and which branch placed the order. An OrderDetail entity contains
information such as the products that were ordered, the unit wholesale price, and the price the
customer paid. Because users typically retrieve information from both entities, there is a
reporting dependency between OrderHeader and OrderDetails. Your users retrieve information
using Cognos Query. Therefore, to give preference to the relationship at run time, you create a
containment relationship whereby the OrderDetails entity is contained by the OrderHeader
entity.
18 Architect ( R )
Control Query Paths That Include Lookup Tables

To create a normalized database, an administrator may create tables that contain only reference
or lookup information for other tables. To identify this type of relationship and thereby control
query path selection at run time, you may want to create reference relationships between the
corresponding entities in the business layer. A reference relationship indicates that one of the
entities is being used as a lookup table by the other and should not be used in a query path that
consists of multiple relationships.
For more information about reference relationships, see the Architect User Guide.
Example
The Customer and Warehouse tables are both related to an Address lookup table. Customer
and Warehouse have different addresses. You do not want the query to pass through the
Address table and erroneously relate customer addresses with warehouse addresses. You want
the query to retrieve address information only for customers, or address information only for
warehouses.
• For PowerPlay Transformer or Cognos Query, change the relationship that associates an
entity to a lookup entity to a reference relationship. For example, create a reference
relationship between Address and Country and between Address and Warehouse.
For Cognos Query users, you may want to combine entities that have reference
relationships between them into one subject in the package layer. For more information, see
"Combine Information into One Query" (p. 32).
For Transformer users, you would typically create a package that contains a subject for each
entity.
• For Impromptu, reduce the table weights for the entities that represent lookup tables. For
more information, see the Architect User Guide.
Filter Data
There are a variety of approaches to designing an Architect model that provide users with
filtered data in a report or query. The process of filtering data is a type of security known as
row-level security, lets a user retrieve information based on specific data values. For example,
you can set up filters that restrict a user from accessing confidential data.
You can also set up filters that represent a distinct portion of data. For example, sales people in
Germany want to retrieve sales information only from their region, rather than sift through all the
data to find their regional data. You can define the filter in the Architect model so that the data is
automatically filtered when a user runs a report or query.
There are two main approaches to setting up filters in Architect. Each approach offers a different
level of control and granularity over row-level security. You can
• create a filter and associate it with an entity via a user class
• create a filter and associate it with an entity
Associate a Filter with a User Class

If many users need access to different subsets of the same information, and user access
privileges frequently change, associate a filter with a user class. This level of data filtering offers
more control over who can access data because it applies only to the user class that is
associated with the filter. Users who belong to other user classes are not affected by the filter
restrictions.
For more information about filtering, see the Architect User Guide.
Associate a Filter with an Entity

If you are creating subtype entities that are specifically designed to represent a persistent
subset of data, associate a filter with an entity. When a user runs a report or query, they retrieve
only the data that the filter permits. This level of data filtering applies to any user who runs a
report or query based on an Architect model, regardless of user class privileges.
For more information about filtering, see the Architect User Guide.

Prepare the Model for Reporting and Querying

You must think about the purpose, structure, and content of each report or query. Every part of
an Architect model plays a role in query formation. However, it is the organization and content of
a package that decides what the report or query looks like, and the type of information the report
or query retrieves. Query designers in Cognos Query, report authors in Impromptu, and
modelers in Transformer can further customize the results in their product. However, they cannot
do their job if the package does not contain the required components. In addition, a package
that is not designed properly may not produce accurate data results.
When you first create a custom package, a default package is automatically created at the same
time. The default package references all the entities, attributes, and relationships that are
present in the business layer. Use this package to initially test the model in the target Cognos
products.
To distribute the model to your users, you should create custom packages to more accurately
meet each user’s information requirements. To create custom packages, you must consider both
the information requirements of your users and which Cognos product or combination of
products they use.
Within the package layer you can
• rename subjects and subject items
• refine the relationships betwee subjects, such as removing ambiguous query paths
• combine subjects, but be careful not to create ambiguous relationships
• organize the contents of a package into folders for Impromptu
For more information about packaging guidelines that are specific to Cognos Query, see
"Prepare the Model for Querying" (p. 31). For packaging guidelines that are specific to
Impromptu, see "Prepare the Model for Reporting" (p. 41).
Add Security to the Model

Before you use security with an Architect model, there are some important areas of security that
you should be familiar with, including
• user classes and how their privileges work within Architect, and within other Cognos
products
• the role of your security administrator, and how their activities affect you as the Architect
modeler
• the access requirements of other Architect modelers and of report and query users, and
how to give each group access privileges to the Architect models
We recommend that you add security to the model before you deploy it to the production
authentication source. You should test the security in the development namespace to ensure
that it is set up properly. After you deploy the model to the production authentication source, you
should verify the security again to ensure that it is set up properly.
User Classes and Their Privileges

Assigning access privileges to an Architect model involves working with user classes, which are
created and maintained by your security administrator in Access Manager Administration.
User class protection is a type of security that prevents a user from viewing data unless the user
provides a user name and password. If the user is a member of the user class that has access
to the data source, they are given access. Setting up user classes helps you to specify what
information users may access and prevent unauthorized users from accessing the information.
User class privileges are based on hierarchy, which means that by default each user class has
the same set of privileges and restrictions as the parent class in the hierarchy. Any changes you
apply to a user class automatically applies to all the child user classes, making global changes
easy to implement. Inheritance flows only downward. Any changes you make to a child class will
not affect the parent class. In addition, a user class cannot have more privileges than a user
class above it in the hierarchy.
20 Architect ( R )
Architect also supports the union of user classes. A union occurs when a user belongs to more
than one user class. When a user accesses an Architect model, they are given a combination of
access privileges, based on all the user classes to which they belong.
The Role of Security Administrator

Setting up security for people who work with Cognos products, whether they are Architect
modelers or report users, is predominantly the responsibility of your security administrator. Your
security administrator sets up security by using Access Manager Administration.
If you plan to secure the Architect model, your security administrator must do the following:
• Set up an authentication source, such as a namespace on a directory server, from which
Architect can access security information.
All the security information that Architect uses must be located within one authentication
source.
• Create users and user classes within the authentication source, assign each user to one or
more user classes, and set up signons and passwords for each user.
A user object must exist in the authentication source for each report user or Architect
modeler who uses the Architect model.
• Define the required data sources that the Architect model uses, and give users or user
classes access privileges to them.
If your report users do not have access to the data sources, they will not be able to retrieve
any data in their reports, even if they have access to the objects within the Architect model.
You can set up data sources yourself by defining them in the Architect model. However, your
security administrator is the only person who can give users access to the data sources.
• Define the Architect model as a metadata source, and give user access privileges to the
model if more than one person will make administrative changes to the model.
If you registered the model when you first created it, you already defined it as a metadata
source. The security administrator does not need to give report users access to the
Architect model. You do that when you set up security within the model.
When your security administrator finishes these tasks, you must ensure that Architect is
configured to locate and use the security information stored within the authentication source.
Configure Architect and other Cognos products by using the Access Manager Configuration
wizard. This step is usually done by the person who initially installed and configured your
Cognos products. For more information, see the installation and configuration guide for your
product.
For more information about setting up security in Access Manager Administration and using the
Access Manager Configuration wizard, see the Access Manager User Guide, or contact your
security administrator.
Set Up Administrative Access

After all security information is set up by your security administrator, you can use Architect to
control which parts of the model other Architect modelers can modify.
Administrative access to a model is two-fold. If you do not register a model in the authentication
source, any user who is defined in the authentication source can open, view, and modify the
contents of a model. If you register the model, you restrict everyone except yourself from
accessing the model. If you want others to have administrative access privileges, you can
associate them with the registered model in Access Manager.
If you do not import user classes into the model, every user who has administrative access to
the model can see and modify everything. When you import user classes, every administrator
still has access to everything. To restrict an administrator from working on a part of the model,
you must restrict their user class from using that object.
You can define multiple privileges for a user class. For example, you can create both
administrative and run-time privileges for another modeler who works on the model and wants to
validate their model changes by running the resulting queries in Cognos Query. However, you
must ensure that the administrative privileges do not conflict with the run-time privileges. To
avoid conflicting security, we recommend that your security administrator create a user class for
model administrators, and a separate user class for report and query users.

Set Up User Access

To set up user access to data, you first need to understand how the different types of security
privileges affect the user. Each type of privilege offers a different level of control over data
security.
You can set up
• design privileges
• run-time privileges
• structural privileges
• data retrieval privileges
• allowable activities of report authors and query designers who work with the model in
Impromptu and Cognos Query
For more information, see the Architect User Guide.
• run-time limits and restrict the query activities of Cognos Query users to help maximize
system resources, and to minimize network and database performance issues
• For more information, see the Architect User Guide.
If you set up access privileges for Cognos Query users, you must also consider the security
settings in Upfront. When you publish a model, a user automatically has access to the resulting
foundation query as long as they have access to the NewsBox.
For more information about giving users access to NewsBoxes, see the Upfront online help.
Set Up Design Privileges

Design privileges decide whether a user can create custom queries that are based on
foundation queries in Cognos Query, modify exported catalogs in Impromptu, and modify
resulting models in Transformer.
To have design privileges, the user class must have access to the relevant objects in the
business layer and package layer. For Impromptu administrators, you must also export the
catalog read/write. To design and test queries, reports, or PowerCubes, the user class must
have a combination of design and run-time privileges.
When defining access privileges to objects in the business layer, do not set restrictions on
business rules, such as prompts or filters. Instead, set the restriction on the entity that uses the
filter, or the attribute that uses the prompt. If you set up privileges so that the user has access to
the entity but not the filter, the filter is ignored at run time.
Set Up Run-Time Privileges

Run-time privileges decide whether a user can retrieve data when they run queries in Cognos
Query, run reports in Impromptu, and analyze PowerCubes in PowerPlay.
To have run-time privileges, the user class must have access to the relevant objects in the data
access layer, the business layer, and the package layer. To restrict access to data, do not give
the user access to the appropriate tables or columns. To restrict a user from querying data that
resides in more than one table, do not give the user access to the appropriate joins.
Set Up Structural Privileges

Structural privileges decide column-level security. They are similar to design and run-time
privileges, except that they involve restricting access to entities, attributes, and relationships by
including or excluding them in packages. For example, if you remove a subject attribute from a
package, it will not appear as an available column in the foundation query. As a result, users will
not be able to retrieve data from the column at run time.
Set Up Data Retrieval Privileges

Retrieval privileges decide row-level security. They restrict a user from retrieving specific data
when they run a query, report, or PowerCube.
22 Architect ( R )
There are two approaches to setting up data retrieval privileges.

• Create filters and prompts in the business layer, and associate them with entities and
attributes.
This level of data restriction applies to all user classes.
• Apply filters to model objects at the user class level.
To achieve this, you must import the user classes into the model, and then on the user class
property sheet, apply the appropriate filters. This level of data restriction applies to the
specific user class and is invoked only at run time.
For more information, see "Filter Data" (p. 19).

24 Architect ( R )
Chapter 3: Create Models for Cognos Query
To create an Architect model for Cognos Query designers and users, there are specific
modeling guidelines that you should follow. For information about general modeling guidelines,
see "Modeling Guidelines" (p. 7).
Cognos Query and Architect

Cognos Query is an ad hoc query product that you use to explore, modify, and create queries on
the Web. Cognos Query uses Architect as part of its enterprise reporting solution for metadata
management. Metadata management involves retrieving metadata from data sources, adding
business information and rules, packaging portions of the model, and distributing those
packages to Cognos Query users.
You distribute packages designed for Cognos Query by publishing them to a NewsBox in
Upfront. This process creates a foundation query for each subject in the package and a New
Query wizard. The New Query wizard lets users create queries that are based on any of the
existing foundation queries.
When a user runs a foundation query, the query is defined by the data retrieval information in
the published package. The information in the package decides which columns to display, which
data to retrieve, which query paths to use, and other characteristics that are specific to the
query.
Objects in Packages and Foundation Queries

Each model object in an Architect package corresponds to a specific object in Upfront.
Object in Architect package Object in Upfront

Subject Foundation query.
When a user clicks a foundation query, the
data results appear in Cognos Query. If the
subject is based on a filtered entity,
Cognos Query will return filtered data
results.
Subject attribute Column in a query.
Subject attribute that has a prompt Column in a query and a drop-down box on
associated with it the Filter page, which contains a list of
available filter values.
Query path Query link in a query.
Import Metadata
To create foundation queries for Cognos Query, you must first import the required metadata
sources into the Architect model. If your users require information that resides in more than one
metadata source, you may need to import more than one source.

If you import metadata from more than one source, we recommend that the sources be logically
related to one another so that you can define joins between them if required. You can then
create one package that contains all the related information. Resulting queries have query links
between them.
If the sources are not related to one another, create individual packages for each source so that
you do not mix unrelated information. Users cannot navigate between all their queries.
Cognos Query does not support stored procedure metadata. If you import a source that
contains stored procedures, decide whether to include them in the model. If the model is
intended only for Cognos Query, exclude them from the import process. However, if you want to
develop a model that can evolve and meet new requirements, you may want to import these
objects for future use.
For more information about general importing guidelines, see "Import Everything into the
Model" (p. 10).

Before you prepare a model for querying in Cognos Query, you must know the type of
information your users want to retrieve, and how they want the information presented. After you
know the information requirements, you can add this information to the model. However, to
provide your users with accurate and meaningful information, you must know how to properly
add business information and rules to the Architect model.
For information about general modeling guidelines, see "Add Business Information" (p. 11).
Design Queries
When you add business information to an Architect model, you are designing and controlling the
overall structure, data retrieval mechanisms, and appearance of all queries. There are different
approaches to designing a query with different effects on the accessibility and reusability of
query components. You can
• customize the business layer in Architect
• create an SQL query in Architect
• customize the query in Cognos Query
Customize the Business Layer in Architect

By adding all the required information to the business model (entities, attributes, calculated
attributes, relationships, and user-friendly labels), and all the required business and display
rules to the business layer (filters, prompts, enumeration values, and styles), you create a robust
Architect model that provides your query users with consistent querying capabilities.
As the Architect modeler, this approach helps you maintain everything in one central location.
As users request new information, the metadata is readily available for packaging and
distribution. Regardless of how many different packages you produce, the metadata is always
consistent.
In addition, this approach lets query designers modify the queries in Cognos Query, if required.
Create an SQL Query in Architect

Another way to design a query is to create an SQL query in the data access layer, and then
include the resulting subject in a Cognos Query package. If you know how to write accurate SQL
statements that produce the query results that your users want, this is an effective approach. Do
not create SQL queries if you are not proficient in writing SQL statements.
The advantage of creating an SQL query is that you can decide exactly how the query will run,
and what it will contain. However, the information in an SQL query is not as reusable as the
information in the business layer.
In addition, you cannot create SQL queries that retrieve information from more than one data
source.
26 Architect ( R )
Customize the Query in Cognos Query

You can also design an Architect model that contains all the basic information, and leave the
responsibility of adding unique information requirements to the query designer. For example,
you can create a model that contains entities and attributes that directly map to the tables and
columns in the database, and include minimal business information and rules. The query
designer can then add or remove objects, such as columns, calculations, and filters.
If you plan to use this approach, contact the query designer and decide which information
requirements belong in the Architect model, and which information requirements they are
responsible for. Consider the following:
• Information in the Architect model is reusable and maintains consistency in all queries.
• When a query designer adds information to a query, they are only customizing that query.
The information is not reusable in other queries.
• A query designer should customize a query only when it is a one-time request, or you
cannot add the required information to the model.
For example, you cannot create a prompt in the Architect model that asks the query user to
pick a filter value at run time. The query designer must create those types of prompts in
Cognos Query. For more information, see "Use Prompts to Filter Data" (p. 29).
Provide Calculated Information

There are two ways to provide calculated information in queries. We recommend that you define
calculated attributes in the business layer of the model and include them in a Cognos Query
package. The query designer can also define calculations for individual queries in Cognos
Query.
If you must provide complex or summary calculations in a query, consider the following before
you decide where to create them:
• Cognos Query supports only simple calculations that are derived from an Architect model.
• Cognos Query supports only specific summary calculations, regardless of where they are
created.
• Summary calculations that are derived from an Architect model automatically format the
resulting queries so that they contain summarized data.
For information about creating calculations in Architect, see the Expression Editor User Guide.
Create Simple Calculations

A simple calculation contains an expression that uses one or more arithmetic or string
operators, such as addition, subtraction, multiplication, and division operators. The following is
an example of a simple calculation that uses addition (+) and multiplication (*) operators:
Sale Price = [(1+OrderDetail.Margin)*Product.UnitCost]
For reusability and consistency, we recommend that you create and maintain simple
calculations at the model level rather than at the query level. If your users require more complex
calculations, such as the calculations that contain logical or comparison operators), the query
designer must create them in Cognos Query. Note that calculations created in Cognos Query
can contain only functions that are supported by the data source from which the query retrieves
information.
Create Summary Calculations

A summary calculation, also known as an aggregate, has the same characteristics as a simple
calculation but it also contains one or more summary functions. The following is an example of a
summary calculation that uses the Total summary function and the Auto component:
Total Orders = Total ([OrderDetail.Quantity])
For reusability and consistency, we recommend that you create and maintain summary
calculations at the model level rather than at the query level. However, if your users want to see
summary and detailed information in a single query, the query designer must create the
summary calculations in Cognos Query. For more information, see "Determine the Presentation
of Summary Results in a Query" (p. 28).

If you decide to create and maintain summary calculations in the Architect model, note that
Cognos Query supports only the following summary functions:
• Maximum for numeric and character data
• Minimum for numeric and character data
• Average for numeric data
• Total for numeric data
• Count for numeric, character, and date data
You must store the calculated attribute in the appropriate entity in the business layer. At run
time, the Auto component directs the Cognos Query server to associate the summary operation
with the entity that the calculated attribute resides in.
Cognos Query also supports calculations that contain more than one summary function. The
following is an example of a calculation that contains a Total and an Average summary function:
Total([OrderDetail.Quantity])/Avg([OrderDetail.Quantity])
However, Cognos Query currently does not support calculations that contain nested or
accumulative summary functions. The following is an example of a nested summary function:
Avg(Total([OrderDetail.Quantity]))
Determine the Presentation of Summary Results in a Query

When you create a summary calculation in Architect, the summary column automatically
appears as the right most column in the resulting query and is associated with the second from
the last column. Also, the data in the query is automatically grouped from left to right and from
highest to lowest according to the summary data. The result is a query that contains
summarized data rather than detailed data.
If your users need to combine summary and detailed information in a query, do not create
summary calculations in Architect. The query designer must create row-level summaries in
Cognos Query. Row-level summaries appear as extra rows at the bottom of the query instead of
as columns, and the summarized data appears at the bottom of each column. Row-level
summaries do not automatically force grouping in a query.
To create row-level summaries in Cognos Query, use the Summarize command on the Design
page. For more information, see the Cognos Query User Guide.
Example
The following query does not contain a summary column. Therefore, the query contains only
detailed information, which is not grouped.
28 Architect ( R )
Here is the same query with a summary column that calculates Number of Orders. To create the
summary column, the following calculated attribute was created in the Architect model and
added to the OrderDetail entity:
Number of Orders = count([OrderDetail.OrderDetailCode])
Notice that the resulting query is grouped according to the information in OrderDetailCode, and
that the information in Number of Orders is associated with the information in OrderDetailCode.
Here is the same query again, but with row-level summary information that was defined in
Cognos Query. To create the row-level summary, a Count summary was applied to the
OrderDetailCode column (Summarize command on the Design page).
Notice that the query contains detailed information, and the number of orders is positioned at
the bottom of the OrderDetailCode column.
Use Prompts to Filter Data

In addition to the general filtering guidelines (p. 19), you can use prompts. Creating a prompt
and associating it with an attribute in Architect produces a list of available filter values in Cognos
Query. The query designer can use the available values to create filters for queries. When a
user runs a query, the filter value that the query designer specified automatically decides what
type of data the query can retrieve. Therefore, when you create prompts in Architect, you are
creating design prompts for query designers. We recommend that you use this approach if you
want to maintain the filter values at the model level, and the query designer requires the
flexibility to apply the filters to the queries at design time.
If query users want the ability to specify the subset of data that they want to retrieve, the query
designer must create the required run-time prompts in Cognos Query. Prompts of this type ask
the user to select a value from a list before the query runs. The selected value determines the
type of information that the query retrieves. Run-time prompts in Cognos Query are
query-specific, and are not reusable.
For more information about creating filters and run-time prompts in Cognos Query, see the
Cognos Query User Guide.

To create and maintain prompts in the Architect model, note these consider the following:
• Cognos Query supports value, model, and file prompts.
• To create a model prompt to use in Cognos Query, specify only the Usage Attribute
property.
Specifying the Display Attribute property is not required.
• Use only value set prompts in conjunction with enumeration values.
For more information, see "Categorize Data" (p. 30).
• Cognos Query does not support prompts that are referenced by filters.
• Cognos Query supports only prompts that are associated with an attribute.
If a package contains subject prompts, you will not be able to publish it to Upfront.
Example
To create a query that retrieves sales information for one salesperson, the query designer must
create a filter retrieves information that relates to a salesperson’s name. If the query designer
were to create the filter in Cognos Query, they would need to know the exact spelling of the
salesperson’s name.
Instead, you create a model prompt in Architect that uses the LastName attribute as the source
of filter values. Then you associate the prompt with the LastName attribute in the SalesStaff
entity so that you can publish the filter information with the attribute. When you publish this
information to Upfront, the resulting foundation query contains a list of last names that the query
designer can use to design filters.
When the query designer selects the LastName column and an operator, such as Equals, on the
Filter page, a Select From a List link appears. The link produces a list of each salesperson’s last
name.
Categorize Data
If users want their queries to contain categories or ranges of data that are more meaningful than
the raw data values in the database, create enumeration values and apply them to attributes in
the Architect model. An enumeration value is an alphanumeric text string that replaces a textual
or numeric value when a user runs a query.
30 Architect ( R )
To properly design enumeration values, consider the following:

• Enumeration values override the default styles that are applied to attributes.
• Use numeric enumeration values only with attributes that contain 32-bit integer values.
If you associate a numeric enumeration value with a different type of attribute, you will not
be able to publish the package.
• To create enumeration values that represent a single character, use string enumeration
values.
Char enumeration values no longer exist.
• To have an enumeration value appear in Cognos Query as an available filter value, you
must associate the enumeration value with a value set prompt, and then you must associate
the enumeration value and the prompt with the same attribute.
At run time, Cognos Query must directly receive enumeration value information. If you use
any other type of prompt, the enumeration value is indirectly referenced via the attribute.
Therefore, the prompt bypasses the enumeration value information. Also ensure that the
value set prompt contains the same set of values that are defined in the enumeration value.
For more information about prompts, see "Use Prompts to Filter Data" (p. 29).
• Enumeration values are not required if the database contains code to string translations.
Example
Users want to retrieve address information about each sales branch. Country information is
stored in the SalesBranch table of the database in the form of a numeric code. For example, the
code for France is 1, Germany is 2, and so on. If users were to directly query the database, they
would get the country codes in their query. To make the country information more meaningful to
the user, you create an enumeration value in the Architect model that associates each numeric
code with a country name.
Then you associate the enumeration value with the CountryCode attribute in the SalesBranch
entity. As a result, when the user runs the query in Cognos Query, the country names that are
defined in the enumeration value automatically replace the numeric codes.
Prepare the Model for Querying

You must think about the purpose, structure, and content of each query. Every part of an
Architect model plays a role in query formation. However, it is the organization and content of a
package that decides what the query looks like, the type of information the query retrieves, and
how easily the user can navigate between related queries. Query designers can further
customize queries in Cognos Query. However, they cannot do their job if the package does not
contain the required query components. In addition, a package that is not designed properly
may not produce accurate data results, and a package that is not designed for Cognos Query
cannot be published to Upfront.

There are a variety of approaches to designing packages for Cognos Query, depending on what
users need:
• To retrieve all information in one query, create a package in Architect containing one subject
that references all the required entities and attributes.
• To retrieve a large amount of information that does not easily fit in one query, provide a
series of related queries that users can navigate between.
To accomplish this task, create a package in Architect containing a group of related
subjects, and define query paths between them.
• To retrieve summary information and drill down to the underlying details, create a package
in Architect that contains two subjects.
One subject contains the summary calculations, and the other subject contains the detail
attributes that the summaries are based on.
Design Cognos Query Packages

To successfully publish a package to Upfront, ensure that the package contains subjects and
subject items that are valid for Cognos Query. Architect provides some metadata modeling
features for other Cognos reporting and analytical products that Cognos Query does not
currently support. If you include subjects or subject items that are not designed for Cognos
Query, the package cannot be published to Upfront.
For information about general packaging guidelines, see "Prepare the Model for Reporting and
Querying" (p. 20).
When creating packages in Architect, note that Cognos Query
• supports subjects related to one or more entities that are derived from tables, views, or
synonyms in the data access layer.
Cognos Query does not support subjects related to entities that are derived from stored
procedures.
• supports subject attributes that represent standard attributes, calculated attributes (simple
and summary calculations), attributes associated with filters, and attributes associated with
specific types of prompts
Cognos Query does not support standalone subject filters or prompts, or subject attributes
that represent complex calculations.
• does not use hierarchical folder structures. Do not organize subjects into subject folders.
The internal structure of the package must contain only one subject level.
Combine Information into One Query

Users may want to retrieve information in a single query, but the information resides in more
than one table or data source, and the join between the tables is one-to-many. To fulfill this
requirement in the Architect model, you must combine attributes from multiple entities into one
subject.
In addition, if an entity uses another entity as a lookup table, we recommend that you combine
these entities into one subject. This approach is particularly effective if the two entities contain
very similar information. By combining entities that have this type of relationship, you are
shielding the query user from the physical implementation of the tables in the data source, and
you are making the querying process more efficient.
To combine information into one query:
• Create an entity in the business layer for each table in the data access layer.
If you combine the required tables into one entity, you may create ambiguous relationships.
As a result, the query will not retrieve accurate data.
• Ensure that the joins between the related tables are mandatory (1..1 to 1..n).
Otherwise, the query will not run properly in Cognos Query.
• Create a package that contains one subject, and base the subject on the entity in the
business layer that contains the most information.
This is the entity associated with the 1..n side of the relationship.
• Add additional subject items that are based on attributes residing in the other entity.
This is the entity that is associated with the 1..1 side of the relationship.
32 Architect ( R )
Example
Inventory staff want to retrieve all the relevant information about products. In the database,
product information is stored in two tables, Product and ProductType. The Product table
contains most of the required information, such as product number, name, production cost, and
introduction date. The product type information resides in the ProductType table. In addition, the
Product table uses ProductType as a lookup table, and there is a one-to-many relationship
between them because each product type has one or more products.
To provide an efficient query that contains all the product information, create entities in the
business layer that represent each table. Then create a subject based on the Product entity, and
add the ProductType attribute from the ProductType entity.
When a user runs the query in Cognos Query, they retrieve all the product information in that
one query.
Create Queries That Users Can Navigate Between

Query users may want to retrieve a large amount of information that does not easily fit into one
query. A query that contains a lot of columns is difficult to navigate through, and a user may find
that there is too much information to manage. In addition, large queries take significantly more
processing time. We recommend that you create a series of related queries that users can
navigate between. Creating multiple queries also provides flexibility. Users can run each query
independently when they want to retrieve only a portion of information, or they can expand their
information gathering by running many related queries.
To produce multiple, related queries from an Architect model, create a package and add the
appropriate subjects. Each subject represents one query. This approach is very effective if you
organize the subject items so that each subject represents a distinct area of information. If you
normalized the entities and defined all the relationships in the business model, most of the
organizing is done before you create packages.
Example
Users want detailed information about each product that the company sells and the orders that
correspond to each product. You want to present this information in two queries. One query
retrieves all the order information, such as the order code, quantity, unit cost, unit price, and unit
sale price. The second query retrieves all the product details, such as number, name, type,
production cost, and date of introduction.

In Architect, you create a package that contains two subjects. One subject is based on a
Product entity, and the other subject is based on an OrderDetails entity. Because these entities
are related to one another in the business layer, a query path is automatically defined between
the subjects in the package.
When the user runs the Product query in Cognos Query, they retrieve all the detailed product
information. In addition, the query contains query links, which the user uses to navigate to the
related ordering information.
By clicking the query link in row 1, the information in the OrderDetail query is filtered by the
value in the ProductNumber column. ProductNumber is the key used to form a relationship
between the underlying Product and OrderDetail entities in the Architect model. As a result, the
query links in Cognos Query follow the same criteria when forming a filtered relationship
between two queries.
Create Summary Queries That Drill Down to Detailed Queries

If you want Cognos Query users to navigate from summary queries to corresponding detail
queries, you must properly organize the subjects in the package before you publish it to Upfront.
34 Architect ( R )
To organize the package for drill-down queries:

• Create two subjects in the package layer, one for summary information, and the other for
detailed information.
In most cases, summary and detail subjects should refer to the same entity in the business
layer. Therefore, you may be required to rename one of the subjects.
• Add the required calculations to the summary subject, and add the attributes that represent
the detail information to the detail subject.
• Ensure that each subject also contains a subject item that references the key for the entity.
If the subjects do not contain a reference to the key, users will not be able to use the query
links to navigate between queries.
• Define the query path between the two subjects.
Typically, query paths in the package layer correspond to relationships in the business layer.
However, when you define a query path between two subjects that relate to the same entity,
a corresponding relationship does not appear in the business layer. Query paths of this type
do not need to have a corresponding relationship.
Example
Sales managers want to research sales profits, production costs, and revenue information. They
also want to drill down to the underlying sales data on which these calculations are based. To
fulfill this requirement, you must create a detail query and a summary query that sales
managers can navigate between.
Most of the sales and order information is represented by an OrderHeader entity in the Architect
model. You add additional attributes to the OrderHeader entity that calculate gross profit, actual
revenue, planned revenue, and product cost. Then you create a package that contains two
subjects. The first subject is based on the OrderHeader entity and contains subject attributes
that reference the detailed information. The second subject is also based on the OrderHeader
entity. However, it contains subject items that reference the calculated attributes you created.
Both subjects also contain a reference to the key for the OrderHeader entity. Finally, you define
the query path between the two subjects based on the key.
When a sales manager runs the summary query, they are presented with all the calculated
sales information. To drill down to the underlying data, they can click the appropriate query link
in the OrderDetail column. The link takes them to the corresponding information in the detail
query.


When adding security to an Architect model that is used by Cognos Query, do the following:
• Before you create models, ensure that Architect is configured to use the same
authentication source as Upfront and Cognos Query.
Otherwise, you cannot publish packages to Upfront. Using the same authentication source
also ensures that the security information is consistent across products. For more
information, see the Cognos Query Installation and Configuration Guide.
• To let query designers modify queries in Cognos Query, give their user class access to all
the required objects in the business and package layers.
If query designers want to test their queries, you must also give their user class access to
objects in the data access layer.
• Before users can retrieve data in a query that is based on an Architect model, you must give
their user class access to all the required objects in the data access layer.
• Restrict Cognos Query users from accessing data only at the table or entity level.
If you restrict access to an attribute or column, the user can still see the column of data
when they run their query.
For information about general security guidelines, see "Add Security to the Model" (p. 20).
Publish Packages to Upfront

When you finish designing and fine-tuning the Architect model, you are almost ready to make it
available to your Cognos Query users. Before you publish packages to Upfront, ensure that
• you verify the contents of each Cognos Query package
If a package contain errors, or metadata that Cognos Query does not support, you will not
be able to publish it to Upfront. For information about model verification guidelines, see
"Verify the Contents of the Model" (p. 9).
• the Publish to Upfront component is installed on the same computer as Architect
Otherwise, you will not be able to connect to the Upfront server. For more information, see
the Cognos Query Installation and Configuration Guide.
• the required NewsBoxes, security, and user classes are defined and available in Upfront
If you have specific publishing requirements, tell your Upfront administrator. Otherwise, ask
you Upfront administrator into which NewBox they want you to publish the package.
• you know how to connect to the Upfront server
Contact your Upfront administrator for connection information.
• you have access privileges to the NewsBox in Upfront so that you can test the resulting
foundation queries. Contact your Upfront administrator for access privileges.
• you have an automatic database signon configured for your user class
When publishing a package to Upfront, Architect connects to the underlying database and
Cognos Query does not prompt you for a database signon. If the database is not accessible,
you will not be able to publish a package.
For more information about working in Upfront, see the Upfront online help.
36 Architect ( R )
Chapter 4: Create Models for Impromptu
To create an Architect model for Impromptu administrators, there are specific modeling
guidelines you must follow. For information about general modeling guidelines, see "Modeling
Guidelines" (p. 7).
Impromptu and Architect

Impromptu is a querying and reporting product. You use it to create reports based on catalogs of
your company’s data. You create catalogs for Impromptu using Architect by organizing portions
of the business model into packages and generating a catalog from each package. A catalog is
a file containing the information that you need to create reports.
If you do not have existing catalogs or you are planning new catalogs, you should use Architect.
Architect is a more sophisticated tool for metadata management, and it lets you reuse your
work. Architect can reduce your data management responsibilities when producing catalogs,
cubes, and queries.
If you are successfully using existing catalogs, you should continue to manage them using
Impromptu Administrator. Catalogs created using Impromptu Administrator continue to be
supported.
Objects in Packages and Catalogs

Each model object in an Architect package corresponds to a specific part of a catalog.
Object in Architect package Object in Impromptu catalog

Subject folder Folder, HotFile
Subject attribute Column
Subject filter Catalog filter
Subject prompt Catalog prompt
Query path Join
Import Metadata
To create catalogs for Impromptu, you must first import the required metadata source into the
Architect model. For Impromptu, the metadata source may be an existing catalog, or it may be a
new metadata source, such as a relational database. If it is an existing catalog, a lot of the
modeling work may already be done. If it is a new metadata source, you must complete all the
modeling stages before you can export a usable Impromptu catalog.
Impromptu cannot retrieve information in a report that spans more than one database. However,
you can import more than one data source into an Architect model and create cross-database
queries in other Cognos query and reporting products. To create successful catalogs, you must
ensure that the packages you create for Impromptu contain metadata from only one data
source. For more information, see "Export Packages to Impromptu" (p. 43).
For information about general importing guidelines, see "Import Everything into the
Model" (p. 10).

Import Catalogs into Architect

You can import secured, shared, or personal Impromptu catalogs into an Architect model. You
cannot import distributed catalogs. Reimporting a catalog into an Architect model may create
duplicate objects. Therefore, do not import a catalog more than once.
In addition to database metadata, you can import security information, such as user access
privileges, that is defined in an Impromptu catalog. You should synchronize the information with
the information that already exists in the authentication source before you begin the import
process. For more information, see "Add Security to the Model" (p. 41).
Before you can import a catalog into an Architect model, you must have administrative access to
the catalog. Administrative access is defined by being a member of the Creator user class. The
Creator user class in Impromptu is the same as the Root user class in Access Manager and
Architect. If the Creator user class does not have a password, anyone who can log on to
Architect and access catalogs can import them into a model.
When you import a catalog, metadata is stored in all three layers of the model:
• The data access layer contains information about the database tables and columns that are
defined in the catalog.
• The business layer contains information about filters, prompts, and aliases that are defined
in the catalog.
• The package layer contains folders, subjects, and subject items, which reflect the structure
and contents of the Impromptu catalog.
Any HotFiles associated with the catalog are also imported. They appear in the data access
layer as a HotFile object, in the business layer as an entity, and in the package layer as a subject
folder.
Catalog Metadata That is Not Imported

When you import an Impromptu catalog, the following objects are not imported into the Architect
model:
• filters, prompts, and calculations that are specific to an Impromptu report
Because these are stored with the report, not the catalog, Architect cannot access them.
• expressions that contain a reference to a data set with Any or All operators
Architect ignores those expressions.
• calculations that contain the Concat function or the user-defined database function named
Microseconds
For more information, see "Provide Calculated Information" (p. 41).
• the Make This a Distributed Catalog property (Catalog Properties dialog box)
You cannot import distributed catalogs.
• references to system tables
Import Catalogs That Reference Secured Databases

To import and export Impromptu catalogs that have encoded database signons, you must
belong to the Root user class that is defined in the authentication source. The database
connection and corresponding database signons must also be defined in the authentication
source, and you must have access privileges to the data source. To set up this information,
contact your security administrator. For more information see the Access Manager User Guide.
If all the database signon information is properly defined in the authentication source, you are
not prompted to log on to the database during the import process of an Impromptu catalog.
Another option is to remove the database signon information from the catalog. If you use this
approach, you will be prompted to log on to the database during the import process.
38 Architect ( R )

If you imported an existing Impromptu catalog into your Architect model, you may not need to
add information to the business layer. If you imported other metadata into your Architect model,
you have to create all the required information for the business model (entities, attributes,
calculated attributes, relationships, and user-friendly labels), and all the required business and
display rules for the business layer (filters, prompts, enumeration values, and styles).
Define Relationships
Architect and Impromptu have different naming conventions for joins and relationships. To add
relationships in Architect, you must understand both systems. For more information about joins
and relationships in Architect, see "Include or Exclude NULL Values in a Report" (p. 15).
Impromptu uses these types of joins:
• equi-joins
An equi-join retrieves all the rows from one table that have matching rows in another table.
The value in the first column of the equi-join is equal to the value in the second column of
the equi-join.
• non-equi-join
A non-equi-join retrieves all the rows from one table that meet the criteria in another table.
By default, Impromptu joins tables with a non-equi-join. For example, you can use the not
equal operator (<>) to list the active accounts that have not purchased a product.
• outer joins
A full outer join causes data to be retrieved from both tables even if there are no matching
rows between them. A right outer join causes all data to be retrieved from the right table,
even if there are no matching rows in the left table. A left outer join causes all data to be
retrieved from the left table, even if there are no matching rows in the right table. For
examples, see Mastering Impromptu.
The following chart shows how joins in Impromptu relate to joins and relationships in Architect.
Impromptu join Architect join or relationship

Equi-join 1..1 to 1..n
The expression for this type of join contains
the equals (=) operator.
Non-equi-join 1..1 to 1..n

The expression for this type of join can
contain the following operators:
• not equal (<>)
• less than (<)
• greater than (>)
• less than or equal to (<=)
• greater than or equal to (>=)
Full outer join 0..1 to 0..1

0..1 to 0..n
0..n to 0..1
0..n to 0..n

Impromptu join Architect join or relationship

Right outer join 0..1 to 1..1
0..1 to 1..n
0..n to 1..1
0..n to 1..n
Left outer join 1..1 to 0..1

1..1 to 0..n
1..n to 0..1
1..n to 0..n
Example
In Architect, you create a join between a Product table and a ProductType table. When you open
the property sheet for that join, you can see the cardinality and which columns you used to
create the association.
When you export a package that contains the corresponding subjects and query paths to an
Impromptu catalog, the join information is also exported. To see the join information in
Impromptu, open the Join dialog box (Join command in the Catalog menu). Notice that the join
information is very similar.
40 Architect ( R )
Provide Calculated Information

To provide calculated information in an Impromptu catalog, consider the following:
• Architect does not support the CONCAT operator.
To create a calculation that contains concatenated strings to use in a catalog, use the
addition operator (+).
• Architect does not support the user-defined database function named Microseconds, and
Impromptu does not support the user-defined database function named D2_microseconds.
If you need a calculation that contains this functionality, use D2_microseconds in the
expression. After you export the package to an Impromptu catalog, open the catalog and
modify the calculation so that the expression uses Microseconds.
• If a calculated attribute is based on a user-defined function, Architect will not export it to
Impromptu unless you make the label attribute of the function identical to the function name
in the database.
The label attribute appears beneath the function name in the impfunct.ini file.
Prepare the Model for Reporting

After you add business information to the model, you can create packages that are designed for
Impromptu. Because an Impromptu catalog is typically used for a variety of different reports, you
should include all the required subjects in one package. Because Impromptu can retrieve
information only from one database at a time, all the subjects in the package must reference the
same database.
You can export several packages if you have several sets of reports that use separate sets of
information. Contact your Impromptu administrator to decide how many catalogs are required.

Catalogs that are created in Architect use an authentication source to control security. These
catalogs contain a reference to a namespace on the directory server where the security
information is stored. You use Access Manager Administration to manage the authentication
source whether it is a directory server or a local authentication export file (.lae). Ensure that
Architect is configured to use the same authentication source as Impromptu. If users do not
have a user name or password defined in the authentication source, they cannot open the
catalog.
You can also use Architect to set up governor options for the user classes in the namespace.
Governor options control what your users can do, creating reports or writing SQL for reports. A
user profile is a user class with all the governor option settings.
All restrictions on the Creator user class are imported as restrictions on all other user classes
except the Root user class in the namespace.

Import Security Information from a Catalog

You can also import the security information from the existing catalogs. Because Architect uses
Access Manager for security, verify the following conditions before importing the catalogs:
• If you use more than one catalog with the same user classes, the user classes should have
the same privileges and restrictions.
For example, the access privileges for the Sales user class in Catalog1.cat must be identical
to the access privileges for the Sales user class in Catalog2.cat. If there are discrepancies
between the security information in the two catalogs, there may be more restrictions on the
Sales user class after the import of Catalog2.cat.
• If different user classes in different catalogs share the same name but have different access
privileges, the user classes should be renamed before you import the catalogs.
For example, Catalog1.cat contains a user class called Sales that regional sales
administrators use to track the performance of each sales representative. Catalog2.cat
contains a user class called Sales that sales representatives use to check their own
performance against quota. You should rename the user classes in the catalogs to Sales
Admin and Sales Rep so that the security information remains unique.
• The user class names in the catalogs must match the user class names in the
authentication source.
Ensure that you synchronize the security information in the authentication source with the
security information in the catalog before you import the catalog into a model. Otherwise,
some user class information may be overwritten. If user class information exists in the
catalog, but not in the authentication source, the import process will add the user class to
the authentication source.
• The user class hierarchy in the catalog must match the user class hierarchy in the
authentication source or you will have conflicting user classes.
• To import security information from a catalog, you must be a member of the Root user class.
In addition, to modify user class privileges, your user class must have privileges to see all
available user classes.
When importing a catalog, in the Catalog Logon dialog box, select the Import Security
Information check box so that Architect compares the security information with the security
information in the default namespace. Any conflicts are shown in the Import Security Information
dialog box. Click the Import button to complete the import of the catalog metadata with the
security information. The Impromptu security information is added to the namespace. Architect
compares user class names in the catalog with the user class names that are defined in the
authentication source. The settings associated with the user profiles from the catalog, such as
whether they have filters and governors, are imported into the Architect model.
Whether you streamline the user classes from multiple catalogs before you import them into the
Architect model, or do that in Architect, there may be conflicts with security. To avoid conflicts,
remove security from all catalogs except the one that contains the security you want to have in
the Architect model. Import that catalog first so that Architect sets up the user classes in the
authentication source and keeps those security profiles.
Resolve Hierarchical Differences Between Catalogs and Namespaces

If a user class from the catalog conflicts with a user class that already exists in the
authentication source, the user class is not created in the authentication source and the user
profile settings are not imported into the Architect model. For example, a catalog has a user
class with the same name as a user class that exists in the authentication source, but these
items have different parent classes. To deal with conflicting user classes, do one of the following:
• Modify the user class hierarchy in the catalog to match the one in the authentication source
before you import the catalog.
• Modify the user class hierarchy in the authentication source using Access Manager
Administration before you import the catalog.
• Import the catalog without removing any user classes.
Any conflicting user classes will not be imported.
42 Architect ( R )
Catalog Security Information That is Not Imported

When you import an Impromptu catalog, the following security information is not imported into
the Architect model:
• The Edit Folders governor setting on the Database tab (User Profiles dialog box)
Architect sets Edit Folders to off, by default. However, when you export a catalog, Edit
Folders is set to on for the Creator user class and off for all other user classes, by default.
For more information, see the Architect documentation.
• The Database Logon setting on the Database tab (User Profiles dialog box)
If there is no database entry or database signon in the Access Manager namespace, the
connection information from the database logon in the catalog is used to create the
necessary information in the namespace.
• Passwords
Passwords are encrypted in the catalog and cannot be added automatically in the
authentication source. After you import a catalog, you can add passwords and change the
privileges as required using Access Manager Administration. You can change governors in
Architect by using the Set Up Model Security command in the Model menu.
• Start Transaction with Isolation Level setting on the Database tab (User Profiles dialog box)
Import Without Security

If you do not want security in your Architect model, do one of the following:
• Specify that you do not want security when you import the catalog.
• Remove the security settings using Architect after you import the catalog. For more
information, see the Architect documentation.
• Create a copy of the catalog and remove user classes from the catalog before you import it.
• Remove user classes from your authentication source after you import the catalog.
For more information about security in a catalog or user classes in an authentication source, see
the Impromptu Administrator and Access Manager documentation.
For more information about security within Architect, see "Add Security to the Model" (p. 20) and
the Access Manager documentation.
Export Packages to Impromptu

After you add security to the model, you are ready to create a package and export it to
Impromptu. Before you export the package, ensure that
• all the subject and subject items in the package reference the same database
Otherwise, you will not be able to export the package to Impromptu.
• Impromptu and Architect are installed on the same computer
Otherwise, you will not be able to export the package. For more information, see the
Impromptu Installation and Configuration Guide.
• you gather administrative requirements from your Impromptu administrator.
These requirements will help you determine whether to export the catalog as read-only or
read-write. If the administrator needs to add Impromptu-specific information to the exported
catalog, you should export the catalog as read-write.
Because a read-only catalog cannot be modified in Impromptu, all metadata maintenance must
be done in Architect. This ensures that metadata is consistent across your Cognos products. In
a read-only catalog from Architect, you cannot open the Folders dialog box, Tables dialog box,
Joins dialog box, or User Profiles dialog box. You also cannot create type-in SQL reports.
A read-write catalog can be modified in Impromptu by any member of the user classes in the
exported catalog, so that Impromptu-specific features can be added. For example, if you want a
calculation that contains the Concat function in your catalog, you can only create this type of
calculation in Impromptu. Architect does not support the Concat function.
Your users must have a database definition for the data source that the catalog is connected to
so that they can retrieve report data. When you notify your users about a new Impromptu
catalog, tell them how to create a database definition if required.

Example
You want to create an Impromptu report that contains a list of all products returned because the
customer was not satisfied. Product return information exists in three tables in the GO Sales
database: Product, ReturnReason, and ReturnedItem. To create a catalog from which the report
will run, you first create a package in Architect. Because you intend to use the catalog for many
different types of reports, you decide to include all the subjects that correspond to the GO Sales
database in the package. You also include a subject filter that forces the query to return only a
list of products returned for unsatisfactory reasons.
After you export the package to an Impromptu read-write catalog, the Impromptu administrator
can verify the contents of the catalog by looking at the available folders. Notice that all the
subjects in the package are represented as folders in the Impromptu catalog, and the filter is
available for the Impromptu administrator to apply to a report. The Impromptu administrator now
has all the required catalog metadata to design the required report.
44 Architect ( R )
When a user runs the report in Impromptu, they see a list of all the unsatisfactory products that
customers returned.
Use Existing Impromptu Reports with Exported Catalogs

You can run existing Impromptu reports using an Impromptu catalog that you exported from
Architect. First, ensure that the exported catalog contains the same metadata as the original
catalog. Also, ensure that the folder names, folder paths, and column names in the exported
catalog are identical, including capitalization, to those in the catalog that you originally used to
create the reports.
If column or folder names were modified in the original catalog, you can modify the
corresponding objects in the package before you export it to Impromptu. Another option is to
export the package as read/write so that the Impromptu administrator can modify the names in
the exported catalog.

46 Architect ( R )
Chapter 5: Create Models for PowerPlay Transformer
Chapter 5: Create Models for PowerPlay

Transformer
To create an Architect model for Transformer modelers, there are specific modeling guidelines
that you should follow. For information about general modeling guidelines, see "Modeling
Guidelines" (p. 7).
Transformer and Architect

Transformer is an OLAP design tool that modelers can use to build models and create cubes.
You can create an Architect package that can become the basis of a well-formed Transformer
model.
Using an Architect package as your source for Transformer gives you the advantage of having
your metadata stored in one place. Any changes you make to the package are propagated to all
the Transformer models that use that package. You can still change your Transformer model, but
these changes cannot be saved back to the Architect model and, therefore, are not available to
other users of the package.
Architect and PowerPlay Integration

The process for using Architect and Transformer to convert your metadata into information that
users can access through PowerPlay is as follows:
• Import the metadata into Architect.
• Create objects in the Business Layer, including the Architect model, business rules, attribute
entities, and relationships that you require, and define the appropriate attribute usage.
• Create an Architect package in the Package Layer for all the entities.
• Build a Transformer model, based on the Architect package.
• Use the Metadata Explorer to analyze the relationships and scope of the measures,
dimensions, and levels. Select the candidate hierarchies that you want in your model.
• Create the model.
You can then use the full functionality of Transformer to enhance your model.
Note: Do not use running or moving aggregate functions in calculated attributes unless you
are sure that the resulting values will be meaningful when they are rolled-up in your
dimensional model.
• Create a cube.
• View the information in PowerPlay.
How Architect Objects Correspond to Transformer Objects

This table shows the relationships between objects in Architect Business and Transformer.
Architect Business Architect Package Transformer/Metada

Layer Object Name Layer Object Name ta Explorer Name
Entity Subject Level
Attribute Subject-Attribute Depends on usage,

for example, measure,
level, label

Architect Business Architect Package Transformer/Metada

Layer Object Name Layer Object Name ta Explorer Name
Relationship Query Path Dimension
Filters Subject Filters Architect data source

filters
Use An Architect Package as a Transformer Data Source

The way in which a data source is used in Transformer depends on whether you are using an
Architect package or a non-Architect data source.
With non-Architect data sources like IQDs (Impromptu Query Definitions) and flat files (.csv),
you build the model by reading the data source and then mapping the columns in the data
source to dimensions, levels, and measures.
With an Architect package, the process is reversed. The data sources are based on the
dimensions, levels, and measures that you select in the Metadata Explorer. Based on these
selections, Transformer creates the appropriate data sources to support the dimensions you
chose. For measures, there will be multiple data sources if the measures you select have
different scopes.
Architect data sources are listed under the Architect package object in the Data Sources
window. The Architect package and the data sources have different properties.

Before you prepare a model for use by Transformer, you must know the type of information your
users want to retrieve, and how they want the information presented. After you know the
information requirements, you can add this information to the model. However, to provide your
users with accurate and meaningful information, you must know how to properly add business
information and rules to the Architect model.
When you are modeling in Architect, keep in mind the following points:
• Work with summarized data in Architect from a data warehouse. Architect can provide the
structure required from an OLTP structure, but this approach requires more advanced
planning by the modeler.
• Know your data. The Normalize Hierarchical Entity Wizard provides you with the structure,
but you must know the foreign and primary key relationships between entities and the
natural structure of your data.
For more information, see "Normalize Entities" (p. 51).
• Determine the attribute usage for each attribute that will be used for the levels, labels,
descriptions, and the performance indicators for the Transformer package.
• If you are using Architect sources for more than one type of reporting, use a generic suffix
for all normalized entities pertaining to a specific cube. The suffix makes these entities
easier to find when you are building your package layer and when you are associating the
drill through from one package (PowerPlay) to another (Cognos Query).
• In the package for Transformer, include only the attributes that are required to create the
Transformer model.
• Consider setting up alternate drill downs in Architect with subtypes.
Set Attribute Usage

All attribute usage settings in Architect correspond to specific roles in the Transformer model.
48 Architect ( R )
When you set the usage for an attribute in your Architect model, you provide information for
Transformer about the association role that the attribute will have in the Transformer model. You
can apply more than one usage to an attribute.
If you do not specify a usage for an attribute, Transformer establishes association roles based
on a predetermined set of rules. This may result in Transformer selecting attributes that do not
suit your needs.
If you set two attributes with the same usage, Transformer picks the first attribute for any role
association that has only one value. This does not apply to measures.
The relationship between the usage settings in Architect and their association roles in
Transformer are as follows.
Architect Usage (Attribute

property sheet, Usage tab) Transformer Association Role
Performance Indicator Source for a measure in the
model (Measure property sheet,
Type tab)
Level Attribute Identifier Source for a level (Level property

sheet, Source tab)
Level Attribute Label Label for a level (Level property

sheet, Source tab)
Level Attribute Short Name Short Name for a level (Level

property sheet, Source tab)
Level Attribute Description Description for a level (Level

property sheet, Source tab)
Level Attribute Sort Attribute Order By for a level (Level

property sheet, Order By tab)
Define an Architect Attribute as a Transformer Measure

To define an Architect attribute to be a Transformer measure, set the usage in Architect to
Performance Indicator. Attributes with this usage are recognized by Transformer as candidate
measures.
Steps:
1. In the Architect Business Layer, open the property sheet for the attribute that is the
candidate measure.
2. Click the Usage tab, and select Performance Indicator.
3. Add the attribute to a Transformer package.
4. Open the package in Transformer. The Metadata Explorer shows the candidate measure,
which you can select to be a measure in Transformer.
Architect Relationships and Transformer Dimensions

A relationship in the Architect Business Layer is equivalent to a dimension in a Transformer
model. For each entity that has attributes with a usage of Performance Indicator, Transformer
creates a dimension for each relationship that has a cardinality of 1:n or 0:n coming out of that
entity. This is also referred to as a dimension path.

How the Number of Levels in a Dimension is Determined

Starting with each entity that has attributes with a Usage of Performance Indicator, Transformer
adds candidate levels until one of the following conditions occurs:
• a cardinality of 1:1 is encountered in a relationship leading out of the entity
• there are no more entities
The following diagram shows entities in the Architect Business Layer. OrderDetail has three
relationships, each with a cardinality of 1:n, coming out of OrderDetail. Therefore, OrderDetail
will form the basis for three dimensions, Country, ProductLine, and Vendor.
The Country and ProductLine dimensions each have three levels because each of these
dimensions has a cardinality of 1:n along its dimension path. Therefore, the Country dimension
has the levels Country, SalesBranch, and SalesStaff, and the ProductLine dimension has the
levels ProductLine, ProductType, and Product. Since Vendor is the last entity along that
dimension path, the Vendor dimension has only one level, Vendor.
You can add all these entities to the Architect package and they become the input to
Transformer. You can use the dimensions and levels that are derived from these entities to
create the Transformer model.
Include a Date Dimension

Transformer automatically creates a date dimension if the following conditions are met:
• there is an Architect attribute with the data type "date" in an entity that also contains
attributes with a usage of Performance Indicator
• the usage of the date attribute is Identifier
Advanced Modeling Concepts

There are a number of operations that you can perform in Architect to enhance your Transformer
model. You can
• normalize entities
• create alternate drill-down paths
• design filters
• define calculated attributes
The advantage of creating alternate drill-down paths and filters and defining calculated
attributes in Architect is that you can maintain them in one place.
50 Architect (R)
Normalize Entities
If you create an Architect model by importing metadata from a star schema database, you can
easily identify the measures and the number of dimensions. However, you must establish the
number of levels in each dimension.
Since an entity is equivalent to a level in Transformer, a standard star schema would produce a
model that has many dimensions, each with one level.
The Normalize Hierarchical Entity Wizard in Architect allows you to take a star schema
dimension table and divide it into a hierarchy that Transformer can interpret as levels.
Normalizing an entity in the Architect business layer does not affect the physical structure of the
underlying database table.
You must normalize entities whenever you use a star schema in order to have any depth in the
resulting Transformer model.
Using the Normalize Hierarchical Entity Wizard enables Transformer to generate the most
efficient SQL queries possible to create dimensions, levels, and roll up measures.
While you normalize, keep the entities that contain the measures open in Architect’s Model
Explorer. That way, you can see which column contains the right information to connect the leaf
level in the dimension to the table containing the measures. This is especially important where
you have more than one fact table or are using relational sources.
For more information about normalizing entities, see the Architect User Guide.
Create Alternate Drill-Down Paths

Alternate drill-down paths allow end users to drill down to data along different paths. In
Transformer, each dimension has a primary drill-down path and one or more alternate drill-down
paths. In Architect, you can create structures that Transformer interprets as alternate drill-down
paths or you can create drill-down paths manually in Transformer.
If your Transformer model does not have any alternate drill-down paths, there will be one data
source for each dimension and one data source for each set of measures that have the same
scope. If there are alternate drill-down paths, each dimension will have one data source for each
primary and alternate drill-down path, and the measure data sources.
Create an Alternate Drill-Down Path in the Architect Business Layer

In the Architect business layer, you can use a subtype entity to model alternate drill-down paths.
To create an alternate drill-down path in Architect, do the following:
• Create a subtype of the entity that you want to use in the alternate drill-down path.
• Associate the subtype entity with the entity that will become the convergence level in
Transformer. The convergence level must be one above the entity that contains attributes
that have a usage of Performance Indicator
Transformer interprets the subtype as an alternate drill-down path in the Metadata Explorer.
Create an Alternate Drill-Down Path Manually in Transformer

In Transformer, you can drag items from the package view pane to the dimension map in the
Candidate Hierarchies pane to create whole dimensions, partial dimensions, or levels. To create
a dimension, drag the item to the dimension line. The new dimension creates a single level with
the same name as the dimension. To add levels, drag items from the package view pane to the
levels area in the Candidate Hierarchies window. You can also drag measures from the package
view pane to the Measures pane.
Design Filters
Filter Your Architect Data Source in Transformer
You can create different types of filters with different subjects in your Architect model and in
Transformer. Each type has different implications for your Transformer data sources.

Filter an Entity in the Business Layer in Architect

Add a filter to an entity in the business layer if you want the filter to be included in every package
where that entity is used as a subject. Transformer will always apply the filter and it will
automatically become part of the SQL definition for your Transformer data source.
Use this filter when you want a filter that will be applied to every associated subject in every
package.
Filter an Entity or an Entity Attribute for a User Class in Architect

Add a filter to an entity or to an entity attribute for a user class, if you want the filter to be
associated with a user class that will be available in the Transformer model if the modeler is a
member of the user class where the filter was included. Once it is applied, the filter will
automatically become part of the SQL definition for your Transformer data source.
Use this filter when you want the filter to be applied based on different user classes using the
entity or the entity attribute.
Filter a Subject for a Specific Package or Without Specifying a Package in

Architect
You can add a filter to a package in the package layer without specifically adding it to any
subjects or you can add a subject filter to a subject in a package. This filter becomes available
for Transformer to use, but will not be used automatically. To use it, you must import the filter for
each data source that you want it applied to. Once the filter is applied, it becomes part of the
SQL definition of your data source.
This is a reusable filter that you can be set up in an Architect model as a productivity aid and
convenience when you are creating Transformer models. In Transformer, you can choose
whether you want to use this filter.
If you are creating filters that affect an attribute and you are importing the filters to the model
manually, ensure that you add them to every data source that contains attributes that are
referenced by the filters.
You can apply multiple filters against your Transformer model. Transformer combines the filters
using the AND operator.
Filters added to structural data sources only affect category generation.
Filters added to a transaction data source affect records in your cube.
Define Calculated Attributes

Calculated attributes let you derive new data for the Transformer model by using existing
attributes and enhancing them with functions, calculations, and constants. A calculated attribute
is equivalent to a calculated column in Transformer, but the calculated attribute is based on an
Architect data source.
You can define a calculated attribute in the Architect business layer, using the appropriate
expression, and add the calculated attribute to the Architect package. You can then use the
calculated attribute when creating the Transformer model.
You can still create calculated attributes in Transformer by adding them to the data source in the
Data Source list.
For more information about creating calculated attributes, see the Architect User Guide.
For more information about calculated columns, see the Transformer Online help.
Steps to Define a Calculated Attribute in Transformer

1. Click the Data Sources list to make it active.
2. From the Edit menu, click Insert Attribute.
3. In the Name box, enter a name for the new column.
4. In the Type box, click Calculated, and then click Calculation.
If you haven't specified a Data Class, Transformer prompts you to specify whether the
column consists of text, date, or numeric data.
52 Architect (R)
5. In the left pane of the expression editor, expand the Columns, Functions, and Value folders
as needed, select each parameter you want to use, and click the arrow to insert the
parameter into the calculation expression (right pane of the editor).
6. When the expression is complete, click OK.

When adding security to an Architect model that is used by Transformer, do the following:
• Before you create models, ensure that Architect is configured to use the same
authentication source as Transformer.
Otherwise, you cannot use an Architect package as a Transformer data source. For more
information, see the Transformer Online help.
• To let Transformer modelers modify cubes in Transformer, give their user class access to all
the required objects in the business and package layers.
If Transformer modelers want to test their queries, you must also give their user class
access to objects in the data access layer.
• Before users can retrieve data in a query that is based on an Architect model, you must give
their user class access to all the required objects in the data access layer.
• Restrict Transformer users from accessing data only at the table or entity level.
If you restrict access to an attribute or column, the user can still see the column of data
when they run their query.

54 Architect (R)
Index
A calculations (cont'd)
guidelines, 12, 27, 41
Access Manager Administration queries in Cognos Query, 27
setting security for Architect, 21 reports in Impromptu, 41
access privileges row-level summary, 28
Architect modelers, 21 simple, 27
report and query users, 22 storing in entities, 12
administrative access summary, 27
Architect models, 21 cardinality, 15, 49
aggregate calculations modifying in the business layer, 15
creating in Architect, 27 modifying in the data access layer, 9
creating in Cognos Query, 28 catalogs
alternate drill-down paths derived from packages, 37
creating, 51 importing, 38
Architect, 5 importing security, 42
configuring, 21 Cognos Query
database objects, 39 modeling guidelines, 25
using with Cognos Query, 25 column-level security
using with Impromptu, 37 setting, 22
using with Transformer, 47 user classes, 22
Architect modelers columns
security settings, 21 adding, 9
Architect models deleting, 9
adding security, 20 moving between tables, 9
administrating, 21 combining
delivering to users, 20 entities into one subject, 32
registering, 21 tables, 9, 14
Architect objects, 47 vertically partitioned tables, 14
attributes configuring
calculated, defining, 52 authentication sources, 21, 36
creating calculations, 12 contacting
defining, 49 Customer Support, 5
defining calculated, 52 containment relationships
deleting, 12 creating, 18
measures, 49 copyright, ii
usage, 48 cross-database joins automatically, 10
authentication sources Customer Support
configuring, 21, 36, 41, 53 contacting, 5
using, 21 customizing models
guidelines, 11
B
business information D
adding to models, 11, 26, 39, 48 data
business layer, 7 categorizing, 30
customizing, 11, 26, 39, 48 filtering, 19, 22, 29
deleting objects, 12, 13 restricting access, 19
security, 22 data access layer, 7
verifying, 9 creating SQL queries, 26
deleting objects, 9
C modifying objects, 9
security, 22
calculated verifying, 9
attributes, 52 data retrieval privileges
calculated attributes, defining, 52 users, 22
calculations data sources
aggregates, 27 Architect, 48
creating Architect, 27 defining connections, 21
creating in Architect, 27

Index
data sources (cont'd) filtering (cont'd)

giving access privileges, 21 entities for a user class, 52
importing multiple, 25, 37 entities in the business layer, 52
non-Architect, 48 entity attributes for a user class, 52
viewer, 50 subjects, 52
database objects filters
Architect, 39 assigning to user classes, 19
databases associating with entities, 19
defining joins, 10 associating with subtype entities, 19
date dimension, 50 foundation queries
defining calculated attributes, 52 derived from packages, 25
deleting
columns, 9
keys, 9
I
tables, 9 importing
denormalization catalogs, 38
guidelines, 13 guidelines, 9, 10, 25, 37
denormalizing metadata, 9, 10, 25, 37
packages, 13 multiple data sources, 25
design privileges security from catalogs, 42
report and query users, 22 user classes, 21
designing filters in Transformer, 51 Impromptu
dimensions exporting packages, 43
date, 50 joins, 39
determining levels, 50 Impromptu administrator, 41
relationships, 49 Impromptu catalogs
documentation derived from packages, 37
email address, 5 importing, 38
ordering extra books, 5 importing security, 42
drill-down paths importing without security, 43
creating alternate, 51 objects not imported, 38
creating alternate in Architect, 51 inner joins
creating alternate in Transformer, 51 creating, 15
drill-through defining in Architect, 39
summary to detail queries, 34 integration
Architect and Transformer, 47
E
entities
J
associating filters, 19 joins
combining into one subject, 32 across multiple databases, 10
combining into subjects, 18 defining keys, 11
defining keys, 13 equi, 39
deleting, 12 generating, 10
designing, 12 guidelines, 10
filtering, 52 Impromptu, 39
filtering in the business layer inner, 39
, 52 interpreting cardinality notation, 15
guidelines, 12 modifying, 9, 10
normalizing, 13, 51 non-equi, 39
purpose, 12 outer, 39
resolving ambiguous relationships, 17
storing calculations, 12 K
entity attributes
filtering, 52 keys
enumeration values defining for entities, 13
creating, 30 defining for joins, 11
equi-joins deleting, 9, 13
defining in Architect, 39 generating automatically, 11
exporting guidelines, 11
packages to Impromptu, 43 knowledge requirements
data modeling, 5, 12
F
L
filtering
Architect data source in Transformer, 51 layers in model, 7
data, 19, 22, 29 lookup tables
designing in Transformer, 51 accessing in queries, 19
56 Architect (R)
Index
lookup tables (cont'd) queries (cont'd)

packaging guidelines, 32 containing combined information, 32
creating query links, 33
customizing in Cognos Query, 27
M excluding NULL values, 15
mandatory relationships guidelines, 26
defining, 15 including calculations, 27
metadata including NULL values, 16
importing, 9, 10, 25, 37 reusability, 26
modeling guidelines using prompt filter values, 29
Transformer and Architect, 47 query designer, 27, 31
query links
N creating between queries, 33
query paths
non-equi-joins controlling at run time, 19
defining in Architect, 39 defining in packages, 33
normalization giving preference, 18
guidelines, 13 resolving ambiguity, 17
normalizing
business layer, 13
entities, 13 R
star schemas, 13 recursive relationships
NULL values modifying, 16
excluding in reports and queries, 15 reference relationships
including from reports and queries, 16 creating, 19
registering
O Architect models, 21
relationships
objects adding, 15
association, 7 containment, 18
modifying in the data access layer, 9 deleting, 17
types, 7, 25, 37 dimensions, 49
optional relationships guidelines, 15, 39
defining, 16 interpreting cardinality notation, 15
outer joins mandatory, 15
creating, 16 optional, 16
defining in Architect, 39 reference, 19
resolving ambiguity, 17
P reports
including calculations, 41
package layer, 7 row-level security
security, 22 all users, 19
verifying, 9 setting, 19
packages user classes, 19
creating a default, 20 row-level summaries
creating catalogs, 37 creating in Cognos Query, 28
creating foundation queries, 25 run-time privileges
customizing, 20, 31, 41 users, 22
defining query paths, 33
denormalizing, 13
designing for Cognos Query, 32 S
designing for Impromptu, 41 security
exporting to Impromptu, 41 Access Manager Administration, 21
guidelines, 20, 31, 41 adding to models, 20
security, 22 Architect modelers, 21
setting up drill-through, 34 Cognos Query users, 20, 36
verifying, 9 data retrieval privileges, 22
prompts design privileges, 22
creating in Architect, 29 guidelines, 20, 36, 41, 53
creating in Cognos Query, 29 importing user classes, 21
guidelines, 29 Impromptu users, 41
publishing models report and query users, 22
guidelines, 36 restricting access data, 19
Upfront, 36 run-time privileges, 22
setting column-level, 22
Q setting row-level, 19
structural privileges, 22
queries Transformer users, 53
based on SQL queries, 26

Index
security (cont'd) verifying

types, 22 business layer, 9
Upfront, 22 data access layer, 9
verifying, 9 package layer, 9
security administrator, 21 packages, 9
self-joins security, 9
modifying, 16
simple calculations
snowflake schemas
creating, 13
SQL queries
creating, 26
guidelines, 26
star schemas
normalizing, 13
stored procedures
using with Cognos Query, 25
structural privileges
users, 22
subjects
combining entities, 18
filtering, 52
subtype entities
creating filters, 19
resolving recursive relationships, 16
subtype relationships
creating, 17
summary calculations
appearance in queries, 28
creating in Cognos Query, 28
row-level, 28
T
tables
combining, 9
deleting, 9
modifying weight, 18, 19
vertically partitioned, 14
Transformer
data sources, 47
dimensions, 47
levels, 47
measures, 47
modeling guidelines, 47
U
Unified Modeling Language
cardinality in Architect, 15
Upfront
publishing models, 36
user access
Architect models, 22
user classes
associating filters, 19
creating, 21
importing, 21
security settings, 22
users
creating, 21
V
verification process, 9
58 Architect (R)

Cognos Architecture

Uploaded by

Copyright:

Available Formats

Cognos Architecture

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Cognos Architecture

Uploaded by

Copyright:

Available Formats

<HEAD>

<meta name="last-review" content="12-10-2001">

<meta name="title" content="Model with Architect">

<meta name="product" content="Architect - BI">

<meta name="prodversion" content="Architect - BI-7.0">

<meta name="area" content="support">

<meta name="classification" content="documentation">

Model with Architect

Model with Architect iii

What Is in This Book

What You Need to Know to Use This Product Effectively

Other Architect Documentation

How to Order Extra Books

Model with Architect 5

The Architect Model

Data Access Layer

Objects in Each Layer

Model with Architect 7

Metadata object in Object in data Object in business Object in

Schema Schema Not applicable Not applicable

Database catalog Database catalog Not applicable Not applicable

Table Table Entity Subject

Column Column Attribute Subject attribute

Join Join Association, Query path

Alias or Synonym Synonym Subtype entity Subject

View View Entity Subject

Primary key Key Key Not applicable

Index Index Not applicable Not applicable

Stored procedure Stored procedure Entity Subject

Not applicable User-defined function Not applicable Not applicable

Not applicable SQL query Entity Subject

Not applicable Not applicable Filter Subject filter

Not applicable Not applicable Prompt Subject prompt

Not applicable Not applicable Style Not applicable

Not applicable Not applicable Enumeration value Not applicable

Verify the Contents of the Model

Verify the Data Access Layer

Verify the Business Layer

Model with Architect 9

Import Everything into the Model

Define Cross-Database Joins

Define Additional Keys

Add Business Information

Model with Architect 11

Within the business layer you can

Design Effective Entities

Keep All Entities and Attributes in the Business Layer

Store Calculated Attributes in Related Entities

Define Unique Keys for Entities

Model with Architect 13

Product ProductNumber (key)

ProductType ProductType (key)

ProductLine ProductLine (key)

Combine Tables into One Entity

Combine Vertically Partitioned Tables

Define Effective Relationships

Define Additional Relationships

Include or Exclude NULL Values in a Report

Define Mandatory Relationships

Model with Architect 15