CODEWITHSUNDEEP
databaseexceltableau-apicognos
Shannon Cooley
Shannon Cooley

Reputation: 23

Can I automatically export data from a Cognos report into a database?

The overall goal is to have data from an automated daily Cognos report stored in a database so that I am able to report not only on that day but also historical data if I so choose. My general thought is that if I can find a way to automatically add the new daily data to an existing Excel file, I can then use that as my data source and create a dashboard in Tableau. However, I don't have any programming experience, so I'm floundering here.

I'm committed to using Tableau, but I chose Excel only because I'm more familiar with that program than others, along with the fact that an Excel output file is an option in Cognos. If you have better ideas, please don't hesitate to suggest them along with why you believe it's a better idea.

Upvotes: 0

Views: 4191

Answers (2)

Shannon Cooley
Shannon Cooley

Reputation: 23

Update: I'm still jumping through hoops to try to get read-only access to the backend database to make this process a lot more efficient, but in the meantime I've moved forward with the long method utilizing Cognos.

I was able to leverage a coworker to create a system file folder to automatically save the Cognos reports to, and then I scheduled a job to run the reports I need. Each of those now saves into a folder in a shared network drive (so my entire team has access to the files), and I wrote a series of macros to append the data each day from those feeder files in the shared drive to a Master File. Now all that's left is to create a Tableau dashboard using the Master File as the data source and I'll have what I need.

Thanks for all your help!

Upvotes: 2

durbnpoisn
durbnpoisn

Reputation: 4669

I'm posting this an an answer because, it's just too much to leave as a comment.

What you need are 3 things.

  1. Figure out how to have COGNOS run your report and download your Excel file.

  2. Use Visual Studio with BIDS (which is the suite of SQL analysis, reporting, and integration services) to automate all the stuff you need to do to append your Excel files, etc... Then you can use the same tools to import that data to your SQL server.

    • In fact, if all you're doing is trying to get this data into SQL, you can skip the Append Excel part, and just append the data directly to your SQL table.
    • Once your package is built, you can save it as an automated job on your SQL server to run whenever you wish.

  3. Tableau can use your SQL server as a data source. Once you have that updated, you can run your reports.

Upvotes: 0

Related Questions