• Posted in: Home
• 21
• Author: admin

Using Microsoft Excel With Oracle 1. Cubeslt Do not delete this text because it is a placeholder for the generated list of main topics when run in a browser This tutorial covers how to use Excel 2. Oracle. 11g OLAP data for easy and powerful ad hoc query and analysis. Approximately 1 hour. The Simba Technologies MDX Provider for Oracle OLAP allows you to use the powerful and popular ad hoc query and analysis capabilities of Microsoft Excel Pivot. Tables and Pivot. Charts with your Oracle OLAP business intelligence data. Pivot. Tables provide cross tab functionality to comprehensively present your data in order to answer complex analytical questions. Pivot. Charts provide visual insight and reporting in a powerful, dashboard like manner. Simbas MDX Provider for Oracle OLAP is a client side driver that leverages Excels strengths for ad hoc query and analysis, as well as new Excel 2. MDX data standard. Simbas MDX Provider natively connects Microsoft Excel with Oracle 1. OLAP Option. It works seamlessly to parse and process multi dimensional queries from Excel and integrate with Oracle OLAP via SQLODBC. Using the Oracle. OLAP sample schema, you will create an Excel 2. Sales for calendar year 2. To achieve this objective, you will View stored, calculated, and custom OLAP measures broken out by the appropriate dimension members. Use hierarchical navigation to drill through the data. Apply basic report formatting options to enhance the clarity of the report. Apply visualization tools to the data using conditional formatting to reveal important information that otherwise may not be readily obvious. Microsoft Ole Db Provider For Oracle 11g ClientYou will further analyze the data using interactive ad hoc query techniques to discover trends in the data from a regional perspective. Finally, you will add a Chart to the report. Microsoft Ole Db Provider For Oracle 11g Express' title='Microsoft Ole Db Provider For Oracle 11g Express' />Hi, I recently installed the Oracle 11g Release 2 ODAC 11. Oracle Developer Tools for Visual Studio. The. 32bit OS. I managed to get this working on Windows XP virtual machine by downloading oracle OLEDB provider from Oracle official website Oracle10g Provider for OLE DB. Using ADO, these properties would have been set as a property on the command. This is not possible if using the Microsofts OLE DB. NET Data Provider. T5dpPgnc/T_FEiHa4IxI/AAAAAAAAAO8/-SVBWNkgTY4/s640/installation+of+oracle+database+xe+11g+r2+7.JPG' alt='Microsoft Ole Db Provider For Oracle 11g New Features' title='Microsoft Ole Db Provider For Oracle 11g New Features' />The following is a list of software requirements Microsoft Excel 2. Excel 2. 00. 7 with SP2 is preferred. Excel 2. 00. 3 is also supported. The MDX Provider for Oracle OLAP The MDX Provider for Oracle OLAP is sold directly by Simba Technologies. It supports Microsoft Excel 2. Oracle Database 1. OLAP Option Release 1 and 2. Product information and evaluation copies are avialable at Simbas web site click here. For pricing and purchasing information from Simbas web site, click here. Before starting this tutorial, you should. Install or have access to Oracle Database 1. Enterprise Edition with the OLAP Option Patch level 1. Download and install the OLAPTRAIN schema. Installing. the Oracle OLAP 1. Sample Schema. Notes The Sample Schema installation package includes two parts 1. Installing the base OLAPTRAIN schema. Installing the SALESTRACK analytic workspace in the OLAPTRAIN. Average Cost Install Shower Pan. Complete both parts of the package. Install and configure the MDX Provider for Oracle OLAP. Instructions are found in the MDX Provider for Oracle OLAP User and Administrator Guide. The guide may be downloaded from http www. MDX for Oracle OLAP documentation. This document is also provided by Simba technologies as part of the MDX Provider for Oracle OLAP package. Follow the instructions for Installation. Client Side and Excel Configuration. Have access to Microsoft Excel 2. In the Prerequistes section, you were instructed to install and configure the MDX Provider for Oracle OLAP. As part of the configuration process, you must create a Microsoft Data Source Name DSN to the Oracle database and the OLAPTRAIN sample schema. After creating the DSN to the Oracle database, use the Excel Data Connection Wizard to complete the connection process. Notes See the Client Side and Excel Configuration topic in the MDX Provider for Oracle OLAP User and Administrator Guide for detailed instructions on creating an Office Data Connection. Oracle Database. As you follow the directions in the Administrator Guide, you use the Data Connection Wizard to select an Advanced data source, and then the MDX Provider for Oracle OLAP as your OLE DB Provider option. Finally, you specify the connection information that leverages your Oracle DNS entry and the OLAPTRAIN schema user information the OLAPTRAIN user is the owner of the sample schema, as shown here Click Test Connection to ensure that you have entered the correct connection information. Then, click Next to change the Friendly Name of the connection. The Friendly Name is what you will see in the list of existing connections when you use the connection for additional spreadsheets. Finally, the wizard displays the OLAP Cubes for which the user has database permissions, as shown below. Select SALESCUBE. Accessing the SALES Cube. Once you click Finish in the Data Connection Wizard, you are presented with the. Import Data dialog, shown in Step 4 below. Go to step 4 to continue. Note If you are creating a new workbook using an existing connection for the OLAP Sales Cube, start with Step 1 below. If you have not already done so, create a new, blank workbook as shown here. Select Data Existing Connections. Result the Existing Connections dialog appears. In the Existing Connections dialog, select the connection for the Sales Cube, and click Open. Note These are the Friendly Names and description fields for the connection, as mentioned earlier. In the Import Data dialog, select the Pivot Table Report and Existing worksheet options. Then click OK. Result The MDX provider makes the database connection, finds the cube, places a pivot table placeholder in the worksheet, and populates the Pivot Table Field List pane with Measures and Dimension Hierarcies from the cube. As stated in the Scenario section, you will create a report to analyze year end Sales for calendar year 2. Later, you will further analyze the data to discover trends in the data from a regional perspective, and then finally you will add a Chart to the report. Use the following steps to create the report, apply some basic formatting, and then add conditional formatting. In the Pivot Table Field List, select the following four measures, in the following order Sales. Sales Ytd Year to dateSales Pr Year Year for the prior yearSales Pr Year Pct Ch Percent change between sales and sales for the prior year. Result The data items automatically appear in the Values box and also in the pivot table. Result As shown below, only the data for Sales appears, since no Time dimension members have been selected yet. This value, in fact, is the total sales for all dimension members in the cube. Scroll down in the Pivot Table Field List to view the Dimensions. Drag and drop the Calendar Hierarchy from the Time dimension to the Row Labels box, like this Result The top level dimension members of the Calendaryear hierarchy appear in the Row axis of the Pivot Table. Notes Since CY2. The CY2. Drag and drop the Standard Hierarchy from the Product dimension to the Row Labels box, below Calendar. Drag the other two dimension hierarchies Sales Channel and Regional to the Report Filter box, like this This action enables you to filter the data by selecting members from either of those two dimension hierarchies on the page axis of the report, as shown here In this subtopic, you apply some basic report formatting to enhance the report clairity, and then you will add condition formatting to assist in revealing important analytic information in the data. Before you begin formatting the report, close the Pivot.