Section 2: Quick Tours

Creating a Basic Dodeca Application

There are several steps to preparing a Dodeca application for deployment. To setup a new application, it is necessary to follow these steps:

  1. Choose a tenant code for the metadata.

  2. Initialize the metadata for the tenant.

  3. Determine the Launch URL for the new application.

  4. Launch the Dodeca client with the new URL.

  5. Create Essbase Connection definitions.

  6. Import dimensions and create Selectors and Selector Lists.

Choosing a Tenant Code

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.

By convention, both the tenant code and the application code are normally expressed in all capital letters with only alphanumeric characters and no spaces.

Initializing the 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 Dodeca Application Setup Utility is installed with the Dodeca server installation and initializes the tenant code. To initialize a tenant code, follow the steps described in this section.

Launch the Application Setup Utility from the Applied OLAP, Inc. entry in the Start menu.

Enter your application server information into the appropriate fields. Click Next and the Application Setup Utility examines the database to look for the required tables.

image

If this is the first time Dodeca has been configured to run against the relational database repository, the database schema validation will fail, and the dialog shown on the following page will be displayed.

When this happens, click Update Database Schema and Dodeca creates the necessary tables in the database.

The schema update will drop and recreate all of the tables Dodeca requires. If the schema tables already exist and have data in them, that data will be lost.  We recommend backing up those tables before proceeding.
image

If Dodeca has been previously configured to run against the relational database repository, clicking the Update Database Schema button wipes out all existing data in Dodeca’s metadata schema tables (including applications, views, property sets, etc.).

If the database schema requires an update, the following confirmation dialog appears. Click OK to confirm the schema update and then click Next to continue.

image
After the schema update is complete, you can click Back and then Next again to force the server to retest your table structure.

Once the tables are present, the next step is to enter a tenant keyword. Enter this keyword into the appropriate textbox and click Next.

image

Next, click Import Metadata from Local Zip File to import the metadata into the relational database.

image

Locate and select the metadata_starter_kit.zip file, then click Open.

image

You can find this file in the metadata subdirectory of your Dodeca Framework installation.

The default location is: "C:\Program Files\Applied OLAP\Dodeca Framework 5.1.0.x\metadata".

On the Import Metadata from Local Zip File form that appears, click the checkbox in the title row of the grid to select all of the metadata in the metadata_starter_kit.zip file.

image

Click Import and the Application Setup Utility populates the tables in the database with the appropriate metadata.

The tenant code shown in the dialog box is the tenant from which the metadata was exported to the zip file.  When imported, the tenant code automatically changes to match the current tenant code.

Once the import is complete, click Close to exit the Import Metadata from Local Zip File form and click Next to proceed to the next screen.

Optionally, Choose a module to update. Modules are usually custom Dodeca extensions that are stored in the metadata. This step is typically not necessary when creating a new tenant or application. Click Next to continue.

image

Click the radio button labeled Select an existing application from tenant “FINANCE” and select the ADMIN Application ID. Then, click Next to continue.

image

No modifications currently need to be made to the ADMIN application settings. Click Next to continue.

image
image

At this point, the initialization of the tenant is complete, and the Launch URL has been computed.

To test and continue configuring the application, click Copy ClickOnce URL to Clipboard, or click on the generated link.

Click Close to exit the Application Setup Utility.

Launching the Dodeca Client

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

image

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

image

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.

image
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.
image

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

image

Creating Essbase Connection Definitions

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.

image

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

The wizard starts and displays the following dialog:

image

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.

The list of servers, applications and databases are limited based on the security credentials of the username entered. We recommend the use of an administrator username for this step of the wizard.

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.

image

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.

image

Creating Selectors and Selector Lists

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.

Importing Dimensions

The easiest way to configure Selectors and Selector Lists is to use the Dodeca Quick Start Utilities Import Dimensions wizard.

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

image

The Import Dimensions wizard launches. It should look something like the dialog shown on the following page:

image

Select an Essbase Connection ID to get a list of dimensions.  Optionally, select the Include Attribute Dimensions check box to see attribute dimensions in addition to the base dimensions.

A dialog prompts for username and password information before the dimensions display.

image

Once the dimensions display, select the dimensions for which to create Selectors and Selector Lists and click Import. Once dimensions have been imported from one Essbase database, the process may be repeated to change connections and import dimensions from another Essbase database.

If the dimension names are repeated in different databases, it is only necessary to import them once. Selectors are not tied to a specific database and may be reused with different Essbase connections when the dimension name is the same.
image

After all desired dimensions have been imported, click Close to close the wizard. The Essbase Selectors metadata editor remains open with the selected dimensions set up as Selectors.

image

At this point, the Selector definitions have not been committed to the database and the Commit button is enabled. The reason the Selectors are not committed is to allow changes to the ID of the Selector. The ID property becomes read-only after the initial commitment process.  By default, the Automatically Add Selector List button is toggled on to indicate that corresponding Selector List objects will be created for each dimension. When this button is toggled on, the Default Selector List column of the Selector Lists metadata editor will have an entry for the Selector List.

Click Commit and the Selectors are committed to the metadata database.  After the commit process is complete, the ID column is disabled because the property is read-only.

image

Additionally, Selector Lists the wizard created were also committed.  To view the Selector Lists, Select Admin > Selector Lists from the Dodeca menu.

image

The Selector List metadata editor opens. Select a Selector List in the Metadata List panel to see its properties.

image

Once an item is selected from the list, Dodeca retrieves the properties from the metadata database and displays them in the metadata editor, as seen in the following screenshot.

image

EssbaseDelimitedString is the default list type for Selector Lists the wizard creates. It displays in the selector control type, EssbaseSelectorTreeView. This combination allows users to select members from a treeview control that is filled from an Essbase outline.  The root member for the treeview control is based on a fixed string which, by default, is the name of the dimension. The user can to pick from a treeview containing all of the members of the dimension.

If the user is assigned to have filter security in Essbase and the filter contains the METAREAD filter tag, the members available for selection are based on the user’s access to members subject to the given METAREAD restrictions.

Default selector list configurations may be edited in the Selector List metadata editor. Once the editing process is complete, close the metadata editors by clicking on the window close icon located on the tab of the metadata editor.

Review

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:

  1. Choosing a tenant code for your metadata.

  2. Initializing the metadata for the tenant.

  3. Determining the Launch URL for the new application.

  4. Launching the Dodeca client with the new URL.

  5. Creating Essbase Connection definitions.

  6. Importing dimensions and created Selectors and Selector Lists.

Creating an Essbase Ad Hoc View

Defining a View

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:

  • Create the new View in the Views metadata editor.

  • Modify relevant properties of the new View and commit the changes.

  • Expose the new View on a View Hierarchy.

  • Test the new View.

Creating a New Ad hoc View Definition

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

image

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

image

The New View dialog displays.

image

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.

image
The View Types available in the dropdown are part of the Dodeca snap-in extensibility model and may vary in your environment. The snap-in extensibility model provides the ability for customers to write their own View Types that implement specific business rules for their company and snap them into the Dodeca Framework using the Modules metadata editor.

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.

image

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.

image

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.

Setting Properties for the New Ad hoc View

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.

image

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.

image

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.

Unlike the Essbase Excel add-in, the MissingLabel is replaced with a numeric zero.

The following diagram displays the Essbase Options category:

image

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.

image

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.

image

The UI category is now complete.

image

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.

Exposing the Ad hoc View to Users in a View Hierarchy

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.

image

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.

image

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).

image

To add a category, press the New Item button.

image

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.

image

Click OK to add the new category.

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

image

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

To add a view, click the Add View button.

image

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

Select the Sample Basic Ad hoc view and click OK.

image

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.

image

Testing the New Ad Hoc View

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

image

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

image

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).

image

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.

image

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

  1. Created the new view in the Views metadata editor.

  2. Modified relevant properties of the new View and commit the changes.

  3. Exposed the new View on a View Hierarchy.

  4. Tested the new View.

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.

Creating an Essbase Excel View

The use of Microsoft Excel templates with Essbase roots dates back to the very early days of Essbase when Microsoft Excel was the only interface to the data. Since that time, there have been many user interface products Hyperion/Oracle and their partners have introduced, but despite that fact, many companies continue to rely on the Essbase Excel add-in for much of their reporting. Further, many companies have written complex VBA-based applications to serve the needs of their users.

The problems with VBA-based applications are numerous. The functionality exposed to the users is often limited due to the skills of the developer.The testing and reliability of the applications are also suspect due to the limited resources available to the developers to test and support the applications. Dodeca was designed to easily replace the Essbase Excel add-in and the applications that customers built using the add-in.Much of the functionality that was custom built by many Essbase customers is built into Dodeca.

Often, custom VBA applications customers build seek to guide users by automatically selecting the Microsoft Excel template, the database connection or connections, and sets up selection boxes to select the point of view. In Dodeca, this functionality is built in.

To create a new Essbase Excel view, it will be necessary follow these general steps:

  1. Create the Microsoft Excel template for the view.

  2. Create the new view in the Views metadata editor.

  3. Modify relevant properties of the new view and commit the changes.

  4. Expose the new view on a View Hierarchy.

The most obvious difference between creating an ad hoc view and creating an Excel Essbase view in Dodeca is that the Excel Essbase view has a Microsoft Excel template. There are other significant differences to be considered. Typical Essbase Excel views are tokenized to allow user selections to be placed into the template at runtime. When views are tokenized, Selectors are configured to allow the user to select the point of view in which they are interested. If there is extraneous text on the template, range names must be placed into the Excel template to indicate where the Essbase data range or ranges are located. Dodeca must be configured to limit the Essbase operations to the indicated retrieve range or ranges.

Essbase Query by Example Basics

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:

  • One retrieval range per sheet is allowed.

  • Each worksheet may only be connected to one Essbase database at a time.

  • All dimensions in the database must be represented or any missing dimensions are inserted automatically.

  • At least one Essbase dimension must be represented in row orientation.

  • No extraneous text is allowed that may be confused with Essbase member name.

  • Numeric member names must be preceded with a single quote to make them look like text rather than numbers.

Information about removing some of these limitations in Dodeca is available later in this section.

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.

image

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:

  • At least one dimension must be represented in row orientation. The reason for this is that Essbase returns data at the data intersections represented by a member in each dimension. If no dimension is represented in Row orientation, then it is impossible to have an intersection represented.

image
  • The cell intersection depicted here is the intersection of the West, Qtr1, Scenario, Product and Measures members from the Market, Year, Scenario, Product and Measures dimensions, respectively.

  • Multiple members in any single column must be from the same dimension unless the Use Both Members and Alias option is selected. In that case, the multiple members from a group of two adjacent columns must be from the same dimension. Members in this orientation are referred to as Row Fields as they form the row headers for the data retrieval.

  • Multiple members in any single row must be from the same dimension if there is more than one member from that dimension represented on the grid. These members are referred to as Column Fields as they form the column headers for the data retrieval.

  • Dimensions that contain only a single member may appear in any cell above and to the left of the first data cell as long as they don’t appear in any row or column used by the Row Fields or Column Fields. These dimensions effectively act as filters for the data retrieval and are referred to as Page Fields.

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.

image

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.

image

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:

  • If the dimension is in row orientation, follow the row left to the column containing the dimension. If that cell does not contain a member, then look up through the rows in that column to find the first member. In the example above, look left to column ‘A’, then up to row ‘4’. That cell contains the member name Colas and thus Colas is the member for the Market dimension. Members in row orientation are very consistent and easy to determine; the same is not true for the columns.

  • If the dimension is in column orientation, follow the column up to the row containing the dimension. If that cell does not contain a member, first look one cell to the left in that row for a member. If that cell does not contain a member, look one cell to the right from the original column. If that cell does not contain a member, look two cells to the left from the original column. Continue this pattern until you find a member. In the example above, the cell F2 contains the member name Feb which represents the Years dimension in the data point.

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.

image

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:

  • More than one retrieval range per sheet is allowed.

  • Each worksheet may retrieve data from multiple Essbase databases.

  • Extraneous text is ignored due to the usage of retrieval ranges.

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.

There is an example of one production Dodeca application which has a view with over 250 retrieval ranges in a single workbook.

Creating an Excel Template

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:

  1. Create the basic data retrieval using the Essbase Excel add-in.

  2. Add some appropriate titles and formatting to the template.

  3. Tokenize the template to allow users to select a point of view.

  4. Add a retrieval range name.

  5. Cleanup the template and do some final preparations.

Creating the Basic Data Retrieval

To start, create a new worksheet in Microsoft Excel, connect to the Essbase Sample Basic database and drill/pivot the data to produce a worksheet that looks like this:

image

Adding Titles and Formatting

From here, add some nice looking headers and formatting. Virtually any formatting functionality available in Excel can be used and properly rendered within the Dodeca environment. Further, it is possible to use Excel conditional formatting and formulas to add business logic to your template. For this simple Excel template, add a few rows to accommodate a title, add some fonts to the titles, format the numbers and add some subtotal and total lines. The formatted template may now look like the template below.

image

Tokenizing the Template To Facilitate User Selections

Selectors and the tokens associated with the selectors were created in an earlier step. As a refresher, here are the selectors created earlier and the associated tokens. It is possible to now use those tokens in our Excel template to replace values selected by users into the template at runtime. In the template, we have the Product, Market and Scenario dimensions oriented as Page Fields. Page Fields are excellent candidates to be tokenized.

In the Essbase Selectors editor, shown on the following page, we can see the corresponding tokens for those dimensions are [T.Product], [T.Market] and [T.Scenario].

image

Tokens have a specific format; ‘[T.’ followed by the dimension name which, in turn, is followed by ‘]’.  The reason for the square brackets and ‘T.’, which is an abbreviation for ‘Token.’ is that tokens must be unique strings that may be replaced in a worksheet, calc script, etc.  Surrounding the dimension name with these prefixes virtually guarantees uniqueness within the string. A user also has the ability, in Dodeca, to use a different convention, such as surrounding the dimension name with dollar signs as in $$Product$$. The key is that the string comprising the token is unique and will not be confused with any possible valid string in the worksheet, calc script, etc where the token may be used.

Replacing Essbase members in the retrieval range is not the only place that tokens are found in a worksheet. It is possible to find them in many places in a worksheet as illustrated in the diagram below. Here tokens are used in both the retrieval range and the titles.

image

Adding a Retrieval Range to the Template

Adding tokens to the title of the template could potentially confuse the Essbase QBE engine when it parses the spreadsheet. Adding a retrieval range eliminates the potential confusion of the title being mistaken for retrieve data.

Dodeca uses defined names in the Microsoft Excel template to indicate retrieval ranges. The range name is in the format Ess.Retrieve.Range.x where x is a number. There can be additional range names in Dodeca by adding additional ranges. For example, if there are two separate retrieve range names required on a worksheet in Dodeca, use the range names Ess.Retrieve.Range.1 and Ess.Retrieve.Range.2 to define them.

The range names are not guaranteed to be retrieved in numeric sequence.

To add a retrieve range to the sample template, select cells A5 through F14 and select

Insert > Name >Define from the Microsoft Excel menu. Type in the name Ess.Retrieve.Range.1 and click Add.

The most common error that occurs when building templates is misspelling the range name. Remember that "i" comes before "e" in "Retrieve!"
image

Finalizing the Template

At this point, the template is nearly complete and would work in Dodeca, but Applied OLAP typically recommends customers follow a couple of general spreadsheet clean-up methods.

  • The template currently has existing numbers populating the cells which could possibly be confused with data coming from the database. To clear out the data enter a zero into every cell.

To enter the same entry into every cell in Excel, select the cells to enter data into, type in the desired data in the first cell then press Control-Enter, and the value populates into every selected cell.
  • Another clean-up tip for the template is to hide any Essbase header rows and columns that are not necessary in the display. Since the relevant information from the Essbase page fields has been placed into the titles of the spreadsheet, it is no longer necessary for the user to see the page fields to understand the context of the view. In the template, hide row 5 to hide the page fields. As this template has a retrieval range that includes row 5, however, the data from that row will still be included in any Essbase retrieve operation.

  • Lastly, it is also a good idea to delete any unused worksheets from the template.

The Excel template is now complete and is ready to be saved to the hard drive. If there are formulas to protect, Excel’s cell protection can be used to do so. In this example, simply save the template to a file named Income Statement.xls.

image

Importing the Excel Template into Dodeca as a Binary Artifact

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.

image

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

image

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

image

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 name used in this example is the same name used for the Excel template. The Excel template is not required to be named the same, or even similar, to the binary artifact ID or binary artifact name.

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.

image

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.

image

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.

image

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.

image
As part of the patent-pending Dodeca metadata subsystem, all of the metadata editors convert their content to Binary Artifact objects and place their metadata contents into the EncodedArtifact property as encoded XML streams.

Creating a New Excel Essbase View Definition

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

image

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

image

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 name used in this example coincidentally is the same name used for the Microsoft Excel template.  The Microsoft Excel template is not required to be named the same, or even similar, to the view ID or view name.
image

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.

image
If you are interested in the programmatic layer underlying Dodeca, the software classes that provide functionality for the__ AdhocEssbase view type is a base class under the ExcelEssbase view type class. In other words, the ExcelEssbase view type inherits from the AdhocEssbase _view type and extends its functionality to allow the use of Excel templates with Essbase.

Setting Properties for the New Excel Essbase View

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.

image

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.

image

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.

image

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.

image

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).

If the Microsoft Excel template does not appear in the dialog, possible problems are forgetting to set the artifact type to__ Excel or forgetting to commit changes in the Binary Artifacts metadata editor.
image

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

image
It is possible create multiple versions of the same template in the Binary Artifacts metadata editor and store them in the Dodeca metadata store. It gives the ability to then easily switch between versions in the Views definition form.

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

image

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

image

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.

  1. Click the arrow button ( > ) to add the dimension to the list.

  2. Optionally configure the properties for the selector.

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

image

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

image

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.

image

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.

image

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.

image

The UI category is now complete.

image

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

Exposing the Essbase Excel View to Users in a View Hierarchy

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:

  1. Select Admin > View Hierarchies from the menu.

  2. Expand the hierarchy by clicking the down arrow expansion icon.

  3. If desired, add a new item (“category”) for the view.

  4. Add the view to the category.

  5. Click Commit to save the hierarchy to the metadata database.

  6. Close the View Hierarchies metadata editor.

Testing the New Essbase Excel View

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

image

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.

image

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.

image

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.

image
The Build View button will not become enabled until valid values have been selected for all dimensions.

Click Build View and the view is generated.

image

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:

  • Create the Microsoft Excel template for the view.

  • Create the new view in the Views metadata editor.

  • Modify relevant properties of the new View and commit the changes.

  • Expose the new View on a View Hierarchy.

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

image

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.

image

Deploying an Application to End Users

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.

image

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.

image

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.

image

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.

image
the HierarchyToRoleMapping property can be used to assign different View Hierarchies to users in different roles.  For more information on using roles in your application, contact Applied OLAP technical support.

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

image

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.

image

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

Adding a Send Range to an Excel Template

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.

image

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

image

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.

image

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

image

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

image

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

Adding the worksheet name followed by ! (Sheet1!, could be Region! or whatever the sheet is named) to the front of the Send Range confines the specified range to the specified sheet (in this case 1) rather than sending the specified range throughout the entire workbook. This is not a required step, but it is recommended to facilitate the use of cascading features.

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.

image

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.

image

Commit the changes.

Creating a SQL Excel View

A SQL Excel view in Dodeca is created similarly to an Essbase Excel View. It applies many of the same basic principles discussed in the previous section, Creating an Essbase Excel View, with the exception that Dodeca will query one or more SQL data sources to populate the view.

To create a new SQL Excel view, the basic steps are as follows:

  1. Define a SQL Connection.

  2. Define a SQL Passthrough Dataset.

  3. Create the Microsoft Excel template for the view.

  4. Create the new view in the Views metadata editor.

  5. Modify relevant properties of the new View and commit the changes.

  6. Expose the new view on a View Hierarchy.

Defining a SQL Connection

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

image

Click New from the SQL Connections metadata editor.

image

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

image

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

ConnectionURL

Specifies the JDBC connection URL

DriverClass

The Name of the JDBC driver class

Password

The JDBC password

Username

The JDBC user name

image

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

image

Defining a SQL Passthrough DataSet

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

image

Click New from the SQL Passthrough DataSets metadata editor.

image

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

image

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

Queries

An array of Query objects to execute

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

image

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.

image

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

image

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.

image

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

image

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.

image

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.

image

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

image

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

image

Click Test Data Set to test the newly added query.

image

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.

image

Press Commit to commit the SQL Passthrough DataSet.

image

Creating an Excel Template

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:

  1. Create a new template.

  2. Add some appropriate titles and formatting to the template.

  3. Add a retrieval range name.

  4. Finalize the template.

Creating a New Template

To start, open a new workbook in Excel. You should be looking an empty worksheet.

image

Adding Titles and Formatting

From here, add some nice looking headers and formatting. Virtually any formatting functionality available in Excel can be used and properly rendered within the Dodeca environment. Further, it is possible to use Excel conditional formatting and formulas to add business logic to your template. For this simple Excel template, add a few rows to accommodate a title, add some fonts to the titles, and set up some column labels and borders for the data you wish to retrieve. The formatted template may now look similar to the template below.

image

Adding a Retrieval Range

Dodeca uses defined names in the Microsoft Excel template to indicate retrieval ranges. By convention, the range names for relational data are in the format DataRange.x, where x is a number. There can be additional range names in Dodeca by adding additional ranges. For example, if there are two separate retrieve range names required on a worksheet in Dodeca, use the range names DataRange.1 and DataRange.2 to define them.

The range names are not guaranteed to be retrieved in numeric sequence.

To add a retrieve range to the sample template, select cells B6 through F7 and select Insert > Name > Define from the Microsoft Excel menu. Type in the name DataRange.1 and click Add.

image

Finalizing the Template

At this point, the template is nearly complete and would work in Dodeca, but Applied OLAP typically recommends customers follow a couple of general spreadsheet clean-up methods.

  • To make the view look nicer, remove gridlines from the worksheet.

  • Another clean-up tip for the template is to add a freeze pane, allowing the user to scroll the retrieved data without scrolling the title and column labels.

  • Lastly, it is also a good idea to delete any unused worksheets from the template.

The Excel template is now complete and is ready to be saved to the hard drive. If there are formulas to protect, Excel’s cell protection can be used to do so. In this example, simply save the template to a file named Employee Contact Info.xls.

image

Importing the Excel Template into Dodeca

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".

image

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

Creating a New SQL Excel View Definition

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

image

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

image

The New View dialog is displayed.

image

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.

image
The name used in this example coincidentally is the same name used for the Microsoft Excel template.  The Microsoft Excel template is not required to be named the same as, or even similar to, the view ID or view name.

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.

image
If you are interested in the programmatic layer underlying Dodeca, the software classes that provide functionality for the__ AdhocEssbase view type is a base class under the SQL Excel view type class. In other words, the SQL Excel view type inherits from the AdhocEssbase _view type and extends its functionality to allow the use of Excel templates with Relational Data.

Setting Properties for the New SQL Excel View

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.

image

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.

image

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.

image

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).

If the Microsoft Excel template does not appear in the dialog, possible problems are forgetting to set the artifact type to Excel or forgetting to commit changes in the Binary Artifacts metadata editor.
image

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

image
It is possible to create multiple versions of the same template in the Binary Artifacts metadata editor and store them in the Dodeca metadata store. Doing so allows an administrator to easily switch between versions in the Views definition form.

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

image

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.

image

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

image

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.

image

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

image

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.

image

Under the Data category, select the DataSheetRangeName property.

image

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.

image

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

image

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.

image

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

image

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

image

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

image

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

image

Exposing the SQL Excel View to Users in a View Hierarchy

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:

  1. Select Admin > View Hierarchies from the menu.

  2. Expand the hierarchy by clicking the down arrow expansion icon.

  3. If desired, add a new item (“category”) for the view.

  4. Add the view to the category.

  5. Click Commit to save the hierarchy to the metadata database.

  6. Close the View Hierarchies metadata editor.

Testing the New SQL Excel View

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

image

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.

image

Click on the new view to test it.

image

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:

  • Create the Microsoft Excel template for the view.

  • Create the new view in the Views metadata editor.

  • Modify relevant properties of the new View and commit the changes.

  • Expose the new View on a View Hierarchy.