One of the recent new features in EPM Cloud is the ability to manage applications with Smart View, this is not to be confused with managing metadata using the Planning Admin Extension in Smart View, that has been around for a while and I wrote a blog on it back in 2013.
The announcements and new features document for June 2017 has the following information on the new functionality.
“Service Administrators can now use Microsoft Excel to manage applications in Oracle Smart View for Office. Using a downloadable Excel workbook template, you can quickly create Planning applications in Smart View. By editing worksheets within the application template workbook, you define application properties, dimension and member properties, security (access permissions), and substitution variables. You can also load data from the template. Additionally, Service Administrators can edit and delete the application from Smart View.”
So basically with the use of an Excel template and Smart View you can create, update or delete an application.
The Excel template allows you to manage:
As I have already covered the metadata management in the past I thought I would look into this alternative method to manage an application, currently this functionality only exists in EPM Cloud and who knows if it will ever be pushed down to on-premise, obviously it is there hidden in the latest versions of Smart View but would require an update to Planning to bring it to life.
It is worth mentioning that there is no need to have the Planning Admin Extension installed in Smart View to use the application management functionality.
For managing an application it is all about the Excel workbook template, Smart View is only really sending the excel file to planning, to get up and running you will first need the template and this can be obtained in a couple of ways.
The first way to obtain the templates is through the downloads page which can be access by clicking your user name once logged into an instance through the web.
Under Planning Extension there is an option to select “Download Application Templates”
This will download a zip file named “PlanningApplicationTemplates” which contains three Excel workbooks, the zip file is downloaded from:
https://<cloudInstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplates.zip
The zip file contains a template for PBCS, EPBCS and a version of the Vision sample application for PBCS.
The alternative method for downloading a single template excel workbook is from the Smart View panel, once logged into the cloud instance in the bottom right corner there should be an option “Download Template” or by selecting EPM Cloud and then by right clicking your instance name.
If no application exists there will also be the option for “Create New Application” which I will cover later.
Selecting “Download Template” will download and open “ApplicationTemplateFile.xlsx", the file is downloaded from
https://<cloudinstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplate.xlsx
The template has the following worksheet tabs which is enough to get you going managing an application.
The template does not include worksheets for attribute dimensions or data so for this post I am going to switch to the sample template, it is based on the vision application so should be familiar to many.
The sample template includes data and attribute worksheets.
Let us run quickly through each of the worksheets in the template to get an idea of the format, to be honest there is nothing complex about them and shouldn’t need much explaining.
Starting with the definition worksheet which defines the application properties and cubes.
There are no list boxes or any type of validations in the template, there are some validations when the file is loaded to planning but not for all areas so you need to make sure you enter the property values correctly, the documentation contains the default and valid values available for each property.
In order to create an application the only properties that are actually required are the application name and description, if any of the other properties are left blank the default value will be used.
Under the cubes section if the type is left blank then BSO will be taken as the default.
The definition sheet is just a replication of the details screen when creating an application through a browser.
The advantage using the template is that you can defined all the cubes instead of the two which are available when creating through a browser.
Moving on to the dimension sheets, I will only cover one of them because they are self-explanatory, the naming convention for the sheet should be Dim.<name>, for example
The actual dimension name is picked up from the worksheet so in theory you can put whatever you like after “Dim.” but it makes sense to keep them aligned.
Not all member properties are included as column headers in the template so if you need to include additional properties then you will have to manually add them.
Also instead of “Parent” there is “Roll up Member” to confuse matters, so there are slight differences between the template, Smart View metadata management and metadata import files.
Using the Smart View metadata management, it is named “Parent Member”.
A metadata import file it is defined as Parent.
I tested updating the template and changed “Roll up Member” to “Parent” and it still worked so it looks like using either should be ok.
If you are looking to find out what a property name should be then have a look at a metadata
import/export file as they should match.
If an invalid member property is included as a header then no error will be generated and the column will be ignored.
If an invalid property value is included like:
An error will be generated when loading.
What does not exist in the excel templates or the documentation is Smart Lists so I am not yet sure if you can create Smart Lists, if you can’t this would mean you couldn’t assign them to members until they have been created.
Once Smart Lists do exist then it is possible to assign them to members using the template as I have successfully tested
Next on to attribute dimensions and the format for the worksheet tab should Attribute.<name>, for example
Once again the dimension name is picked up from the worksheet so you can put anything you like after “Attribute.”
On to the advanced settings worksheet which should be named the same:
During my initial testing I couldn’t get the advanced settings to be picked up and the default values were used, I am not sure if this is a bug.
Anyway, the worksheet allows you define the evaluation order settings for each cube in the application.
The equivalent settings in the planning applications are:
The sheet has a section to define the dense/sparse settings, for some reason the template includes populated values for ASO cubes which doesn’t make sense.
The equivalent settings in the planning application are:
The dimension settings can also be defined in the worksheet.
It is also possible to define which dimensions are valid for each dimension in the application.
Now on to substitution variables where the sheet should be named “Variables”.
I don’t think I need to explain the contents of this worksheet as hopefully it should be clear.
On to access permissions where the worksheet should be named Security.
This is quite useful as it is a simple to use format like when using the on-premise planning importsecurity utility, instead of the forced XML format in migrations (LCM).
If you are looking for the best place to understand all the possible values for the worksheet, check out the on-premise importsecurity documentation as currently the cloud documentation does not provide all the detail.
Finally on to the data worksheet which should be named “Data.”, it is up to you what you put after that, for example.
The cube name is defined in the worksheet in cell B3
The column header containing the members should be set as “Dimension”, I did try to set it to the dimension names but when I did the data was not updated in the application.
I did notice a slight issue if there are blank cells in the data, if I load the above data set then the data is fine.
After loading the data you can see the periods where the blank cell exists have shifted
To get around this any blank cells should be populated with #missing.
The data will then be correct.
It is also worth pointing out that only up to 1,000 rows of data can be loaded, anything more than that will be ignored, for example I tried to load the following which had more than 1,000 rows of data.
No error was returned but a retrieve shows that the data after 1,000 was ignored.
It looks like data is loaded directly to Essbase so only numeric can be included, the default load method will be to overwrite any existing values in the database with the values from the file.
That covers the worksheets in the template so let us move on to creating a new application.
After connecting to a cloud instance through Smart View and if no application already exists there should be the option to “Create New Application” in the bottom right panel
Once selected the excel file will be uploaded and the application creation process will be initiated, be patient as creating an application can take a while and Excel will look like it is not responding until the process is complete.
If the creation was successful you should see the application, dimensions (if you have the planning admin extension enabled) and cubes in the Smart View panel
After creating the application you can update the application using the Excel templates, there should be the “Update Application” option in the bottom right panel.
Alternatively you can right click the application and select “Update Application”.
The application can be updated with any of the sheets from the template file, so for example if I want to create a new substitution variable I can create an excel file with a single worksheet named “Variables”
The only requirement is the worksheet sticks to the standard format as the template file.
The advantage of only including the sheets where you want to update the application is that the update process is much faster.
Using my example a new substation variable has been created in the application.
The functionality is similar to LCM where it will not delete and only creates/updates are carried out.
If any members are updated then a planning refresh will automatically be performed.
Not that it will be used that often there is also the option to delete an application which can be done from the bottom right panel or by right clicking the application.
Once deleted a confirmation message will be displayed.
I am going to leave it there for this post, if I find the time I will create a follow up post to go through in more detail the process behind the application creation, update, deletion and replicate this outside of Smart View.
The announcements and new features document for June 2017 has the following information on the new functionality.
“Service Administrators can now use Microsoft Excel to manage applications in Oracle Smart View for Office. Using a downloadable Excel workbook template, you can quickly create Planning applications in Smart View. By editing worksheets within the application template workbook, you define application properties, dimension and member properties, security (access permissions), and substitution variables. You can also load data from the template. Additionally, Service Administrators can edit and delete the application from Smart View.”
So basically with the use of an Excel template and Smart View you can create, update or delete an application.
The Excel template allows you to manage:
- Application properties
- Dimension and member properties
- Attribute dimensions
- Data loads
- Access permissions
- Substitution Variables
As I have already covered the metadata management in the past I thought I would look into this alternative method to manage an application, currently this functionality only exists in EPM Cloud and who knows if it will ever be pushed down to on-premise, obviously it is there hidden in the latest versions of Smart View but would require an update to Planning to bring it to life.
It is worth mentioning that there is no need to have the Planning Admin Extension installed in Smart View to use the application management functionality.
For managing an application it is all about the Excel workbook template, Smart View is only really sending the excel file to planning, to get up and running you will first need the template and this can be obtained in a couple of ways.
The first way to obtain the templates is through the downloads page which can be access by clicking your user name once logged into an instance through the web.
Under Planning Extension there is an option to select “Download Application Templates”
This will download a zip file named “PlanningApplicationTemplates” which contains three Excel workbooks, the zip file is downloaded from:
https://<cloudInstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplates.zip
The zip file contains a template for PBCS, EPBCS and a version of the Vision sample application for PBCS.
The alternative method for downloading a single template excel workbook is from the Smart View panel, once logged into the cloud instance in the bottom right corner there should be an option “Download Template” or by selecting EPM Cloud and then by right clicking your instance name.
If no application exists there will also be the option for “Create New Application” which I will cover later.
Selecting “Download Template” will download and open “ApplicationTemplateFile.xlsx", the file is downloaded from
https://<cloudinstance>/epmstatic/wspace/PlanningSmartviewExtension/PlanningApplicationTemplate.xlsx
The template has the following worksheet tabs which is enough to get you going managing an application.
The template does not include worksheets for attribute dimensions or data so for this post I am going to switch to the sample template, it is based on the vision application so should be familiar to many.
The sample template includes data and attribute worksheets.
Let us run quickly through each of the worksheets in the template to get an idea of the format, to be honest there is nothing complex about them and shouldn’t need much explaining.
Starting with the definition worksheet which defines the application properties and cubes.
There are no list boxes or any type of validations in the template, there are some validations when the file is loaded to planning but not for all areas so you need to make sure you enter the property values correctly, the documentation contains the default and valid values available for each property.
In order to create an application the only properties that are actually required are the application name and description, if any of the other properties are left blank the default value will be used.
Under the cubes section if the type is left blank then BSO will be taken as the default.
The definition sheet is just a replication of the details screen when creating an application through a browser.
The advantage using the template is that you can defined all the cubes instead of the two which are available when creating through a browser.
Moving on to the dimension sheets, I will only cover one of them because they are self-explanatory, the naming convention for the sheet should be Dim.<name>, for example
The actual dimension name is picked up from the worksheet so in theory you can put whatever you like after “Dim.” but it makes sense to keep them aligned.
Not all member properties are included as column headers in the template so if you need to include additional properties then you will have to manually add them.
Also instead of “Parent” there is “Roll up Member” to confuse matters, so there are slight differences between the template, Smart View metadata management and metadata import files.
Using the Smart View metadata management, it is named “Parent Member”.
A metadata import file it is defined as Parent.
I tested updating the template and changed “Roll up Member” to “Parent” and it still worked so it looks like using either should be ok.
If you are looking to find out what a property name should be then have a look at a metadata
import/export file as they should match.
If an invalid member property is included as a header then no error will be generated and the column will be ignored.
If an invalid property value is included like:
An error will be generated when loading.
What does not exist in the excel templates or the documentation is Smart Lists so I am not yet sure if you can create Smart Lists, if you can’t this would mean you couldn’t assign them to members until they have been created.
Once Smart Lists do exist then it is possible to assign them to members using the template as I have successfully tested
Next on to attribute dimensions and the format for the worksheet tab should Attribute.<name>, for example
Once again the dimension name is picked up from the worksheet so you can put anything you like after “Attribute.”
During my initial testing I couldn’t get the advanced settings to be picked up and the default values were used, I am not sure if this is a bug.
Anyway, the worksheet allows you define the evaluation order settings for each cube in the application.
The equivalent settings in the planning applications are:
The sheet has a section to define the dense/sparse settings, for some reason the template includes populated values for ASO cubes which doesn’t make sense.
The equivalent settings in the planning application are:
The dimension settings can also be defined in the worksheet.
It is also possible to define which dimensions are valid for each dimension in the application.
Now on to substitution variables where the sheet should be named “Variables”.
I don’t think I need to explain the contents of this worksheet as hopefully it should be clear.
On to access permissions where the worksheet should be named Security.
This is quite useful as it is a simple to use format like when using the on-premise planning importsecurity utility, instead of the forced XML format in migrations (LCM).
If you are looking for the best place to understand all the possible values for the worksheet, check out the on-premise importsecurity documentation as currently the cloud documentation does not provide all the detail.
Finally on to the data worksheet which should be named “Data.”, it is up to you what you put after that, for example.
The cube name is defined in the worksheet in cell B3
The column header containing the members should be set as “Dimension”, I did try to set it to the dimension names but when I did the data was not updated in the application.
I did notice a slight issue if there are blank cells in the data, if I load the above data set then the data is fine.
If I remove the value for “BegBalance” so the cell
is blank.
After loading the data you can see the periods where the blank cell exists have shifted
To get around this any blank cells should be populated with #missing.
The data will then be correct.
It is also worth pointing out that only up to 1,000 rows of data can be loaded, anything more than that will be ignored, for example I tried to load the following which had more than 1,000 rows of data.
No error was returned but a retrieve shows that the data after 1,000 was ignored.
It looks like data is loaded directly to Essbase so only numeric can be included, the default load method will be to overwrite any existing values in the database with the values from the file.
That covers the worksheets in the template so let us move on to creating a new application.
After connecting to a cloud instance through Smart View and if no application already exists there should be the option to “Create New Application” in the bottom right panel
Once selected the excel file will be uploaded and the application creation process will be initiated, be patient as creating an application can take a while and Excel will look like it is not responding until the process is complete.
If the creation was successful you should see the application, dimensions (if you have the planning admin extension enabled) and cubes in the Smart View panel
After creating the application you can update the application using the Excel templates, there should be the “Update Application” option in the bottom right panel.
Alternatively you can right click the application and select “Update Application”.
The application can be updated with any of the sheets from the template file, so for example if I want to create a new substitution variable I can create an excel file with a single worksheet named “Variables”
The only requirement is the worksheet sticks to the standard format as the template file.
The advantage of only including the sheets where you want to update the application is that the update process is much faster.
Using my example a new substation variable has been created in the application.
The functionality is similar to LCM where it will not delete and only creates/updates are carried out.
If any members are updated then a planning refresh will automatically be performed.
Not that it will be used that often there is also the option to delete an application which can be done from the bottom right panel or by right clicking the application.
You will need to confirm that the application should be deleted.
Once deleted a confirmation message will be displayed.
I am going to leave it there for this post, if I find the time I will create a follow up post to go through in more detail the process behind the application creation, update, deletion and replicate this outside of Smart View.
John,
ReplyDeleteThanks as always for your thorough reviews. We have looked into your feedback and are taking care of many of the issues you noted in the next 2-3 months.
regards
Shankar - Product PM