Schedule file upload

[Reinhard]

Hi,

you can upload external files and then use them in data modules and so on.

How to schedule the upload?

Regards,
Reinhard

[MFGF]

Hi,

you can upload external files and then use them in data modules and so on.

How to schedule the upload?

Regards,
Reinhard

Hi,

Uploading files is an ad-hoc process and it isn't something that can be scheduled. The upload functionality is there to provide agility for data discovery - you can access your data right now rather than having to wait for it to be loaded into your data warehouse. However, it's not a governed, reliable process - how well can you really trust data from undisclosed sources that users upload? The appropriate robust, scalable, reliable, governed process is to use ETL processes to clean, merge and load the data into your data warehouse for production use - that way you know the data is accurate, reliable and trustworthy.

Cheers!

MF.

[Reinhard]

No need to argue with me:-) I totally concur but in every training people ask if this is possible....
...and somehow I wonder why it is not possible.

Thanks,
Reinhard

[therese1]

we have used Python script to auto upload from google share folder to cognos... we are looking for IBM or google solution but Python for now is fully scalable, sitting it in the same folder as the files.

[MFGF]

Putting my data-bigot hat on for a moment...

A scheduled upload implies this isn't an ad-hoc data discovery requirement for data, but an ongoing business process. Honestly, file uploads are not the appropriate solution here. The data is ungoverned, potentially unclean, and therefore not something to be relied upon in day-to-day business reporting. You end up getting yourself into a situation where there are siloes of dirty data that don't correspond and introduce risk to the business. What organization wants to base critical operational decisions on data you don't know you can trust and can't be correlated?

If you are thinking about scheduling data uploads into CA, you have made the wrong architectural decision somewhere further back along the line. If I was auditing an organization, this would raise a big red flag for me. Your data should be checked, cleaned, deduplicated and governed, and file uploads don't offer any of this. They are perfect for ad-hoc data discovery - when you want to test assumptions or you need an answer right now regardless of whether you can really trust it. It's not a production-robust solution, though. If you get told an organization must have this facility, push back and find out why. Then suggest a more sensible approach instead.

Ok. Data-bigot hat is now off again. Normal muppet behaviour resumed :)

Cheers!

MF.

[Reinhard]

Nana, no bigotry here. You are right 100%, MFGF. I know since thats what I keep telling my customers and then they tell me that their IT and database admins are not doing or allowing proper ETL loads for their excel files....

You see the ouboros....

[ZenCog]

I understand both statements:
(A) Need for robust and governed data sources.
(B) Sometimes some data is readily available in CSV/XLS files, so customers would like to use those directly without additional ETL/DB administrative work.

However, no matter how much we agree with case A then customers keep asking over and over again why cannot I schedule the refresh of Excel data uploads in Cognos Analytics. They could have a perfectly valid scenario where this would work just fine. Maybe those Excel files are generated by an automated factory system where we don't have direct database access at all (data in proprietary binary system), so no manual work creating and maintaining these CSV or XLS files?

So, the question still remains. How people have automated and scheduled re-uploading an XLS/CSV datasets in CA?

Therese1: You mentioned a python script. What does this python script does? Does it send a "recorded web session" to simulate what people would normally do manually in CA web portal?

Or is there Cognos SDK application or SDK API interface which could refresh an existing uploaded XLS/CSV dataset? SDK API can do a lot, but I still haven't found an API to do programmatic file uploads.

Or are people using some "web session recording" tools to replay those manual Cognos Analytics steps to refresh an uploaded file?

Please share you solutions even when IBM haven't got around to provide an out-of-the-box solution. I'm leaning over to those "web session recorder/macro player" tools for now.

[erikc]

We are looking for a functionality like this too.

Did you already voted for this idea?
https://ibmanalytics.ideas.aha.io/ideas/CAOP-I-1209

[ZenCog]

Yes, I have voted that feature suggestion. It seems to be one of the most voted topics there at the moment, so it just underlines the fact how much customers are waiting for that out-of-the-box solution (schedule file uploads based on scheduled time or Cognos scheduler trigger).

Meanwhile I think we just have to live with semi-working "web recorder/macro player" type of solution to simulate what people normally do manually via a browser. If anyone know a SDK API or if IBM has created a SDK application to do this file uploading programmatically then please share you tips.

[dougp]

ZenCog:  To schedule the upload, the service account running Cognos would need access to the file.  Unless the Cognos Administrator is involved to educate the user where to save the file, Cognos has no way to find it.

Here's what I implemented in MS Windows (with the help of my server support folks) to enable automatic connection to an external file using Manage External Data in a package.  The scenario in this thread sounds like it could be handled following the same approach.

• Create a folder on the Cognos server.
• Set the permissions such that a user can read anything from the folder (and share).
• Set the permissions such that a user can create a file, then edit that file.
  
     
  • Grant all users the ability to create files/write data.
  • Grant CREATOR OWNER full control.
• Grant the service account full control on the folder.

The result is that a user can create and edit a file in that folder.  Others can read it but not modify it.  Cognos has access to it.
If you have secret squirrel data, you may want numerous folders with different permissions.  That way those battlin' business units can't see each others' stuff.

Note:  This may not be 100% reliable.  I have heard about some problems when using CREATOR OWNER.  I don't understand the issues.  That's for my server support people to deal with.

Now you're just missing the part where you can create the scheduled refresh in Cognos.


                                 There may be a way to do this using the Cognos SDK.


erikc:  You do realize that aha.io is not IBM's site, right?  The suggestion should be added to https://www.ibm.com/developerworks/rfe.  Unfortunately, Cognos Analytics is no longer in the product list on the site.

MFGF:  I agree in concept, but all I can do is recommend processes that leads to a single version of the truth.  At the end of the day, I'm just the IT guy.  I provide solutions and features that may enable the users to shoot themselves in the foot.

[MFGF]

erikc:  You do realize that aha.io is not IBM's site, right?  The suggestion should be added to https://www.ibm.com/developerworks/rfe.  Unfortunately, Cognos Analytics is no longer in the product list on the site.

It looks to me like IBM have migrated all their Analytics content from the old RFE site to this new one - https://ibmanalytics.ideas.aha.io
I guess they are just trying to keep us on our toes :)

MFGF:  I agree in concept, but all I can do is recommend processes that leads to a single version of the truth.  At the end of the day, I'm just the IT guy.  I provide solutions and features that may enable the users to shoot themselves in the foot.

I fully understand. I just want less experienced Cognos people than you to have a better understanding of the bigger picture and pause to think about their architectural decisions, rather than jumping in with both feet and adopting an "implement it whatever it takes" approach without thinking through the consequences. Sometimes we have no choice, but we can at least record our reasons for suggesting other approaches, even if the business decides on a "quick and dirty" solution :)

Cheers!

MF.

[dougp]

I verified with IBM Support that the RFE site specifically for Cognos Analytics was outsources to Aha!.  Oddly, they didn't tell me that the last time they told me to create a new RFE.

[KTownJerry]

We are looking for a functionality like this too.

Did you already voted for this idea?
https://ibmanalytics.ideas.aha.io/ideas/CAOP-I-1209

New URL = https://ibm-data-and-ai.ideas.ibm.com/ideas/search?utf8=%E2%9C%93&query=CAOP-I-1209

[KTownJerry]

we have used Python script to auto upload from google share folder to cognos... we are looking for IBM or google solution but Python for now is fully scalable, sitting it in the same folder as the files.

Here's the IBM technote on this topic:
https://www.ibm.com/support/pages/ability-schedule-refresh-uploaded-file

[dougp]

Another (sketchy) solution -- at least if you use SQL Server -- is to use SQL Server to present the Excel file as a view in the database.  Then you can put it in a package and the data is updated whenever the user updates it.  Everybody has the most current version for reporting.

Wow!  Did I really recommend yet another way to help users make horrible decisions based on faulty data?