Automatic upload of flat files to Cognos team content folder

[raghunori]

Hello Team,

I have source files in the form of excel files stored in a cloud storage application called "Box". I need to upload these files onto Cognos team content folder twice a month so the dashboards are refreshed.

Is there any automatic way of uploading these files in Cognos Team content folder? Any python script? Appreciate your help in this regards

[adam_mc]

You can use Python via Jupyter Notebooks.

See the following link for a video - https://www.youtube.com/watch?v=R213e_N1nPc

Credit: Rich Chester - President, Chester Business Analytics (previously LPA).

Hope this is helpful.
Thanks,
Adam.

[raghunori]

Thank you Adam. Its a great video by Rich Chester. It is quite informative. But unfortunately, It does not seem to work for my setup. First of all, mine is IBM CA on Demand on Cloud. The video suggests the Cognos should be on-prem version  :(

Is there any similar solution for Cloud version of IBM CA?

[MFGF]

Thank you Adam. Its a great video by Rich Chester. It is quite informative. But unfortunately, It does not seem to work for my setup. First of all, mine is IBM CA on Demand on Cloud. The video suggests the Cognos should be on-prem version  :(

Is there any similar solution for Cloud version of IBM CA?

Hi,

The on-demand cloud offering is an online shared instance with potentially thousands of other users from other organizations. The potential for bad actors to insert malicious code would be extremely high if IBM was to allow this kind of access, so they have it completely locked down (as they must to ensure security). It's simply not possible (as far as I know) to use this approach in the on-demand cloud environment, sorry.

Cheers!

MF.

[raghunori]

Thank you.

Can you suggest any alternative?

All I am trying to do is to automate sourcing files from "Box" into "Cognos"

[MFGF]

Thank you.

Can you suggest any alternative?

All I am trying to do is to automate sourcing files from "Box" into "Cognos"

I can think of a couple of approaches.

1. Look at using an RPA product to automate the uploads from Box into Cognos Analytics. I imagine you could easily build a bot to do this in Automation Anywhere or UiPath.
2. Use an ETL tool to load the files from Box into tables in a database that your Cognos Analytics instance can connect to as a data server. A database such as Informix would work - you would just need to find an ETL tool to load the data - maybe something like Alteryx?

Cheers!

MF.

[raghunori]

ETL tool approach, we have something called data refinery flows in CP4D. I am able to establish a connection to Box but I am unable to establish a target connection to Cognos Analytics on Cloud. It gives me below error:
The test was not successful

The assets request failed: CDICO0103E: Connection failed possibly due to invalid credentials. Error is: CDICO0103E: Request to URL https://ap2.ca.analytics.ibm.com/rds/auth/logon failed with the status code 403 for the following reason: Forbidden

The Bot approach, can you point me to some kind of documentation? I have no idea about RPA product or creating bots

[MFGF]

ETL tool approach, we have something called data refinery flows in CP4D. I am able to establish a connection to Box but I am unable to establish a target connection to Cognos Analytics on Cloud. It gives me below error:
The test was not successful

The assets request failed: CDICO0103E: Connection failed possibly due to invalid credentials. Error is: CDICO0103E: Request to URL https://ap2.ca.analytics.ibm.com/rds/auth/logon failed with the status code 403 for the following reason: Forbidden

The Bot approach, can you point me to some kind of documentation? I have no idea about RPA product or creating bots

Hi,

If you have Cloud Pak for Data, you could deploy an instance of Db2 Warehouse in CP4D, and use that as the target for your Data Refinery flows. You can then add a data server connection in Cognos Analytics to point to the DB2WH database, allowing you access to the data. You can't write to Cognos Analytics - it is a reporting/visualization tool, not a database, so don't waste time trying to get that to work.

For the bot approach, maybe take a look at Automation Anywhere or UiPath:

https://docs.automationanywhere.com/bundle/enterprise-v11.3/page/enterprise/topics/aae-client/bot-creator/using-the-workbench/create-bot.html
https://www.uipath.com/learning/video-tutorials

Cheers!

MF.

[raghunori]

Thank you again..appreciate your time on responding to my queries..

so the problem with DB2 DWH on cloud is the cost associated with it..I tried DB2 approach with trial version of DB2 on cloud but it ran out of storage space, which is limited in trial version.

So about writing to Cognos Analytics, I understand your point on not able to write to Cognos Analytics..However, I have a seen a connection type of "Cognos Analytics" in the list of connections available in Cp4D..So just was wondering if this was something I can try by making CA as my target and Box as my source in CP4D data refinery flow..I was able to connect to box as source but not CA as target...

[MFGF]

Thank you again..appreciate your time on responding to my queries..

so the problem with DB2 DWH on cloud is the cost associated with it..I tried DB2 approach with trial version of DB2 on cloud but it ran out of storage space, which is limited in trial version.

So about writing to Cognos Analytics, I understand your point on not able to write to Cognos Analytics..However, I have a seen a connection type of "Cognos Analytics" in the list of connections available in Cp4D..So just was wondering if this was something I can try by making CA as my target and Box as my source in CP4D data refinery flow..I was able to connect to box as source but not CA as target...

Hi,

Cognos Analytics is not something you can write to in a Refinery Flow. You can connect to it as a source (so that you can read from it and benefit from the metadata defined there - eg calculations, friendly names etc) but you cannot write to it.

You could look for an open source database to use if DB2WH is too costly - just make sure it is in the list of databases the On-Demand Cloud version of CA can connect to.

Cheers!

MF.

[raghunori]

Thanks again!

I dont see any open source DB which are free of cost hosted on Cloud.

Do you think we can make some kind of API calls to Box from Cognos?

I know Cognos supports rest api but I dont know the process..

[MFGF]

Thanks again!

I dont see any open source DB which are free of cost hosted on Cloud.

Do you think we can make some kind of API calls to Box from Cognos?

I know Cognos supports rest api but I dont know the process..

Hi,

You're not going to be able to do this for free, whichever approach you take. You either need to invest in a database you can use, or invest in an RPA tool.

As I mentioned before, the on-demand cloud version of Cognos Analytics is locked down - you're not going to be able to make API calls from CA On Demand Cloud.

Sorry - probably not what you wanted to hear.

Cheers!

MF.