IntelligentApps 4excel

Introduction

Welcome to IntelligentApps 4excel™. IntelligentApps 4excel™ is a new product dedicated to providing the Microsoft® Excel user with a new dimension in business analysis and modeling power. IntelligentApps 4excel™ runs as an Add-In to Microsoft® Excel, exploiting all the capabilities and usability features of Excel with the power and scalability of Microsoft®’s Analysis Services. A number of Wizards are available to guide the user through the different steps required to build a cube from a Excel report or via edition, create data entry sheets or data load profiles to enter data into the cube and produce several styles of reports to display the analyzed information.

Cube Wizard | Using the cube wizard you can build cubes either from existing Excel applications or in free form mode.
Data Loading Wizard | You can load data into your cubes from either csv files or existing Excel reports. The data loading wizard takes you through this process in a step-by-step manner.
Data Entry Wizard | You might not have all your information available in existing file or reports or you might want to perform what-if analysis. You can create any number of data entry worksheets using the data entry wizard and once you have the worksheet formatted in the way you work, you can perform direct data entry, what-if simulations, reference information in any other worksheet and perform data entry at higher levels.
Pivot Table Wizard | A PivotTable is known to most Excel users as a means of analyzing data in relational databases and complex spreadsheets. The pivot table wizard makes use of this same technology, but greatly simplifies the process of creating a pivot table.
Formatted Report Wizard | The report wizard provides you with a very powerful multi-dimensional report writer. with this you can build as many report queries as you like, link them together and specify how you want the report sets packaged and distributed to the customers.
Briefing Book Wizard | The briefing book wizard allows you to perform both simple and very complicated analysis on a cube. You can save any view as part of a briefing book and once complete, you can export this book as a live powerpoint application.


The Cube Wizard

Use the Cube Wizard to create your cubes either based on an existing Microsoft® Excel report or in you own free format layout. In each case,

a series of steps will guide through mandatory and optional items to help you generate the required cubes.

The Cube Wizard can also analyze the Excel report to search for calculations defined in it and generate equivalent calculations in your cube.

Loading the report data when first creating the cube is also possible via the Cube Wizard.

Use the Back and Next buttons to navigate through the Wizards steps.

Use the Finish and Close buttons to stop using the Wizard.

Use the Help button to call the different levels of help available.

Back button | Takes the Wizard to the previous step. If the button is disabled, the previous step is no longer available for the user.
Next button | Takes the Wizard to the next step. If mandatory information in the step has not been entered, a message will appear and the product will remain in the current step.
Finish button | Takes the wizard to the last step for the user to save the cube if the information provided is the minimum required or takes the wizard to the next mandatory step.
Cancel button

Closes the wizard without saving any of the information previously entered. A message asking the user to confirm the action will appear if any information related to the cube has been entered.
Help button

Opens a panel with the levels of help available in the product:

Hot Tips | Explains the contents of the screen elements. The mouse pointer will show a question mark when moved over an element with a Hot Tip attached to it. Clicking on that element will open a box with a small description of the element and its function. Clicking again in the element will close the box.
Teach Me | Teaches the OLAP concepts relevant to the IntelligentApps 4excel application.
Help | Opens this help file.


Creating a new cube

In this first step you must decide how you want to start the creation of your new cube:

  • From a blank, completely empty cube
  • Continue an interrupted creation by using the automatically recovered cube definition
  • From an existing cube, used as a template to add, modify or delete dimension, members and/or other components
  • From an IntelligentApps Template, built for specific purposes (Budgeting, Sales & Marketing, etc) and available via the IntelligentApps Web site

New empty cube | Default option. You must enter a minimum amount of information to create a cube. Mandatory steps and minimum data will be specified on each step.
Continue with automatically saved cube | The system will automatically save the cube being created every minute or, at a frequency specified by the user (Options item in main menu). If the creation of the cube is halted by any reason, you can continue at the point of the last save by selecting this option.
New cube based on one previously built | You can select any cube that has been previously built with IntelligentApps 4excel. The original cube structure (or sections of it) can then be modified, removed or added to by the user. If the new cube name is the same as the one used as template, any data in the original could be lost. The system will warn the user if this is about to occur.
New cube based on a template from the web | Pre-built cubes for specific themes will be available on the IntelligentApps web site. Customers running any of the Professional Edition versions will be able to download and use these templates.


New Page 1

How would you like to create your cube?

You can create your new cube from an existing Excel report or in a free format manner. In this step you must select how you would like to

create your cube.

New cube based on an existing Excel report | This is the default. You can create your cube from an existing Excel report. Dimension members can be defined by selecting their names from the spreadsheet. You can also create dimensions that do not exist in the report and/or add members to the ones present in the spreadsheet. The product can analyzed the report for existing calculations and converted them to their equivalent calculation rules to be applied to the cube. Loading of the data values present in the report is done by default but it can be cancelled by the user. This is the recommended option for new users with none or little knowledge of OLAP.
New cube in free format mode | You can create your cubes by defining dimensions, members, calculation rules, attributes and properties in a free format layout. No pre-define Excel report is required. This option is recommended for advanced users.
Open the free form Cube Planner Workbook The Cube Planner Workbook will guide through one of the ways to define dimensions using the Free format mode. Select the Check box and the system will open the Cube Planner Workbook in your Excel session


New Page 1

Creating the Measures dimension

The Measures dimension must exist on every cube created. As the product will automatically create a Measures dimension, you only have to enter at least one member to the dimension, using one of the methods available. The system will not allow you to continue with any further step until at least one measure has been added.

You can add measures in two ways:

1. By selecting the measures from your Excel report

  • If you are basing your cube on an existing Excel report and it is not already opened then open it now
  • Press the button "Select From Excel"
  • The Wizard will be minimized and a small window will appear at the bottom right corner of your Excel report, as shown on the figure bellow

  • If your Excel report includes multiple sheets, like the one in the example, the sheet names will be listed on the left hand side of the small window
  • The top (current) sheet is selected by default. You may select all of the listed sheets (by pressing the "Select All" button) or some of them (by clicking the individual check boxes besides each sheet name). You can use the "Unselect All" button to unselect all currently selected sheets but selecting none will result in no members being selected from the report
  • If the measures dimension members are indicated by the names of the sheets, you should click the "Use selected worksheet names as list" check box
  • Press Cancel if you want to stop the selection process. The system will return to the Measures dimension step
  • Press OK when you finish selecting the sheets to use or directly highlight the columns or rows that contain the names of the Measures members. You can highlight consecutive rows or columns or disjoint rows or columns by using the Ctrl key.
  • On either of the two cases in the previous item, the system will display the window shown in the graphic bellow

  • If you selected the Measures members while selecting the sheets to use, the range selected will be displayed in the text area of the small window. But if you have not selected the members or the range is not displayed in the text area, you should make the selection now
  • If you wish to delete the range of cells selected, double click on it and it will be cleared
  • Once the selection of members is made, you must decide whether to add the selected members to an existing list of measures or to create a new list. Press the "New list" button to create the measures list for the first time or to overwrite an existing list. Press the "Add to list" button to add the selected members to an existing list of measures
  • Use Cancel if you want to stop the selection process
  • Once the selection process is concluded, the system returns to the Measures step, displaying the selected measures in the left hand side area of the window, as shown bellow

  • You can enter a description per measure by clicking on the measure name and typing the description in the Description box
  • You can delete or rename a measure by right clicking on the name and selecting the appropriate option from the menu
  • You can add new measures by right clicking in the window and selecting the Add option from the menu
  • Use the Up and Down arrow button on the left hand side of the Members list to modify the order of a particular member with respect to the other members

Note: The system will replace any special characters, like %, $, -, etc., in member names with an underscore (_). The member caption, visible in the majority of the screens will remain the same as the one entered.


New Page 1

Creating the Time dimension

The Time dimension must exist on any cube created using the Excel report based Wizard. As the product will automatically create the Time dimension, you only have to enter at least one member to the dimension, using one of the methods available. The system will not allow you to continue with any further step until at least one Time member has been added.

You can add Time dimension members in two ways:

1. By selecting the members from your Excel report

  • In similar fashion to the Measures dimension, you can select the Time dimension members from your Excel report
  • Press the button "Select From Excel"
  • Once the members are selected in the report, a similar window as the one shown bellow will be displayed

  • The small window in the center of the screen displays the selected range of cells that point to the Time dimension members

  • The same options available on the Measures dimension step are also available for the Time dimension
  • A typical Time dimension will include hierarchy, i.e. Months roll into Quarters, Quarters roll into Years. The members selected in this example are the 12 months of the year

  • By right clicking in the window and selecting the Add option, you can include members not present in the report, that will allow you to create a hierarchical time dimension with the system automatically totaling values that belong to the children of a higher level member

  • In this example, we have added 5 new members to the selected 12 months from the report: Q1, Q2, Q3, Q4 and 2001
  • In order to create the hierarchy between the members, you must drag and drop "child" members into its "parent". This can be done directly in the window where the list of members is displayed

  • Or you may press the button with the magnifier glass to make the list of member's window bigger

  • Drag and drop the child members into their parents to obtain a hierarchy similar to the one displayed in the example

  • Once finished, close the window. The Time dimension step will now show the dimension with the hierarchy specified


New Page 2

Creating Category dimensions

The cube requires at least one Category dimension with at least one member. The product will not allow you to continue any further unless a category dimension has been created with at least one member.

  • Category dimensions do not exist by default in the system. You must create the dimension first and then add the members to it
  • Press the "Add Dimension" button. The system will open a small window to enter the dimension name as shown in the graphic bellow

  • Enter the dimension name and press OK to return to the main window
  • Press Cancel if you wish to stop the action
  • Use the same methods explained in the Measures and Time dimensions steps to select the member for each dimension from your Excel report
  • Use the same method explained in the Time dimension step to define the hierarchies between the members of the dimensions


Verifying the data load process

If you wish to load the data values from your Excel report into your cube, it is recommended to verify that the system has located each data cell value correctly in your report and will be loading those values in the correct members on each dimension.

  • This step is not mandatory. If you do not wish to load the data you may press the Next or the Finish button to continue
  • The data verification will occur on a single sheet at a time. If you report contains more than one sheet you may chose to run the verification process on each sheet, by selecting the sheet name in the left hand side list
  • If all the sheets in your report follow exactly the same layout, there is no need to verify every one of them. The mapping of the cells against the dimension's members will be the same in each case
  • The result of the verification will be indicated using one of the symbols described on the Key window
  • The system automatically sets up the range of cells to be used during the verification process. This range is based upon the members selected for every dimension in the previous three steps. If you wish to change this range press the "Select Range" button and highlight the range of cells you wish the system to use
  • Use the "Reset to Default" button to revert the range of cells to the one the system defined by default
  • Press the "Verify Data Coordinates" button to start the process in the selected sheet
  • The verification process consists in a comparison between the data values contained in the report against the data values contained in a temporal cube created by the process
  • If all the values match, you will be presented with a message indicating that there are no mismatches found
  • If the system finds any difference between the two sets of values, it automatically creates a temporal sheet with the original values from your report and a comment attached to each data cell, giving you details of the information coming from the cube. The data cells in this temporal sheet are also color coded. The following window will appear in front of the verification report.


New Page 2

Generating the calculation rules

The system can analyze the report to detect any calculations defined in the cell values. It will then convert those calculations into cube type calculations and attach them to the relevant members in the cube.


Saving the cube

At this point all the major components of the cube have been created. The last step of the wizard is to create the cube.

  • Enter the cube name and description in the relevant boxes
  • Select the worksheets from which you want to load data
  • Click on the "Build Cube" button.