. 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.
The target of drill-through access is always a saved report definition. The report can be created in Report Studio, Query Studio, or Analysis Studio.
You can create a drill-through path in a source report in Report Studio, or using Drill-through Definitions in 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 Cognos Connection. This type of drill-through definition is stored in the source package and can be used when you want to drill between any combination of Analysis Studio, Query Studio, or Cognos Viewer reports in any package.
For any target report that contains parameters, the target parameters must be mapped 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.
A report-based drill-through path refers to a path created and stored in a Report Studio source report. The path is associated with a specific data column, chart, or cross tab, and is available only when users select that area of the report. The drill-through definition, if it exists, appears as a hyperlink in the source report when it is run.
The report-based drill-through is limited to Report Studio source reports and any target reports. This type of drill-through can be used when you want to pass the data item values or parameter results within a source report to the target report, the results of a report expression to a target report, or a URL link as a part of the drill-through definition.
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 or Analysis Studio.
Reports can be output as HTML Web pages, 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 or Query Studio, the report can be run and opened 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.
An example of a this type of drill-through action can be an application where a dashboard style report of high-level data can be drilled through 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 Report Studio is not supported for the Express Authoring Mode. The Report Studio Professional Mode does not display data results.
When you drill through, the values that you pass are usually, but not always, used to filter the report using parameters. Cognos 8 Business Intelligence supports bookmarks within saved PDF reports so that a user can scroll a report to view the relevant part based on a URL parameter. For example, you may define a report with one page per product and add a bookmark of the product number on each page. Report consumers can then select a bookmark to see the product that they want.
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 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 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
and must also be from a conformed dimension .
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 must match the relational target
value, which is most often the business key .
The member unique name (MUN) is a unique identifier for a member in 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, 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 Cognos Series 7 Step-by-Step Transformer.
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 Cognos PowerCube as the source and the target. In the previously mentioned example of the Revenue and Inventory data stores, 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.
When performing a drill-through from a member to a relational value, the business key of the member is passed. 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.
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 Cognos PowerCubes, the business key value is the Source property defined for the level in Cognos Transformer. 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, you can determine what the member business key is using an expression such as roleValue('_businessKey',[Camping Equipment]). This expression is case sensitive.
MSAS 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.
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. Here is an example:
[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.
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, MSAS to MSAS. |
| 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 |
Scope is specific to drill-through definitions created using Drill-through Definitions in Cognos Connection. It 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, for a target report that 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 is suppressed from the list of available drill-through target reports in the Go To page.
In report-based drill-through access, where the drill-through path is associated with a specific report column, the column serves as the scope.
