Creating data module from package & excel file

[Nikhil Shenoy]

I am working on Cognos 11.0.6.

I have package for which data source is SAP BW. This package contains a dimension named as "Key".

The description for this Key is maintained in a separate excel file which I need to link this package.

I tried creating a data module by adding the package & the uploaded excel file but unable to link it.

Is there any way out to achieve what is desired?

[robblob]

Unfortunately, I don't think you're going to be able to achieve what you're wanting to do in a module.  Packages that are used in modules only appear to pull in the entire package as a whole and doesn't allow you to drill into query subjects or query items in order to perform the join.

Have you tried adding the package and Excel file as sources in the report, creating queries against each, then joining them there? 

[MFGF]

Unfortunately, I don't think you're going to be able to achieve what you're wanting to do in a module.  Packages that are used in modules only appear to pull in the entire package as a whole and doesn't allow you to drill into query subjects or query items in order to perform the join.

Have you tried adding the package and Excel file as sources in the report, creating queries against each, then joining them there?

You can join to/from query items in a relational package when you bring it into a data module, but I haven't ever tried using a SAP BW package in a data module. I wonder if Cognos is treating it as an OLAP package?

What do you see when you add the package and try to create a join? With a relational package, you need to expand the package to be able to see the query subjects - you pick one of those then you can choose the item(s) to join on. Do you get the same option with your BW package?

MF.

MF.

[robblob]

You can join to/from query items in a relational package when you bring it into a data module, but I haven't ever tried using a SAP BW package in a data module. I wonder if Cognos is treating it as an OLAP package?

MF.

MF,

What am I missing?  I attempted pulling in the relational GO sales (query) from sample models (attached) and it doesn't pull the query items in order to join to another data source. 

[Nikhil Shenoy]

You can join to/from query items in a relational package when you bring it into a data module, but I haven't ever tried using a SAP BW package in a data module. I wonder if Cognos is treating it as an OLAP package?

What do you see when you add the package and try to create a join? With a relational package, you need to expand the package to be able to see the query subjects - you pick one of those then you can choose the item(s) to join on. Do you get the same option with your BW package?

MF.

MF.

When I add that SAP BW package in the data module, there is no drill down available for that package. Whereas for excel I get drill down.

[MFGF]

MF,

What am I missing?  I attempted pulling in the relational GO sales (query) from sample models (attached) and it doesn't pull the query items in order to join to another data source.

This is what I see...



What browser and version are you using? Have you cleared your browser cache?

MF.

[robblob]

What browser and version are you using? Have you cleared your browser cache?

MF.

I should have known...

I see what you see when I attempt to build the module in IE.  Our default browser is Chrome, so this appears to be one of those situations where you have to use IE in order to get the full scale of capabilities when building modules.  Good to know.  Thanks again, MF! 

[MFGF]

I should have known...

I see what you see when I attempt to build the module in IE.  Our default browser is Chrome, so this appears to be one of those situations where you have to use IE in order to get the full scale of capabilities when building modules.  Good to know.  Thanks again, MF!

Weird! It works just fine for me in Chrome!

MF.

[Nikhil Shenoy]

Weird! It works just fine for me in Chrome!

MF.

I used Chrome as well as IE..not working for me in both :(

[MFGF]

I used Chrome as well as IE..not working for me in both :(

As above, I wonder if Cognos is treating your BW package as dimensional rather than relational? You can't use joins with dimensional packages...

As a sanity check, can you join a file to one of the sample relational packages such as GO Sales (query)?

MF.

[Nikhil Shenoy]

As above, I wonder if Cognos is treating your BW package as dimensional rather than relational? You can't use joins with dimensional packages...

As a sanity check, can you join a file to one of the sample relational packages such as GO Sales (query)?

MF.

In case of relational package, it works fine at my end. BW package is dimensional...

[MFGF]

In case of relational package, it works fine at my end. BW package is dimensional...

Ah, ok. It's not a defect, then. You can join relational packages but you can't join dimensional packages of any sort - joining is a relational concept, and influences SQL generation, but dimensional packages don't utilise SQL - they use MDX.

One possible solution is to create a data set from your package and use this in your data module. You can schedule the data set to be refreshed, too, if you like.

Cheers!

MF.

[Nikhil Shenoy]

Ah, ok. It's not a defect, then. You can join relational packages but you can't join dimensional packages of any sort - joining is a relational concept, and influences SQL generation, but dimensional packages don't utilise SQL - they use MDX.

One possible solution is to create a data set from your package and use this in your data module. You can schedule the data set to be refreshed, too, if you like.

Cheers!

MF.

Cool..will try it out and update on this topic.

Thanks guys for you time & help!