In the recent release of OBIEE 11.1.1.9 there was one interesting new feature from an EPM perspective.
OBIEE at last supports planning as a data source, it has been a long time coming as other EPM data sources such as Essbase and HFM have been supported in OBIEE as a data source for a long time.
The ability to use a planning data source compared to an essbase source should give the additional reporting features such as Smart Lists, Text, Date type measures and cell comments.
So I thought I would go through having a look at getting up and running with a planning data source as to be honest the documentation seems a little light.
The documentation currently does suggest the following:
The Hyperion Planning Server must be version 11.1.2.4 or above.
This is actually incorrect as if you look in the issues and workarounds documentation it states:
The section "Importing Metadata From Hyperion Planning Data Sources" contains the following requirements:
I am sure the documentation will be updated in the future to remove these statements but unfortunately it doesn't say which versions of planning are actually supported, I did check the OBIEE support matrix and it does not yet contain any reference to the planning data source.
I will be using Planning 11.1.2.4 so there should be no problem there so let’s get on with it.
The first step is to import the Planning metadata from within the OBIEE Admin tool.
The connection type to select is “Hyperion ADM”, ADM stands for Analytic Data Model and has been around in the hyperion world for an age and is mainly used by Financial Reporting, it is basically a set a Java classes that allow a standardised way of querying of data sources such as Essbase, Planning and HFM.
HFM also takes on board the ADM functionality when added as a data source in OBIEE.
The URL format to connect to planning is supposedly:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<Server>:<Port>:<Application>
I say supposedly as not everything is as it seems which you will find out shortly.
I am going to be using the Vision sample application and going via the web server to connect to planning though it is certainly possible to go direct to planning on the default 8300 port.
Using the suggested URL format is where the fun and games begin.
Not a great start to be hit with an error, if you look closer at the error it is generating a http URL to connect to planning but is ignoring the port which wouldn't be a problem if the web server is running on port 80.
I tested connecting by removing the port and the application name.
The syntax should be:
adm:[thin|native|corba]:<driver>:<server>:<application>
So you can clearly see there is no mention of port which I admit did through me for a while but I then thought what happens if I just encode the colon between the server name and port.
The encoded format for a colon is %3A so this means the URL would be:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<SERVERNAME>%3A<PORT>:<APPLICATION>
The new format brings much more success and connects to the planning server which then allows to select which plan types to be used when importing the metadata.
After stumbling with this issue I subsequently found that in the OBIEE issues and workarounds documentation there is reference to this problem which means at least the main documentation will be updated in the future:
In the section "Importing Metadata from Multidimensional Data Sources," Table 5-1 Multidimensional Connection Options, the following additional information is available for the Hyperion ADM connection type:
For Hyperion Planning using the ADM thin client driver, include the driver and the application name (cube name), in the following format:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:[server]%3A[port]:[application_name]
On a side note if you do have technical issues with the planning ADM drivers they can be found in the following locations in the OBIEE deployment.
Currently the ADM version in OBIEE 11.1.1.9 matches EPM 11.1.2.4
Anyway, once the metadata has been imported into the physical layer it is broken down by both measures and dimensions.
There are multiple value columns to support different account member data types such as Text and Date.
All Hyperion Planning dimensions are modelled as parent-child hierarchies in the Physical layer. Shared members, alternate hierarchies, and unbalanced hierarchies are supported.
Each dimension has a set of properties which are available as columns, if you are not clear on what the properties represent then select view data on the dimension.
Among the many properties there is also sort order which should allow the dimension members to be displayed in the same order in a report as set within the planning application.
By default the connection pool to planning will be using a Shared Logon which means all reports will be connecting to planning using this account.
I will get on to using SSO later on in this post.
I am not going to go into more detail with the admin tool and simply drag the plan type over to the BMM and Presentation layers.
Once saved and OBIEE restarted the plan type will be available in the subject areas ready to start creating reports.
One thing that drove me crazy when attempting to build some simple reports against planning was the caching as the BI Server cache was enabled, once this was disabled it made life a little less frustrating.
I am far from ever being a report developer but even with my simplistic mind it didn't take too long to knock up a basic report.
One thing to watch out for when using a planning ADM data source say over say an essbase data source is the speed of retrieval, while it seemed pretty snappy for simple reports I am not sure how it will handle with complex reports.
Another area which is not the best with ADM is the diagnostic logging, if you do get hit issues then it will be difficult to find some meaningful information in the logs on both the EPM and OBIEE sides.
In the essbase application log you won’t get much more than the time it took to execute the retrieval, unlike when using essbase as a data source where you can enable tracing to view the MDX that is being run.
Local/Vision/Plan1/admin@Native Directory/5272/Info(1020055)
Spreadsheet Extractor Elapsed Time : [0.828] seconds
When designing reports and selecting members in OBIEE a flat list is returned which is defined by the sort order, if you have a large hierarchy then the search is your friend, below is an example of Financial Reporting using a planning data source and then on the right the equivalent in OBIEE.
I was interested in whether attribute dimensions would be available because with Financial Reporting and planning this has never been possible, I didn’t expect it to work with OBIEE as it also using the same ADM driver and to be honest I hit problems when the planning application contained attribute dimensions.
The attribute dimensions were imported as metadata but when viewing members for many of the dimensions all that was returned was the attribute members and not the actual members, no reports could be created or run because of this problem and once I deleted the attribute dimension from planning everything started to work again in OBIEE.
I have not had a chance to look at it in more detail but it felt like a bug.
I also wondered whether it would be possible to use substitution variables in a report as it is certainly possible in Financial Reporting which is uses the ADM drivers, I tested using a substitution variable directly in a filter.
Unfortunately it doesn't look it is possible to directly use the variable and I tried a number of different combinations but they all generated ADM query errors.
Not to worry as there is an alternative method which can be used by adding a connection to essbase in the RPD.
Once added if you look at the variable manager in the Admin tool a new initialisation block is automatically added.
If you view the property of the initialization block you will be able to see all the substitution variables that have been brought through from essbase.
I am going to add the “CurVersion” substitution variable which has a value of “Working” to the analysis I created earlier.
The filter for version was edited and a new repository variable added.
The format for the variable is:
<SERVER>:<APPLICATION>:<VARIABLE>
Now the analysis is using the substitution variable to return the version member “Working”
Earlier I mentioned that in the connection pool Shared logon is selected by default, this is fine if you are not interested in the reports taking on the users access permissions that are defined within the planning application.
If you are then you make use of the single sign on property.
In order for this to work then there are some configuration changes that are required which I covered in previous posts.
It is possible to have single sign back to planning for a user when they log directly into OBIEE or you can have the option of integrating OBIEE into workspace and sharing a single SSO token.
Once SSO enabled is enabled then users should only see members which they have access to, in the below example a user is running the same example report I created but does not have access to the “Return of Equity” member.
This also demonstrates that the ADM functionality is honouring the permissions applied in the planning application.
If the integration between OBIEE and Workspace has been configured then a provisioned user will see addition OBIEE menu options available in workspace.
The user will then be able to access OBIEE without the requirement of having to log in again.
In the above example you can also see that Smart Lists, Text Members and Cell Text can easily be displayed, I saw a slight issue where the cell text would also return html code for a line break which might be a bug.
Well I am going to leave it there for now as putting these posts together takes a large amount of my own time, hopefully this overview has been useful.
OBIEE at last supports planning as a data source, it has been a long time coming as other EPM data sources such as Essbase and HFM have been supported in OBIEE as a data source for a long time.
The ability to use a planning data source compared to an essbase source should give the additional reporting features such as Smart Lists, Text, Date type measures and cell comments.
So I thought I would go through having a look at getting up and running with a planning data source as to be honest the documentation seems a little light.
The documentation currently does suggest the following:
The Hyperion Planning Server must be version 11.1.2.4 or above.
This is actually incorrect as if you look in the issues and workarounds documentation it states:
The section "Importing Metadata From Hyperion Planning Data Sources" contains the following requirements:
- Hyperion Planning Server must be version 11.1.2.4 or above.
- The servers that run Hyperion Planning 11.1.2.4 or above must be licensed for Oracle Enterprise Performance Management Financial Planning Analytics.
I am sure the documentation will be updated in the future to remove these statements but unfortunately it doesn't say which versions of planning are actually supported, I did check the OBIEE support matrix and it does not yet contain any reference to the planning data source.
I will be using Planning 11.1.2.4 so there should be no problem there so let’s get on with it.
The first step is to import the Planning metadata from within the OBIEE Admin tool.
The connection type to select is “Hyperion ADM”, ADM stands for Analytic Data Model and has been around in the hyperion world for an age and is mainly used by Financial Reporting, it is basically a set a Java classes that allow a standardised way of querying of data sources such as Essbase, Planning and HFM.
HFM also takes on board the ADM functionality when added as a data source in OBIEE.
The URL format to connect to planning is supposedly:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<Server>:<Port>:<Application>
I say supposedly as not everything is as it seems which you will find out shortly.
I am going to be using the Vision sample application and going via the web server to connect to planning though it is certainly possible to go direct to planning on the default 8300 port.
Using the suggested URL format is where the fun and games begin.
Not a great start to be hit with an error, if you look closer at the error it is generating a http URL to connect to planning but is ignoring the port which wouldn't be a problem if the web server is running on port 80.
I tested connecting by removing the port and the application name.
This generated a different error but provides further information to the format required to connect.
The syntax should be:
adm:[thin|native|corba]:<driver>:<server>:<application>
So you can clearly see there is no mention of port which I admit did through me for a while but I then thought what happens if I just encode the colon between the server name and port.
The encoded format for a colon is %3A so this means the URL would be:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:<SERVERNAME>%3A<PORT>:<APPLICATION>
The new format brings much more success and connects to the planning server which then allows to select which plan types to be used when importing the metadata.
After stumbling with this issue I subsequently found that in the OBIEE issues and workarounds documentation there is reference to this problem which means at least the main documentation will be updated in the future:
In the section "Importing Metadata from Multidimensional Data Sources," Table 5-1 Multidimensional Connection Options, the following additional information is available for the Hyperion ADM connection type:
For Hyperion Planning using the ADM thin client driver, include the driver and the application name (cube name), in the following format:
adm:thin:com.hyperion.ap.hsp.HspAdmDriver:[server]%3A[port]:[application_name]
On a side note if you do have technical issues with the planning ADM drivers they can be found in the following locations in the OBIEE deployment.
Anyway, once the metadata has been imported into the physical layer it is broken down by both measures and dimensions.
There are multiple value columns to support different account member data types such as Text and Date.
All Hyperion Planning dimensions are modelled as parent-child hierarchies in the Physical layer. Shared members, alternate hierarchies, and unbalanced hierarchies are supported.
Each dimension has a set of properties which are available as columns, if you are not clear on what the properties represent then select view data on the dimension.
Among the many properties there is also sort order which should allow the dimension members to be displayed in the same order in a report as set within the planning application.
By default the connection pool to planning will be using a Shared Logon which means all reports will be connecting to planning using this account.
I will get on to using SSO later on in this post.
I am not going to go into more detail with the admin tool and simply drag the plan type over to the BMM and Presentation layers.
Once saved and OBIEE restarted the plan type will be available in the subject areas ready to start creating reports.
One thing that drove me crazy when attempting to build some simple reports against planning was the caching as the BI Server cache was enabled, once this was disabled it made life a little less frustrating.
I am far from ever being a report developer but even with my simplistic mind it didn't take too long to knock up a basic report.
One thing to watch out for when using a planning ADM data source say over say an essbase data source is the speed of retrieval, while it seemed pretty snappy for simple reports I am not sure how it will handle with complex reports.
Another area which is not the best with ADM is the diagnostic logging, if you do get hit issues then it will be difficult to find some meaningful information in the logs on both the EPM and OBIEE sides.
In the essbase application log you won’t get much more than the time it took to execute the retrieval, unlike when using essbase as a data source where you can enable tracing to view the MDX that is being run.
Local/Vision/Plan1/admin@Native Directory/5272/Info(1020055)
Spreadsheet Extractor Elapsed Time : [0.828] seconds
When designing reports and selecting members in OBIEE a flat list is returned which is defined by the sort order, if you have a large hierarchy then the search is your friend, below is an example of Financial Reporting using a planning data source and then on the right the equivalent in OBIEE.
I was interested in whether attribute dimensions would be available because with Financial Reporting and planning this has never been possible, I didn’t expect it to work with OBIEE as it also using the same ADM driver and to be honest I hit problems when the planning application contained attribute dimensions.
The attribute dimensions were imported as metadata but when viewing members for many of the dimensions all that was returned was the attribute members and not the actual members, no reports could be created or run because of this problem and once I deleted the attribute dimension from planning everything started to work again in OBIEE.
I have not had a chance to look at it in more detail but it felt like a bug.
I also wondered whether it would be possible to use substitution variables in a report as it is certainly possible in Financial Reporting which is uses the ADM drivers, I tested using a substitution variable directly in a filter.
Unfortunately it doesn't look it is possible to directly use the variable and I tried a number of different combinations but they all generated ADM query errors.
Not to worry as there is an alternative method which can be used by adding a connection to essbase in the RPD.
Once added if you look at the variable manager in the Admin tool a new initialisation block is automatically added.
If you view the property of the initialization block you will be able to see all the substitution variables that have been brought through from essbase.
I am going to add the “CurVersion” substitution variable which has a value of “Working” to the analysis I created earlier.
The filter for version was edited and a new repository variable added.
The format for the variable is:
<SERVER>:<APPLICATION>:<VARIABLE>
Now the analysis is using the substitution variable to return the version member “Working”
Earlier I mentioned that in the connection pool Shared logon is selected by default, this is fine if you are not interested in the reports taking on the users access permissions that are defined within the planning application.
If you are then you make use of the single sign on property.
In order for this to work then there are some configuration changes that are required which I covered in previous posts.
It is possible to have single sign back to planning for a user when they log directly into OBIEE or you can have the option of integrating OBIEE into workspace and sharing a single SSO token.
Once SSO enabled is enabled then users should only see members which they have access to, in the below example a user is running the same example report I created but does not have access to the “Return of Equity” member.
This also demonstrates that the ADM functionality is honouring the permissions applied in the planning application.
If the integration between OBIEE and Workspace has been configured then a provisioned user will see addition OBIEE menu options available in workspace.
The user will then be able to access OBIEE without the requirement of having to log in again.
In the above example you can also see that Smart Lists, Text Members and Cell Text can easily be displayed, I saw a slight issue where the cell text would also return html code for a line break which might be a bug.
Well I am going to leave it there for now as putting these posts together takes a large amount of my own time, hopefully this overview has been useful.
As usual, top work John!!
ReplyDeleteAs usual thanks John for such elaborative explanation. I tried out the same way and step by step and as expected the url encode helped me in connecting to planning application.
ReplyDeleteI am facing an issue when trying to connect to one of planning application, I am able to connect to all other planning application. The error that comes up when I do the Import metadata, failed to connect to :port [0] planning server on the error screen and when I checked on the planning server it showed me entry saying Connect to application failed, the error seems to say that the user is not getting authenticated. But when I used the same user through epm workspace, I am able to access the same application properly. This is happening to only this application, any suggestions from your side to look into any specifics.
Thanks
SK