Cognos Architecture

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

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

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

<meta name="version" content="7.0">

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

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

Cognos

Architect

(R)

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



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 Add Business Information 26 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 Add Security to the Model 36 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 Add Business Information 39 Define Relationships 39 Provide Calculated Information 41 Prepare the Model for Reporting 41 Add Security to the Model 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 Add Business Information 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 Add Security to the Model 53 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

Model with Architect 7

Chapter 2: Modeling Guidelines 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.

8 Architect ( R )

Metadata object in database

Object in data access layer

Object in business Object in 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, container, or reference relationship

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

Chapter 2: Modeling Guidelines

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.

Model with Architect 9

Chapter 2: Modeling Guidelines 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 )

Chapter 2: Modeling Guidelines 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.

Model with Architect 11

Chapter 2: Modeling Guidelines 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 )

Chapter 2: Modeling Guidelines

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.

Model with Architect 13

Chapter 2: Modeling Guidelines 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 )

Chapter 2: Modeling Guidelines

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.

Model with Architect 15

Chapter 2: Modeling Guidelines 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 )

Chapter 2: Modeling Guidelines

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.

Model with Architect 17

Chapter 2: Modeling Guidelines

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 )

Chapter 2: Modeling Guidelines

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.

Model with Architect 19

Chapter 2: Modeling Guidelines

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 )

Chapter 2: Modeling Guidelines 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. Model with Architect 21

Chapter 2: Modeling Guidelines

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 )

Chapter 2: Modeling Guidelines 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).

Model with Architect 23

Chapter 2: Modeling Guidelines

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 Subject

Object in Upfront 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 associated with it

Column in a query and a drop-down box on 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. Model with Architect 25

Chapter 3: Create Models for Cognos Query 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).

Add Business Information 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 )

Chapter 3: Create Models for Cognos Query

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).

Model with Architect 27

Chapter 3: Create Models for Cognos Query 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 )

Chapter 3: Create Models for Cognos Query 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.

Model with Architect 29

Chapter 3: Create Models for Cognos Query 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 )

Chapter 3: Create Models for Cognos Query 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. Model with Architect 31

Chapter 3: Create Models for Cognos Query 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 )

Chapter 3: Create Models for Cognos Query

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.

Model with Architect 33

Chapter 3: Create Models for Cognos Query 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 )

Chapter 3: Create Models for Cognos Query 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.

Model with Architect 35

Chapter 3: Create Models for Cognos Query

Add Security to the Model 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).

Model with Architect 37

Chapter 4: Create Models for Impromptu

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 )

Chapter 4: Create Models for Impromptu

Add Business Information 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). For information about general modeling guidelines, see "Add Business Information" (p. 11).

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

Model with Architect 39

Chapter 4: Create Models for Impromptu

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 )

Chapter 4: Create Models for Impromptu

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.

Add Security to the Model 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. For information about general security guidelines, see "Add Security to the Model" (p. 20).

Model with Architect 41

Chapter 4: Create Models for Impromptu

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 )

Chapter 4: Create Models for Impromptu

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.

Model with Architect 43

Chapter 4: Create Models for Impromptu

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 )

Chapter 4: Create Models for Impromptu 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.

Model with Architect 45

Chapter 4: Create Models for Impromptu

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 Layer Object Name

Architect Package Layer Object Name

Transformer/Metada ta Explorer Name

Entity

Subject

Level

Attribute

Subject-Attribute

Depends on usage, for example, measure, level, label Model with Architect 47

Chapter 5: Create Models for PowerPlay Transformer

Architect Business Layer Object Name

Architect Package Layer Object Name

Transformer/Metada 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.

Add Business Information 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. For information about general modeling guidelines, see "Add Business Information" (p. 11).

Set Attribute Usage All attribute usage settings in Architect correspond to specific roles in the Transformer model.

48 Architect ( R )

Chapter 5: Create Models for PowerPlay Transformer 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.

Model with Architect 49

Chapter 5: Create Models for PowerPlay Transformer

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)

Chapter 5: Create Models for PowerPlay Transformer

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.

Model with Architect 51

Chapter 5: Create Models for PowerPlay Transformer

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)

Chapter 5: Create Models for PowerPlay Transformer 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.

Add Security to the Model 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. For information about general security guidelines, see "Add Security to the Model" (p. 20).

Model with Architect 53

Chapter 5: Create Models for PowerPlay Transformer

54 Architect (R)

Index A Access Manager Administration setting security for Architect, 21 access privileges Architect modelers, 21 report and query users, 22 administrative access Architect models, 21 aggregate calculations creating in Architect, 27 creating in Cognos Query, 28 alternate drill-down paths creating, 51 Architect, 5 configuring, 21 database objects, 39 using with Cognos Query, 25 using with Impromptu, 37 using with Transformer, 47 Architect modelers security settings, 21 Architect models adding security, 20 administrating, 21 delivering to users, 20 registering, 21 Architect objects, 47 attributes calculated, defining, 52 creating calculations, 12 defining, 49 defining calculated, 52 deleting, 12 measures, 49 usage, 48 authentication sources configuring, 21, 36, 41, 53 using, 21

calculations (cont'd) guidelines, 12, 27, 41 queries in Cognos Query, 27 reports in Impromptu, 41 row-level summary, 28 simple, 27 storing in entities, 12 summary, 27 cardinality, 15, 49 modifying in the business layer, 15 modifying in the data access layer, 9 catalogs derived from packages, 37 importing, 38 importing security, 42 Cognos Query modeling guidelines, 25 column-level security setting, 22 user classes, 22 columns adding, 9 deleting, 9 moving between tables, 9 combining entities into one subject, 32 tables, 9, 14 vertically partitioned tables, 14 configuring authentication sources, 21, 36 contacting Customer Support, 5 containment relationships creating, 18 copyright, ii cross-database joins automatically, 10 Customer Support contacting, 5 customizing models guidelines, 11

B business information adding to models, 11, 26, 39, 48 business layer, 7 customizing, 11, 26, 39, 48 deleting objects, 12, 13 security, 22 verifying, 9

C calculated attributes, 52 calculated attributes, defining, 52 calculations aggregates, 27 creating Architect, 27 creating in Architect, 27

D data categorizing, 30 filtering, 19, 22, 29 restricting access, 19 data access layer, 7 creating SQL queries, 26 deleting objects, 9 modifying objects, 9 security, 22 verifying, 9 data retrieval privileges users, 22 data sources Architect, 48 defining connections, 21

Model with Architect 55

Index data sources (cont'd) giving access privileges, 21 importing multiple, 25, 37 non-Architect, 48 viewer, 50 database objects Architect, 39 databases defining joins, 10 date dimension, 50 defining calculated attributes, 52 deleting columns, 9 keys, 9 tables, 9 denormalization guidelines, 13 denormalizing packages, 13 design privileges report and query users, 22 designing filters in Transformer, 51 dimensions date, 50 determining levels, 50 relationships, 49 documentation email address, 5 ordering extra books, 5 drill-down paths creating alternate, 51 creating alternate in Architect, 51 creating alternate in Transformer, 51 drill-through summary to detail queries, 34

E entities associating filters, 19 combining into one subject, 32 combining into subjects, 18 defining keys, 13 deleting, 12 designing, 12 filtering, 52 filtering in the business layer , 52 guidelines, 12 normalizing, 13, 51 purpose, 12 resolving ambiguous relationships, 17 storing calculations, 12 entity attributes filtering, 52 enumeration values creating, 30 equi-joins defining in Architect, 39 exporting packages to Impromptu, 43

F filtering Architect data source in Transformer, 51 data, 19, 22, 29 designing in Transformer, 51

56 Architect (R)

filtering (cont'd) entities for a user class, 52 entities in the business layer, 52 entity attributes for a user class, 52 subjects, 52 filters assigning to user classes, 19 associating with entities, 19 associating with subtype entities, 19 foundation queries derived from packages, 25

I importing catalogs, 38 guidelines, 9, 10, 25, 37 metadata, 9, 10, 25, 37 multiple data sources, 25 security from catalogs, 42 user classes, 21 Impromptu exporting packages, 43 joins, 39 Impromptu administrator, 41 Impromptu catalogs derived from packages, 37 importing, 38 importing security, 42 importing without security, 43 objects not imported, 38 inner joins creating, 15 defining in Architect, 39 integration Architect and Transformer, 47

J joins across multiple databases, 10 defining keys, 11 equi, 39 generating, 10 guidelines, 10 Impromptu, 39 inner, 39 interpreting cardinality notation, 15 modifying, 9, 10 non-equi, 39 outer, 39

K keys defining for entities, 13 defining for joins, 11 deleting, 9, 13 generating automatically, 11 guidelines, 11 knowledge requirements data modeling, 5, 12

L layers in model, 7 lookup tables accessing in queries, 19

Index lookup tables (cont'd) packaging guidelines, 32

M mandatory relationships defining, 15 metadata importing, 9, 10, 25, 37 modeling guidelines Transformer and Architect, 47

N non-equi-joins defining in Architect, 39 normalization guidelines, 13 normalizing business layer, 13 entities, 13 star schemas, 13 NULL values excluding in reports and queries, 15 including from reports and queries, 16

O objects association, 7 modifying in the data access layer, 9 types, 7, 25, 37 optional relationships defining, 16 outer joins creating, 16 defining in Architect, 39

P package layer, 7 security, 22 verifying, 9 packages creating a default, 20 creating catalogs, 37 creating foundation queries, 25 customizing, 20, 31, 41 defining query paths, 33 denormalizing, 13 designing for Cognos Query, 32 designing for Impromptu, 41 exporting to Impromptu, 41 guidelines, 20, 31, 41 security, 22 setting up drill-through, 34 verifying, 9 prompts creating in Architect, 29 creating in Cognos Query, 29 guidelines, 29 publishing models guidelines, 36 Upfront, 36

Q queries based on SQL queries, 26

queries (cont'd) containing combined information, 32 creating query links, 33 customizing in Cognos Query, 27 excluding NULL values, 15 guidelines, 26 including calculations, 27 including NULL values, 16 reusability, 26 using prompt filter values, 29 query designer, 27, 31 query links creating between queries, 33 query paths controlling at run time, 19 defining in packages, 33 giving preference, 18 resolving ambiguity, 17

R recursive relationships modifying, 16 reference relationships creating, 19 registering Architect models, 21 relationships adding, 15 containment, 18 deleting, 17 dimensions, 49 guidelines, 15, 39 interpreting cardinality notation, 15 mandatory, 15 optional, 16 reference, 19 resolving ambiguity, 17 reports including calculations, 41 row-level security all users, 19 setting, 19 user classes, 19 row-level summaries creating in Cognos Query, 28 run-time privileges users, 22

S security Access Manager Administration, 21 adding to models, 20 Architect modelers, 21 Cognos Query users, 20, 36 data retrieval privileges, 22 design privileges, 22 guidelines, 20, 36, 41, 53 importing user classes, 21 Impromptu users, 41 report and query users, 22 restricting access data, 19 run-time privileges, 22 setting column-level, 22 setting row-level, 19 structural privileges, 22 Transformer users, 53

Model with Architect 57

Index security (cont'd) types, 22 Upfront, 22 verifying, 9 security administrator, 21 self-joins modifying, 16 simple calculations creating in Architect, 27 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 Architect, 27 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)

verifying business layer, 9 data access layer, 9 package layer, 9 packages, 9 security, 9