Creating an Essbase Excel View (Creating an Essbase Excel View, Part 1/3)

Uploaded by appliedolap on 25.02.2011

Now we're going to build our first Essbase Excel Dodeca Report.
So far what we've done is set up a basic Dodeca environment.
In the previous videos, we set up a tenant code, imported some metadata,
imported some Essbase connections, and then imported some dimensions in order to create
Selector objects.
What we'll do now is take an Excel file,
which is an Essbase-aware Excel file, and import it into Dodeca for use in a View.
There are three basic steps to creating and deploying an Essbase Excel View.
First, we're going to make some modifications to the Excel template and then import it
into Dodeca as a Binary Artifact.
Second, we're going to create a View.
A View is a container for a Dodeca report, and it will allow us to define the properties
that will define the behavior and display of the report in Dodeca.
Finally, we'll deploy the report to end-users by placing it on a View Hierarchy.
So first, we'll need to make a few modifications to our Essbase-aware Excel file.
You'll notice that we have three page fields.
What we need to do first is to tokenize these fields so that end-users can make selections
for the Product, Measures, and Scenario that they desire to see in the View.
This report has the Market Dimension on the row orientation
and the Year Dimension on the column orientation.
In order to determine which tokens we need to use in the worksheet,
let's return for a moment to the FINANCE application created in the previous video series.
In the previous video, we imported Dimensions to create Selector objects in Dodeca.
To view our previously-created Selectors, navigate to Admin > Selectors from the Dodeca Menu.
You'll recall that we have Selectors for each imported Essbase Dimension.
So in the Selectors Metadata Editor which has opened,
find the Token values which correspond to the Product, Measures, and Scenario dimensions.
Remember that we recommend using the following format for tokens:
[T.Dimension Name]
If you used the default values recommended by Dodeca when you imported your dimensions,
the Token values will be [T.Product],
[T.Measures], and [T.Scenario].
Start by copying [T.Product] from the metadata editor, and then return to the Excel template.
Paste the value [T.Product] into the "Product" page field, replacing the current text, "Product".
Now, do the same for the "Measures" and "Scenario" page fields.
You can copy and paste the Token names from Dodeca or, optionally, you can just type in the values.
Tokens, by the way, are not case sensitive.
In addition to adding the appropriate Token names to the worksheet,
you may want to limit the range within which Essbase will perform the retrieve.
In this View, we'll want to limit the Essbase Retrieve Range to the range of cells from
A4 to F10.
In order to do that, we'll need to create a Range Name that Dodeca will recognize.
In Microsoft Excel 2007,
We'll go to the Formulas tab on the Ribbon,
open up the Name Manager,
and then select "New" to create a New Name.
For the name, type in "Ess.Retrieve.Range.1".
The first three segments of the name are mandatory,
so your new name must begin with "Ess.Retrieve.Range.",
followed by any unique string you wish to use.
For the sake of simplicity in this tutorial, we will use "Ess.Retrieve.Range.1".
You can use any unique identifier you wish after the "Ess.Retrieve.Range.",
but note that if multiple Retrieve Ranges are contained in a worksheet,
we cannot guarantee the order in which they'll be retrieved.
For instance, there's no guarantee that "Ess.Retrieve.Range.1" will be retrieved before
We want to make sure that the Retrieve Range we're creating is scoped at worksheet level.
This means that the Retrieve Range name is only valid for this particular sheet in the workbook.
This becomes important if, for instance, we start using this worksheet with cascading,
because Dodeca will need to know where the ranges are located on each specific sheet
in the cascade.
So, from the dropdown labeled "Scope:", select "Sheet1".
Click "OK" and then "Close" on the Name Manager.
We've now created a range name that Dodeca can recognize.
Go ahead and select a single cell, and then Save the workbook.
Next, we'll import this workbook into Dodeca for use in a View.
Go ahead and leave the workbook open in Excel, in case we wish to make changes to it at any point.
To import the Excel workbook, we'll start by creating a Dodeca Binary Artifact.
From the Dodeca Menu, navigate to Admin > Binary Artifacts.
Binary Artifacts is basically just a fancy name for files stored in the Dodeca database.
Go ahead and create a new Binary Artifact by pressing the "New" button in the center pane.
Let's call this new Binary Artifact "My Report".
Note once again that IDs in Dodeca may contain spaces.
For now, let's assign the value "My Report" to both the ID and the Name.
Click on the Type dropdown and assign this Binary Artifact the "Excel" Type.
It's important that we assign the correct tied to our new Binary Artifact, because later,
when reconfiguring the associated View, Dodeca will need to know that it is working with
an Excel template.
Press "OK", and then click the "Import" button to import the Excel template from the local file system.
In our case, the template is saved to the Desktop,
so we'll navigate to the Desktop,
select "MyReport.xlsx", ".xlsx" being the Excel 2007 file format,
and then click "Open".
You'll notice that as the template is imported, Dodeca will populate the property grid with
a few properties.
For this type of object, most of the properties are read-only, and they represent values associated with
the view that are stored in the database, including the encoded binary data of the template itself.
Now that the Binary Artifact has been imported,
go ahead and press "Commit", and then press "Yes" to confirm that you wish to save the changes
and store the Excel template in the Dodeca database.
At this point, our Excels template has been imported and is ready for use in Dodeca.
The next step in the process is to create a new View definition.
Remember that a View definition is the set of properties that comprise a View, telling it
everything about the data to be retrieved,
which Connections and Binary Artifacts to use, as well as how to behave.
To create a new View, we'll start by opening the Views Metadata Editor.
From the Dodeca menu, navigate to Admin > Views.
Create a new View, and assign the value "My Report" for both the ID and the Name.
Note that you can assign any ID and Name that you wish, but for the sake of consistency,
we've chosen to use the same values assigned to the Binary Artifact that we plan to associate
with the View.
The View type is a very important property, because it determines the set of properties that will
be available for us to configure within the Views Metadata Editor.
In this case, we have an Excel template that requires Essbase operations,
so we'll choose the "ExcelEssbase" View Type from the dropdown.
Doing this will give us access to the set of properties that are relevant for an
Excel template that uses Essbase operations.
Go ahead and press "OK" to create the new View.
The properties for the new View will be displayed in the Metadata Editor to the right.
There are many configurable properties available,
but in order to get the view up and running, we'll only have to configure few of them.
Start by clicking the "Required" button located at the top of the Settings panel.
Clicking the "Required" button filters the list of available properties down to those required
in order to run the View.
You'll notice that there are actually very few properties required in order to run an
"ExcelEssbase" View in Dodeca.
First, under the "Essbase Connection" properties category,
select an "EssbaseConnectionID" from the dropdown.
In this case we'll be using the "Sample.Basic" Connection imported in an earlier video tutorial.
Go ahead and select "Sample.Basic" from the dropdown,
and then click on the "EssbaseLoginServiceObjectTypeID" property.
This property tells Dodeca how to prompt the end-user for his or her Essbase credentials.
Select the "EssbaseLoginDialog" property value from the dropdown.
This property value tells the View to present an Essbase connection dialog that provides
the name of the Essbase server, application, and database,
and it allows the end-user to enter his or her credentials.
Under the "Excel Template" properties category,
select the "ExcelTemplateBinaryArtifact" property and click the button to the right.
This dialog allows you to specify the Binary Artifact that you wish to associate with the View.
Choose the "My Report" Excel template, and then press "OK".
Under the "UI" properties category,
select the "ViewToolbarsConfigurationID" property,
and choose the "Essbase View Standard Limited" configuration ID.
Remember that toolbars, like most objects in Dodeca, are comprised of metadata.
This means that you can easily modify existing Toolbars, as well as create new ones from within
a Dodeca Metadata Editor.
We'll cover this in the later tutorial,
but for now, the "Essbase View Standard Limited" Toolbars Configuration will provide all the
functionality we need.
The naming convention used for the Toolbars shipped with Dodeca is as follows:
"Essbase" indicates that the Toolbar provides Essbase operations.
"View" indicates that the Toolbar is for use within a View.
"Standard" indicates that the Toolbar will be displayed inside of the View Tab,
as opposed to on the Main Menu.
Let's take a second to discuss the relevant Toolbars Configurations for providing Essbase functionality.
"Essbase View Main All",
because it is a "Main" Toolbars Configuration, provides all available Essbase operations
on an Essbase Menu, which is integrated into Dodeca's Main Menu.
"Essbase View Main Limited", again, provides an Essbase Menu integrated into the Main Menu,
but it provides only limited Essbase operations to the end-user, such as Retrieve and Send.
"Essbase View Standard All",
because it's a "Standard" Toolbars Configuration, will be displayed with the report inside of
the View Tab and provides all available Essbase operations.
"Essbase View Standard limited", again, is displayed inside of the View Tab,
and it provides the same limited set of Essbase operations mentioned earlier.
For most views in Dodeca, the "Standard" Toolbars are used to provide Essbase operations and
At least in our experience, the "Standard" Toolbars are the more commonly-used of the two types.
We selected "Essbase View Standard Limited", because at this point, we don't intend for the
data to be drilled or pivoted, and the "Limited" set of operations will provide
all the functionality we need.
Finally, select the "WindowsViewUIObjectTypeID" property.
This property specifies the user interface that will be displayed for the View.
Select "WorkbookView" from the dropdown to the right.
The "WorkbookView" property value tells the View to display a single workbook.
So far, so good.
At this point because we have already added a few Dodeca-specific elements to our
template, such as Tokens and Retrieve Range,
we'll need to configure few additional View properties in Dodeca.
To expand the lists of properties to those commonly used,
press the "Common" button at the top of the Settings panel.
Scroll to the top of the panel and find the "Behavior" properties category.
Select the "RetrievePolicy" property, and choose "RetrieveRanges" from the dropdown.
This tells Dodeca that my template contains at least one Retrieve Range name,
and that it should look for those Retrieve Ranges when it performs an Essbase retrieve operation.
In our case, the named range is "Ess.Retrieve.Range.1".
We also want to configure some of the available "Essbase Options" for the View.
For instance, we can use the "MissingLabel" property to specify what will appear in place
of missing values returned by Essbase operations.
For missing values,
I don't want to see a "#Missing" label.
I'd prefer to see a numeric zero instead, so let's type "0" for our "MissingLabel".
For this particular report, let's display aliases instead of member names.
For the "UseAliases" property value, select "True".
There are currently no formulas in the template, so at this point,
we don't need to change the default value of "False" for "RetainOnRetrieval".
Finally, we do want to configure Selectors,
so in the "Selectors" properties category,
click on the "SelectorConfiguration" property.
Press the button on the right to display the "Configure Selectors" dialog.
Our Excel template contains Tokens for the Product, Measures, and Scenario dimensions.
Because we created Selectors for those dimensions in the previous tutorial,
now all we need to do is choose them from a list of Available Selectors on the left side of this dialog.
Choose the "Product" Selector from the list and click ">" button to add it to the View.
Then add the "Measures" and "Scenario" Selectors in the same way.
For the purposes of this tutorial, the default Selector configurations will be just fine,
so go ahead and press "OK" to add the chosen Selectors to the View.
At this point, our report is functionally complete, and we can now test the View.
Go ahead and press the "Commit" button,
and then press "Yes" to confirm that you wish to commit the changes and to save our View
to the database.
To test the View,
click the "Preview" button in the center pane.
You'll be prompted for your Essbase User Name and Password.
Notice that the Server, Application, and Database names are displayed, because we used
the "EssbaseLoginDialog" for our "EssbaseLoginServiceObjectTypeID" property.
Enter your Essbase credentials and then press "OK".
Select a Product by clicking on the Product Selector.
This will open a tree view populated by the members of the Product dimension.
I'll go ahead and select "Cola".
Notice that when you hover over a member in the tree, a tooltip, which is called a Membertip
will be displayed that shows the Name, Alias, Generation, Level,
and Shared Status of that member.
There are a number of additional pieces of information that can be optionally shown to users.
The most popular pieces of additional information that can be shown in Membertips are UDAs
and Attributes.
After you select a product, select a measure and a scenario.
I'm selecting Sales and Actual, but
you can make any selections you wish.
At this point, we can build the View, so go ahead and click the "Build View" button to do so.
We now have a Report which is filtered, based on our selectors,
"Cola", "Sales", and "Actual".
If you wish to see the report for another product, you can either click on the
Product Selector, here,
or you can choose the Product Selector from the tabs displayed at the bottom of the panel
on the right.
I'll choose "Diet Cola" and then click the "Build View" button to see the new report.
So now I have the same report for "Diet Cola"
or "Caffeine Free".
If you wish to do so, you can press the Auto-Build button to turn "Auto-Build On".
This tells Dodeca to build the View every time a new selection is made.
At this point, we've imported our Excel template as a Binary Artifact and created, configured,
and tested a new View using that template.
The next step is to make the View available to end-users.
To do so, we'll start by opening the View Hierarchies Metadata Editor.
From the Dodeca menu navigate to "Admin > View Hierarchies".
A View Hierarchy is an object that provides the full list of Views displayed to users in the
Runtime View Selector.
The Runtime View Selector is located over here on the left.
Hierarchies are assigned to individual Applications.
The default Hierarchy, "Standard", has been assigned to the Application we're currently running,
which is the ADMIN Application for the Tenant FINANCE.
In Dodeca, you can control which views are available to end-users by assigning specific
Hierarchies to different Applications.
Hierarchies are assigned using the Applications Metadata Editor,
which is located under "Admin > Applications" on the Dodeca Menu.
If you select the ADMIN Application, "Dodeca Administrator Console", you can find the View
Selector properties category,
which contains all of the Application
properties that control the View Selector.
I'll go ahead and filter the list of properties by pressing the "Common" button.
Under the "View Selector" properties category, you'll see the "HierarchyID" property.
This property of the Application object defines which Hierarchy will be displayed.
The Metadata Starter Kit shipped with Dodeca contains only the "Standard" Hierarchy,
so that's the default Hierarchy used for our initial ADMIN and USER Applications.
At this point, the "Standard" Hierarchy is empty,
so let's go ahead and add our newly created--
At this point, the "Standard" Hierarchy is empty,
so let's add our newly created Essbase Excel View, "My Report".
Start by clicking on the "Standard" Hierarchy to expand it.
The first thing we need to do is add a Category to the Hierarchy.
A Category is basically just a group of Views on a Hierarchy.
You can think of Categories sort of like Parent Members on an Essbase Outline.
Right-click on the expanded "Standard" Hierarchy, and then select "New Item" from
the context menu that appears.
Categories are a type of Item on the Hierarchy in Dodeca.
Go ahead and assign an ID and Name to the new Category.
We'll call it "Reports".
Then, select "Category" from the Item Object Type drop-down and press "OK".
Next, right-click on the newly added "Reports" Category, and then select "Add View"
from the context menu that appears.
In the dialog, select "My Report", and then click "OK".
Now that our View has been added to the "Standard" Hierarchy,
press the "Commit" button at the bottom and, then press "Yes" to confirm that you wish to
save the changes to the database.
After you commit the changes to the "Standard" Hierarchy,
if you right-click anywhere inside the View Selector and then select "Refresh" from the context menu,
the Hierarchy will be re-read from the server
and the View Selector will be populated with the changes.
You'll now see the "Reports" Category displayed, along with the "My Report" View that was added to it.
This is typically how available reports are displayed to and selected by end-users.
Go ahead and run "My Report" from the View Selector by clicking on it.
You'll notice that the Dodeca Selectors remember the member names that were
selected the last time you ran the report.
Press the "Build View" button, and congratulations, your EssbaseExcel View has
successfully been deployed.
To review the basic steps involved in creating an EssbaseExcel View, we:
1) Made some Dodeca-specific modifications to an Excel template,
adding Tokens and a Retrieve Range name.
2) We imported that Excel template into Dodeca as a Binary Artifact.
3) We created a View and then defined the values for a few required and commonly-used properties.
4) We tested the View.
And finally,
5) We deployed the report by placing it on a View Hierarchy.