Hi,
I have 4 excel sheets to use as data sourse. All the four excel sheets are having flat data. There is no measure and hierarchies. So ,I think creating a cube in transformer is not sensible.
Can you tell me is it possible to use multiple excel sheets as data sourse in FM? . If so can you let me know the procedure? . Anyone having experience on this?
Regards
Quote from: realgowri on 06 Feb 2015 06:59:15 AM
Hi,
I have 4 excel sheets to use as data sourse. All the four excel sheets are having flat data. There is no measure and hierarchies. So ,I think creating a cube in transformer is not sensible.
Can you tell me is it possible to use multiple excel sheets as data sourse in FM? . If so can you let me know the procedure? . Anyone having experience on this?
Regards
It's entirely possible, yes. To do this you will first need to define a Named Region in each Excel sheet to define a name that Cognos 10 can reference (in lieu of a table name in a database). Are the sheets all in the same Excel document or are they in different documents?
You will need to create an ODBC connection to the Excel document as a first step. Make sure you are using the 32-bit ODBC console when you do this.
Once you have your ODBC connection you can add this as a data source in Cognos 10 BI, then use this for metadata import in Framework Manager. The Named Region(s) in the document will appear like tables in a database.
Cheers!
MF.
Hi MF,
Thanks for your reply.
In fact it is 4 different excel files. Sorry for not mentioning clearly in my prev query.
Do I need to create 4 odbc connections(1 for each excel file)?. Is it possible in FM?
Also,is it mandatory to use 32 bit ? ' coz mine is 64 bit?
Regards
Quote from: realgowri on 06 Feb 2015 11:47:29 PM
Hi MF,
Thanks for your reply.
In fact it is 4 different excel files. Sorry for not mentioning clearly in my prev query.
Do I need to create 4 odbc connections(1 for each excel file)?. Is it possible in FM?
Also,is it mandatory to use 32 bit ? ' coz mine is 64 bit?
Regards
Hi,
Yes - in that case you need four ODBC connections - one for each Excel document (since they each have different filenames and an Excel ODBC can point to only one filename). You can import metadata from each in FM, then add relationships to link them (if needed).
You must use the 32-bit ODBC console, yes. ODBC connections in Cognos 10 are served using Compatible Query Mode, which is 32-bit (even in a 64-bit instance of Cognos 10 BI on a 64-bit server). 64-bit windows servers usually have both 32 and 64 bit ODBC administration utilities. Often the one linked to from Control Panel is the 64-bit. You can launch the 32-bit version by navigating to C:\Windows\SysWOW64 and running odbcad32.exe from there. The defined ODBC connections will need to be set up on the System DSN tab.
Cheers!
MF.
Thanks for your Input, MF.