A powerful tool to build the IBM Cognos data Manager Database
This article describes in detail the use of Data Manager, the latest version of Cognos 10.2, for database managers, and for those who want the system to understand Cognos Data Manager, and the IBM data mart build method, and who are knowledgeable about the latest version of Cognos security. Practical and instructive significance.
Liao Xian, Senior Technical Consultant/Business Analysis architect, IBM Business Intelligence and performance management, IBM
Liao Xian, currently at IBM (China) Limited, is responsible for the west Side enterprise business analysis and performance management consulting, worked at the HP Global Application development and Support Center, Cisco Global Market Intelligence Center, ANZ Global Operations center. With years of experience in business intelligence and Data Warehouse architecture and analytics projects, we are now focused on enterprise-class business intelligence and data warehousing research and consulting, IBM Business Analytics and optimization solutions experts.
November 20, 2014
Develop and deploy your next application on the IBM Bluemix cloud platform.
Start your free trial now
As an ETL tool for Cognos itself, IBM Cognos data Manager is a proven extract,transform,load, transform, and load tool that extracts and processes data from a variety of heterogeneous data sources, including filtering, morphing, Star mode transformation, snowflake mode transformation, data Warehouse structure deformation, and so on, and then export the processed results to different databases, data warehouses, data marts, or data files.
The primary purpose of IBM Cognos Data Manager is to help users create data warehouses and data marts for reporting, analysis, and performance management, with the main tasks of Data Manager:
As an important component of the IBM Cognos Enterprise platform, IBM Cognos Data Manager V10.2 represents an integral part of the next generation of innovation for the IBM Cognos 10 family. IBM Cognos Data Manager V10.2 can be used to transfer data to a single database business table and to a more complex conversion of fact tables and dimension tables. and IBM Cognos Data Manager V10.2 relies on interactive metadata for IBM Cognos Framework Manager to integrate with other IBM Cognos Business Intelligence products, which will allow the target data warehouse and data store to leverage the data directly Model, making it easy for IBM Cognos business intelligence applications and performance management projects.
In addition, in addition to its own ETL capabilities, IBM Cognos Data Manager V10.2 joined the 64-bit database management engine, allowing users to participate in additional 64-bit computing environments available. This greatly increases the amount of data that can be loaded and reduces the performance requirements of memory management technology.About installation
The installation of IBM Cognos Data Manager is simple and requires only a few clicks, but there are several items that require special attention, language, path, and installation components. Run IBM Cognos Data Manager on the Window platform and select the desired language.
Confirm the path you need to install, and if you have previously installed Cognos Server, please overwrite the installation under the same Cognos path to ensure service consistency.
Select the Data Manager component that you want to install, including:
Wait for the installation to complete.
Back to top of pageCreate a data source name
First, you need to set a data source name (NAME-DSN), you can use the existing data connection methods, such as ODBC method to set up, open the Cognos data Manager program, in the "Tools" bar Select "ODBC Administrator ".
Then select Create a System DSN, add Systems Data Sources, and select Microsoft Excel Driver (*.xls) driver to connect to the sample database for access in the Select ... Xiangli Select all the data files in the MSAccess and DB folder under the C10_location\webcontent\samples\datamanager\ path, establish an ODBC connection, and then close the ODBC data source management interface.Figure 1. Select the database file path
|DSN Naming Recommendations||Access Database file name||file path|
Back to top of pageCreate a Data Manager catalog
The IBM Cognos Data Manager Catalog database provides a repository of information that defines data extraction, transformation, and import, which can be used as a conversion logic for storing dedicated databases or for other purposes such as Target data mart design.
This directory consists of a series of data tables that have access to the database and can create a directory for each database, which can be used to illustrate:
You can browse the existing data management directory, select Open Directory, and then select the DSN name of the connection data source and click Confirm.Figure 3. Data Manager opens the source of the corresponding directory
Then in the open window you can see the tree Design window on the left, which contains the contents (catalog), build (Builds), Workflow (jobstreams), metadata (Metadata), dictionary (Library) and other elements, this tree-like window is IBM Cognos Data Manager Designer's design window, at the level, contains the build-to-workflow (Builds and Jobstreams) folder, the metadata (Metadata) folder, and the dictionary (Library) folder.Figure 4. Tree-like browsing structure of directory in Data Manager
At the bottom of the window, there are 6 tabs with different view of the catalog elements, namely:
|Catalogue Catalog||Display the overall directory structure catalog|
|Facts Build Fact Builds||Show only fact table data contained in the catalog fact Builds|
|Dimension Build Dimension Builds||Only the dimension table data contained in the catalog is displayed Dimension Builds|
|Workflow Jobstreams||Show only the workflows contained in the catalog Jobstreams|
|Dictionary Library||Only dimension Dimensions, Connection Connections, and feature Functions that are included in the catalog are displayed|
|Meta Data Metadata||Only metadata dimensions Dimensions and collections that are included in the catalog are displayed collections|
We can design and build on the basis of the project ETL design and the free conversion element view.
Click on any build or workflow, you can see the detailed design in the right window, in the example, we can click "Ds_tutorial" under the "Bimart" fact build, the fact that the overall construction will be presented, you can double-click on any component icon to browse and property settings.Figure 5. Bimart Map of fact building
Click on the Mappings tab above this window to see the mapping between the source table columns (DataSource), the Data Flow items (DataStream), and the elements of the transformation Model (transformation model) built by the fact table (Mapping).Figure 6. Mapping relationships between source, data flow, and transformation models
Click on the Conversion Model tab to see the mapping between the fact table and the dimension table with the hierarchy design, in the dimension hierarchy design, the tick mark on the left means that the transformation is required, and the tick mark on the right means that the output is delivered.Figure 7. Conversion model
By clicking the Fact Delivery tab, you can see the mapping between the transformation model elements and the columns that deliver the fact tables.Figure 8. Mapping relationship between the transformation model and the Data mart
Of course, we can also create a new data Management directory (catalog), click on the new icon, the new directory created in the pop-up window named "MyCatalog" and then click "Next", in the New Data Connection window to the left of the "ODBC", and then under the data source name, select "Ds_ New "ODBC connection as the latest data source connection, finally click" Done ", a new, blank data management directory is built.
Back to top of pageEstablishing a data connection
As a standard ETL tool, it is very important to establish a data source connection, you need to establish a database connection is not only the data source, also contains the target data connection. This connection needs to provide all the information connected to the database, identifies the specific database type and the method of connection established, and provides the information required by the database management system, such as user name and password.
IBM Cognos Data Manager supports multiple vendor database management systems, including: Ibm,oracle,microsoft,informix,sybase and Teradata, and can connect to other databases using local drive and ODBC methods. Data Manager also supports the use of the IBM Cognos Data Manager Connector for SAP R/3 product add-on as a data source for the SAP R/3 system.
In addition, data Manager can use the Sqltxt driver to connect to a text file (ASCII or EBCDIC) source, which can support any application that can have text data everywhere.
Next, let's simply connect an ODBC database, open the "MyCatalog" Data management directory you just created, right-click on the dictionary icon, and select Insert Connection (insert Connection), and fill in the alias of the popup window with "Go_ Sales ", then click on the" Connection Details "tab on the window and select ODBC on the left connection type, select" Gosales "in the data source name on the right and click on the" Test Connection Connection) ", and then click OK to confirm that an ODBC database connection is built, follow this step and add go_vendors (with Govendors as the data source) and Ds_marts (Ds_marts as the data source) two data connections.Figure 9. Select a data connection
Of course, we can also try to establish additional data connections of the following types:
Here, we can try to establish a sqltxt type of connection for later use, right-click on the Library's Connections folder, and then select Insert a connection, then pop up the dialog box, Alias in the "Ds_sources", in the Connection In Details, select Sqltxt, and then, in the section of the file definition, find the corresponding def file: C:\Program files\ibm\cognos\c10_64\webcontent\samples\datamanager\sqltxt\ Ds_sources.def, test, success, complete a sqltxt type of data connection.
Back to top of pageCreate a dimension framework
The dimension framework defines the hierarchy of core business elements, which demonstrates a way for organizations to rethink the construction of business data, and each grouped dimension contains information that is associated with a particular business scenario. A dimension provides the context for analysis, such as products, customers, sales employees, and so on, while IBM Cognos Manager supports unlimited dimension references, so it is possible to perform an infinite connectivity dimension table analysis.
Reference dimensions contain reference structures (hierarchies, hierarchy of automatic hierarchies, and table lookups), and one or more templates.
The hierarchy presents a specific view of the business dimension. It contains the definition of the relevant reference data at a fixed order of magnitude. Each level contains a set of member data at that level of magnitude. In the hierarchy, the member data is divided into the parent class level, as well as the subclass level, has the upper and lower distinction.
The hierarchy of automatic hierarchies does not have a fixed number of hierarchy concepts, it is purely a parent-child relationship structure. For example: The employee reports to the manager, and the manager reports to the other manager relationships.
A lookup is a single hierarchy that is used primarily for data validation.
Templates define attributes for dimension tables, as well as behavior, and a template requires proper maintenance and uses the appropriate reference dimension tables.
The business dimension is the core business component of the analytics report, such as the sample data "The Great Outdoors Company", which relies on the sale of products for profit, a product dimension allows managers to rely on the product, product type and product line level to analyze the company's performance. Sales of products may be a seasonal trend, understanding this trend can help management to better plan the production of each product, so as to achieve market success, if the need to identify the trend in the data mart must include the Time dimension. In addition, the sales staff dimension allows managers to analyze the different sales regions, countries, sales offices, and individual sales personnel performance in the product sales and profit generation situation. Finally, great Outdoors retailer is the first-level customer of great Outdoors, and the retailer dimension can respond well to management "How are our different retailers performing?" ”。
In summary, we can use the IBM Cognos Data Manager design to build the following dimensions:
Next, let's look at how to set up a dimension in IBM Cognos DataManager, open the MyCatalog Data Management directory We created earlier, select the dimension (Dimensions) in the dictionary, right-click Insert the reference dimension (insert Reference Dimension), enter "Product" in the Name column of the dimension attribute box that pops up, click OK, and follow the same steps to create the Time,salesstaff,retailer three new dimensions.Figure 10. Inserting reference dimensions
We can then build the hierarchical design of the dimension, taking the retailer dimension as an example, to create the retailer hierarchy from the related tables in the Go_vendors database, and the hierarchy to follow a one-to-many relationship between the tables. A retailer type (Retailertype) can have multiple retailers (retailer), but a retailer can only have one retail type, similar, each retailer may have multiple sites (site), but a site can only correspond to one retailer, These table relationships present a hierarchical design style for snowflakes.Figure 11. Hierarchical description of building dimensions
Select the Hierarchy Wizard from the toolbar to see three options for creating a new level:
Here we select "Create a hierarchy (snowflake model) from multiple tables", then click Next, fill in the "Input hierarchy name" retailer, then select Retailer as the dimension in the following window:Figure 12. Select hierarchy Information
Then click Next to accept all the level names created by default, then on the next page, click on "Allretailer" and select Add new level on the right, fill in the name in the new window, select the data source database, and define the available data column types as follows:Figure 13. Fill in the hierarchy information in detail
Then create a new retailer.Figure 14. Add hierarchy Retailer
Finally build a Site as the level of the first layer.Figure 15. Add Hierarchy Site
Select Done, click Save, and then you can see the hierarchy of the retailer you created.Figure 16. Implementing Hierarchical mapping relationships
When you build the good one dimension, you can try to browse or test each level of the hierarchy you created. Find the retailer level in the Dimensions folder, then right-click Retailer, in the Pop-up dialog box, select Explore, and then click OK in the Reference Explorer dialog box that pops up, sometimes there will be a popup about no primary key ID Warning, you can accept it, and then you can see all the layered elements under a retailer, which you can open to check.Figure 17. Check hierarchy elements
Of course, in the ETL tool as a data integration, naturally also has the ability to create a date level, you can create a date in the sample database Ds_sources Financial Date table, in this table each row of data identifies the relevant day, week, month, quarter and year, showing a star schema. The process of creating is also to find the Hierarchy Wizard under Tools, and then there are three options: Create a Star hierarchy, create a hierarchy of single-table rows, and create a snowflake hierarchy.
Select structure: Select Create the Hierarchy form the columns of one table (Star Schema), next.
Define the hierarchy: Enter "Dates" in the "enter the name of the Hierarchy" dialog box and select "Tim" in the "Select the reference dimension to use for this hierarchy" dialog E ", next.
Determine the hierarchy Source: Under Locate The source table for this hierarchy, select the Fiscal table under the Ds_sourcess data source that you created earlier, next.
Hierarchy: Under the alldates level of the default build, add Year,quarter,month,week,day, select the corresponding ID as "source column for Id", select the corresponding Caption as "source Colu MN for caption ", complete.
Finally save this directory and you will see this newly-designed date-level chart:Figure 18. Date Hierarchy
Back to top of pageCreate a Data mart
Building a data mart is at the heart of the fact table, and we can easily build a fact table based on the IBM Cognos BI Mart (Star) type, deliver the data to the Data mart, view the data in the Data mart, and use the execution log. The IBM Cognos data Manger Fact Builder explicitly specifies how to extract, transform, and pass data, which can be derived from multiple data sources, merged and purged, aggregated in fact tables, and finally combined with dimension data to form one or more data marts.
So, unlike the dimension table, we need to be clear about the IBM Cognos Data Manager
The transformation model in the construction of the facts (transformation models), including:
Property elements are not dimensions or metric information, and property information is not aggregated as measured information.
A derived element is a value that is computed from the source data, and then by using the defined expression, after data Manager.
A dimension element contains the context in which the measured data is applied, for example, a measure of the quantity of a product that needs to know the dimension of the product number.
A derived dimension element allows the lookup of other dimension tables, and this lookup value can be obtained from a previously derived lookup or calculation.
A measurement element is a value that can be calculated and analyzed, such as cost, revenue, and so on.
When Data Manager aggregates, it consolidates metrics and derivations in the direction of the dimension. So next, we're trying to create a fact build. We open a directory ds_lessons and then find the "Fact build Wizard" In the Tools menu, and in the popup dialog, "Enter the name of the" the "Build" and enter "Bimart". Then in "Select the type of fact build to create" select "IBM Cognos BI Mart (Star)" Star fact table build, and finally in the "Select the connection into which the Build is to deliver data "select Ds_marts" and select "Perform a full refresh on the target data".Figure 19. Build Facts
Then we create a data stream, define a data source, click "Data Source", select "Add", select "Go_sales" as the data source for this stream, and then choose Order_Details and Order_header Some of the columns in the table are the source, and you can see the corresponding SQL on the right, then click Done and Next.Figure 20. Select the corresponding table field for the data source
In this window, you can adjust the dimensions and the location of the measurements, as well as type changes, next.Figure 21. Dimensional adjustment of metrics
In "Use Reference" select the corresponding dimension level, such as product_number corresponding product,retailer_site_code corresponding to retailer,sales_staff_code corresponding staff. Then click Next, accept the default fact data and the naming rules for dimensional data delivery, click Finish, and build a fact build.Figure 22. Forming a fact map
Back to top of pageCreate a derivation
The so-called derivation is the value computed by the IBM data Manager using an expression, rather than the value obtained directly from the data source, you can build the derivation in the following application: Data Flow (datastreams), data source and the conversion model (transformation models). For the added derivation, you can use the expression in the tree structure on the left to build the derivation logic to the right, thereby achieving the effect of the data conversion.Figure 23. Derived logical editing interface
For example, creating a derivation in the transformation Model (transformation models) is called the Transformation Model derivation (transformation models derivation). Open the Ds_lessons directory, right-Additional the conversion model under the fact-building, select "Insert derivation", in the popup dialog, name "Gross_margin", then select the Calculation bar and select Funcations->user Defined Find the expression of the margin, and double-click, you can see the sample "margin (price, cost)" and then on the left, selected under Elements unit_sale_price alternative pric E,unit_cost replacement cost, modified to "Margin (Unit_sale_price, Unit_cost)", click OK, a gross_margin derivation is built.
Alternatively, we can build a derivation in the data stream called the data genre (DataStream derivations). Under the Additional fact construction right-click on the DataStream, clicking on the properties, in the DataStream Properties dialog box Select derivations Add, named "Sales_total", in the calculation column found under the DataStream Items "QUANTITY" and "Unit_sale_price", and on the right to form the expression "Quantity*unit_sale_price", click Finish, and then in the Mapping window, you can also see the new addition of a DataStream derivation.Figure 24. Mapping of data sources, data flows, and transformation models
When the derivation is done, you can also use DataStream to execute, select DataStream, and click the Execute Execute button under Actions.Figure 25. Performing mappings
Finally, you can also map DataStream to the transformation model, right-Additional the transformation model under the fact-building, select Mapping (map), and in the open window select "Create New Elements as" under Auto map Measure "creates a new element as a measure, and Data Manager automatically selects the new transformation model element.Figure 26. Auto Map
Then click OK, and then you can see that the DataStream derivation is now mapped to the transformation model.Figure 27. Automatic Mapping Confirmation
Finally, you need to deliver the data from the Additional facts to the Data mart, right-Additional The fact-building icon, choose Execute execution, keep the default configuration, click OK, and then close the popup command window until the execution is complete.
Back to top of pageCreating dimension builds and workflows
Dimension builds can provide dimension data to many facts constructs, and fact constructs can also be used to pass dimension data, but this can lead to duplication of effort by delivering the same data for more fact building. Dimension builds generate unique dimension data, and this dimension is built primarily to provide dimension reference references that are appropriate for the data mart analysis.
Open the Ds_lessons directory, select "Dimension Build Wizard" in the toolbar, name "retailer", select Star schema,dimension to be delivered in the Schema type Retailer,deliver into connection Select Ds_marts, next all default options until ADD surrogate Keys to the Dimension Tables tick to finish.Figure 28. Open Dimension Build
Finally, right-click the dimension retailer that you just built, choose execute execution, and deliver this dimension build.
In addition, Jobstreams is used to assemble the various components under IBM Cognos Data Manager for orderly logical processing. A jobstreams can contain multiple nodes, which can be a step in a process. You can set Jobstreams to several different applications, such as a confirmed Data mart, a status notification, a preprocessing area building for dimension data, data cleansing, index maintenance, coping with source data for different attainment rates, partitioning tasks, and custom program logging. To support different applications, the Jobstream contains the following nodes:
Let's create a jobstreams, open the Ds_lessons directory, right-click the "Builds and Jobstreams" folder, select "Insert jobstream" in the popup dialog named "Example", click Add in the Parameters bar, Then name the parameter "statusdates" and type select BOOLEAN, then follow the same method to establish Statusproduct,statusstaff,statusretailer, and finally click OK.
Then locate and click on the icon for the Insert Dimension build Node in the Example jobstream design box, and find Dates in the associated build, then put the Result v in the Details column Replace the value in ariable with Statusdates, click OK, repeat this step and build the Product,staff,retailer three dimension node.
Then in the toolbar, select Insert Condition node icon, and then in the new Popup dialog, name the "CheckStatus" condition node, and then in the Action bar, enter the following logic and click OK:
IF ($StatusDates and
Getting Started 75
Chapter 10:creating a Jobstream
Then insert a fact-building node, click on the Insert Fact-node icon, locate Conformedmart in associated build, and click OK.
Then insert a program node, click the Insert Procedure node icon, the breakpoint handler named Abort, enter the following in the Action bar, then click OK.
Logmsg (' Node checkstatus failed. Conformedmart build aborted. ');
Then use the Insert link icon to connect to each node and save.Figure 29. Building a work execution flow
Finally, right-Example the Jobstream, choose Execute execution, complete the ETL workflow execution.
To summarize, IBM Cognos data Manager is a choice component of IBM Cognos itself, dedicated to building data marts, so the layers, dimensions, facts, derivations, and workflows involved are based on data mart design as an industry-leading data mart ETL tool. Cognos IBM Cognos Data Manager has the following features and benefits for your reference:
Please login or register to post a comment.
IBM Cognos 10.2 Latest Experience Tour