You might have heard/used the new feature in Smartview, which allows you to define shared connection in an XML file. I like the idea of accessing Essbase, Planning and Reporting with a single URL, compared to multiple URLs (in Private Connections) in Smart View. The part that I didn’t like was to use different URLs for different environments.
The new feature just solved my problem.
Starting from Smart view 11.1.2.2.310, now you can add Shared Connection information can be added to an XML.
You can create an XML as given below and use that in Shared Connections URL
<?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product id="APS" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Provider Services - DEV" context="http://DEV:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - DEV" context="http://DEV:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - DEV" context="http://DEV:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="APS" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Provider Services - QA" context="http://QA:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - QA" context="http://QA:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - QA" context="http://QA:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="APS" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Provider Services - Prod" context="http://Prod:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - Prod" context="http://Prod:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - Prod" context="http://Prod:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="APS" name="APS-11.1.2.3" displayVersion="version">
<Server name="Oracle Essbase - Local" context="http://myserver:9000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.3" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - Local" context="http://myserver:9000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.3" displayVersion="version">
<Server name="Reporting and Analysis Framework - Local" context="http://myserver:9000/raframework/browse/listXML"></Server>
</Product>
</res_GetProvisionedDataSources>
I was looking for an option to upload that to a server and distribute it to all users.
I started looking at the problem from a Web Application server perspective and was looking at options to upload this into Weblogic server. In the end I thought of adding this to workspace tmp directory and was able to achieve what I was looking for.
The problem is it is a tmp directory.
It was then the great “John Goodwin” told why are you trying to load the file to a tmp directory of Application server when you have ohs (Web server).
Sometimes you need someone to tell you that there are different options.
If you look at the documentation HTTP Access for Shared Connection XML File it talks about HTTP server 🙁
You can follow the below given steps to make the XML file accessible from a server.
If you just want to access the file without any modification to http config file then keep the file under <EPM Drive>:OracleMiddlewareEPMSystem11R1commonepmstatic.
You can then access the file using this URL http://servername:port/epmstatic/xmlfilename.xml
If you are interested in adding the XML to a different folder, then you’ll have to add an Alias in http config file.
If you are using automatic configuration and ohs then the file will be under <EPM Drive>:OracleMiddlewareuser_projects<instance>httpConfigautogeneratedohs.
Add an entry in epm.conf similar to the one given below. (Remember to keep this one as the last entry)
I created a folder call SmartViewXML under epmstatic and an Alias entry was added.
Alias /SmartViewXML “C:/Oracle/Middleware/EPMSystem11R1/common/epmstatic/SmartViewXML”
You can then access the file using http://servername:port/epmstatic/SmartViewXML/SmartView_Connections.xml
This is how it looks if everyone goes well.
It would have been lot easier if we can load this file to workspace and then use the Smart Cut 🙂
Hope that will be considered and hope that we can add custom names to Essbase connections, No matter what name you use for APS, it shows up as Oracle®Essbase (bug ????)
Update 11/13/2013
For Essbase Server names are not picked up and that was the bug that I was talking about. Now that Aarthi Krishnan gave us a nice hack, here is how it looks after the change.
<?xml version="1.0" encoding="UTF-8"?>
<res_GetProvisionedDataSources>
<Product id="Oracle Essbase - DEV" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Essbase - DEV" context="http://DEV:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - DEV" context="http://DEV:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - DEV" context="http://DEV:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="Oracle Essbase - QA" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Essbase - QA" context="http://QA:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - QA" context="http://QA:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - QA" context="http://QA:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="Oracle Essbase - Prod" name="APS-11.1.2.2" displayVersion="version">
<Server name="Oracle Essbase - Prod" context="http://Prod:19000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.2" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - Prod" context="http://Prod:19000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.2" displayVersion="version">
<Server name="Reporting and Analysis Framework - Prod" context="http://Prod:19000/raframework/browse/listXML"></Server>
</Product>
<Product id="Oracle Essbase - Local" name="APS-11.1.2.3" displayVersion="version">
<Server name="Oracle Essbase - Local" context="http://myserver:9000/aps/SmartView"></Server>
</Product>
<Product id="HP" name="HP-11.1.2.3" displayVersion="version">
<Server name="Oracle Hyperion Planning, Fusion Edition - Local" context="http://myserver:9000/HyperionPlanning/SmartView"></Server>
</Product>
<Product id="RAFramework" name="RAFramework-11.1.2.3" displayVersion="version">
<Server name="Reporting and Analysis Framework - Local" context="http://myserver:9000/raframework/browse/listXML"></Server>
</Product>
</res_GetProvisionedDataSources>
Who would have thought to change Product ID, where the documentation asks to change the Server name. I still think it is a bug 😉
Celvin, We have set this up in 11.1.2.1 it works well except Essbase Connection rename as discussed above. Is this bug fixed in 11.1.2.3?
Celvin-thanks for this wonderful post.
Essbase showing up as " Oracle Essbase" without the environment name is not a bug. I was able to override it this way. You could change the " Product id " from APS to APS DEV. This helped me. This has to be repeated from the to for all the environments. Hope this helps.
@Aarthi nice hack. I've updated the blog accordingly. Thank you for sharing
Celvin,
I'm on 11.1.2.3 and I've got the .xml file/formatting working from the client, but when I place it under the epmstatic folder the SmartView Shared Connections dropvdown returns
"Cannot connect to provider. The server name could not be resolved.". I can browse the .xml file contents via browser on the IE8/Win7 client that I'm testing. I've got the http://:19000 already in my trusted sites. This is Excel 2010 and SmartView 11.1.2.5. Should I anticipate issues with Excel's "Trust Center"?
-Vince
@Vince Did you trying creating a folder and adding it there?
Support is saying that the Shared Connection .xml under epmstatic supports OBIEE connections only (EPM 11.1.2.3). has someone been successful with this release?
-Vince
Create a folder under epmstatic and add my .xml file there?
OK, did that, now I have "Client authorization is needed to accept the certificate.". Do I need to restart my services to get past this?
-Vince
@Vince
Did you restart OHS after adding the file under epmstatic? Were you able to view the xml file in browser using the url http://servername:port/epmstatic/yourxmlname.xml?
If you go the route of adding a folder you'll have to update the config file and add an alias as described above in the post.
You can also try adding the xml file under epmstatic/wspace/SmartView folder and access it using http://server:port/epmstatic/wspace/SmartView/SmartViewProviders.xml
Let me know if none of these work
Yes, restarted all services. I get the this same response in both dev and prod. I will try each suggestion and report.
Thanks.
Hi Celvin.It worked for me.
Celvin,
Thanks we have this working. However, we have a handful of workstations that will not connect, nor allow us to browse the contents of the XML file. SmartView (11.1.2.5.000) returns:
Connection to Shared Servies provider failed, the provider running at "url" is not a Shared Servies provider
–> Modify
current URL
"http://:19000/epmstatic/SmartViewProviders.xml"
–>Retry
When we try to browse the URL link, we cannot get the XML text to display. Have you seen this before?
Thanks,
-Vince
Can those machine connect to workspace?
Another test you can try is to access SmartView.exe
http://server:port/epmstatic/wspace/SmartView/SmartView.exe
If they can get to SmartView.exe then you can move the XML file into commonepmstaticwspaceSmartView
Yes, they can get to Workspace. I have an alternate .xml file in wspaceSmartView and I'll have my user test this. We don't have out users access the SmartView.exe since they are predominantly XP users ans we can't get .NET 4.5 on those clients to allow the remote install.
-Vince
Hi, really meant to say http://:19000/epmstatic/SmartViewProviders.xml.
-Vince
I am only able to view the Essbase Servers and want to replace the Essbase Servers with the aliases in the drop down in Smartview. For Example: EssbaseCluster-1 should be visible as EssbasePROD (Alias for EssbaseCluster-1) in the Drop down list for Users in Smartview. How can we add a new Essbase Server to the essbase Servers list in Smartview?
Did you read the post above? It does talks about adding Essbase servers with an alias. However you cannot rename EssbaseCluster-1
HI Celvin, is there any way around this? if you have two environments, they both come up with “EssbaseCluster-1” and it is difficult to know which is which. We have templates that use the Smart view vba so we have a lot of Private Connections. Switching between Dev and Prod is a bit of a ‘pick one and see what comes up’ issue at the moment. Without deleting the entire cluster and renaming it, is there any way to get Smart view to use an ‘alias’ as Satish has mentioned above? thanks
Celvin – since APS reads off of shared services repository, can't we just replace Essbase server names with intended alias in properties file itself?
If I were you I wouldn't do that as it is used everywhere else, it used by Essbase to recognize which cluster, used by APS, if you've planning then there, reporting and shared services.
I Just ran into a pitfall…
Please be careful when upgrading.
I just lost the directory during the updade to 11.1.2.4.
So, remember to backup your special dir and xml-file before you start the upgrade!
Andre
Andre