Section 2: Quick Tours

The first necessary step in setting up a new application is choosing a tenant code under which to store metadata. The tenant code is a unique ID that separates applications metadata from the metadata of other applications which may be deployed from the same Dodeca server.  Technically speaking, the tenant code is one of the primary keys in the main database table underlying the Dodeca system and is intended explicitly for separating the metadata of significantly different applications which may be in the system.

The difference between a tenant code and an application for which there is a metadata editor can be somewhat confusing. Think of a tenant code as a major segregation between the different applications to be deployed while an application is a minor segregation. A minor segregation means that there are relatively minor differences between the applications but many pieces of metadata, including View definitions, Toolbars, Essbase Connections, Selectors and Selector Lists that may be shared between the applications. It may be easier to see the differences through an example.

Assume an Administrator wishes to deploy Dodeca to their Finance organization and requires having several different types of users in the application. They may want to designate a few users as administrators, some as power users and still others as data input users. These users will be reading data from the same Essbase databases and may even see many of the same views. In this case, the Administrator may choose a tenant code such as FINANCE and, within the tenant code, configure three different applications, ADMIN, POWERUSERS and INPUT. The name of the tenant and application codes can be anything but generally they are descriptive.

Now assume that this same administrator also wishes to use Dodeca in their Marketing department. Should they use the same tenant code or set up a separate tenant code?  If they do not plan to reuse any of the View definitions, Essbase Connections or other artifacts for the Marketing users, they should use a different tenant code. Otherwise, the FINANCE tenant code could be used by adding one or more marketing oriented applications to the single tenant code.

After choosing a tenant code, it is necessary to initialize this new tenant code in the Dodeca system. The initialization process is used to seed the metadata database with basic metadata that every Dodeca application uses. The metadata that is required includes toolbars and at least one application object. Fortunately, Dodeca ships with a collection of metadata called the Metadata Starter Kit that includes all of the pieces of metadata necessary to start a typical application.

The Dshell is installed with the Dodeca server installation and initializes the tenant code. To initialize a tenant code, follow the steps described in this section.

Click on dshell.cmd to open the Dshell Command window.

Enter the command “connect dodeca-hibernate.properties”

The image above indicates when a successful connection to the database has been completed.

The command “list-tenants” will show what tenants are available in the database.

In the metadata directory, samples of metadata are available for testing. For this example, “metadata_starter_kit” will be used.

Enter the following command to create a Tenant along with a metadata zip file. “create-tenant <TENANT> ../metadata/metadata_starter_kit.zip"

Enter the command “list-tenant” again to see the new Tenant created.

On a web browser, enter the following URL “http://<server>:<port>/dodeca/metadata-service". Click on “Launch Smart Client” hyperlink.

The ClickOnce launcher dialog appears while the Microsoft .NET ClickOnce process checks the computer for the proper installation of the Dodeca client.

If the Dodeca application needs to be installed on the computer, the following Application Run dialog displays. Click Run to install the Dodeca client.

The Publisher field reflects the name of the organization that owns the digital signing key. If you use the temporary key provided by Applied OLAP, Inc., the value of the Publisher field will be printed as Unknown Publisher.The installation proceeds and updates its progress in the dialog. The amount of time required to install Dodeca on a client workstation varies based on network speed.

The install only occurs on the first access to the product and when an updated version is available from the server.

After the installation is complete, the window below will appear.

Enter your Tenant and Application information and click on “Launch Dodeca Application.”

The Smart Client will launch for the desired Tenant.

Open Microsoft Internet Explorer and paste the URL copied in the previous step into the address bar.

Press Enter or click the Go To button to launch an instance of the ADMIN application for the tenant FINANCE.

The ClickOnce launcher dialog appears while the Microsoft .NET ClickOnce process checks the computer for the proper installation of the Dodeca client.

If the Dodeca application needs to be installed on the computer, the following Application Run dialog displays. Click Run to install the Dodeca client.

The installation proceeds and updates its progress in the dialog. The amount of time required to install Dodeca on a client workstation varies based on network speed.

Once the installation is complete, the Dodeca Smart Client launches.

Once the Dodeca Smart Client has launched, the next step is to configure one or more Essbase Connections for use in the application. The easiest way to configure Essbase Connections is to use the Import Essbase Connections wizard.

To use the wizard, select Admin > Quick Start Utilities > Import Essbase Connections.

The wizard starts and displays the following dialog:

Enter the URL to the Dodeca Essbase service installed on the server along with a Username and Password. Optionally, enter a Connection ID pattern for use to name connections. When those items have been properly entered, click Connect to get a list of servers, applications and databases available at the given Essbase service location.

Select the check box for each connection you want to import into Dodeca.

If you want to select all of the available connections, click the check box on the title bar of the grid selector.

Click Import to create the specified connection objects and click OK when prompted.

The Essbase Connections have now been created and stored in the metadata database.

Click Close to exit the wizard.

The Essbase Connections metadata editor opens in a new tab and displays your newly imported connections.

The next step in the configuration process is to create both Selectors and Selector Lists. To reiterate, Selectors are the objects that define the dimensions or sources from which users may make selections to filter their views. Selectors also define the tokens that may be used to substitute into worksheets, Essbase report scripts, Essbase calc scripts and SQL scripts. Additionally, Selector Lists provide both the contents of the list from which a user may choose in a selector and the configuration of the user interface.

The necessary infrastructure to create a view in Dodeca is complete.

Now is a good time to review the processes you followed to set up a new application. Preparing the Dodeca application for deployment required following these steps:

Views are the objects in Dodeca that bring together the numerous types of objects to form the content that end users see. Views typically use a number of different objects including Essbase Connections, Selectors and Selector Lists, Microsoft Excel templates (stored as Binary Artifacts), Toolbar Configurations and Workbook Scripts. Views are exposed to users on a View Hierarchy. This section will discuss creating simple Essbase Ad hoc views and progress to building more complex view types.

The simplest type of view to create is an Essbase Ad hoc view. This view type emulates the freeform Excel Essbase add-in functionality. The exception to this comparison is that, with Dodeca, the administrator controls which Essbase server and database are used by the view.

To create a new Essbase Ad hoc view, follow these steps:

To create a new Ad hoc view in the Views metadata editor, select Admin > Views from the Dodeca menu.

Click New, located in the command buttons area located at the bottom of the metadata list panel.

The New View dialog displays.

In this dialog, enter the ID and Name of the new view. The ID is the name under which the view is stored in the database and the Name is the friendly name the user sees. It is recommended that the ID and the Name be the same for ease of administration. For example, use Sample Basic Ad hoc for the ID and Name of an ad hoc view that talks to the Sample Basic database.

The most important property in this dialog box is the View Type. The View Type defines both the behavior of the view that is created and the number and names of properties that are available to configure the view. For example, the Excel Essbase view type in Dodeca needs more information to build the view than an ad hoc view because it needs to know information about the Excel template used and the ranges where data is to be retrieved into the view.

Finally, the Description field is optional and is used primarily as documentation for the purpose of the view.

To create the view, enter the ID and Name as Sample Basic Ad hoc, choose the View TypeAdhocEssbase and optionally, add a Description.

Click OK and the view is added to the metadata list with an icon indicating it has been edited. The new view is also selected and the property grid displays the properties for the new view. The Commit button also becomes enabled.

At this point, the new view exists only in the metadata editor and has not been stored in the Dodeca database. After setting some properties in the next section, we will commit the view to the Dodeca database.

To complete the view and make it useful for end users, you must modify some key property values. First, as an Ad hoc view typically doesn’t require the end user to select members for a point of view. It is recommended that you set the AutoBuildOnOpen property, located in the Behavior category, to True. Setting this property to True tells Dodeca to automatically build the view when the view opens. A False setting would require the user to click Build View to perform the initial build.

The ad hoc view also needs information about what Essbase database to connect to and how to log in to Essbase. The EssbaseConnectionID property is used to look up the connection metadata that defines the Essbase connection settings for the view. In our example, select the dropdown and choose the connection ID for the Sample Basic database (in the example, essprod01.Sample.Basic).

The EssbaseLoginServiceObjectTypeID property specifies the object type ID of the login service that obtains the credentials used to sign onto the Dodeca Essbase service. This value is required as Dodeca has no hard-coded methodology for logging into Essbase but rather uses snap-in services architecture in order to accommodate nearly any requirement a company may have for authenticating user credentials in Essbase. Dodeca ships with one login service, the EssbaseLoginDialog service, which prompts the user for a username and password that is then validated against Essbase.

The Essbase Options category has a number of properties that are often changed. The properties in the Essbase Options category control whether the user gets to change any of their Essbase options and, if so, which options they get to change. Further, the administrator can control any properties the user isn’t allowed to change.

Because this is an Ad hoc view, typically the user is able to control at least some of the available Essbase options. Set the AllowUserEssbaseOption property to True to allow the user to control the Essbase options. When this property is set to True, the Essbase Options button on the Essbase toolbars is enabled and when it is clicked, the Essbase Options dialog displays.

There are some Essbase Options that are better controlled by the system administrator. Dodeca allows administrators to configure the Essbase Options dialog in order to include or exclude only the Essbase Options they want users to see. The AllowedUserEssbaseOptions property controls the options displayed in the Essbase Options dialog. For example, if the Essbase database is not using Dynamic Time Series functionality, the option to choose Dynamic Time Series related properties can be removed from the Allowed User Essbase Options dialog. Another use for this property is to standardize properties such as the Missing Label and the No Access Label to provide consistency for users.

To configure the properties that are available, click the dropdown for the AllowedUserEssbaseOptions property and a version of the Essbase Options dialog displays with a check box next to each item in the dialog. Uncheck the check box next to any items that should not be displayed to the user. At runtime, the Essbase Options dialog will not contain those options.

The diagram shows what the dialog will look like after the Dynamic Time Series and Replacement categories have been unchecked.

Click OK to save the property changes.

In this example, the Missing Label has been suppressed from the Essbase Options dialog. When a property value is suppressed, the administrator has absolute control over the property value used at runtime. In the property grid, locate the MissingLabel property and set its value to 0.

The following diagram displays the Essbase Options category:

Scroll down to the UI category to see the final properties that need to change for the ad hoc view. The UI category contains information about the menus, toolbars and display controls for the view.

The most important property in this category is the WindowsViewUIObjectTypeID. This property is the object type of the Windows form that contains the user interface controls used to display the view data. Due to the snap-in architecture of Dodeca, the view does not have a hard-coded value for the display controls but rather a configurable value. Further, you can expand this object type via the Dodeca extensibility model to implement customer-specific business rules. For our Ad hoc view, choose the WorkbookView object type to display the data in a spreadsheet control.

Toolbar configurations in Dodeca are configurable as metadata in the Toolbars Configurations metadata editor. Because toolbar configurations are metadata, they are completely controllable within the Dodeca environment. The Dodeca Metadata Starter Kit contains several toolbar configurations. There are two main toolbar properties that are normally configured in a view.

The first of the toolbar properties in the property grid is the MergeableToolbarsConfigurationID. This toolbar definition defines the view specific toolbar and menu items that are merged with the application level toolbar and menus. Choose the toolbars configuration named Essbase View Main All for the ad hoc view. In the Toolbar Preview shown below, the first view shows additional menu items on the File menu and the second shows the Essbase tools.

The second toolbar property, the ViewToolbarsConfigurationID, defines the view-specific toolbar and menus that are displayed within the view window itself. For this property, choose the toolbars configuration named Essbase View Standard All. This toolbar contains view level tools including various Essbase operation tools.

The UI category is now complete.

At this point, the basic configuration of the ad hoc view is complete.  Click Commit to save the changes to the Dodeca metadata database and then close the Views metadata editor.

The final step to expose the view to users is to place it on one or more view hierarchies. To place the view on a view hierarchy, first open the View Hierarchies metadata editor from the Admin menu.

The View Hierarchies metadata editor will open and show the hierarchies currently in the system. In the graphic below, the Standard hierarchy is the only one present in the system.

When the metadata editor opens, the hierarchies are displayed collapsed. Click the down arrow expansion icon in the upper right hand corner of the hierarchy to expand it open.

Once the hierarchy is open, the items in the hierarchy will be displayed and additional command buttons will become enabled.

There are two different types of items that can be added to a hierarchy; categories and views. Category Items are used for grouping the views appearing in the Hierarchy, allowing views to be added as “children” to the category items (left column in below diagram). A category can also be grouped with a category as a sub-category with views added to it (right column in below diagram).

To add a category, press the New Item button.

Type in the ID and Name for the new item.

Note that the Item Object Type defaults to Category. The Item Object Type is another type of object that may be customized via the Dodeca extensibility model; Dodeca ships with the Category Item Object Type.

Click OK to add the new category.

The Ad hoc Views category will now display in the View Hierarchies list.

Now that the category has been added, a view can be added to the category.

To add a view, click the Add View button.

The Select View(s) to Add dialog will be displayed.

Select the Sample Basic Ad hoc view and click OK.

The dialog closes and the selected view is added to the hierarchy.

Click Commit button to save the hierarchy to the metadata database and close the View Hierarchies metadata editor.

Test the new view by right-clicking on the View Selector and selecting Refresh from the context menu.

The Refresh button forces the hierarchy metadata to be re-read from the server and updates the display to show the newly updated hierarchy.

Click on the new view to launch it. During the launch process, Dodeca queries the metadata database for all of the metadata required to create and run the view. In the case of an ad hoc report, Dodeca will request metadata for the view definition, the Essbase connection and the toolbars configurations objects used in the view.

An Essbase login dialog displays (as provided by the EssbaseLoginDialog specified in the EssbaseLoginServiceObjectTypeID property configured earlier).

Enter a valid Essbase username and password and click OK or press the Enter key.

The view will connect to the Sample Basic database, as specified in the configuration, and return the default top-of-dimension Essbase retrieval.

The simple ad hoc view is complete. To review, the creation of the new Essbase Ad hoc view followed these steps:

The next section will expand on the steps taken in the ad hoc view and will introduce the concept of using Microsoft Excel to create templates for Dodeca views.

Microsoft Excel templates must conform to typical Essbase retrieval layouts to be retrievable using the standard Essbase Query By Example (”QBE”) paradigm. QBE is a very powerful concept as it makes it very easy for business users to lay out a Microsoft Excel worksheet which places data into the format they need. QBE templates are created by typing the Essbase member names into the worksheet in a specific manner. You need to be familiar with the rules QBE follows before creating a Microsoft Excel template. Here are some basic Essbase retrieval rules and terminology used in this guide:

Consider the following simple Essbase retrieval in Microsoft Excel from the Sample Basic database which contains five dimensions. In this example, three dimensions are oriented in page orientation, one dimension is oriented in column orientation and one dimension is oriented in row orientation. Collectively, dimensions in an orientation are often referred to as fields as in Page Fields, Column Fields and Row Fields.

When the Essbase engine parses the grid, it looks at the cells selected in the worksheet, and if no cells are selected, looks at the entire used range of the worksheet. While performing the parsing operation, it looks for members from each dimension in the connected database and follows these rules:

Extraneous text may appear within the retrieval as long as it doesn’t resemble an Essbase member name. If Essbase confuses the text with a member name, the parsing algorithm used in the QBE query engine will get confused and will return a Member Out of Place error. Similarly, if a numeric member name is not entered as text by pre-pending the member name with a single quote, the parsing engine will get confused and return a Data item found before member error.

Determining the members represented at data intersections, or data points, in Essbase is generally very easy to understand. That being said, there are some potentially confusing layouts that bear some discussion.

The data intersection depicted in the graphic above shows a block header layout that clearly represents all dimensions in the row and column of the data intersection itself. Sometimes, however, the intersection is not so clear.

These cases can be best illustrated by examples.

What members are represented at this data point? Looking left from the data point, it is clear that South is the member that represents the Market dimension. Looking up from the data point, it is also clear that Qtr4 represents the Years dimension and Budget represents the Scenario dimension. However, the row and column have no members representing the Measures and Product dimensions. Page Fields represent the entire retrieval and thus Measures and Product represent the members from their dimensions regardless of their position on the grid.

Now, consider this layout and try to determine the members for the indicated data point.

In this case, the members West and Variance are the only two members in the row and column of the data point, so those two members are easy. Further, Sales is the only member from the Measures dimension and thus is a page field; it is automatically included. But what about the members from the Product and Year dimensions? The rules for those dimensions get a little more involved and are made more difficult. One reason it is so difficult is the rules for determining data point members for row and column fields are a bit inconsistent. The rules are:

This inconsistency can easily cause confusion with its ambiguous layout. Fortunately, there is an easy way to resolve it via the use of block headers. Block headers explicitly list the members for each intersection in each row and column. Below is the confusing grid shown above after being converted to use block headers. Note the correct variance is now retrieved in the indicated data point.

There is anecdotal evidence that an Essbase retrieve using block headers performs slightly slower than one not using block headers (i.e. pyramid headers). However, the risk of a user relying on an incorrect interpretation of the numbers is a huge price to pay for a slight performance improvement.

Dodeca leverages the power and reduces the limitations of QBE. Dodeca removes or minimizes the effects of the following limitations because:

Dodeca allows the implementation of multiple retrieval ranges per worksheet. This is achieved by the use of reserved range names in the worksheet that are used to limit the scope of the cells that are subject to the Essbase retrieval process. In the Essbase Excel add-in, multiple retrievals require users manually select the retrieval range before retrieving data. Alternatively, multiple retrievals may be automated in the Essbase Excel add-in by writing VBA code to retrieve each range. The ability to retrieve into multiple ranges in Dodeca is built into the system and requires no special actions by an end user to work properly.

Each reserved range name in Dodeca has a optional corresponding reserved range name to indicate the Essbase Connection to use for each retrieval range. This gives Dodeca the flexibility to retrieve data from one or more Essbase databases into a single worksheet or workbook quickly and easily. A workbook or worksheet may have as many retrieval ranges as necessary to meet business needs.

Having reviewed the basics of how Essbase works, it is now possible to create an Excel template that can be used in Dodeca. For this first template, assume the need for a template that allows users to create a quarterly Income Statement view. Further, the user should be able to select the Product, Market and Scenario for the view. To create this template, follow these steps:

Excel templates in Dodeca are called Binary Artifacts and are stored in the Dodeca metadata database. Binary Artifacts are managed in Dodeca in the Binary Artifacts metadata editor. To import the Excel template into Dodeca select Admin > Binary Artifacts from the menu as shown below.

Once the Binary Artifacts metadata editor opens, click New, located at the bottom of the metadata list panel.

Clicking on the New button launches the New Binary Artifact dialog.

Enter the ID and Name of the new Binary Artifact to create. The ID is the name under which the artifact will be stored in the database and the name is the "friendly" name the administrator will see when using the artifact.

It is recommended that the ID and the Name be the same for ease of administration. In this case, for example, the name Income Statement can be used to reference the Income Statement binary artifact create in this section to hold the Income Statement Excel template.

The Type property, required for this binary artifact is Excel. The Type property is used as a filter in some of the metadata editors so it is quite important to set it properly. Setting the Type to something other than Excel would prevent selecting this binary artifact as the Excel template in the view definition.

Click OK and the new Binary Artifact object is created.

At this point, the shell artifact in which the Excel template will be stored in the Dodeca metadata store has been created. To get this newly created Excel template into the Dodeca database, press the ‘Import’ button located in the command buttons area.

This will prompt a standard Windows File Open dialog. Navigate to your saved Excel template file, select it and press the ‘Open’ button. The Excel template will be imported into the Binary Artifact metadata editor.

The Excel file is imported in an encoded format. Press the ‘Commit’ button and the Binary Artifact, along with its enclosed Excel file, will be saved to the Dodeca metadata store. Once the Binary Artifact has been committed to the metadata store, the Excel file will be available for use in an Excel Essbase view.

To create a new Excel Essbase view in the Views metadata editor, select Admin > Views.

Once the Views metadata editor opens, click New located in the command buttons area at the bottom of the metadata list panel.

The New View dialog is displayed.

In this dialog, enter the ID and Name of the new view to be created. The ID is the name under which the view is stored in the database and the name is the "friendly" name the user sees. It is recommended that the ID and the Name be the same for ease of administration.  In this case, for example, the name Income Statement could be used as we are going to create an Income Statement view.

The View Type for an Excel Essbase view is ExcelEssbase. This view type is similar in some ways to the AdhocEssbase type but adds properties that are unique to using a Microsoft Excel template in the view.

Click OK and the view is added to the metadata list with an edited icon, the new view is selected, and the property grid shows the properties for the new view. Further, the Commit button becomes enabled. At this point, the new view exists only in the metadata editor and has not been stored in the Dodeca database.

To complete the view and make it useful for end users, some key property values must be modified.

Before we continue, press the 'Common' button at the top of the settings panel to limit the visible settings to those commonly used. The settings now available will be sufficient for our purposes here.

First, as a retrieve range was used in the template, Dodeca needs to be told to look for the retrieve ranges. In the Behavior category, set the RetrievePolicy property to the value RetrieveRanges.

Like the ad hoc view we configured earlier, the Excel Essbase view also needs information about which Essbase database to connect to and how to login into Essbase. Select the EssbaseConnectionID for the Sample Basic database and set the EssbaseLoginServiceObjectTypeID property to use the EssbaseLoginDialog service. Also, in the Essbase Options category, set the MissingLabel property to 0 and the UseAliases property to True.

Scroll down to the Excel Template category. The ExcelTemplateBinaryArtifact property ties the Excel Template you stored earlier as a Binary Artifact object to your Excel Essbase view.  To select the template, select the ExcelTemplateBinaryArtifact property, then click the dialog indicator button located at the right side of the property value column as shown below.

When you select this button, the Select Excel Template Binary Artifact dialog displays. In this dialog, select the Microsoft Excel template (Income Statement in the example).

Select the Income Statement version 1 and click OK. The template information is then pulled into the View definition.

Next, scroll down to the Selectors category and select the SelectorConfiguration property. Again, a dialog indicator button displays.

Click the dialog indicator button and the Configure Selectors dialog displays.

This dialog allows the Administrator to select the Selectors to display for this view along with the Selector Lists that will be used to configure the Selectors. For this view, the Market, Product and Scenario dimensions were tokenized allowing the user to select the values used for those dimensions at runtime. In this form, it is necessary to add selectors for these dimensions.

To add a selector, follow these steps:

. Select the selector from the list on the left side of the dialog.

In this case, select the Market dimension and the arrow button becomes active.

Click the arrow button ( > ) and the Market dimension is added to the Selectors list.

If a Default Selector List for the dimension has not been created, then choosing a Selector List is mandatory. In this case the Selector Lists were imported and they were automatically marked as the default Selector Lists for their respective dimensions, so there is no need for this step.

Repeat this process for the Product and Scenario dimensions and the dialog should look like the one depicted below.

Click OK to complete the Selector configuration.

Scroll down to the UI category to see the final properties that require change for this Excel Essbase view. Set the MergeableToolbarsConfigurationID to Essbase View Main Limited, the ViewToolbarsConfigurationID to Essbase View Standard Limited and the WindowsViewUIObjectTypeID to WorkbookView.

The Essbase View Standard Limited toolbars configuration is a similar to the toolbar that was selected for the ad hoc view except that it does not include tools for some Essbase operations such as zooming in, zooming out and pivoting. The user is not authorized to perform those operations on the template that was created, so a different toolbar will be used so those operations are not an option.

The UI category is now complete.

Click Commit to commit the view definition to the Dodeca metadata database and close the Views metadata editor.

Just like the Ad hoc view, the final step necessary to expose the view to the user is to place it on one or more view hierarchies. The steps are the same as those used to place the Ad hoc view on a hierarchy:

To test the new Essbase Excel view, right click on the View Selector and select Refresh from the context menu.

The Refresh button forces a re-read of the hierarchy metadata stored on the server (which, of course, was just modified) and displays the newly updated hierarchy.

Click on the new view to test it. Dodeca opens the view and allows selections to be made before the view is generated.

Click Open Selector on the Market Selector to choose a Market for your view. Expand the tree-view of Essbase members and select a value for the Market dimension.

Once a selection has been made, the value selected highlights in the tree-view and is also placed into the Selector text box. Repeat the process for all of the other dimensions for which Selectors are displayed.

Click Build View and the view is generated.

The new Essbase Excel view is now complete and ready to be deployed to users. The same general steps used in creating the Ad hoc view were used to create the Essbase Excel view:

To gain more screen real estate, unpin the selector panels.

Unpinning the selectors causes them to collapse into the border of the window. To show the selector, move the mouse pointer over the collapsed window and the window expands open and allows selections to be made.

Once you have built and tested the Views and View Hierarchy, there are typically very few steps remaining before deploying the application to users. Most notably, the end users normally do not have access to the Admin menu. In Dodeca, it is easy to use all of the views, view hierarchies and other artifacts and package them in a manner that eliminates the end user’s ability to perform any process that appears on the Admin menu. In Dodeca, the Application configuration controls this behavior and, in fact, the metadata starter kit ships with the USER Application object that is configured for end users.

To view the USER application object, select Admin > Applications to open the Applications metadata editor.

Select the Dodeca User Console application from the metadata list.  Dodeca retrieves the metadata from the selected application from the metadata store and presents it in a property grid.

The most important properties to note in the information section of the property grid are the ID and the Name properties.

The Name property is the name displayed in the metadata list of the metadata editor.

The ID property has two very important functions. It is the value that is stored in the relational metadata store and is also used in the URL which launches Dodeca to indicate the Application object which configures the smart client for the user.

The ApplicationCaption property is used as the caption in the title bar of the smart client window.

Scroll down in the property grid to see the properties in the UI category. The UI category determines the menus and toolbars used in the smart client window. Specifically, the ToolbarsConfigurationID is the ID of the ToolbarsConfiguration object that will be the base menu and toolbar Dodeca uses. Further, the UseDefaultAdminToolbarsConfiguration configures Dodeca to show the built-in administrative toolbars only when administrative toolbars are desired.

To configure an end user application, select the View Console in ToolbarsConfigurationID and set the UseDefaultAdminToolbarsConfiguration property to False.

Scroll down to the View Selector category. Note the ViewSelectorObjectTypeID property is set to ViewSelectorTree. This property allows administrators to configure the look and feel of the view selectors used by their users. Further, the View Selector implementation may have its own set of properties which are used to configure the View Selector.

The View Selector configured for the Dodeca User Console uses the ViewSelectorTree implementation which renders the selected View Hierarchy in a tree-view. Further, the AutoExpandNodes property is set to True which causes the tree-view to open in an expanded format.

Once you have committed any changes made in the Applications metadata editor, use the following URL to launch the end user application.

Note that the application parameter on the URL indicates the ID of the desired Dodeca User Console application. Using this URL, Dodeca opens and appears somewhat different.

The Dodeca User Console application does not have an Admin menu option and has a tree-view based view selector.

Retrieve ranges were added to the Income Statement template (reference page 59) to differentiate the data within the range from other tokenized information in the template. Following this same reasoning, send ranges can now be added to the template.

First, export the template back to the computer’s desktop to edit it in Microsoft Excel. To do this, from the Dodeca Administrator Console select Admin > Binary Artifacts.

Click on Income Statement to open its editor and click Export.

When the Export dialog opens, save the .xls file to the desktop.

The template opens in Microsoft Excel.

NOTE: When the template was created, row 5 was hidden.

Make row 5 available again, and then highlight A-5 through F-14

In the Microsoft Excel toolbar select Insert > Name > Define to open a dialog.

In the dialog, type in Sheet1!Ess.Send.Range.1 in the Names in workbook box and click Add. Click OK.

Now that you have edited the template, go back to the Dodeca Administrator Console and into the Binary Artifacts metadata editor.

This time, click Import. In the dialog select Income Statement and click Open to import the edited template. Once the edited template is imported back into Dodeca, commit the changes in Binary Artifacts and close it.

Open the Views editor and click on Income Statement. There are some necessary changes to be made in order to facilitate these edits. In the Behavior Properties set the AllowSend property to True. This simply turns on the Send function. Still in Behavior Properties, set the SendPolicy to SendRanges. This prompts Dodeca to look for the Send Range name to limit which cells are sent to the database. Otherwise the entire worksheet would be sent.

Commit the changes.

To define or edit a SQL Connection, select Admin > SQL Connections from the Dodeca menu.

Click New from the SQL Connections metadata editor.

Define an ID and Name for the connection and click OK.

In the properties section for the newly defined SQL Connection, specify the following information:

Test the connection using the Test Connection…​ button, and then Commit the connection.

To define or edit a SQL Passthrough Dataset, select Admin > SQL Passthrough DataSets from the Dodeca menu.

Click New from the SQL Passthrough DataSets metadata editor.

Define an ID and Name for the SQL Passthrough Dataset and click OK.

In the properties section for the newly defined SQL Passthrough DataSet, specify the following information:

SessionCachingEnabled - Controls whether the data generated by the SQLPassthroughDataset queries are added to the session cache.

To define one or more SQL Queries, click on the ellipsis to the far right of the Queries property. This displays the Query Editor dialog box.

Click Add to add a new query.

In the Connection properties category, specify the SQLConnectionID of the SQL Connection we just created.

Next, under the SQL category, click on the ellipsis to the far right of the SelectSQL property. This opens the SQL editor dialog box for easy input and editing of a Select SQL statement.

The SQL editor dialog box will look something like the following screenshot.

For the purposes of this example, we’re using an AdventureWorks sample database for Microsoft SQL Server 2005. Make sure that you update your SELECT statement to work with the relational data you wish to query, and then click OK.

In the Data Table properties category, specify the DataTableName.  This is the table from which you are querying your relational data. Next, under the PrimaryKey property, click the ellipsis to the far right of the Columns property. This opens the String Collection Editor.

Specify the name of the column representing the Primary Key for the table being queried, and then click OK.

Click OK to add the query to the SQL Passthrough Dataset.

Click Test Data Set to test the newly added query.

If the query executes successfully, the SQL Passthrough DataSet Preview dialog will appear, showing you a preview of the relational data requested by the SQL Passthrough DataSet’s queries.

Press Commit to commit the SQL Passthrough DataSet.

Similarly to creating a template for an Essbase Excel View, we will now create an Excel template that can be used in Dodeca for working with relational data. For this first template, assume the need for a template that allows users to display an Employee Contact Info view. To create this template, follow these steps:

For a step-by-step guide to importing an Excel template into Dodeca, see the "Importing the Excel Template into Dodeca as a Binary Artifact" section of the previous chapter.

For the purposes of this tutorial, when creating the new Binary Artifact, assign a Name and ID of "Employee Contact Information" and a Type of "Excel".

Then, simply import the recently created Employee Contact Info.xls and then click, Commit.

To create a new SQL Excel view in the Views metadata editor, select Admin > Views.

Once the Views metadata editor opens, click New located in the command buttons area at the bottom of the metadata list panel.

The New View dialog is displayed.

In this dialog, enter the ID and Name of the new view to be created. The ID is the name under which the view is stored in the database and the name is the "friendly" name the user sees. It is recommended that the ID and the Name be the same for ease of administration. In this case, for example, the name Employee Contact Info could be used as we are going to create a view for displaying employee contact information.

The View Type for a SQL Excel view is SQLExcel. This view type is similar in some ways to the AdhocEssbase and ExcelEssbase view types, but adds some properties to the view that are unique to using a Microsoft Excel template populated by relational data.

Click OK and the view is added to the metadata list with an edited icon, the new view is selected, and the property grid shows the properties for the new view. Further, the Commit button becomes enabled. At this point, the new view exists only in the metadata editor and has not yet been stored in the Dodeca database.

To complete the view and make it useful for end users, some key property values must be modified.

Before we continue, press the Common button at the top of the settings panel to limit the visible settings to those commonly used. The settings now available will be sufficient for our purposes here.

First, in the Behavior category, set the AutoBuildOnOpen property to True. Because there are no selectors to choose from, this property allows the view to build automatically upon opening.

Scroll down to the Excel Template category. The ExcelTemplateBinaryArtifact property ties the Excel Template you stored earlier as a Binary Artifact object to your SQL Excel view. To select the template, select the ExcelTemplateBinaryArtifact property, then click the dialog indicator button located at the right side of the property value column as shown below.

When you select this button, the Select Excel Template Binary Artifact dialog displays. In this dialog, select the Microsoft Excel template (Employee Contact Info in the example).

Select the Employee Contact Info version 1 and click OK. The template information is then pulled into the View definition.

Next, scroll down to the SQLPassthroughDataSet Ranges category and select the DataSetRanges property. Again, a dialog indicator button displays.

Click the dialog indicator button and the DataSet Range Editor dialog displays.

The DataSet Range Editor is used to associate a SQLPassthroughDataSet with a view and specify the related settings.

To add a DataSetRange, begin by clicking Add.

Under the Behavior category, set the AutoBuildOnViewBuild property to True.

Next, under the DataSet category, set the SQLPassthroughDataSetID property to use the SQL Passthrough DataSet that we defined earlier in Defining a SQL Passthrough DataSet.

Then, under the DataTableRanges category, select the DataTableRanges property. A dialog indicator button displays.

Click the dialog indicator button and the DataTable Range Editor displays.

The DataTable Range Editor is used to define the DataTableRanges that map each DataTable to a sheet range.

To add a DataTable Range, click Add.

Under the Data category, select the DataSheetRangeName property.

The DataSheetRangeName property value must correspond to the name of the range name specified in the Microsoft Excel template created earlier.  See Adding a Retrieval Range for additional information.

The DataTableName property value must correspond to the name of the table from which you are querying your relational data, as specified earlier in the SQL Passthrough DataSet. See Defining a SQL Passthrough DataSet for additional information.

The SetDataFlags property controls how the relational data is copied to the retrieval range. For our purposes, because we wish to insert cells for each row of data retrieved, and because our column headers are outside of the retrieve range, we will specify InsertCells and NoColumnHeaders from the dropdown.

Next, under the Name category and property, specify a name for the DataTable Range.

When the property values have been assigned, click OK to assign the newly created DataTable Range to the DataSet Range and return to the DataSet Range Editor.

Upon returning to the DataSet Range Editor, specify a name for the DataSet Range under the Name category and property.

Finally, click OK to exit the DataSet Range Editor and return to the Views Metadata Editor.

Under the UI Category, set the ViewToolbarsConfigurationID property to SQL View Standard and the WindowsViewUIObjectTypeID to WorkbookView.

Click Commit to save the changes to the view definition, and then close the Views metadata editor.

Just like the Ad hoc and Essbase Excel views, the final step necessary to expose the view to the user is to place it on one or more view hierarchies. The steps are the same as those used to place the Ad hoc view on a hierarchy:

To test the new Essbase Excel view, right click on the View Selector and select Refresh from the context menu.

The Refresh button forces a re-read of the hierarchy metadata stored on the server (which, of course, was just modified) and displays the newly updated hierarchy.

Click on the new view to test it.

The new SQL Excel view is now complete and ready to be deployed to users. The same general steps used in creating the Ad hoc and Essbase Excel Views were used to create the SQL Excel View: