Sunday, February 15, 2009

FRAMEWORK MANAGER










































CREATING PACKAGES FROM FRAMEWORK MANAGER(ABOVE)

Framework Manager
•CognosFramework Manager is a metadata modeling tool.
•A model is a business presentation of the tables/data in one or more data sources.
•Model is used by Reports and Queries to fetch the data.

Framework Manager Components
•Projects
•Models
•Namespaces
•Folders
•Query Subjects
•Query Items
•Data Sources
•Packages

Project
•A project is a set of models, packages, and related information for maintaining and sharing model information.
•Often a single project will span many more data sources or tables than any set of users requires access to.

Model
•A model is the set of related objects required for one or more related reporting applications.
•Most importantly, it provides a business view of the information in the source data.

The business view can
–organize data items in folders that represent business areas for reporting
–format data items with numeric, currency, date, time, and other formats
–present multilingual folder and item names, description, tips, and data so that users can operate in their language of choice
–automate the generation of SQL queries sent to the database
–specify default prompting.

•The Framework Manager modeler can
–Specify the rules governing query generation
–restrict user access to specific rows or columns of data
–Model data relationships to hide the complexity of data from report authors

Query Subject
•In most cases, query subjects behave like tables.
•Query subjects produce the same set of rows regardless of which columns were queried.
•There are several types of query subjects:
–data source
–model
–stored procedure
•You can refine data source query subjects in various ways.
•For example, you can add calculations or filters, or you can add or modify relationships between query subjects to define the query generation rules for report authors.

Query Items
•For report authors, query items are the most important objects for creating reports.
•Query items are contained in query subjects.
•For example, a query subject that references an entire table contains query items that represent each column in the table.

Namespace
•Namespaces are containers like folders. Objects in a Framework Manager project must be uniquely identifiable. If you have two objects that have the same name, they must reside in two separate namespaces.
•A namespace uniquely identifies query items, query subjects and other objects.
•You import different databases into separate namespaces to avoid duplicate names.

Data Sources
•Database details for connection

Folders
•You can create folders to organize objects by subject or functional area.
•This makes it easier for you to locate metadata in projects that have many query subjects or dimensions.
•You can nest folders by creating new folders in an existing folder.

Packages
•A package is a subset of the query subjects and other objects defined in the project.
•Multiple packages can be published from one Project.
•A package is what is actually published to the CognosReportNetserver, and it is used to create reports and adhocqueries.

Designing a Project
•A project is a set of models, packages, and related information for maintaining and sharing model information.
•You design a project in Framework Manager to organize the metadata and data you want, in a format that is meaningful to report authors.

Metadata Sources
•Framework Model
•Database
•Other XML Definition files
•Erwin Model etc.

Granularity
•Fact Detection enabled
•Required to avoid double counting while using for multiple facts in Model which have different granularity
•E.g. Revenue at Date level and forecast at month level

Security Filters
•You can restrict the data represented by query subjects in a project by creating a security filter.
•The security filter controls the data that is shown to the report authors when they set up their reports.
•For example, your Sales team consists of a Sales Director, and four Sales Managers. You create a security filter that includes the groups directors and sales managers, and apply the filter to the salary query subject. When the package is available for report authors, and a report is generated for the Sales Managers and the Sales Director, only the Sales Director can see the salary information for the sales managers.

Externalize Method
•When publishing a package, you have the option to externalize query subjects and dimensions into formats that you can use in Transformer or other applications.
–CSV Method (Comma separated file)
–Tab Method (Tab separated file)
–IQD (Native SQL file)
The Externalize Auto Summary Property
•You can specify that the output be aggregated. By default, Framework Manager returns rows at the detail level without applying any aggregation.

Add Items to Query Subject
•You can add any combination of objects to a query subject, such as
–query items
–other query subjects
–dimensions.
–You can add stand-alone calculations and filters, and you can also embed calculations and filters in the query subject.

Determinants
•A determinant is the set of query items that can be used to uniquely identify a set of data.
•Determinants are imported based on key and index information in the data source.
•We recommend that you always review the determinants that are imported.
•Model query subjects do not have determinants defined for them automatically. If determinants are needed, you must define them manually.
•Stored procedure query subjects do not have determinants.
•You cannot use determinants with user-entered SQL.

Change How the SQL Is Generated
•You can specify how Framework Manager generates the SQL that retrieves data from relational data sources.
•The SQL Generation type of a query subject can be set to either As View or Minimized. By default, it is set to Minimized.
•When the generation type is set to Minimized, the generated SQL contains only the minimal set of tables and joins needed to obtain valuesfor the selected query items.
•When the generation type is set to As View, Framework Manager generates queries that contain the full SQL statement that defined the query subject.
•Use As View when you want to ensure that the query is run as a block.

Usage Property
•The Usage property identifies the intended use for the data represented by each query item.
•They are also used to set the aggregation rules of query items and calculations.
•For relational query items, the value of the Usage property depends on the type of database object the query item is based on.

Regular Aggregate Property
•The Regular Aggregate property identifies the type of aggregation that is associated with the query item or calculation when you publish it. The report author can either use this default setting to perform calculations on groups of data, or use the reporting application to apply a different type of aggregation.
•For example, if the Regular Aggregate property value of the Quantity query item is sum, and the report author groups it by Product Name, the Quantity column in the report shows the total quantity of each product.
•During metadata import, Framework Manager uses the usage value to determine the value of the Regular Aggregate property of eachquery item.
•During metadata import, Framework Manager uses the usage value to determine the value of the Regular Aggregate property of each query item.
–Fact (Sum)
–Identifiers (Count)
–Attributes (Unsupported)
•The following aggregation values are supported for relational data sources:
–Sum, Count, Max, Min, Average, Variance etc.

Prompt
•Prompts help users quickly find the information they need in a report.
•Prompts are generally defined in the reporting tool. However, you can change the definition of the query subject in the model so that a prompt appears automatically when report authors create filters.

Verifying Relationships
•A relationship describes how to create a relational query for multiple objects, such as dimensions and query subjects.
•Without relationships, these objects are isolated sets of data.
•Relationships work in both directions. You often must examine both directions to fully understand the relationship.
•The different types of relationships are
–one-to-one
•One-to-one relationships occur when one instance of data in a query subject relates to exactly one instance of another. For example, each student has one student Id.
–one-to-many or zero-to-many
•One-to-many or zero-to-many relationships occur when one instance of data in a query subject relates to many instances ofanother. For example, each teacher has many students.
–many-to-many
•Many-to-many relationships occur when many instances of data in a query subject relate to many instances of another. For example, many students have many teachers.

Relationships
•Can be defined Manually
•Can be detected Automatically

Cardinality
•Cardinality indicates the nature of the relationship between two objects.
•The cardinality specified in the relationship between query subjects or dimensions determines how and when Cognos8 generates stitched queries.
•A stitched query uses multiple subqueries, one for each star, brought together by a full outer join on the common keys.
•Stitched queries are needed for multiple-fact querying across conformed dimensions and across different levels of granularity.

•When cardinality is combined with dimensions to control how queries are generated, you can
–prevent double-counting (When Facts are having different granularity)
–automatically resolve loop joins

Detecting Cardinality from the Data Source
•When importing from a relational data source, cardinality is detected based on a set of rules that you specify. The available options are
–use primary and foreign keys
–use matching query item names that represent uniquely indexed columns
–use matching query item names
•The most common situation is to use primary and foreign keys as well as matching query items that represent uniquely indexed columns.

Notation
•Possible end labels are
–0..1 (zero or one match)
–1..1 (exactly one match)
–0..n (zero or more matches)
–1..n (one or more matches)
•The first part of the notation specifies the type of join for this relationship:
–an inner join (1)
–an outer join (0)

Making Metadata Available to Report Authors
•A package is a subset of the query subjects and other objects defined in the project.
•You then publish the package to the CognosReportNetserver so that the report authors can use the metadata.
•You can create several packages from the same project, each package used to meet different reporting requirements.
•To publish a project:
–Check the project to ensure that the contents are consistent anddo not contain any errors
–Set governors for reports
–Create custom packages to suit different reporting requirements
–Add security to the package
–Specify languages in the package
–Publish the package to a location that report authors can access

Query Governors…..
Report Table Limits
–You can control the number of tables that a user can retrieve in a query or report.
–A setting of zero (0) means no limit is set.
Data Retrieval Limits
–You can control the number of rows that are returned in a query or report. Rows are counted as they are retrieved.
–A setting of zero (0) means no limit is set.

Query Execution Time Limits
–You can limit the time that a query can take.
Large Text Items Limit
–You can control the character length of BLOBS (binary large objects) that a user can retrieve in a query or report.
Allow Enhanced Model Portability at Run Time
–This governor is selected upon initial upgrade. It prevents rigid enforcement of data types so that a Cognos8 model can function as a ReportNet1.x model until you update the data types in the metadata

•Allow Dynamic Generation of Dimension Information
–Select this governor upon initial upgrade of a ReportNet1.x model.
–This governor allows consistent behavior with ReportNet1.x by deriving a form of dimension information from the relationships, key information, and index information in the data source.
•Allow Usage of Local Cache
–Select this governor to specify that all reports based on this model should use cached data. By default, this governor is disabled.
•Outer Joins–You can control whether outer joins can be used in your query or report.–If you keep the setting as deny, you are notified if you set the cardinality for outer join & generate SQL.

•Cross-Product Joins
–You can control whether cross-product joins can be used in your query or report.
–A cross-product join retrieves data from tables without joins. This type of join can take a long time to retrieve data.
•Use With Clause When Generating SQL
–You can choose to use the With clause with CognosSQL if your data source supports the With clause.

With Clause
•If the data source supports it, you can use the With clause with CognosSQL.
•The With clause is used to generate more readable SQL and to let the data source generate a more optimal plan for data retrieval.
•The data source can more easily detect the cases where the same tables must be scanned and can then resolve these as an inline view or temporary table.
•Use the With clause for better query performance.

Working with Dimensions
•A dimension is a broad grouping of descriptive data about a major aspect of a business, such as products, dates, or markets.
•The types of dimensions that you can work with in Framework Manager are
–regular dimensions (Business Dimensions)
–measure dimensions (Facts)

Star Schema
•A measure dimension and regular dimensions organized into a cluster is often referred to as a star schema group but can also be referred to as a functional or subject area group.

Types of Framework Manager Dimensions
•If the source of a dimension is SQL statements, the dimension is a data source dimension.
•If the source is other objects in the model, it is a model dimension.
•A model can contain data source regular dimensions, model regular dimensions, data source measure dimensions, and model measure dimensions.

Create a Regular Dimension
•A regular dimension contains descriptive and business key information and organizes the information in a hierarchy, from the highest level of granularity to the lowest.
•It usually has multiple levels and may have multiple key segments to define a level.
•It may also have multiple hierarchies.
•Use regular dimensions to organize and present descriptive information to guide the end user experience in the report authoring tools.

•When creating regular dimensions, you must understand the dimensionality of the data. You must be able to answer the following questions:
•What are the levels in your dimension?
•What is the order and combination of levels that form hierarchies?
•What uniquely identifies a level?

Roles
•The default roles include the following:
•_businessKey
–Represents the key for the level. This role is also used to drill through from one data source to another because the business key should be consistent across your organization.
–The _businessKeyrole can be assigned to only one attribute in a level.
•_memberCaption
–Presents the caption for a member that will be shown in the report authoring tools.
–The _memberCaptionrole is necessary to leverage member functions and to enable dragging and dropping levels in the report authoring tools.
–Ensure that the data type is set to string for the item that will be assigned the _memberCaptionrole.
•_memberDescription
–Returns the description for a member within a dimension.

Steps for Regular Dimension
1. Select a namespace or folder where you want to place the dimension. From the Actions menu,
click Create, Regular Dimension, and then click the Dimension tab.
Tip: In the Dimension Map tab, click the regular dimension button.
2. Click the Dimension tab.
3. Click Add Hierarchy and then drag one or more objects from the Available items box to the
Hierarchies box.
You can define multiple hierarchies for a dimension. The first hierarchy is used as the default,
or primary, hierarchy.
You can also create an alternate hierarchy by copying a level. Click a level and drag it to the
right border of the dimension. You can only copy a level within the same dimension.
Tip: In the Dimension Map tab, drag objects from the Project Viewer to the Dimensions box.
4. Click Add Level and then drag one or more objects from the Available items box into the new
level.
You can also create copies of levels in the Dimension Definition dialog box or in the
Dimension Map tab. Click the level and drag it to another position in the hierarchy. All
attributes of the level are also copied. You can only copy a level within the same dimension.
Tip: In the Dimension Map tab, drag objects from the Project Viewer to the Dimensions box.
•Which data elements are associated at each level?

5. If you want to use a different item in a level, drag it from theAvailable items box to the Select
a level in the hierarchy control to see the query items box.
You are prompted to specify its role.
By default, Framework Manager adds the name of the namespace.
You can explore or change measures. Tip: In the Dimension Map tab, click Measures,
right-click one or more measures, and click the command you want.
You can have a multiple-part key such as first name plus last name. Create a new attribute
that combines these two items and then specify that the new attribute is the business key.
6. If you want to indicate that the keys of the levels above the current level are not necessary to
identify the members in this level, select the item and select the Unique Level check box.
Do this only if the key values are unique regardless of their context, such as postal code
values.
7. Choose the options that you want:
•Specify roles
•Embed calculations by clicking Add and then defining the expression
To change a calculation that has been embedded in the dimension,in the Dimension Map
tab, click Attributes, right-click the query item, and click Edit Expression.
•Embed filters
•Test the dimension
•Edit the SQL and change various options
8. Click OK.
9. To change the default hierarchy for a dimension with multiple hierarchies, do the following:
•In the Properties pane, click the ellipsis (...) button in the Default Hierarchy box.
•Select a different hierarchy, and click OK.

Measure Dimension
•A measure dimension is a collection of facts such as Quantity Sold or Price.
•You can add value by embedding calculations based on existing business rules, such as Profit Margin.
•You cannot define hierarchies or levels for a measure dimension.

Define Scope Relationships for Dimensions
•A scope relationship defines that measure dimensions and regular dimensions can be related for reporting purposes.
•If you set the scope relationship for the measure dimension, the same settings apply to all measures in the measure dimension.
•If data is reported at a different level for the measures in the measure dimension, you can also define a scope relationship for each measure.
•Scope relationships should always be set at the level of the underlying relational join or higher.

Creating Business View of Metadata
•When you create a business view of the metadata, you make it easier for your users to find and understand the data contained in the model.
•The business view represents an organizational structure that mirrors your business and your decision-making processes.
•To Create Business View you can
–Organize objects by creating folders or namespaces
–Include metadata in several folders by using shortcuts
–Organize query subjects by creating query item folders

No comments:

Post a Comment