Leveraging EXternal Data source for 100K csv file

[raj_aries81]

Hello All,

I have an external csv file with 100K records and I would like to integrate it with my existing Package using External Data source capability in Cognos.
Just want to understand, if I override the Maximum personal data file size & Maximum personal data row count Governor setting to something more than 2.5MB and 20000 respectively will I be able to run the report or is 2.5MB and 20K the max acceptable values.


Regards
Raj

[MFGF]

Hello All,

I have an external csv file with 100K records and I would like to integrate it with my existing Package using External Data source capability in Cognos.
Just want to understand, if I override the Maximum personal data file size & Maximum personal data row count Governor setting to something more than 2.5MB and 20000 respectively will I be able to run the report or is 2.5MB and 20K the max acceptable values.


Regards
Raj

Hi,

You need to understand that Cognos is addressing the data in the external file in-place. It isn't uploading it to a database. This means that any summary/grouping/filtering etc of the data in the file has to scan the entire record set of data. Add to that the requirement to join it to your existing data from your package. The joining and any calcs/filters/summaries/groups etc from your package data would also be done locally on the Cognos server. The more data you address from the external file, the more workload you put on the Cognos server, and the slower and more unreliable your report becomes. My frank advice is to stay away from using the External Data feature unless the world is about to end otherwise. Investigate alternatives, such as having an ETL process to move the data to your database. You could then extend your FM model to include this data.

Cheers!

MF.

[Lynn]

External data is not a substitute for faulty data architecture. It can be very useful for prototyping purposes so that detailed requirements can be worked out before expensive database, ETL, and modelling changes are made. It is also a good option for those one-off needs that do not warrant large architectural changes.

Heed the sage advice that the Muppet has offered.

[raj_aries81]

Hi,

You need to understand that Cognos is addressing the data in the external file in-place. It isn't uploading it to a database. This means that any summary/grouping/filtering etc of the data in the file has to scan the entire record set of data. Add to that the requirement to join it to your existing data from your package. The joining and any calcs/filters/summaries/groups etc from your package data would also be done locally on the Cognos server. The more data you address from the external file, the more workload you put on the Cognos server, and the slower and more unreliable your report becomes. My frank advice is to stay away from using the External Data feature unless the world is about to end otherwise. Investigate alternatives, such as having an ETL process to move the data to your database. You could then extend your FM model to include this data.

Cheers!

MF.

Thanks MF & Lynn for taking time and responding. This is a one off request and this being a monthly activity, just looking at shortcuts to get away from ETL effort as it has to go through whole nine yards of planning, estimation and execution etc. Other option I can think of is having an ODBC for the excel/csv file as datasource and then bringing it into the main package. Any suggestions.


Regards
Raj

[MFGF]

Thanks MF & Lynn for taking time and responding. This is a one off request and this being a monthly activity, just looking at shortcuts to get away from ETL effort as it has to go through whole nine yards of planning, estimation and execution etc. Other option I can think of is having an ODBC for the excel/csv file as datasource and then bringing it into the main package. Any suggestions.


Regards
Raj

Do you have the luxury of being able to upgrade to Cognos Analytics (Cognos 11)? That introduces a new data upload facility that copies the data into a columnar data store on the Cognos server. You can then join it to other relational DQM data sources using a Data Module - a simple task even a business user could perform.

Just a thought...

Cheers!

MF.

[raj_aries81]

Do you have the luxury of being able to upgrade to Cognos Analytics (Cognos 11)? That introduces a new data upload facility that copies the data into a columnar data store on the Cognos server. You can then join it to other relational DQM data sources using a Data Module - a simple task even a business user could perform.

Just a thought...

Cheers!

MF.

Hi MF,

I wish I could upgrade to Cognos Analytics but unfortunately we are still on 10.2.1. Yes, Data Module maybe the right use case for this.However, I didnt get a chance get my hands dirty on it yet, but I'm getting my head around on the cloud version of it.

Regards
Raj

[raj_aries81]

Hi MF,

I wish I could upgrade to Cognos Analytics but unfortunately we are still on 10.2.1. Yes, Data Module maybe the right use case for this.However, I didnt get a chance get my hands dirty on it yet, but I'm getting my head around on the cloud version of it.

Regards
Raj

I finally ended-up creating an ODBC for the CSV file, I know there are some trade-offs and performance implications as the file grows but want to use this as a tactical solution.

Regards
Raj