[Resolved] Single report from multiple cubes

[cognostechie]

Anybody knows if there is a way to make ONE report that can involve items from TWO different Transformer cubes? If so, how would the data get linked?

Thanks

Sorry for posting it in multiple forums, didn't know which one would be the appropriate one for this..

[CognosPaul]

The other Report Studio category is for the ReportNet version. I'm assuming you have Cognos 8, so I deleted the other one.

It is possible to create a report on two or more cubes, transformer or otherwise, by building the queries and unioning them locally.

First you need to get them into a single package. You do this by linking to the cubes in framework manager, and including them both in the same package.

In report studio you'll need to create two queries, one for each cube, in a list format. These means that some of the advanced dimensional functions will be unavailable, or you'll have to find workarounds. Make sure that each query has exactly the same fields in exactly the same place. You'll need to use a join them with a union. The union will be performed on the Cognos server.

The problem with working with two or more Cubes means that, in addition to not being able to efficiently use some of the functions, you'll also need to find work arounds for the prompts. One way that I've found is to use the filter function. So instead of using #prompt('Years','memberuniquename','[YearLevel]','set(','[YearLevel]',')')# you can use filter([YearLevel],caption(currentMember([YearHier])) in (#promptmany('Years','integer')#)). Since the MUNs for both cubes are different you can filter to the correct members by the captions.

[MFGF]

The other option you have (apart from UNION between your two queries) is to set up a Master/Detail link between the two queries within your report.  This would be more appropriate where different data was required from each cube (but ensuring that they have at least one common item).  As Paul said, the difference in the MUNs between the two cubes makes things slightly more complex - here it will rear its head when trying to link the cubes in the Master/Detail relationship.  The trick is to create a calculated item for the linking value from each cube, utilising the Caption() function, then link these caption values.

Regards,

MF.

[cognostechie]

Thanks guys for the replies.

I was wondering if you have alrerady tried this?

Before posting the question, I actually created one package in FM for both the cubes (Two datasources, each pointing to one cube and then created one package to include both the cubes). I did make a report that had two list reports , one from each cube, created a third list report which was a result of joining List 1 and 2. When I run it, it gives an error that it is not supported from OLAP sources. Doing some research on Cognos KB says to mark the Data Sources as 'Relational' instead of 'Multidimensional' in the Data Source property in FM. I did that and the report validated but still failed while running Query 3. It does run Query 1 and 2. The Cognos KB says that this feature was supported in 8.1 but it is now deprecated. No longer supported in 8.3 or 8.4.  I have 8.3 and 8.4 both on my laptop.

I posted the question to get more ideas on how to do this.

Thanks again for the replies. Appreciated.

[nmcdermaid]

It might be worth thinking about why you are trying to link cubes. If there is enough shared information in both of them to try linking, then you may as well create one cube with all of the information in it, and use just one cube for your report.

[MFGF]

I did make a report that had two list reports , one from each cube, created a third list report which was a result of joining List 1 and 2.

Hi,

Joins are not supported between OLAP-based queries, as they use MDX not SQL, so you cannot use query joins in this way.  You need a subtly different approach.

1. Create a list using the relevant items from the first cube.
2. Drag a List object from the toolbox into your existing list, and populate this new inner list from the second cube.
3. Use the "Master/Detail Relationships" option to link the inner and outer lists together (utilising items coded with the Caption() function id the two cubes have different MUNs).

Regards,

MF.

[cognostechie]

Thanks MFGF !! That worked. The sad part is it works for List report but not Crosstab and some of our reports need a Crosstab.

Thanks though ! Appreciated !

[MFGF]

Hi,

As long as you start with an outer list, you can then drag multiple crosstabs into this, and use the list simply to drive the Master-Detail relationships.  Just a thought.

MF.

[cognostechie]

YUP !  That worked.

Thanks MFGF.

Sorry, but I started backwards. Usually people learn RS first and the rest later. I started modelling and DW first and RS later  :)

[MFGF]

YUP !  That worked.

Thanks MFGF.

Sorry, but I started backwards. Usually people learn RS first and the rest later. I started modelling and DW first and RS later  :)

You're welcome!

No apology needed - everyone has to start somewhere.  Although prior knowledge of the reporting tool is always handy when getting to grips with the modelling and DW side, it is not mandatory.  Having existing modelling and database knowledge certainly helps when learning Report Studio too.

Cheers!

MF.

[Cog_rookie]

Could you please inform me the solution for the crosstab please I am out of my wits here.

[MFGF]

Hi,

Start with a list, and populate it with the items you want to use to link your crosstabs together. Then drag your crosstabs inside the list and populate each as required. Finally, right-click each crosstab and use the Master/Detail option to link them up to the list.

Good luck!!

MF.

[Cog_rookie]

Thanks

[aumdy]

I would like to take this thread one step further and ask the following:

I am able to display two cubes in a single report using master-detail relationship. I now need to perform a calculation that takes the total from Cube A minus total from Cube B. Is this possible?

I have tried creating two queries and joining them together in a third query. I then bring in the total from Cube A and total from Cube B and perform A minus B calculation, but I keep getting this error when I run them:

XQE-GEN-0005
Found an internal error: Array index out of range: 5

I did not find any useful articles on IBM support site and so a PMR was opened. I have sent them a sample report xml to see if they can take a look at it but IBM is saying this is not possible, but they do not provide us with an explanation why. I have proved IBM wrong before and I am hoping someone here can help me resolve this issue here.

I am attaching the sample report I created.

Thanks in advance!

Environment:
Reports are built using Cognos 10.2 Fixpack1
Cubes are built using TM1 10.1

[MFGF]

I would like to take this thread one step further and ask the following:

I am able to display two cubes in a single report using master-detail relationship. I now need to perform a calculation that takes the total from Cube A minus total from Cube B. Is this possible?

I have tried creating two queries and joining them together in a third query. I then bring in the total from Cube A and total from Cube B and perform A minus B calculation, but I keep getting this error when I run them:

XQE-GEN-0005
Found an internal error: Array index out of range: 5

I did not find any useful articles on IBM support site and so a PMR was opened. I have sent them a sample report xml to see if they can take a look at it but IBM is saying this is not possible, but they do not provide us with an explanation why. I have proved IBM wrong before and I am hoping someone here can help me resolve this issue here.

I am attaching the sample report I created.

Thanks in advance!

Environment:
Reports are built using Cognos 10.2 Fixpack1
Cubes are built using TM1 10.1

Hi,

You can't join cube queries - see my explanation earlier in the thread. Joining is a relational concept and won't succeed with dimensional sources.

You have two options to get data from multiple cubes into the same report. Either you can union the queries or you can link the queries via master/detail. Master/detail relationships still won't allow you to perform inter-cube calculations though. If you union cube queries then you will end up with separate rows from each cube.

I think the closest you can get is the following:

Bring in a query for each cube using the following strategy:

Query 1:
Data Item - caption([level from the first cube that links to the other cube])
Measure - the measure value from the first cube you want the total of
Dummy calc - with an expression of 0 (name it the same as the measure from the second cube)

Query 2:
Data item - caption([level from the second cube that links to the first])
Dummy calc - with an expression of 0
Measure - the measure value from the second cube you want the total of

Query 3 - a UNION of Queries 1 and 2, containing all three items

List - based on Query 3
Add the caption data item from the query to your list
Add a query calc with the expression maximum([measure item in query 3 from the first cube] for [caption data item])
Add a query calc with the expression maximum([measure item in query 3 from the second cube] for [caption data item])
Add a query calc that subtracts these two

It's horrible! It's not a technique I would implement as it is using relational summaries on the results of dimensional sources. I hate it with a passion almost as keen as my dislike for JavaScript!

If you absolutely must do it, this is the only way I can conjure up.

Taking a figurative step back for a moment, you are using TM1 cubes. TM1 cubes support rules that reference other cubes - but you build these in the cube itself using Architect/Perspectives/Performance Modeler. My advice is to use every excuse under the sun not to link cubes in your reports and use this method instead.

Cheers!

MF.