.
The method by which a MUN is derived depends on the cube vendor.Before you set up drill-through access, you must understand the key concepts about drilling through. Knowing these concepts will help you to avoid errors so that report consumers drill through as efficiently as possible.
You can create a drill-through path in a source report in Report Studio (Professional authoring mode), or using Drill-through Definitions in IBM Cognos Connection. A drill-through path is the definition of the path that is taken when moving from one report to another, including how the data values are passed between the reports.
Using Drill-through Definitions, you can create a drill-through path from any report in the source package to any target report in any other package in IBM Cognos Connection. This type of drill-through definition is stored in the source package. Users of any report in the package can use the drill-through definition to drill between any combination of Analysis Studio, Query Studio, PowerPlay Studio, or Cognos Viewer reports in any package.
For any target report that contains parameters, you should map the target parameters to the correct metadata in the drill-through path. This ensures that the values from the source report are passed to the correct parameter values, and that the target report is filtered correctly. If you do not map parameters, then the users may be prompted for values when the target report is run.
A report-based drill-through path refers to a path created and stored in a Report Studio source report (Professional authoring mode). This type of drill-through path is also called "authored drill through". The path is associated with a specific data column, chart, or cross tab in the source report, and is available only when users select that area of the report. If an authored drill-through definition is available, a hyperlink appears in the source report when it is run.
Report-based drill-through is limited to Report Studio source reports (Professional authoring mode) and any target reports. Use this type of drill-through access when you want to pass data item values or parameter results from within a source report to the target report, pass the results of a report expression to a target report, or a use URL link as a part of the drill-through definition.
The selection context represents the structure of the values selected by the user in the source. In Analysis Studio, this includes the context area. When a package drill-through definition is used, the selection context is used to give values for mapped parameters (parameterized drill-through) or also to map the appropriate data items and values.
Drill-through links can also be defined to open the target object at a bookmark. The content of this bookmark may also specified by the selection context.
Drill-through access is possible between most combinations of the IBM Cognos 8 studios. Each studio has been optimized for the goals and skills of the audience that uses it, and in some cases for the type of data source it is designed for. Therefore, you may need to consider how the various studios manage the selection context when you drill through between objects created in different studios, and how the data sources are conformed. During testing or debugging, you can see how source values are being mapped in different contexts using the drill-through assistant.
The settings in the drill-through definition determine how users see the report results. For example, the users may see the reports in Cognos Viewer as an HTML Web page, or the reports may open in Query Studio, PowerPlay Studio, or Analysis Studio. If your users have PowerPlay Studio, then they may also see the default view of a PowerCube.
Reports can be opened as HTML pages, or as PDF, XML, CSV, or Excel formats. When you define a drill-through path, you can choose the output format. This can be useful if the expected use of the target report is something other than online viewing. If the report will be printed, output it as PDF; if it will be exported to Excel for further processing, output it as Excel or CSV, and so on.
If you define a drill-through path to a report that is created in Analysis Studio, PowerPlay Studio, or Query Studio, consumers can open the report in its studio instead of in Cognos Viewer. This can be useful if you expect a consumer to use the drill-through target report as the start of an analysis or query session to find more information.
For example, if an application contains a dashboard style report of high-level data, you can define a drill-through link to Analysis Studio to investigate items of interest. The Analysis Studio view can then be drilled through to a PDF report for printing.
Note: Drilling through to open the target in Report Studio is not supported for the Express Authoring mode. The Report Studio Professional authoring mode does not display data results.
You can set up drill-through access between different packages. The two packages can be based on different types of data source, but there are some limits.
The following table shows the data source mappings that support drill-through access.
| Source data source | Target data source |
| OLAP | OLAP Note: OLAP to OLAP drill through is supported only if the data source type is the same, for example, SSAS to SSAS. |
| OLAP | Dimensionally modeled relational |
| OLAP | Relational data Note: For more information, see Business Keys. |
| Dimensionally modeled relational | Dimensionally modeled relational |
| Dimensionally modeled relational | Relational |
| Relational | Relational |
When you drill through, the values that you pass are usually, but not always, used to filter the report. IBM Cognos 8 Business Intelligence supports bookmarks within saved PDF and HTML reports so that a user can scroll a report to view the relevant part based on a URL parameter. For example, you have a large inventory report scheduled to run daily or weekly during off hours because of resource considerations. Your users may want to view this report as a target because it contains detailed information, but you want them to view the saved output rather than run this large report. Using this Action option and bookmark settings, users can drill through from another source location based on products to open the saved report to the page that shows the product they want to focus on.
When a bookmark in the source report is used in a drill-through definition, it provides the value for the URL parameter. When report consumers drill through using this definition, they see the relevant section of the target report.
Bookmark references are limited to previously run reports that are output as PDF or HTML and contain bookmark objects.
Dimensionally modeled data, whether stored in cubes or stored as Dimensionally Modeled Relational (DMR) data, organizes data into dimensions. These dimensions contain hierarchies. The hierarchies contain levels. And the levels contain members.
An example of a dimension is Locations. A Locations dimension may contain two hierarchies: Locations by Organization Structure and Locations by Geography. Either of these hierarchies may contain levels like Country and City.
Members are the instances in a level. For example, New York and
London are members in the City level. A member may have multiple
properties, such as Population, Latitude, and Longitude. Internally,
a member is identified by a Member Unique Name (MUN) .
The method by which a MUN is derived depends on the cube vendor.
Relational data models are made up of data subjects, such as Employees, which are made up of data items, such as Name or Extension. These data items have values, such as Peter Smith.
In IBM Cognos 8, the methods of drilling through available are
Dimensional (member) to Dimensional (member)
Dimensional (member) to Relational (data item value)
Relational (data item value) to Relational (data item value)
If the target parameter is a member, the source must be a member.
The source and target should usually be from a conformed dimension .
However, if the data will support it, you may also choose to define
a mapping using different properties of the source metadata item.
If the target parameter is a value, the source can be either
a value or a member. If the source is a dimensional member, you
must ensure that the level or dimension is mapped to the target data
item correctly in the drill-through definition. The business key
from which the member is sourced should usually match the relational
target value, which is most often the business key . However, if the
data will support it, you may also choose to define a mapping from
the caption of the source metadata item.
The member unique name (MUN) is a unique identifier for a member in IBM Cognos reports. It is stored in the report specification when the member is referenced in the report directly. The MUN is used in drill-through between OLAP data sources. The member keys in the MUN for the different OLAP data sources must match.
The MUN is used to find the member in the data source, which is similar to how business keys are used to find records in a table. For example, when you create OLAP dimension Products, you use the Product Line database column as s label for the members in your Product Line level. However, you use the Product Line Code business key from the database table to ensure that all the Product lines are unique in that level. The source value that you used to create the members is used in combination with the data source name, hierarchy, and level information in the member unique name.
If the MUN changes, members that are directly referenced in expressions, filters, or reports are no longer found. Changes to the MUN may be related to other changes. For example, changes to the hierarchy and level structures may change the level unique name, and changes to the business key values may change the member key path. Other factors that can affect the MUN are application changes during the design stage or over time, IBM Cognos PowerCube category codes that are unpredictably unique, the production environment that has more members than the test environment, or removing the member from the data source.
To avoid potential problems, we recommend the following best practices when you build OLAP data sources:
Use unique codes and keys within a dimension for the member keys.
Define your OLAP and relational packages using unique conformed values for the source values (business keys) within similar dimensions or data values where drill-through between applications may be required.
Ensure that the business keys and dimension metadata structure are the same in the production and test environments.
Do not change the business keys in Framework Manager in the production environment.
Resolve the non-unique keys in a dimension in the data source before you build the cube.
Ensure that there are no duplicate source values in all levels of a dimension before you build a PowerCube. We do not recommend using the tilde character (~) in the category codes.
For more information, see the section about uniqueness in the IBM Cognos Series 7 Step-by-Step Transformer.
For information about PowerCubes migrated from IBM Cognos Series 7, see the IBM Cognos 8 BI PowerPlay Migration and Administration Guide.
If you work with more than one dimensional data source, you may notice that some dimensions are structured the same, and some are not. The reason that dimensions can be structured differently is that the data sources may serve different purposes.
For example, a Customer dimension appears in a Revenue data store, but not in an Inventory data store. However, the Products dimension and the Time dimension appear in both data stores.
Dimensions that appear in multiple data stores are conformed if their structure is identical for all of the following:
hierarchy names
level names
level order
internal keys
Drilling through is possible between different dimensional data stores only if the dimensions are conformed, and if the dimension data store is of the same vendor type, such as IBM Cognos PowerCube as the source and the target. For example, in two data stores for Revenue and Inventory that contain Products and Time dimensions, it is possible to define the Products and Time dimensions differently for each data store. However, for drill-through between the Products and Time dimensions to work, their structures must be identical in each data store.
If you are not sure whether your dimensions are conformed, then you should check with the data modeler to ensure that the drilling through will produce meaningful results.
Ensure that each level contains a business key that has values that match your PowerCube or other DMR models. Also, you must also ensure that the Root Business Key property is set and uses the business key of the first level in the hierarchy. This helps to ensure that you have a conformed member unique name when attempting to drill through using members from this dimension.
When drill-through access is defined from a member to a relational value, the business key of the member is passed by default. This means that your relational target parameter must be set up using the data item with a matching value, which is most often the business key data item. You can also choose to pass the caption of the source metadata item.
For example, employees are usually uniquely identified by an employee number, not by their name, because their name is not necessarily unique. When you drill through from a dimensional member to a relational data item, the value provided is the business key. Therefore, the parameter in the target report must be defined to accept a business key value. The exact logic used to define the business key value supplied depends on the cube vendor. For IBM Cognos PowerCubes, the business key value is the Source property defined for the level in IBM Cognos Transformer. IBM Cognos Series 7 Transformer PowerCubes pass the source value if the drill-through flag was enabled before the cube was built. Otherwise, the category code is used.
In Report Studio (Professional authoring mode), you can determine what the member business key is using an expression such as roleValue('_businessKey',[Camping Equipment]). This expression is case sensitive.
SSAS 2005 multi-part business keys are not supported in drill-through operations.
Tip: When other users run your drill-through report, you may not want them to be prompted for a business key. In Report Studio, you can build a prompt page with a text that is familiar to the users, but filters on the business key. Your Framework Manager modeler can also set the Display Item Reference option for the Prompt Info property to use the business key when the data item is used in a prompt.
Scope is specific to drill-through definitions created using Drill-through Definitions in IBM Cognos Connection (package drill-through definitions). The scope you set defines when the target report is shown to the users, based on the items they have in the source report.
Usually, you define the scope of a drill-through path to match a parameter that it passes. For example, if a target report contains a list of employees, typically you only want to display the report as an available drill-through choice when a user is viewing employee names in a source report. If employee names are not in the source report and the scope was set on the employee name in the drill-through definition, the employee report does not appear on the list of available drill-through target reports in the Go To page. You can set the scope to a measure or to an item in the report.
In report-based drill-through access, where the drill-through path is associated with a specific report column, the column serves as the scope.
Drill-through targets may contain existing parameters, or you may choose to add parameters to the target for greater control over the drill-through link. You usually map all parameters in a drill-through target to items from the source.
When you map source items that are OLAP or DMR members to target parameters, you can select from a set of related member properties to satisfy the requirements of the target parameter. For a dimensional target, a dimensional source item uses the member unique name by default. For a relational target, a dimensional source item uses the business key by default.
For example, you could change the source member property that is used for a mapping to the member caption instead of the business key to match the parameter in a relational target. For a dimensional target, you could define a parameter that accepts a particular property (such as business key or parent unique name), then pass the appropriate source property to satisfy that target.
Note that if you define drill through between non-conformed dimensions, you should test carefully to ensure that the results behave as expected.
If you do not specify parameter mappings, then by default, you will be prompted for any parameters required in the target when you use the drill-through link. To customize this behavior, use the display prompt pages setting.
When the action is set to "Run using dynamic filtering", then additional filtering is applied if names from the context in the source report match names of items in the target. Use this action as well when there are no parameters defined in the target.
If parameters are not mapped correctly, then you may receive an empty report, the wrong results, or an error message.
The source and target cannot contain identical parameter names when they are from different packages, even if the data structure is conformed. If the source and target are from the same package, there is no restriction.
If you have the necessary permissions, you can use the drill-through assistant to look at what source parameters are passed, and what target parameters are mapped for a given drill-through link.
Usually, drilling through from OLAP to relational packages requires that the target report parameter is set using the business key in the relational data. However, this method does not work well for dates. OLAP data sources typically view dates as members, such as Quarter 1 2006, while relational data sources view dates as ranges, such as 1/Jan/2006 to 31/March/2006.
A special feature exists for drilling through between PowerCubes and relational packages. Ensure that the target report parameter is set up using in_range. Note that the parameter must be of type date-time, and not integer.
An example follows:
[gosales_goretailers].[Orders].[Order date] in_range ?Date?
Also ensure that the drill-through definition maps the parameter at the dimension level and that the PowerCube date level is not set to suppress blank categories. Enabling the option to suppress blank categories in the Transformer model before you build the cube may cause the drill-through on dates to be unsuccessful. This happens because there are missing values in the range.
