Cognos 10 Framework Manager_ZZP.docx

IBM Cognos Framework ManagerInstall and DevelopBy ZZP, 2011-11-30.I wrote this document last week, when I was studying the FM, most of the contents are copied form Handbook. I just make it simpler, and added the description on configuration and segment. Please forgive me if you find any mistake in this document, I am a rookie. Appreciate if you can correct and share it. Regretfully I havent done the Security excise. Anyway, it may help you a little on Cognos FM.For more information please go to handbook: IBM Cognos Business Intelligence V10.1 HandbookEN: /abstracts/sg247912.html?OpenCN: /developerworks/cn/data/tutorials/dm-1111wumd2/index.htmlTable of Contents1.Install and configure41)Install Cognos FM step by step42)Install Oracle Client43)Configure TNS file44)Copy Oracle JAR file to FM client45)Copy CSK folder from Cognos server to FM client46)Open Configuration and set the Environment47)Save your changes52.Interface introduction:51)Projects62)Models63)Namespaces64)Dimensions75)Query Subjects76)Query Items77)Parameter Map78)Packages83.Create a project81)Create a project by Model Design Accelerator82)The three Views in the Model143)Import additional metadata and create the relationship154.Specify Determinants261)Add Determinants for Time262)Add Determinants for Product275.Add business logic to the model281)Add filters to the model282)Filter Options293)Add calculations to the model296.Create dimensional objects for OLAP-style reporting311)Create Regular Dimensions322)Create Time Regular Dimensions327.Create and configure a package391)Create Namespace Query & Analysis392)Create packages408.Parameter Maps and Macros441)About Parameter Maps and Macros442)Cerate Parameter459.Verify the model5010.Security in Framework Manager521)Object level security522)Row level security533)Package level security5411.Other Introduction551)Segmenting and Linking Projects552)Introduce about the items in Metadata Wizard593)Relationships between tables604)User-defined function60Environment:Cognos Server: 36Database (Oracle 11g): 051. Install and configure1) Install Cognos FM step by step If you are installing FM client on the Cognos server, you can install FM by default steps.If you are using this FM client connect to other server, please dont install FM client to the folder where the server installed.2) Install Oracle Client Make sure you have oracle client installed on the Cognos FM client that has the datasource entry. 3) Configure TNS fileThe tnsnames.ora file must have the correct configuration for the Oracle server that you are trying to connect to. Setup TNS in your Oracle Client like below:Orcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 05)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XXXX) ) )4) Copy Oracle JAR file to FM clientCopy C:appAdministratorproduct11.2.0dbhome_1jdbclibojdbc5.jar and paste to C:Program FilesIBMcognosFMwebappsp2pdWEB-INFlib5) Copy CSK folder from Cognos server to FM clientCopy the folder configuration/csk from the Cognos server and paste to the same path in FM client.6) Open Configuration and set the EnvironmentChange the Gateway URI and Dispatcher URI path to your own server address;7) Save your changesClick the Save button on the toolbar. Ignore the Certificate error.2. Interface introduction:Projects, Models, Namespaces, Dimensions, Query Subjects, Query Items, Package.1) ProjectsA project contains a model, namespaces, packages, data sources, and related information for maintaining and sharing model information. A single project can span many data sources or tables.2) ModelsA model is the set of related dimensions, query subjects, and other objects required for one or more related reporting applications.The Framework Manager model is a metadata layer that adds value to a data source in several ways. Most importantly, it provides a business view of the information in the source data to simplify building reports, analyses, and queries.3) NamespacesA namespace uniquely identifies query items, dimensions, query subjects, and other objects. You import different databases into separate namespaces to avoid duplicate names.4) DimensionsA dimension is a broad grouping of data about a major aspect of a business, such as products, dates, or markets.The types of dimensions that you can work with in IBM Cognos Framework Manager are regular dimensions and measure dimensions. In SAP BW, measure dimensions are called key figures.5) Query SubjectsA query subject is a set of query items that have an inherent relationship. In most cases, query subjects behave like tables. Query subjects produce the same set of rows regardless of which columns were queried.There are different types of query subjects: Data sourceData source query subjects directly reference data in a single data source. IBM Cognos FrameworkManager automatically creates a relational data source query subject for each table and view that you import into your model. ModelModel query subjects are not generated directly from a data source but are based on query items in other query subjects or dimensions, including other model query subjects. By using model query subjects, you can create a more abstract, business-oriented view of a data source. stored procedureStored procedure query subjects are generated when you import a procedure from a relational data source. IBM Cognos Framework Manager supports only user-defined stored procedures. System stored procedures are not supported.6) Query ItemsA query item is the smallest piece of the model that can be placed in a report. It represents a single characteristic of something, such as the date that a product was introduced.Query items are contained in query subjects or dimensions. For example, a query subject that references an entire table contains query items that represent each column in the table. For your users, query items are the most important objects for creating reports. They use query item properties of query items to build their reports.7) Parameter MapUse parameters to create conditional query subjects that allow for substitutions when the report is run. Parameter maps are objects that store key-value pairs. Parameter maps are similar to data source look-up tables. Each parameter map has two columns, one for the key and one for the value that the key represents. You can manually enter the keys and values, import them from a file, or base them on existing query items in the model.8) PackagesA package is a subset of the dimensions, query subjects, and other objects defined in the project. A package is what is actually published to the IBM Cognos BI server, and it is used to create reports, analyses, and ad hoc queries.3. Create a project1) Create a project by Model Design Acceleratora) Open Cognos Framework Manager, and click Create a new project using Model Design Acceleratorb) Enter a new Project Namec) Login Cognos Serverd) Choose the Design Languagee) Choose the DataSourcef) Open GOSALESDW and choose below tables GO_TIME_DIM SLS_ORDER_METHOD_DIM SLS_PRODUCT_DIM SLS_PRODUCT_LOOKUP SLS_PRODUCT_TYPE_LOOKUP SLS_PRODUCT_LINE_LOOKUP SLS_SALES_FACTg) Close the User Guide widowh) In the Model Accelerator panel, right click Fact Query Subject and rename it as Sales Fact. Expand SLA_SALES_FACT from Explorer tree, and drag below items to Sales Fact. QUANTITY UNIT_COST UNIT_PRICE UNIT_SALE_PRICE GROSS_MARGIN SALE_TOTAL GROSS_PROFITi) Rename New Query Subject 1 as Products Expand SLS_PRODUCT_LINE_LOOKUP and drag PRODUCT_LINE_EN to Products Expand SLS_PRODUCT_TYPE_LOOKUP and drag PRODUCT_TYPE_EN to Products Expand SLS_PRODUCT_LOOKUP and drag PRODUCT_ NAME to Productsj) You will see the Pop-up Relationship Editing Mode for: Products, that because Framework Manager couldnt detect the relationship between SLS_PRODUCT_LOOKUP and SLS_SALES_FACT. So lets add it manually. Press Ctrl and choose PRODUCT_NUMBER in SLS_PRODUCT_LOOKUP, PRODUCT_NUMBER in SLS_PRODUCT_DIM, and click Create a Model Relationship button from the top toolbar. Click OK in window Modify the Relationship, and also click OK to close window Relationship Editing Mode for: Products.Take care about the relationship cardinality is one to many.k) Expand SLS_PRODUCT_DIM from Explorer tree panel and drag blow items to Products. PRODUCT_KEY PRODUCT_LINE_CODE PRODUCT_TYPE_KEY PRODUTCT_TYPE_CODE PRODUCT_NUMBER PRODUCT_IMAGE INTRODUCTION_DATE DISCONTINUED_DATEl) Rename New Query Subject 2 as Time; Expand GO_TIME_DIM from Explorer tree panel and choose and drag all the items between DAY_KEY and WEEKDAY_EN to Time.m) Rename New Query Subject 3 as Order Methods; Expand SLS_ORDER_METHOD_DIM from the Explorer tree panel and drag below items to Order Methods. ORDER_METHOD_KEY ORDER_METHOD_CODE ORDER_METHOD_ENn) At last you will see the Model Accelerator panel as below. Click Generate Model and click Yes in the prompt box. Model Accelerator will generate the model for you.o) Click the save button. Now the first model is created successfully.2) The three Views in the ModelEach time you generate a model, namespaces are created in the open Framework Manager project. Physical View contains only the tables that were needed to support the star schema model. Business View contains model query subjects with only the columns you selected when building the query subjects. These are the objects you built in Model Design Accelerator. Presentation View contains a series of shortcuts to the model query subjects you created in the Business View.3) Import additional metadata and create the relationshipWe have already created a metadata model by Model Design Accelerator. As we have to add some new items into this model, here we use Metadata Wizard to add new metadata.a) In IBM Cognos Framework Manager, in the Project Viewer, right-click the gosalesdw namespace under the Physical View, and then click Run Metadata Wizard.b) Select the GOSALESDW data source and expand Tables folder, select the table SLS_SALES_TARG_FACT.c) Select Between each imported query subject and all existing query subjects in the model, then click Import. d) Double-click the gosalesdw namespace, and click the Diagram tab to view the new query subject in the diagram, and to examine any relationships. Notice that no relationships were generated.e) In the Project Viewer, select MONTH_KEY from GO_TIME_DIM, and select MONTH_KEY from SLS_SALES_TARG_FACT. Right-click over the selected items, point to Create, and then click Relationship.f) The relationship is configured as desired. The dimension is on the 1.1 side, and the fact onthe 1.n side. Click OK.g) Using the same process, create a relationship between SLS_PRODUCT_DIM and SLS_SALES_TARG_FACT on PRODUCT_TYPE_KEY. Now that the new sales target object is imported and relationships are created.h) Create Sales Target Fact in Business View. In the Project Viewer, right-click the Business View namespace, point to Create, and then click Query Subject. i) In the Name box, type Sales Target Fact, and click OK.j) Under Available Model Objects, expand Physical View, gosalesdw, SLS_SALES_TARG_FACT. Drag SALES_TARGET to the Query Items and Calculations pane, and click OK.k) Click the new Sales Target Fact query subject in the Business View namespace, Ctrl-click Products and Time (these are related dimensions), right-click one of the selected items, and then click Create Star Schema Grouping.l) In the Create Star Schema Grouping dialog box, type Sales Target in the namespace field and then click OK.m) Drag the Sales Target namespace to the Presentation View.n) Right-click the Presentation View, Create a namespace and named as Sales, and then drag the Sales Fact, Products, Time, and Order Methods shortcuts into the Sales namespace.o) Save the change, now we have finished the import metadata job.4. Specify DeterminantsDeterminants reflect granularity by representing subsets or groups of data in a query subject and are used to ensure correct aggregation of this repeated data. Determinants are most closely related to the concept of keys and indexes in the data source and are imported based on unique key and index information in the data source.Double counting can occur in scenarios where a relationship based on a key that is not unique and repeats in the data is used in a query. Specifying determinants resolves this issue.1) Add Determinants for Timea) In the Project Viewer, in the Physical View namespace, double-click the GO_TIME_DIM query subject, and then click the Determinants tab.b) Right-click pk, click Rename, type Day, and then press Enter. Under the Determinants pane, click Add. Rename New Determinant as Year, and then press Enter. c) With the focus still on Year, from the Available items pane, drag CURRENT_YEAR to the Key pane.d) Select the Group By check box beside Year.e) Repeat the steps to a create Quarter determinant, with Key = QUARTER_KEY and select the Group By check box beside Quarter. Drag CURRENT_QUARTER into the Attributes box.f) Repeat the steps again to create a Month determinant, with Key = MONTH_KEY, and select the Group By check box beside Month. g) Drag the following items into the Attributes box: CURRENT_MONTH MONTH_NUMBER MONTH_ENh) Click the Up Arrow key on the right to sort these items.2) Add Determinants for ProductThis model also includes an SLS_PRODUCT_DIM dimension that has a relationship on PRODUCT_TYPE_KEY, which is also a repeating non-unique key just like the MONTH_KEY is in the GO_TIME_DIM. Try applying determinants for the SLS_PRODUCT_DIM query subject. 5. Add business logic to the modelAgain, typically business logic such as filters and calculations are applied in the Business View layer. However, there are instances where it makes sense to implement items in the Physical View where the cost of performance and maintenance is low. One example of this is the requirement we saw earlier to apply a filter on the SLS_PRODUCT_LOOKUP query subject, which had multiple rows for the same product to support multiple languages. Another example might be the requirement to implement aliases in the Physical View to control query paths between objects. If model query subjects are used to create these aliases or to consolidate multiple underlying query subjects, then applying filters and calculations on these query subjects makes sense because they are not data source query subjects.1) Add filters to the modelFilters come in two forms in IBM Cognos Framework Manager: Embedded filters are created within query subjects and their scope is restricted to that query subject. They are appropriate when the filter is intended for just one query subject or dimension. Embedded filters can be converted to stand-alone filters after they are created. Stand-alone filters are filters available across the model. They are appropriate when required in multiple query subjects or dimensions or to make commonly used filters readily available for authors.In the following steps, we apply an embedded filter to the SLS_PRODUCT_LOOKUP data source query subject to filter on English values.a) In the Project Viewer, in the gosalesdw namespace under the Physical View, double-click SLS_PRODUCT_LOOKUP. Click the Filters tab, click Add, and then in the Name box, type Language Filter.b) In the Available Components pane, double-click PRODUCT_LANGUAGE. Click in the Expression Definition pane at the end of the expression, and then type =EN.2) Filter OptionsNotice the Usage column beside the Name column. Each filter has a Usage setting with the following options: AlwaysThe filter is applied in all instances regardless of whether the filtered query item is in the query. OptionalThe filter is not mandatory and users can choose to enter a filter value or leave it blank. (This option applies only to filters that use a prompt value or macro.) Design Mode OnlyThis option limits the amount of data that is retrieved when testing in IBM Cognos Framework Manager or at report design time (when authoring reports in IBM Cognos Query Studio, IBM Cognos Report Studio, and IBM Cognos Business Insight Advanced).3) Add calculations to the modelYou can create calculations to provide report authors with values that they regularly use. For example, you might want to include a product break even value as one of the measures in the Sales Fact query subject in the form of Quantity *Unit Cost. Calculations can use query items, parameters, and functions.There are two types of calculations: If you want to create a calculation specifically for one query subject or dimension, you can embed the calculation directly in that object. For query subjects, this calculation can be done for either data source query subjects or model query subjects. However, it is recommended that you apply calculations in model query subjects wherever possible. This allows for better maintenance and change management. Create a stand-alone calculation when you want to apply the calculation to more than one query subject or dimension. Stand-alone calculations are also valuable if you need to do aggregation before performing the calculation. This aggregation can be accomplished by changin