Is there a way to connect to Hyperion?

[erica_tax2019]

My input file has Hyperion formulas. Before I utilize Alteryx to extract & join certain data, I need my Hyperion formulas to be refreshed within my input file. Is there a way to connect to Hyperion so that when I run my workflow, all of the Hyperion formulas within my input file are refreshed?

[RolandSchubert]

Hi @erica_tax2019 ,

are you using the formulas to calculate within Excel or is it just SmartView/HSGetValue? Which Hyperion product do you use? Hyperion Financial Management, Hyperion Planning, Hyperion Essbase?

Best,

Roland

[erica_tax2019]

I am using Hyperion Financial Management. Within the excel file, I have embedded formulas to pull from Hyperion so that my data is always the most recent. In order to refresh my formulas within the excel file, I usually click on "Smart View" & refresh all worksheets in order to update the excel file.

[RolandSchubert]

I think the most simple solution would be to use Excel VBA to execute the "Refresh All" (e.g. using HypMenuVRefreshAll) within the workbook. Then you'll have to create a small VB Script to Open - Calculate - Save to Excel workbook. This could be called using the RunCommand tool in Alteryx. What do you think?

Best,

Roland

[BrandonB]

@RolandSchubert has a good suggestion. Here is an article that goes over the process: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel... 

The one tweak that I would suggest you make is instead of using a run command tool in the workflow to execute the VBA script, you would actually use a workflow event to do a run command before workflow run as seen below: 

 

[erica_tax2019]

Thank you for your suggestions.

I am not very familiar with VBA scripts, but I will read the article that you sent over to try & figure it out!

[BrandonB]

Feel free to let us know if you get stuck! You can think of a VBA script as just a little bit of code to automatically kick off the press of your SmartView button in Excel. 

[erica_tax2019]

Great! Thank you!!

[JoeL]

If anyone comes across this in the future, the VBA code to execute what is suggested would be a worksheet calculate:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel...

[BrandonB]

Thank you for this write up @JoeL!