Hi Team,

Would like to know what are all the limitations or disadvantages when we use csv files as a source for reporting in Cognos. It's a long term solution where we create a odbc connection and import these csv to have a seamless connection. But what would be the disadvantages when we go for this kind of approach in traditional Cognos reporting. Thanks

Regards,
Sesi.

------------------------------
Sesidhar A
------------------------------

#CognosAnalyticswithWatson

Hi Sesidhar

It depends on a lot of things.... creating an ODBC connection to a flat file would mean it has to go through the file every time you ask for something... unless you cache it..... so would work for smaller files, but large files would of course benefit from having a real database behind to handle those queries... Either storing it in a database like Db2 or just uploading the flat file to Cognos Analytics and let the Apache Parquet system handles it with all the advantages coming with that.

Torben

------------------------------
Torben Noer
------------------------------

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Hi @brenda grossnickle,

Uploading files is definitely much better in Cognos 11 than Cognos 10 as you mentioned! Scheduling file uploads is something we hear a lot about and is something a lot of our customers have shown interest in based on the idea here. You can vote on it and add a comment with your use case as well so we can design it accordingly to meet your needs. It is not on our official road map yet but definitely something on our radar. 

In the meantime, we have seen very creative ways of how some customers have accomplished this. Here is an example of how a customer used RPA to do it.

Best Regards,

------------------------------
MOHAMMED OMAR KHAN
Offering Manager
IBM Cognos Analytics
------------------------------

Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

I voted for the refresh of data. thanks. 

I glanced at the RPA deck and honestly, I could not make head nor tails of it. What is the jest? That someone could run a key logger to refresh the data?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Mon April 06, 2020 05:51 PM
From: MOHAMMED OMAR KHAN
Subject: Limitations when flat files as a source to Cognos

Hi @brenda grossnickle,

Uploading files is definitely much better in Cognos 11 than Cognos 10 as you mentioned! Scheduling file uploads is something we hear a lot about and is something a lot of our customers have shown interest in based on the idea here. You can vote on it and add a comment with your use case as well so we can design it accordingly to meet your needs. It is not on our official road map yet but definitely something on our radar. 

In the meantime, we have seen very creative ways of how some customers have accomplished this. Here is an example of how a customer used RPA to do it.

Best Regards,

------------------------------
MOHAMMED OMAR KHAN
Offering Manager
IBM Cognos Analytics
------------------------------


Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

Thanks for the vote! And I am not an Robotic Process Automation (RPA) or key logger expert but saw a customer do it in one of the user groups I attended and thought of sharing it. The jest of it is pretty much that they were able to automate the file upload using RPA tools. So although we don't have it in the product yet, there are ways to achieve the result if needed

------------------------------
MOHAMMED OMAR KHAN
Offering Manager
IBM Cognos Analytics
------------------------------

Original Message:
Sent: Mon April 06, 2020 05:58 PM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I voted for the refresh of data. thanks. 

I glanced at the RPA deck and honestly, I could not make head nor tails of it. What is the jest? That someone could run a key logger to refresh the data?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------


Original Message:
Sent: Mon April 06, 2020 05:51 PM
From: MOHAMMED OMAR KHAN
Subject: Limitations when flat files as a source to Cognos

Hi @brenda grossnickle,

Uploading files is definitely much better in Cognos 11 than Cognos 10 as you mentioned! Scheduling file uploads is something we hear a lot about and is something a lot of our customers have shown interest in based on the idea here. You can vote on it and add a comment with your use case as well so we can design it accordingly to meet your needs. It is not on our official road map yet but definitely something on our radar. 

In the meantime, we have seen very creative ways of how some customers have accomplished this. Here is an example of how a customer used RPA to do it.

Best Regards,

------------------------------
MOHAMMED OMAR KHAN
Offering Manager
IBM Cognos Analytics


Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

RPA is an automation framework / tool that can automate any human actions on a software system. So instead of human updating the file from a specific path into cognos, a robot can be created using an RPA tool like UIPath, Automation Anywhere etc and scheduled to update the file periodically. 

regards

------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi
------------------------------

Original Message:
Sent: Mon April 06, 2020 05:58 PM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I voted for the refresh of data. thanks. 

I glanced at the RPA deck and honestly, I could not make head nor tails of it. What is the jest? That someone could run a key logger to refresh the data?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------


Original Message:
Sent: Mon April 06, 2020 05:51 PM
From: MOHAMMED OMAR KHAN
Subject: Limitations when flat files as a source to Cognos

Hi @brenda grossnickle,

Uploading files is definitely much better in Cognos 11 than Cognos 10 as you mentioned! Scheduling file uploads is something we hear a lot about and is something a lot of our customers have shown interest in based on the idea here. You can vote on it and add a comment with your use case as well so we can design it accordingly to meet your needs. It is not on our official road map yet but definitely something on our radar. 

In the meantime, we have seen very creative ways of how some customers have accomplished this. Here is an example of how a customer used RPA to do it.

Best Regards,

------------------------------
MOHAMMED OMAR KHAN
Offering Manager
IBM Cognos Analytics


Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

Hi brenda grossnickle,

I believe when we create the odbc connection for the CSV file and check the checkbox "automatically server load", the report picks the latest data for each run as everytime it goes through the source file each time as you mentioned. Isn't it?

Regards,
Sesi.

------------------------------
Sesidhar A
------------------------------

Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

@Sesidhar A - you are probably right. My memory on the External Data Source is sketchy because we stopped telling our clients to use it years ago. But maybe it works for many others. So everyone should try it for themselves. We are a cognos reseller and have hundreds of clients. Theyall have their own cognos instance setup at either their own location or at data centers. It is not cookie cutter. So things have to be pretty perfect for an extremely wide group of settings for us to recommend. ​doing a quick google on the "automatically server load" that might have been part of the issue. that the security at some of the locations (especially the data centers) might have been set too tight to allow the use of that feature.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

Original Message:
Sent: Mon April 06, 2020 10:30 PM
From: Sesidhar A
Subject: Limitations when flat files as a source to Cognos


Hi brenda grossnickle,

I believe when we create the odbc connection for the CSV file and check the checkbox "automatically server load", the report picks the latest data for each run as everytime it goes through the source file each time as you mentioned. Isn't it?

Regards,
Sesi.

------------------------------
Sesidhar A
------------------------------


Original Message:
Sent: Mon April 06, 2020 11:44 AM
From: brenda grossnickle
Subject: Limitations when flat files as a source to Cognos

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

------Original Message------

I apologize in advance as I am short on time and do not have time to look at my notes or even to fireup cognos.

In Cognos 10 we tried to use the feature called something like Import External Data? it was really sensitive (flaky) and many of our clients could not get it to work so we stopped telling people to use it. It also did not update the data in cognos as the data in the file was updated. In Cognos 10 we had the best luck setting up a SQL Server agent job that ran each night to import the data using SQL Server Import Wizard to create an SSIS package (a lot of words but simple for a SQL Server person). unfortunately though you have to setup a sql agent job for each file. Then in Cognos they had to know the sql table and column names and write custom SQL queries to use the data. (We use framework manager and deliver packages to our clients but each client has different external files so we could not include it in the FM packages).

with cognos 11 we tried the new Data Modules which seems to be much better than Import External Data. but again the data was stale. our clients wanted the data in cognos to change as the data in the file changed. you have the ability to REFRESH the data module data, but this is a purely manual process. we could not find an automated way to refresh. so we opted to again use a SQL agent job to import the data (this time I wrote a generic sql stored procedure instead of an ssis package to import text data in sql server) into SQL tables. But with the new c11 Data Modules the clients could create a sort of Framework Manager package (data module) that could be selected in a report instead of having to use Customer SQL.

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson