Data Modules and existing ODS data

[lporter@wsutech.edu]

The organization I just started working for has lookup data stored in Excel spreadsheets outside of our Banner (Oracle) database. (We can't add tables to Oracle for some reason.)

We are running Cognos (11.0.7) and using Framework Manager/ODS.

Is it possible to upload the Excel spreadsheet lookup data into a Data Module in Cognos and join it with an existing ODS table? The keys are there but before I go for a deep dive, I'd like to know if this is even possible.

Thank you,

[bus_pass_man]

Yes, it is possible to add an uploaded file into a module and create relationships between it and query subjects in the module.

You will want to make sure that the usage and aggregate properties of the uploaded file are correct.

[lporter@wsutech.edu]

But everything has to be inside the module? Can you join from existing ODS data?

[bus_pass_man]

I don't understand the question. 

But everything has to be inside the module?

Everything you want to anything which uses the module to be able to use needs to be in the module.

Can you join from existing ODS data?

You need to explain what you mean.  The relationship would be defined in the module and the objects which you want to have related need to be in the module.  Anything you want a consuming application (dashboard or authoring etc.) to be able to use needs to be in the module.

[lporter@wsutech.edu]

If we want to upload Excel file to a data module and add an ODS package to same module, then join those two entities, does that require our Framework Admin person to do something in Framework Manager?

[bus_pass_man]

I think it depends on what you want people to use as relationship keys, the actual keys or attributes.

I ended up exposing the keys in the business layer query subjects so that they could be used in relationships.   

You could probably get by joining attributes ( i.e. not keys but columns which contain actual information, such as product name etc.) but if the data in either the column in the file or the query subject doesn't exactly match you will have a problem.  This has a draw back if you want to use facts, which don't have attribute data, just identifiers and facts, so you might need to require your people to use keys.

The annoyance of using keys is that you might need to add them to your xls files and you would need to make sure that key match the ones in the FM package, but then again you need to do that if you use the attributes.

The key question holds true for both the case where relationships are generated between the FM package and the uploaded files or if you create views in the module from stuff in the FM package and create relationships between them and the files.   I think the latter is better as you can see which objects have relationships and the UI for creating relationships using FM packages is a bit clunky.

The latter requires that you upgrade to 11.1.x, which you might want to start thinking about anyway as they have released 11.1.7 LTS, which I think will supplant the 11.0.13 LTS.

[srmoure]

I need to join to fields from the excel spreadsheet (employee Id and date) to the package. But I can only join one.  I cannot find a way to link the spreadsheet with 2 different tables in the package. Probably I'm missing something, this easily done in reporting with multiple queries, so data modules should be able to handle it.

[bus_pass_man]

You can create many relationships between things in a package and other objects in a module.

One way to do it is to select the package and the other object, click the right mouse button, and choose new relationship.  You then navigate through the tree on each side and pick the keys you want to use.

Another way is to select either the package or the other object, click the right mouse button, and choose new relationship. This would require that you click on the select a table drop downs to choose the tables you want.

In the module properties is the relationships tab.  There is a button to create a new relationship there.  This is a third way.

[srmoure]

Thanks. I was able to use "add new relationship" from the properties menu. Anyway, the result of the JOIN in DATA modules didn't pan out as expected.  I had to create a query from the package in reporting and then join it to a query from the excel. 
Join in the data module was creating tons of duplicated rows event though they were inner joins. I still find multiple issues when I try to use packages in a data module. It could be my lack of knowledge but I'm never sure if it's that or a bug. 
I would like to use data modules but I find them too finicky.  It changes the names of the source columns to C1, C2, etc. which I find extremely annoying.