Issues drilling between OLAP reports (conformed dimensions in different cubes)

[cogswell]

I want to set up drill-throughs between two OLAP reports based off two separate cubes. The cubes share two conformed dimensions (Time and Products). Report1 (Cube1) should drill-through to Report2 (Cube2), and vice versa.

I created drill-through definitions in both the cube packages to support this. The 'Source Metadata Item' is the Years hierarchy under the dimension tree ([Cube].[Time].[Years]) for Time and the dimension itself ([Cube].[Products]) for Product. The 'Source metadata item properties' is 'Default'. In the reports, the targets are slicers shown below:

Code Select Expand

set([CubeN].[Products].[Products] -> ?Products?)
[CubeN].[Time].[Years] -> ?Time?

I run Report1, select the prompts ('2009' for Time and 'Total All Products' for Products) and the report displays without issues. Then when I try to drill-through to Report2, on clicking 'View Target Mapping' I see the following:

ParamName	Display Value	Use Value
Products	Total All Products	[Cube1].[Products].[Total All Products].[Total All Products]->:[PC].[@MEMBER].[PRODUCT TYPE]
Time	2009	[Cube1].[Time].[Years].[Year]->:[PC].[@MEMBER].[20090101-20091231]

The drill-through errors out, saying that the query contains a reference to at least one object that does not exist (QE-DEF-0359).

Questions:
1. The target is Cube2, and it should show that in the 'View Target Mapping' section. What am I doing wrong here?
2. How do I enable drill-through for the alternate hierarchies of the Time Dimension? Change my slicer expression in the reports to something like what is shown below?

Code Select Expand

([CubeN].[Time].[Years] -> ?Time?) OR ([CubeN].[Time].[YTD] -> ?Time?)

[cogswell]

Bump... anyone?

[Kanag]

Do u have the reports created in Power play??

Regards,
KR

[CognosPaul]

Communication between two cubes is possible, however you can't rely on Cognos to pass the MUNs correctly. The trick is to use the filter function with attributes.

For example, the sample cubes Sales and Marketing and Great Outdoors Sales both have a time dimension consisting of Years, Quarters and Months. The Category Code (Business Key) for both cubes are identical. So you could use the following expression:

Code Select Expand

filter(
   [great_outdoors_sales_en].[Years].[Years].[Year]
   , [great_outdoors_sales_en].[Years].[Years].[Year].[Year - Category Code] in (#promptmany('Years','string')#)
)
to return a set of values where the category code of the year level is in the Years parameter list.

Create a new report based on the Sales and Marketing cube. Create a prompt page with a multiselect value prompt, Years - Category Code for the use and the Years level for the display. Now in the report page, create a list with the expression:

Code Select Expand

filter([sales_and_marketing].[Time].[Time].[Year],[sales_and_marketing].[Time].[Time].[Year].[Year - Category Code] in (#promptmany('Years','string')#))

When you run the report you'll be prompted to choose several years, and the years will be returned in the list.

Next do exactly the same thing with the Great Outdoors Sales cube.  When you're done, click on the years in the list and set up a drill through to the Sales and Marketing report you made previously. Pass the data item value, Value: Years, Property to Pass: Business Key. Create a text item below the list with a drillthrough that passes the Parameter Value.

When you click on an individual row in the list it will pass the business key of that year. When you click on the text item it will pass all of the years you've selected.

Let me know if you're running into difficulties and I'll upload the xml for both reports.

[cogswell]

Thanks for the reply. If it matters at all, note that I am using drill-through definitions along with the 'Related Links' feature to drill through between cubes. There are no direct drill-through links on the crosstab itself. Once I figure this out, we will put more drill-through definitions on the cube package for our other cubes.

The Time dimension contains alternate hierarchies, and it is not used on the source or drill-target crosstab displays. However, both the source and target crosstab queries are filtered on Time using slicers. A multi-select tree prompt on both the reports supplies values for the Time dimension.

Questions:

• Can I still pass the Time dimension to the drill-through target crosstab report, if it is not used on either the source crosstab report or the drill-through target crosstab?
• How do I write my slicer expression(s) to accommodate alternate hierarchies? Will I need to create separate parameters and slicers for them? Or can I use a single slicer/filter expression instead?
• Your filter expression example only allows filtering on the Years level, yes? Our Time dimension goes down to the month level. How do I pass Feb 2010 when it is chosen on the prompt on the source report? Note the part in green above! :)

Unfortunately, we don't have the Cognos Samples installed. Thanks again PaulM!

[CognosPaul]

Unfortunately I'm not familiar enough with alternate hierarchies to give you a fully meaningful answer. That being said however, as long as the value being passed between reports are the business keys or captions, and as long as both cubes share the same keys or captions it should not matter which hierarchy you are drilling from, or drilling to (assuming alternate time hierarchies).

The example I gave before is for only the years level in the time dimension. You can easily adapt that to search the entire hierarchy:

Code Select Expand

filter([sales_and_marketing].[Time].[Time],roleValue('_businessKey',currentMember([sales_and_marketing].[Time].[Time])) in (#prompt('Time','string')#))

[cogswell]

Thanks for the reply, PaulM. I still have a question unanswered:

Using drill-through definitions (package-based drill-through), can I pass the Time dimension to the drill-through target crosstab report, if it is not used on either the source crosstab report or the drill-through target crosstab? As I mentioned before, the source crosstab is filtered on Time. I want to filter the target crosstab on Time too, but Time is not present on either axes of the target crosstab (just like it is not present on any axis of the source crosstab).

[CognosPaul]

Drill through definitions allow you to pass the attributes from specific levels and properties of the selected member. No screenshots - client is blocking tinypic and other similar sites.

The target report must have an expression at least similar to this:

filter([Cube].[Time].[Time],caption(currentMember([Cube].[Time].[Time]))=#prompt('TimeCaption','string',sq('Time'))#)

You can put it into the slicer if you don't want it appearing in the crosstab. 'Time' is the caption for the root member in my cube.

In the drillthrough definitions select a target. There will be a table with you can select which each row showing the available parameters in the target report. Click on "map to metadata" to select the hierarchy or level you want to drill from. You can also expand the individual levels and see the properties.

Once you've selected the item (I chose the hierarchy), you can change the "Source metadata item properties". Since the target expression is searching the entire hierarchy for a caption, you can select Member Caption.