Home

Quick Start Manual

1. Choose Fact Data The Import Data dialog box is displayed Import Data File Name Browse o Tab Comma f Period o Other EJ Cancel Help Click Browse to select the file to import In the Open dialog box select the QSData txt file from the PowerOLAP Examples subdirectory Click Open You are returned to the Import Data dialog Keep Tab as the selected Delimiter gt Click OK The Import Fact Data dialog is opened This dialog box shows the Dimensions that appear in the text file as well as in the selected Cube at the top right into which you will import data The Current Year Budget cube is selected PowerOLAP Quick Start Manual 31 import Fact Data Cube to import data Dimension Method E Se Accumuate C Cee om te PP Click on the Months Dimension A message box appears stating that the selected Dimension appears in both the import file and the Cube Also it states that showing common Members could be time consuming here it will not Click Yes to confirm that you want to continue Note that in the Members list box only January is selected for this Dimension this is because the import file contains data for January only If you click on the Accounts and Regions dimensions you will see that the import file contains data for several of their Members but the data will only be for the month of January Click OK to begin the import proces
2. PowerOLAP Quick Start Manual Version 7 2006 PARIS Technologies Inc Published March 2006 DISCLAIMER Information in this document is subject to change without notice Companies names and data used in examples ate fictitious unless otherwise noted No part of this document may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose without the express written permission of PARIS Technologies Inc The software described in this document is furnished under a license agreement and may be used only in accordance with the terms of such license All Products described or otherwise referenced herein are the property of their respective companies Published March 2006 Table of contents OIC A A A E E I Quick Start Introd dco hit eels 1 Creating a PowerOLAP Database n 2 KOC ACI EAMCNSION sisi lancia ainia E lola E E EE dipolo incaico sida 6 Adding Members to Dimensio0DS ooocccoocconocccnocccnocccnocccnncccnocccnocccnnccnnoccnnccncncccnccnnocccnnccnnnss 8 Creatine a Dimensi n Hierarchy isiin innia niania oaia aia iiin 10 The Dimension Hierarchy Tool Dar nena aa cae a a ai 13 ASE di dd A 13 Creatine a Cubana nai 14 Creatina A SCS VIC Weiss iia 16 Atransino Slice DINERO Ea 19 DELCO HAO Parc IMD aca 20 Chanemio me Gnid Layon teres a Ne an ne Pee ee er ee 22 Fote me WALA ma Scania asec es acces 23 Setting General Format Preferences ccccssscssssccsssccsssccs
3. Select Page Member icon from the menu bar 20 PowerOLAP Quick Start Manual gt Click the OK button on the toolbar to close the dialog and return to the Accounts by Months slice The current Slice now displays data for United States which is indicated in the Page list box beside Regions 1 e Regions United States PowerOLAP Quick Start Manual ZA Changing the Grid Layout You can change the layout of the Slice grid by moving Members of a Dimension within the Slice Content list box Double click on the Months dimension in the Columns list box in your current Slice The Edit Slice dialog box is displayed Currently Total Year is at the bottom of the Slice Content list box which corresponds to the rightmost column in the grid you may need to scroll rightward in the grid to see Total Year By dragging and dropping Total Year to the top of the list you can move it to the leftmost column Select Total Year from the Slice Content list box on the right Drag and drop Total Year above January as circled below fe E 5 F ih Fi Fi F F F De La F E Is E E 5 Click the OK button Y on the toolbar You are returned to the Accounts by Months slice and Total Year is now displayed in the first column of the Slice 22 PowerOLAP Quick Start Manual Entering Data in a Slice So far you have demonstrated PowerOLAP s remarkable flexibility in organizing and displaying data within a
4. All v Filter B s 23 er gt gt as T Q v Net Sales 8 Accounts Cost of Sales Gross Profit gt Gross Profit Net Sales Cost of Sales 1 gt Click the OK button Y on the toolbar when complete You ate returned to the Dimension dialog Click OK You are returned to the main application window In PowerOLAP the default aggregate weight is equal to 1 Thus a parent Aggregate member is simply the sum of all Child members defined in the Dimension hierarchy In the example exercise all aggregates you defined are standard with the exception of Gross Profit in the Accounts dimension Therefore you do not need to define aggregate weights for the remaining Aggregate members Creating a Cube Using the Dimensions created in the previous exercises you will now create a PowerOLAP Cube that will store and model your data To create a Current Year Budget cube From the main application window select Model Cubes The Cubes dialog is displayed 14 PowerOLAP Quick Start Manual Cubes x Add Delete Rename Formulas Meta Security Data Locks Push Ranges Push F Administrative Spstem Cube OK Cancel Help Type Current Year Budget in the Cubes text box Click the Add button The Define Cube dialog appears in which you select Dimensions to be used by the Cube Define Cube Cube Name Current Year Budget Available Dimens
5. 101 259186 38 7263 00 7263 00 5733 00 1998 O1 1 86395 46 2421 00 2421 00 1911 00 1998 011 12 86395 46 2421 00 2421 00 1911 00 1998 911 13 86395 46 2421 00 2421 00 1911 00 1998 02 259186 38 7263 00 7263 00 573 00 y goggg99R0 S59 009 90 o oa o o 9 oa 0 o0 0 a 0 a a a o o ODIO O O oO O o O DIOIODiO O O OD O goggg S999 909 nonoo Oo o Oo o o0 0 10 10 90 0 o0 0 a oa o0 1a o o All the Fact Data in an MS Analysis Services cube has also been delivered to the PowerOLAP Cube and appears in the Slice To update the Fact Data in Cube all you need to do is hit F9 whether you are in PowerOLAP or an Excel worksheet created from a Slice Concerning write back provided the Cube was created using the ReadWrite capability and the cell into which you type data is a all Detail 1e leaf Members and the Cube is enabled for write back and you have the requisite rights to do so you can hit F9 also to send that data point to the MS Analysis Services cube Finally the Update Linked Cube function allows you to update the PowerOLAP cube with any new Meta Data and Fact Data changes that were made to the provider Cube e g an MS Analysis Services cube Select Tools PowerOLAP Services Update Linked Cube Tools Synchronization Server PowerOLaP Services New Linked Cube Update Linked Cube The Update Cube dialog appears CI x
6. gt From the Excel menu bar select PowerOLAP Edit Formula The Edit PowerOLAP Formula dialog is displayed Edit PowerOLAP Formula a ES Parameters Function oLaPReadwrte Database Pick i N Cube Pick N Dimensions Ok Cancel Select OLAPRead from the top drop down menu to the right of Function Press the Pick button le next to Database The Select Database dialog box is displayed as below PowerOLAP Quick Start Manual 43 Select Database Available Databases gram FilessPowerLAP E samples O5 Database c ceca _ tee The current database which contains the value you want s selected Click OK You are returned to the Edit PowerOLAP Formula dialog Press the Pick button Pick to the right of Cube The Select Cube dialog box is displayed Likewise this Cube contains the value you want to be reference into the Excel cell D13 gt Click OK Again you are returned to the Edit PowerOLAP Formula dialog box Now you have defined several of the parameters of the Cube reference formula as shown in this detail of the dialog box Edit PowerOLAP Formula Ei arameters Function OLAPRead Database Fick C Program Files PowerOL E3 C Program Pick Current Year Budget s Current Year Budget Cube mensions Months Pick 045 s February Accounts Pick EF Regions Pick Loo N Note that the Dimensions area now displays
7. that you wish to create a Products dimension You will go through the same steps which are Define Links Define Members Conditional Retrieval if checked Order Preference if checked Partial Fields if checked Drill Through Values if checked Define Aliases Define Properties Define Hierarchy Define Rollups if checked Relational Summary In this example assume that you have completed the creation of a Products dimension After seeing the Relational Summary you would click Finish and be returned to the Dimensions dialog 54 PowerOLAP Quick Start Manual Dimensions At this point 1f 1t 1s logical that a Cube with 3 dimensions will yield worthwhile results for your planning analysis and reporting requirements and for this example it certainly will you can click OK to create the Cube Note that upon clicking OK an option appears to save the template of the Dimensions you created this will be useful for additional Cubes you might create with those Dimensions The progress bar will appear onscreen until the Cube creation process is complete When finished a message box appears indicating the number of data points in the Cube Having created the Cube has you will successfully have bridged the gap from relational to multidimensional worlds and even better you will have all the modeling capabilities described in the initial exercises in this manual Assuming you create spreadsheet repo
8. the Server Typically a new database shared by multiple clients would be created from the MDB server Control Program It is worth noting though that a user can create a database in standalone mode that can then be made available to the Server PowerOLAP Quick Start Manual 5 a Create a New Dimension Creating Dimensions Dimensions are lists of related names known as Members that are used to identify data stored within a PowerOLAP database These lists include names that you would see as headings across rows and down columns in a spreadsheet or standard report e g January February March etc or the names of salespeople or regions in your organization If you are using OLAP Exchange to integrate data from a relational database application lke an accounting system your structure of accounts would supply Members for the Accounts dimension see the OLAP Exchange manual Dimensions then are lists of related terms used to organize your data Thus a natural Dimension name for the members January February and March might be Months Dimensions in turn are used to construct Cubes the multidimensional structures in which you store and model data In the model we are about to create we will define three Dimensions Months Accounts and Regions Create the Months Accounts and Regions dimensions as follows Select Model Dimensions The following Dimension dialog is displayed x x e y Type Months in
9. 0 0 Columns 3 Months All Rows 8 Accounts All oh For Help press F1 Local as Database Z PowerOLAP Quick Start Manual 19 Selecting Page Members Currently you are viewing data for Canada To view data for other Members defined in the Regions dimension within the Accounts by Months slice e g to change the view from Canada to United States Double click on Regions Canada in the Page list box Page Mal Regions Canada The following Edit Slice dialog is displayed in this example Edit Regions for Accounts by Months Set Page Member 8 Edit Regions for Accounts by Months i iol x Members Hierarchy gt S 8 als gt 2 Y lan il Canada El United States Tal United States A a ASAE TEER EE EEN 3 Ed ee 57 Total Regions 2 North America S South America Venezuela Colombia Z Total Regions North America S South America Note that the Detail member icon to the left of Canada in the Slice Content list box is yellow indicating that this Member is the currently selected Page member Canada United States Venezuela Colombia E Total Regions E North America E South America You can select any Member in this list box as the Page member to view within the slice Double click United States in the Slice Content list The icon beside it is now yellow You may also select a Page member by clicking on the
10. Cubes E Budget Cancel Help The dialog lists the Cubes that were created as a linked cube Select the Cube s to rebuild and click OK 58 PowerOLAP Quick Start Manual Note that this action deletes all existing Members from the current Cube and rebuilds them from the OLE DB for OLAP compliant source cube including all Meta Data and associated Fact Data This completes the overview of PowerOLAP Services the feature that shows how PowerOLAP along with being an optimized cube builder itself can function as an especially strong front end for MS Analysis Services and other OLE DB for OLAP systems as well Power OLAP Quick Start Manual 59 60 Power OLAP Quick Start Manual
11. Expire Date Thursday August 31 2006 Maintenance Expire Date Sunday March 11 2007 Feature Info Local Database lM MDB Server Relational Update Alllow Editing Synchronization Server F Unlimited Cubes Create Formulas Enable HTTP 7 Unlimited Dimensions Enable Security Connect By Named User M Unlimited Members OLAF Services M Olap Exchange r Register By Phone By Intenet By Browser Profile Portal Tab The Portal Tab provides information to enable and set up a Portal ia a Y General Format Licensing Portal F Enable PowerOLAP Portal menu option and default file locations Default location for web files to be opened in a web browser A The default location for web files to be opened references a shared network folder a FTP site or a WEB site that has been established as the location for PowerDLAP files Please contact your Network Administrator for specific details regarding this file location Note When specifying an FTP site as the location for the General WEB File Location be sure to enable the Enable Folder view for FTP sites under the Browsing section of the 4dvanced Tab in the Internet Options Dialog of Internet Explorer Cancel Help PowerOLAP Quick Start Manual as Saving Changes to a Database There are two options for saving a database The Save Database command found on the File menu allows you to save an open database to disk or hard dr
12. OTS HEE Select Slice Worksheet or press F8 or click on the create worksheet button E from the menu bar PowerOLAP launches Excel assuming it is not running displaying the newly created worksheet A new Excel worksheet appears as follows G pe Edit wew insert Foma Tools Daa PowerOLAP Window Hep Acrobat UON i dase RES AAA Ala 3 G pa CAProgram FilesVPowerDLAPExamplestOS Database ma 2 Cuba Current Year Budge AL a Page Regions United Slates M Monihs Along Columns Accounts Along Rows EA Total Year January February March April May June July B Margin 40 40 40 o 0 O o O Net Sales 1260000 S00000 750000 ao o O 0 O 10 Cosi of Sales fS50000 00000 450000 O E O 0 O 11 Gross Profil 500000 200000 300000 0 0 O 0 0 a The first few rows of the worksheet display information indicating the PowerOLAP database the Cube the Page Dimension member s that the Slice data shows in the figure above one Page Dimension Regzons and United States is shown and the Dimensions Along Rows and Along Columns The worksheet can now be saved as an XLS file via Excel s Save command PowerOLAP Quick Start Manual 39 Selecting a Page Member to View in Excel Change the Page member in Excel as follows Double click Page member cell e g cell C3 United States The Select a Member dialog box appears Note the two tabs circled you can find Members based on where they appe
13. below it see following figure January a Months February 8 4 Total Year March 1st Quarter April 2nd Quarter May 32 31d Quarter June 4th Quarter July August September October November December 1st Duarte 31d er 4th Quarter Total Year Mala Select January hold down the Shift key then select March in the Members list box so that February is highlighted also 10 PowerOLAP Quick Start Manual Drag and place the selection just below 1st Quarter in the Hierarchy Definition box Now the sigma sign appears beside 1st Quarter Pressing the Ctrl key select April May and June Release the Ctl key Now Select 2nd Quarter in the Hierarchy Definition box Months Hierarchy Subset Jar Fit ala 00 a er 8 January B Months February E Total ear March 1st Quarter April January May February Piet eee March July E 2nd Quarter August Sid Quarter September 4th Quarter Press the Add Selected Members as Child button E on the toolbar Notice that the sigma sign appears next to 2 Quarter y Bring over July as a Detail member under 3rd Quarter Highlight August and September from the list on the left and highlight July on the right Click on the Add Selected As Sibling button gt to add August and September into the Hierarchy under the 3 Quarter Now August and September are show
14. on application launch is selected the last opened database will open when PowerOLAP is opened When Allow Reserved Characters is checked PowerOLAP can accept any of the following characters in Meta Data Double Quote 5 Single Quote E Period Comma Semi colon Brackets Braces Parenthesis If this function is not checked PowerOLAP will NOT accept these characters in the Meta Data This becomes important when working with OLAP Exchange as you will not be able to build a Cube if the source data contains any of these characters To Use Default Directory i e set up the PowerOLAP database to default to a certain directory upon opening or saving a file Select the Use Default Directory tick box This option saves you from having to browse for the database when opening each time by defaulting to the folder you designate Note you must have the database closed when setting this option PowerOLAP Quick Start Manual 25 Use Excel Version During installation PowerOLAP prompted you for a version of Excel to use You can select a different version of Excel to use here Excel 97 Excel 2000 XP Excel 2003 or Latest Installed version Language Select your language preference here PowerOLAP is available in the following languages English Danish French Portuguese After selecting a language you will be informed that in order for the selection to take effect you must close and then re open PowerOLAP Ca
15. poet fume pi pea omeo nion we tent D3 O CLAP Read 0 Progam Ple Pr LA PE amplis Database aip Tumer Year Budget DET Gimes Profi United mai mmes i The reference formula Total Yem January February March April May June returns a value yao x E in this worksheetcell Power OLAP Quick Start Manual 45 46 You now have a ready view of the February Gross Profit tor United States within a dynamically connected spreadsheet that shows figures for Canada Now whenever February Gross Profit for United States or for that matter Canada changes it will be reflected in this worksheet You can save the current Database with changes you have made from within Excel gt Select PowerOLAP Save Current Database in Modeler The PowerOLAP database is saved but not closed PowerOLAP Quick Start Manual Closing a Database The Close Database command located on the File menu in PowerOLAP closes an open database When you have completed work within one database you may still wish to work with another database You must first close the currently open database before opening another database To close an open database gt Select File Close Database If any Slices are open PowerOLAP will prompt you to save Slices Clicking Yes will save all database changes to disk and close the database file Clicking No will close the database file without saving any changes made to the da
16. position the next to last Dimension in the Columns position and any remaining Dimension s in the Page position When we created the Cube in the last exercise we brought all Dimensions into the cube at once in the order they were listed In the above example Slice Accounts are displayed as columns Regzons as rows and Months as the Page dimension currently displaying January which is the first Member entered for the Months dimension Select Slice Save As to save the Slice The following dialog is displayed Save Slice PowerOLAP Quick Start Manual 17 Save Slice As ES Cancel Help Type Regions by Accounts in the Slice Name text box gt Click OK You ate returned to the Slice note that both the Cube name Current Year Budget followed by the Slice name Regzons by Accounts appear in the title bar NOTE PowerOLAP s default calculation mode is set to Manual Thus when you make changes to a Slice you will need to Press F9 or the calculator button on the menu bar to see those changes reflected in the Slice You can change the calculation mode to Automatic by selecting Edit Options and clicking on the Automatic radio button in the General tab Change the calculation mode from Manual to Automatic on the Edit menu under Options General Format Licensing Calculation Mode Manual Now you will see changes instantly on screen as they are made throughout the remainder of this manual
17. s exercises 18 PowerOLAP Quick Start Manual Arranging Slice Dimensions To demonstrate how quickly and easily views can be changed you will now arrange the Dimensions of this Slice to view data with Accounts as rows Months as columns and Regzons displayed as a page Start by dragging and dropping the Dimension names into the appropriate list boxes Select Months in the Page list box and drag it down to the Columns list box below Accounts Note that a nested view is created assuming you are operating in Automatic calculation mode Select Regions in the Rows list box and drag it up to the Page list box Select Accounts in the Columns list box and drag it down into the Rows list box By placing Regzons in the Page list box you display data for a single Member of the Regions dimension The Page member you see when you first arrange a Slice is the Member at the top of that Dimension s member list In this case the Slice grid displays the data for all Accounts and all Months tor the Regions member Canada Select Slice Save As and type Accounts by Months in the Slice Name text box Click OK You have created and saved a second Slice Accounts by Month PowerOLAP Current Year Budget Accounts by Month Ioj x File Edit View Model Slice Data Tools Window Help l x nslala AEL 28 iO is igi Panay OO Cost of Sales Page April 8 Regions Canada o o 0
18. specified even new components of a business model which themselves are created entirely within PowerOLAP 1 e not dependent on any static model of business data gt Select Model Dimension Double click Accounts in the Dimension list box The Accounts Hierarchy dialog box is displayed Click on the Create New Member button on the toolbar Type Margin so that it appears in the Members list box To modify the Accounts dimension hierarchy Expand Accounts in the Hierarchy Definition box on the right Select Margin from the Members list box and drag it to the Hierarchy list box and release it just under Accounts The completed hierarchy will appear as in the following figure E ES CCOUNLS SEJ Margin E e 5 Gross Profit o 4 Net Sales Click the OK close the Dimension Hierarchy dialog Click OK in the Dimensions dialog box Margin now appears as the top row in the Accounts by Month slice Next you will define a Cube formula that creates values for the new Margin the values will be based on a formula that divides Gross Profit by Net Sales PowerOLAP Quick Start Manual 37 gt Select Model Cube Double click the Current Year Budget cube Press Enter twice in the Content Area to move the previously written formula down two lines Begin on the top line of the Formula Content atea z e place this formula above the other Priority which is top to bottom in the Formula
19. text boxes for you to enter choices for the Months Accounts and Regions dimensions In fact February has been pre selected for you If you wanted another Months member for your formula you would press the Pick button to make a different selection Since you do want to select February data continue to the Accounts and Regrons dimensions Use the Pick button and the corresponding Select A Member dialog boxes to choose data for Gross Profit and United States respectively After you have made these choices the Edit PowerOLAP Formula dialog box will look as follows 44 Power OLAP Quick Start Manual Edit PowerOLAP Formula OLAPRead C Program FilesiPowerC kJ Cif United States Click OK to update Excel with the new formula reference Your dynamically connected Excel spreadsheet will appear as in the following figure You now have a ready view of the February Gross Profit tor United States within a dynamically connected spreadsheet that shows figures for Canada Now whenever February Gross Profit for United States or for that matter Canada changes it will be reflected in this worksheet You can save the current Database with changes you have made from within Excel gt Select PowerOLAP Save Current Database in Modeler The PowerOLAP database is saved but not closed term faced QE dore by Binge SEAL ua Inn HE gt T j pal i 8 sE Ya AeA T IE FA gee ERAM AD em ae
20. Cube feature for this feature see the PowerOLAP User Manual Furthermore with the Cubes now in PowerOLAP you can use all familiar spreadsheet capabilities with no loss of the everyday and powerful features Excel affords you A full explanation of working with Linked Cubes from Analysis Services occurs in the PowerOLAP User Manual What follows is a brief description of the procedure beginning with the configuration a connection from PowerOLAP to the Microsoft Analysis Services environment Assuming you have created a new PowerOLAP database or wish to create a Linked Cube in an existing database gt Select Tools PowerOLAP Services New Linked Cube Tools Synchronization Server gt New Linked Cube Update Linked Cube You will next be prompted for the Cube Provider x To link to an OLE DB compliant server you must first select a valid Provider The provider allows PowerOLAP to communicate to a specified database Provider MENTA Description Microsoft OLE DB Provider for OLAP Services Cancel Help Select a Provider e g MSOLAP as above and click Next The next dialog concerns the Server name Si ynpPEgmI xi The server specifies the location of the OLE DB compliant database If you are unsure of the server name contact your database administrator Server coca to Copy paste or type in the Server name then click Next 56 PowerOLAP Quick Start Manu
21. Slice Next you will demonstrate another key function of the Slice using a Slice to enter data directly into the underlying PowerOLAP database Currently the data in the Current Year Budget cube 1s all zeros because it is a new Cube and data has not yet been entered into it To enter data into the Accounts by Months slice Click the cursor at the intersection of January and Net Sales Type 5000 then press Enter Click the cursor at the intersection of January and Cost of Sales Type 3000 then press Enter Notice that PowerOLAP has automatically adjusted the values in data cells that occur at the intersection of Ageregate members reflecting the values entered above PowerDLAP Current Year Budget gt Accounts by Month O file Edi View Model Sloe aia Jodi Window Help 218 x jale xaje ee is anion pel drian ameni Try to type 100000 at the intersection of Total Year and Gross Profit PowerOLAP does not allow you to change the values in data cells involving one or mote Ageregate members PowerOLAP will automatically update these cells only when the values in relevant Detail members change Setting General amp Format Preferences Before continuing to work with data in a Slice we will take a look at some preferences among which are those that affect the look of a Slice General Tab Selecting the Options command from the Edit menu enables you to set several general da
22. a breakdown of the syntax of the Cube formula Left of equal Area of cube to populate Right of equal Formula Months February Dimension and Member to populate Current Year Budget Source cube Months January Range within source cube 1 5 Value in this case 50 Ends formula statement gt Click OK to save the formula If you have mistyped the formula you will recetve a message indicating that there is syntax problem You ate returned to the Cubes dialog box Click OK Press F9 inn the Accounts by Month slice to recalculate values Notice that the February column has been populated by the Cube formula defined in the previous steps 36 PowerOLAP Quick Start Manual Net Sales 1250000 00 500000 00 750000 00 250000010 300000 00 450000 00 Cost of Sales STO i 500000 00 0000000 The Cube formula has populated February throughout the database 300000 00 Further hierarchy calculations like Gross Profit update automatically Next you will create a Cube formula that calculates a ratio of two Members You will first need to add a new Member Margin to the Accounts dimension and then modify the Accounts dimension hierarchy This Cube formula exercise points up two important strengths of PowerOLAP in comparison to static modeling tools OLAP or otherwise the capability to create new on the fly calculations which can of course be subsequently saved for precisely
23. al PowerOLAP finds and connects to the Server available databases on the Server are shown in the ensuing dialog xi OLE DB servers allow data to exist in multiple databases In order to access a specific cube you must select the database in which it resides Below is a list of the databases that reside in the selected server Database EAN lt Back Cancel Help Select a Database as above FoodMart 2000 then click Next The Cubes available on the database are shown in the ensuing Cube dialog drop down shown below You access one Cube at a time to become a PowerOLAP Linked Cube xi OLE DB databases may contain multiple cubes Below is a list of the cubes that reside in the selected database p Cancel Help Click Next The Read ReadWrite dialog appears Read ReadWrite xi PowerOLAP allows both read and write capabilities to OLE DB compliant cubes Assuming your provider supports write back you may choose to use the ReadWrite function See your database administrator if you wish to use write back but you are not sure your provider supports it C Read Readwrite lt Back Cancel Help The final step is to select whether the method of creating the Linked Cube should be a Read or a Read Write formula OLEDBRead will read data from the data source but the data is not editable thus it is Read Only OLEDBReadWrite also brings in data by reading the data source but it
24. also makes the data editable so that it is possible to write back to the source if the cell is a Detail 1 e leaf level cell Thus select Read or ReadWrite next click Finish to complete the Linked Cube creation The progress bar will appear onscreen until the process is complete When finished the following message box appears PowerOLAP x AN Successfully created a new linked cube gt Click OK You are returned to the PowerOLAP main application window PowerOLAP Quick Start Manual 57 You can next select Model Cube to see the Linked Cube itself or select Model Dimensions to see what Dimensions have been created gt To determine what a Slice looks like select Slice New F4 Budget Untitled 2 P8 Account All Account 1997 398755 69 DO D 0 00 8 Category All Category 1997 01 98068 78 00 8 Budget_Measures Amount 1997 01 11 31686 50 00 1997 01 2 30205 60 D0 O0 00 oo 00 00 DO DD D0 DO 00 DO 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 0 00 19971101 36176 68 1997 02 91106 70 1997 102 14 29025 06 1997 02 15 30603 10 1997 1921 16 31478 54 1997 103 98591 28 1997110317 36353 69 1997 03 18 32305 85 1997110319 29931 74 1997 04 110988 93 1997 04 110 25448 04 0 00 1997 Q4 11 39469 48 0 00 1997 04 12 43071 41 0 00 0 00 0 00 Rows 1998 103781752 29052 00 29052 00 22932 00 BA Time Al 1998
25. appears as below A 8 6 I e EE e Database C Program Files PowerOLAPExamples aS Diatabase olp 2 Cube Current Year Budget 3 Page Regions Canada 4 Months Along Columns 5 Accounts Along Rows E OLAPTable E Total Year January February March April Miary oO Margin 72 30769 20 20 O 100 0 9 Net Sales 1625000 250000 375000 O 1000000 0 10 Cost of Sales 4650000 160000 270000 ol ol 0 ee oross Profit 1175000 fOO00 105000 Q 1000000 0 Now to see the dynamic connection back to the PowerOLAP cube Return to the PowerOLAP Accounts by Months slice showing Canada as the Page Member Press F9 to update PowerOLAP The Slice appeats as follows pq Current Year Budget Accounts by Months loj xj 37 93 28 00 28 00 0 00 100 00 0 00 725000 00 250000 00 375000 00 0 00 100000 00 0 00 450000 00 180000 00 270000 00 0 00 oof 000 275000 00 70000 00 105000 00 0 00 100000 00 0 00 Columns 8 Months Subset Rows 8 Accounts All The data you entered in the Excel worksheet is now reflected in the PowerOLAP database Because PowerOLAP s function connecting to the worksheet OLAPT able in this case is bi directional you can enter data in either Excel or PowerOLAP and select F9 to update Note you can not write into Aggregate member spreadsheet cells or cells governed by a Cube formula just as in a Slice PowerOLAP Quick Start Manual 41 The strength and power of the spreadsheet co
26. ar in the dimensional hierarchy or in the Member list this tab is selected below Regione Histarchy Members Canada Urmbed State Werezuels Colombia Total Regens Hath Amenta south Amenta cues e gt Select Canada Click OK and then press F9 to update the worksheet The worksheet now shows data for the new Page Member Canada You can repeat this means of selection via the Select a Member dialog which PowerOLAP has made available in Excel to pick other countries in the Regions dimension Were this a fout five etc dimensional cube you could pick any number of Page members to view multiplying your potential sheaf of reports manyfold 40 PowerOLAP Quick Start Manual Entering Data from within Excel You can enter data into a PowerOLAP database using an Excel worksheet This has great applicability in forecasting planning and budgeting systems that use PowerOLAP All data entered into a worksheet is automatically updated using one of PowerOLAP s functions OLAPTable has been shown here each of which maintains a bi directional dynamic spreadsheet connection between PowerOLAP and Excel To enter data via an Excel worksheet gt Select a cell at the intersection of detail members such as F7 which is the cell at the intersection of April Net Sales gt Type 100000 Press Enter and then press F9 if Excel is set to Manual calculation to recalculate the worksheet The worksheet
27. assword and then verify it See the PowerOLAP User Manual Chapter 5 Applying Database Security for a full explanation of Security features PowerOLAP Quick Start Manual 3 The Allow Reserved Characters checkbox allows you to use so called reserved characters e g double quote period comma etc in your database See General Options further on in this manual for a list of these characters The Synchronization Server area of the dialog refers to a PowerOLAP component that allows PowerOLAP databases to be synchronized via a shared file The Synchronization Server area is activated if your license includes Synchronization Server capabilities see the Synchronization Server manual otherwise it is grayed out as above Consult your Administrator to determine whether this tool is part of your application 4 PowerOLAP Quick Start Manual Click OK Note that in the PowerOLAP window all eight menu items appear and that several more toolbar buttons are now active Notice the status area circled in the figure From left to right the boxes indicate Whether you are working in Local or Server mode Server name will be indicated The Database Name and Synchronization Server Name if active Only one database file olp may be open at a time Therefore a new database can not be created if a database is currently open IMPORTANT If you are working as a Client to an MDB Server you cannot create a database on
28. changes made from within Excel into the PowerOLAP modeler and closing the PowerOLAP database knowing that you can reopen it from a normal Excel worksheet Now that you have grasped the concepts and demonstrated these many functions you are well prepared to use PowerOLAP in a production environment For more detailed instruction on using PowerOLAP and to learn additional features see the PowerOLAP User Manual On the following pages read more about two important advanced features of PowerOLAP OLAP Exchange and PowerOLAP Services 48 Power OLAP Quick Start Manual About OLAP Exchange Note This feature 1s enabled thro Licensing Check Edit Options Licensing to see 1f OLAP Exchange functionality is enabled on your system One of the key features of PowerOLAP is the back end integration technology OLAP Exchange which bridges the gap between relational and multidimensional worlds Through the rapid deployment of OLAP Exchange organizations can propagate data dynamically between transactional and analytical databases for advanced online real time planning budgeting forecasting analysis and reporting needs These next several pages provide an overview of the deployment of OLAP Exchange to create a Cube directly by sourcing data from in the example shown a SQL Server example database Please see the OLAP Exchange manual for a detailed explanation of the entire range of features and benefits of OLAP Exchange Keep these t
29. ching The sliding bar determines the size of the cache threshold PowerOLAP stores ageregation and formula calculations in cache based on the amount of cache available If the cache threshold is set to high PowerOLAP will continue to store calculations in cache hence decreasing the time to retrieve values If you have a large amount of RAM on the Server machine you can move the bar to the right If the cache threshold is set to low PowerOLAP will store only a minimal amount of information in cache and recalculations will be retrieved from the data store Move the bar to the left therefore to limit the amount of cache devoted to calculations if you have less RAM this may be the better choice You will continue to see calculations quickly though not as fast as if they were retrieved from cache 26 PowerOLAP Quick Start Manual Format Tab If you would like to make format changes to the numbers or the text decimal places colors font size as they appear in a Slice choose the Format tab Options General Format Licensing Portal Display Fonts Detail Data Arial 10 Read Only Data Arial 10 Font Detail Label Arial 10 Font Aggregate Label Arial 10 Font Reset to Defaults Number Format 0 00 0 0 0 00 F 0 000 0 000000 0 000e 000 0 000000e 000 LJ 0 000000000e 000 pes HHH HHH HHH NN OK Cancel Help E J From here you can ad
30. draw links between common fields akin to creating an ad hoc star schema In the preceding figure you see that the Order ID field in the Order Details 1 e Measures table has been linked to the Order ID field in the Orders table and the Customer ID field in the Orders table has been linked to the Customer ID field in the Customers table In this manner you can now proceed to use the last table Customers to supply the logic for the Customers dimension After the proper links have been created click Next The Define Members dialog appears In this dialog like the Define Members dialog for Measures you will want to specify the field that will supply Members for the Customers dimension A logical choice is the CompanyName field thus this field has been moved to the right hand pane in the following figure F Allow Duplicates FT Define Rollups I Partial Members I Define Drill Through J Name Blank Members UnDefined I Conditional Retrieval J Define Order E E Note in the dialog the further options available at the bottom right of the dialog These options are explained in detail in the OLAP Exchange manual It is worth noting here though the customization options that OLAP Exchange affords for dimension creation for example the ability to create your own rollups consolidated Members that you name or creating Member names in a certain Order or composed of a fixed number of characters After s
31. editor is very important for determining data calculations consult the PowerOLAP User Manual Using the Formula Editor dialog create the formula All and Accounts Margin Current Year Budget Accounts Gross Profit Current Year Budget Accounts Net Sales 100 Click OK to save the formula You ate returned to the Cubes dialog box gt Click OK Press F9 to recalculate values in the Accounts by Month slice Margin 40 40 4D 250606 500088750000 Cost of Sales 70000 300000 450000 Gross Profit 00000 200000 200000 Margin o values exist for all months based on the Cube Margin is now calculated for all Months in the Current Year Budget cube The figure above shows the Margin figures for United States You can select other Regions members to verify that all members in the Regzons dimension have been updated as well Select File Save All to save the data and the Slice which now includes Margin to disk 38 PowerOLAP Quick Start Manual Creating an Excel Worksheet One of PowerOLAP s key features is that it enables you to create an Excel worksheet from a PowerOLAP slice You can then work with data in Excel utilizing all that product s features and functions while maintaining a dynamic connection to the PowerOLAP database This is Gane why PowerOLAP is credited with having a spreadsheet front end Excel To create an Excel worksheet from the Accounts by Months slice Ny
32. eed under the assumption that you next wish to create a Customers dimension The objective which will be accomplished in the Define Links dialog is to work back from the eventual table that will supply the data for a Customers dimension to the Measures table This is a good example of the need to link via other tables back to Measures in this DZ PowerOLAP Quick Start Manual case knowing something about the logic of the underlying tables you could reach Measures by creating links via the Order table In other words first with Order Details table already showing in the Define Links dialog this crucial Measures table always shows as the first table in the link schema you would double click on the Orders table to make it show in the dialog then do the same for the Customers table x Tables El 21m Dimension Name Customers 9 Categories E CustomerCustomerD E CustomerDemograpr pi der Ord ers OrderlD int OrderlD int ide H CustomerlD n USTOMEerL i EmployeelD ir OrderDate dat RequiredDate E Employees EJ EmployeeT erritories EJ Order Details Address nvare City nvarchan Discount real ShippedDate 1 Shipvia int Freight money Phone nvarchz EJ Territories T Fax nvarchar DERECE 0AHLHEA ShipCountry nr md lt Back Cancel Help Next to define links between the tables hold down the top left of your mouse and
33. electing the field to supply Member names click Next The Define Aliases dialog appears In this dialog you can select a field from which to create coordinating unique PowerOLAP Quick Start Manual 53 Alias names for the Member Names vety useful if you wish to report on for example Customer ID numbers along with Customer names Click Next The Define Properties dialog appears Here too you can leverage the logic of underlying tables to report on Properties which need not be unique characteristics of Members Click Next The Create Hierarchy dialog appears In this dialog also you can construct the Dimension in such a way to ultimately analyze report on data according to the hierarchical logic of the elements in a table for example as shown below your Customers may exist within Countries or Regions of Cities which is recorded in the underlying table In the Define Hierarchy dialog you would drag Country under Customer in the right hand pane to create this hierarchy in the PowerOLAP dimension Note too that there is a provision checkbox bottom right to create an Aggregate Member for the Dimension here it will be Total Customers IV Create Aggregate Total Member coca to Click Next The Relational Dimension Summary for the newly created Dimension appears When you click Add again in the Dimensions you start the process of creating another Dimension via OLAP Exchange Assume for example
34. ep by step procedures to create a PowerOLAP model We recommend that new OLAP users become familiar with the Introduction to PowerOLAP as a prerequisite to this manual The Introduction also contains information about Version 7 of PowerOLAP including new features and their benefits By working through this Quick Start Manual you will learn many of PowerOLAP s elemental functions how to create databases Dimensions Cubes and Slices how to import data from a text file into a Cube and how to create an Excel worksheet from a Slice view This manual shows how to construct a model by working entirely with the product s modeler Note that especially if you are an Administrator or power user you will not build Cubes business models if you will only by the steps described here Instead many PowerOLAP Cube features Meta Data will be built using the OLAP Exchange component and the actual figures you work with Fact Data will likely come from another source However you will undoubtedly need to understand the concepts and follow the steps covered in these pages because they convey how to use customize and advance your own models These skills in turn will enable you to vastly increase the potential business uses and benefits of the product at your firm The PowerOLAP User Manual in contrast to this manual introduces PowerOLAP fundamentals by first allowing you to view and manipulate data within a pre built model then goes on to demon
35. hin the Member list box on the left in the dialog and select New Type January in the Members text box over the currently highlighted text e g Untitled3 Press Ctrl Enter Type February in the Membets text box gt Press Ctrl Enter The Member list box will appear as follows Months Hierarchy mi setja aef Maa a El RA ES o e Using this procedure enter the remaining months of the year these will be the Detail members in the Months dimension Next you will create what will eventually be the Aggregate members for the Months dimension in the same manner we say eventually because before a Hierarchy is created all Members appear with the Detail icon the number sign 8 PowerOLAP Quick Start Manual Press Ctrl Enter Type 1st Quarter in the Members text box Press Ctrl Enter Type 2nd Quarter in the Members text box Press Ctrl Enter 0 0 08 02 0 OQ Complete the Months dimension by adding 3rd Quarter 4th Quarter and Total Year You can double click on a name or use the right mouse button to rename or delete a name in the Member list box When you are done the Member list box will appear as follows 8 Month Hierarchy Subset E January February March April May June July August September October November December Tet Quarter 2nd Quarter 3rd Quarter If the Member names are not in the order you want click and drag the
36. hree important points in mind as you proceed OLAP Exchange apart from being able to source data from for example SQL Server tables can reach other ODBC compliant relational data sources Thus there is no fixed back end database or application that OLAP Exchange requires to create Cubes OLAP Exchange provides limitless customization options in the way it allows users to create Cubes Put another way OLAP Exchange does not force users to build so called standard Cubes for example a reporting Cube from a General Ledger only While in theory standard Cubes that can be built by off the shelf Cube generating products sound like a good idea in reality they can be extremely difficult to maintain and next to impossible to customize OLAP Exchange overcomes this serious limitation by allowing rapid individual customization in the creation of the initial Cube and in the creation of any number of subsequent Cubes required for planning analysis and reporting After creating Cubes via OLAP Exchange you still enjoy all the features available in PowerOLAP for sophisticated business specific modeling As a simple example assume you have created a six dimension Cube from tables in an underlying accounting package you can next create a Forecast dimension and use the six already created Dimensions to create a seven dimension Forecast cube Further you can populate this Forecast Cube via Cube Formulas and then create inpu
37. ialog appears Again Months is selected it is the Dimension we want to work with so leave as is Select January from the Member list on the right The Selected radio button is selected as a consequence Note that at the top of the dialog there is a Cubes drop down This points up an important feature the ability to create cross cube formulas which is explained in the PowerOLAP User Manual There is only one cube in our database Current Year Budget in the formula we are creating data will come from this cube to populate another area of the same cube The Build Cube Reference dialog appears as follows PowerOLAP Quick Start Manual 35 Build Cube Reference F x Cube Curent Year Budget y m Dimension m Members C All Selected 8 Accounts 8 Regions April May June July August September October November December Current Year Budget Months January Cancel Help Click OK The Formula Editor content area appears as follows you can hit Enter after the show the formula on two lines CE 27 to All and Months February TCURESHE cear Paget Months Janwary Use the buttons in the Formula Editor the asterisk for multiplication the numbers and the semi colon to complete the formula to that it appears like so All and Months February CUECA Wear Budget Months dJahuary Jj de Following is
38. ing Dimension hierarchies and assigning Ageregate weights results in the natural summing up of values Cube formulas represent a powerful extension of your ability to perform mathematical calculations throughout a PowerOLAP database With Cube formulas you can perform all manner of calculations to populate a cell ranges of cells even entirely different Cubes Presently your Current Year Budget cube contains data for January for all Accounts and Regions The following Cube formula will populate the month February Select Model Cube The Cube dialog box is opened Click on the Current Year Budget cube to select it Click on the Formulas button The Formulas dialog is opened To 7 8 9 7 Fomulas Dependencies Persistent Calculations EEN EA Eg beg Use Dependencies Unlimited Calculation Depth ia ew SSeS Eee Check Syntax ay Eri Functions ABS number ACOS number AGGREGATESUM dim m ALIAS dim group index ALIASTOMEMBER dim grc AND condition condition ASIN number Ja CHILDCOLINT dim memb CODE text We will now make use of some buttons in the Formula dialog also known as the Formula Editor These buttons will enable us to specify the area of the cube we want to populate with data and where the data will come from Click on the squiggly brackets 4H The Build Range Reference dialog appears Months is selected in this dialog at the top of the Dimensio
39. ions Selected Dimensions B Accounts B Regions T Numeric Data Only Select all of the dimensions in the Available Dimensions list box by clicking the gt button All three Dimensions are moved to the Selected Dimensions list box on the right Click OK Note that the Current Year Budget cube is now listed in the Cubes dialog PowerOLAP Quick Start Manual 15 Add ES Current ear Budget Delete Rename All of the Cubes dialog s buttons on the right are now activated These buttons control functionality associated with Formulas and setting Security privileges as well as the OLAP Exchange capability to push data ranges back to a relational database they are covered in depth in the PowerOLAP User Manual and OLAP Exchange manual respectively Click OK to return to the main application window The Current Year Budget cube is now ready for data input A 1 Creating a Slice View ID New Slice PowerOLAP provides a method for looking into a Cube to view and input data This means of viewing and inputting data is known as creating a Slice A Slice is a two dimensional view of a Cube that arranges data in a grid just as a spreadsheet does You can create slices on the fly to see any view of a Cube or you can save and re open Slices for ongoing data viewing or inputting Finally as you will see you can instantaneously create an Excel spreadsheet from any Slice view To create a Slice ca
40. ish to see in a drill through from an eventual Slice or even an Excel spreadsheet If you enable Drill Through the next dialog will allow you to specify those fields gt Click Next A Relational Dimension Summary dialog appears as in the detail figure below This dialog sums up the logic of the Dimension you just created Measure Table Name Order Details Dimension Name Order Details Dimension Members UnitPrice Dimension Members Quantity Dimension Members Discount Conditional SQL Conditional Retrieval Off Order By Preference Off Conditional SQL This completes the creation of the Measures dimension in this example Since this is a Sales Analysis cube built from the example Northwind SQL Server database Next you will create Customer and Product dimensions in order from the Cube to analyze and report on the Quantity Unit Price and Discount of the products your customers purchased Click Finish in the Relational Dimension Summary dialog The Dimensions dialog appears with the first 1 e Measures dimension listed Click Add to proceed to the creation of the next Dimension The Select Dimension appears again This time New Dimension is indicated radio button enabled Click Next If you were working on a second or further Cube you could select Shared Dimension i e one that was created for use in a Cube created previously The Define Links dialog appears on screen Let us proc
41. ive however this does not save modified Save Database new or modified Slices To save the database with the current name Select File Save Database To save the database under a new name select File Rename Database PowerOLAP provides an additional Save command Save All which is represented on the Toolbar by the following button a The Save All command will save the current database all Slices all entered data and additionally Save All will save any changes made to the Slice layout Before choosing this option decide if you want to save all your open Slices as they are currently configured 50 Power OL AP Quick Start Manual Importing Data from a File At this point in the example you have entered and stored some data in the Current Year Budget cube for the United States in January The next exercise will populate the remaining detail Regzons members by importing January data contained within a sample text file included with the PowerOLAP application This file Osdata txt is located in the CA Program Files PowerOLAP Examples subdirectory assuming that the C drive is where your Program Files directory is located created during the installation process Note that importing text data along with entering data via a Slice or an Excel worksheet are two methods bring data into PowerOLAP Another method is to dynamically integrate Meta and Fact data from a relational database via OLAP Exchange Select Data Import
42. just the appearance of the Display Fonts and the Number Format in PowerOLAP To change the appearance of these elements within Excel after creating a worksheet from a Slice as will be discussed shortly format the worksheet as you normally would For Display Font changes Click on the Font button corresponding to the element whose appearance you wish to change This brings up the Font dialog Font Eont Font style arial JR egular Aral Black od Tr Arial Narrow Tr Arriba Arriba LET Tr Arus Elk BT Tr Arz BT Tr Balloon Bd ET Effects T Strikeout T Underline Astovyze sige Hary E Script i westen al Select the preferred Font Type Style Size Effects Color and Script for your database and click OK Power OLAP Quick Start Manual 27 Number formatting refers to decimal places and commas in numbers Highlight the Number Format you prefer and click OK 28 PowerOLAP Quick Start Manual Licensing Tab The Licensing Tab provides information about your current license and the functionality available after activation It also provides for registering and activation of your product SS 0 ptions E General Format Licensing Fortal License Information User Info User training Company paristech Client ID 2060 Count United States Product Info Current Product PowerLAP Designer MDB Server Users 0 Synchronization Users O Lease
43. lled Regzons by Account Select Slice New The following New Slice dialog box is opened The list box displays the names of available cubes in your database In our case we just created the only cube listed Current Year Budget iNew Slice Cube Current ear Budget With the Current Year Budget cube selected click OK 16 Power OLAP Quick Start Manual PowerOLAP Current Year Budget Untitled 1 File Edit View Model Slice Data Tools Window Help gt aja le 2 8 lizia 2 4 Current Year Budget Untitled 1 Page Net Sales Cost of Sales Gross Profit 8 Months January Columns j Accounts All i r bi acs Rows local OS Database Z The Slice View dialog is opened in the Content Area of the main application window as in the above diagram The window displays a Current Year Budget slice as yet untitled and with no data in the grid Press F9 This manually recalculates the grid s data explained further below Keep in mind that as yet no figures have been entered into the Cube so you will see zeros as data throughout the Slice T PowerOLAP Current Year Budget Untitled 1 File Edit View Model Slice Window Help njala P 8 Months January Canada United States Columns 8 Accounts All a Rows For Help press F1 By default when PowerOLAP creates a new Slice it places the last Dimension brought into the cube when it was created in the Rows
44. m to the desired position in the list Proceed to the next page for the next step in Dimension design PowerOLAP Quick Start Manual 9 Creating a Dimension Hierarchy Once the Members have been defined for a Dimension the Hierarchy of these Members must also be defined The Hierarchy determines the aggregation of Dimension members You will now create the Hierarchy by selecting Members and moving them to the Hierarchy Definition box on the right side of the Dimension Hierarchy dialog We will proceed by creating the Hierarchy for the Months dimension Define the Hierarchy for the Months dimension as follows Select Total Year from the Members list box on the left Drag it so that it is placed just below Months in the Hierarchy Definition box on the right so that it appears as below e Months Hierarchy E x j Subset Ar y Fite als 0 8 ot 3 Hla E Bly January a Months February Total Year March April May June July August September October November December 1st Quarter 2nd Quarter 31d Quarter 4th Quarter Total Year Select 1st Quarter press the Ctrl key and holding it down select 2nd Quarter 3rd Quarter and 4th Quarter Place the selection just below Total Year in the Hierarchy Definition box so that a sigma sign for sum l amp l appears beside Total Year This indicates that Total Year is now an Ageregate member the sum of the Members you placed
45. n list it is the Dimension we want to work with so leave as is For the Qualifier top left select the radio button Details This indicates that only Detail members are to be calculated by the Cube formula Ageregate members will be calculated according to the Dimension hierarchy Note it is possible to overwrite hierarchy calculations via a Cube formula a very important feature if you wish to calculate a what if or budgeted Aggregate data point so that it contrasts to actual figures in Detail data points 34 PowerOLAP Quick Start Manual On the right select February among the Months The Selected radio button is selected as a consequence The dialog appears as follows with the formula as it exists so far at the bottom Build Range Reference x Qualifier Members C All C Aggregates Details C Al Selected Dimension January February 8 Accounts March 8 Regions April May June July JAugust September October CINovember December ist Quarter 2nd Quarter 3rd Quartner 4th Quartner xi Filter a Details and Months February m Pe Cancel Help gt Click OK Click on the equals sign in the Formula Editor E The left hand side of the formula is completed and is shown in the content area All and Months February i Next click on the square brackets wi The Build Cube Reference d
46. n the toolbar in the Dimension Hierarchy dialog and the functions they perform are as follows y aja a He Sal a 2 2 AAA 1 Create New Member Add a new Member or press Ctrl Enter to successively add new Members Format Select formatting option for the corresponding cells Member Aliases Define and edit Aliases and Alias Groups for the selected Member Alias Group Add or delete Alias Groups and assign names for each Member m EA S can be displayed on an Excel worksheet when needed 6 Mark Member as Persistent Tag a Member as persistent to prevent its data from being overwritten when updates or Cube re builds occur 7 Add Selected as Sibling Insert the selection in the Member list on the left into the Hierarchy Definition on the right below the selected Member 8 Add All as Sibling Insert all Members in the Member list after the selected Member in the Hierarchy making them sibling Members 9 Add Selected Members as Child Insert selected Member s in the Member list as children of selected Member in the Hierarchy 10 Add All Members as Child Insert all Members in the Member list as children of selected Member in the Hierarchy 11 Specify Member Weights Edit the weight of a child Member active only when a child Member is selected in the right hand Hierarchy Definition box 12 Help Accesses the help screen for the Dimension Hierarchy 13 OK Exits the dialog with changes made and sa
47. n under July and the three Members comprise 3rd Quarter Note that other buttons are available for Hierarchy creation Add all Members as Child 88 and Add All As Sibling button J Complete the Hierarchy for the 4th Quarter so that when you ate done the Hierarchy looks as follows Months Hierarchy Subset an e Fiter als bal sal el 2 gt January B Months February E Total Year March Sst Quarter April January May February June March July E end Quarter August April September May October June November E 31d Quarter December July gt Ist Quarter August E end Quarter September E Std Quarter E 4th Quarter E 4th Quarter October Saar E EN ewer December PowerOLAP Quick Start Manual 11 Close the Dimension Hierarchy dialog by clicking the OK button 7 the rightmost button on the toolbar You are returned to the Dimension dialog box Next you will create the Hierarchy for the Accounts and Regrons dimensions Add the following Members into the appropriate Dimensions You will define their type whether Detail or Aggregate according to the Hierarchies you see in the following two figures next page Dimension Member Type ACCOUNTS Net sales Detail Lost of Sales Detail Gross Profit _ Aggregate Regions canada Detail United States Detail venezuela Detail Colombia _ Detail Total Regions Aggregate North America Agg
48. nnection to PowerOLAP cubes are central to the use of the product PowerOLAP disburdens Excel of its calculation tasks hierarchies aggregate weights formulas are calculated in PowerOLAP s engine across specifiable multidimensional data ranges further PowerOLAP relieves users organizations of the difficulties of maintaining hundreds or more linked spreadsheets 42 PowerOLAP Quick Start Manual Defining Cube References The bi directional connection shown so far is OLAPTable function shown in cell A6 In fact when you click on cell A6 in the worksheet you will see in Excel s formula bar the single formula that references all the worksheet cells that connect to data in the PowerOLAP cube OLAPTable B 1 B 2 B5 R5 A6 A9 C 3 The OLAPTable function is one of many functions you can use to dynamically link data between a worksheet and a PowerOLAP database The other PowerOLAP functions for creating a worksheet from a Slice OLAPReadWrite and OLAPPivot and their differences are discussed in the PowerOLAP User Manual The next exercise will demonstrate how to define a Cube reference that returns a pertinent value into a cell by using the OLAPRead function Select cell D13 in Excel in the current worksheet In this cell you will define a Cube reference formula that shows the Gross Profit for United States in February in order to make a quick visual comparison to Gross Profit for Canada which appears in cell D9
49. ohonlzation Server Hone Create a Synchronization Server gt Create Local Database From a Synchronization server e Cancel Help Click Browse in the Database Name text box 2 PowerOLAP Quick Start Manual In the Save As dialog type QS Database in the File name text box and follow the directory path C Program Files1PowerOLAPX Examples Database rd GE File Save in Examples e E Fae J S File name as Database Save as type PowerOLAP Files olp y Cancel Me A O Click Save The New Database dialog returns displaying the Database Name as well as the path and file name of the database file In this case the Database Name will be the same as the File Name shown in the following figure you have the option to type in a different Database Name New Database x Database Database Name E 5 Database File Warne C Program Files PowerJLAPSExamplesO5 Databaze o Secure Database W Allow Reserved Characters Sinehronization Server None f Greate a Synchronization Server Create Local Database From a Synchronization Server OF Cancel Help Notice the Secure Database and the Allow Reserved Characters checkboxes Leave the default settings unchecked and checked respectively The Secure Database checkbox enables you to require a password to open the database Thus if you check the box then click OK you will be prompted to give a p
50. regate South America _ Aggregate When complete the Dimension Hierarchies for Accounts and Regions should look as follows Accounts Hierarchy f X Subset All v Filter s tl sa 7 s gt 23 ES KAKA Net Sales Accounts Cost of Sales E Gross Profit gt Gross Profit Net Sales Cost of Sales 1 Accounts Hierarchy dialog box 4 Regions Hierarchy o swe e lp a of 0 la al El BI gt Total Regions 8 Regions gt North America Total Regions Canada 5 5 North America United States Canada South America United States Venezuela 5 5 South America Colombia Venezuela Colombia Regions Hierarchy dialog box A hierarchical relationships as you completed in the three Dimensions Months Accounts and Regons defines a parent child relationship between Members Just as we have member siblings that exist on the same level in a Hierarchy under an Aggregate member we also speak in terms of chi d members which are defined as all Members that make up parent aggregations It is important to note that a child member is not necessarily a Detail member Child members may themselves be parents of other Members within a Hierarchy For example in the Regzons dimension North America is an Aggregate member but it is also a child of Total Regzons 12 Power OL AP Quick Start Manual Specify Member Weight The Dimension Hierarchy Toolbar The buttons o
51. rts or templates from Slice of the Cube you will enjoy the capability of an online dynamic data flow from underlying relational database tables through to your desktop With a few clicks you can Update Cube for Incremental Data that has been recorded in relational tables if the Cube supports Incremental Updating or Rebuild the Cube completely These and other more advanced functions are accessible via Data OLAP Exchange on the PowerOLAP application window Note that OLAP Exchange also provides for sending OLAP data back to relational tables in this way OLAP Exchange is truly bi directional Please consult the OLAP Exchange manual for full details PowerOLAP Quick Start Manual 55 About PowerOLAP Services Note This feature is enabled thro Licensing Check Edit Options Licensing to see if Analysis Services functionality is enabled on your system PowerOLAP Version 7 includes a dynamic front end capability to Microsoft Analysis Services cubes and to other OLE DB for OLAP compliant cubes This capability is known as PowerOLAP Services PowerOLAP Services can be employed for a number of critical uses particularly given the power afforded the user in the PowerOLAP Cube Modeler Dimensions can be added going forward with a few clicks just as you have demonstrated in the preceding exercises Data can be sent into new Cubes created in PowerOLAP via the Formula writer which you also demonstrated previously or via the Transfer
52. s You will recetve the following prompt indicating that the import was successful PowerOLAP x AN 9 Data point s successtully imported Click OK You ate returned to the Accounts by Months slice Select Slice Open to access the Open Slice dialog Double click on Regions by Accounts within the Slice list box The Regions by Accounts slice now displays the data just imported 32 PowerOLAP Quick Start Manual _PowerOLAP Current Year Budget Regions by Accounts Iof xi File Edit View Model Slice Data Tools Window Help 8 x nEle el e deajeajaj 2 Pa etsales Cost of Sales Gross Profit 10116511 8 Months January 250000 180000 70000 500000 300000 200000 300000 250000 50000 150000 200000 50000 Columns Total Regions 1200000 930000 270000 Wir North America 750000 480000 270000 South Americz 450000 450000 0 Rows 8 Regions All ya For Help press F1 Loca OS Database 5 The import file populated Net Sales and Cost of Sales data for January for all Members of the Regions dimension PowerOLAP automatically calculated the cells at the intersection of Aggregate members for example Net Sales for Total Regions etc Select File Save All to save changes you made both to data and to slice views Close the Regions by Accounts slice leaving open the Accounts by Months slice PowerOLAP Quick Start Manual 33 Creating Cube Formulas You have seen how creat
53. ssccsssccsssccsssccsssccesscosescoeess 23 CUE AAA EE EE E E acneates senacioncctaddeaincddasineddastonsatetaieeateonetes 23 Formae Tadeo Zi A PP 29 O ea ane te acta cst E E NE 29 Importing Data irom a Fleitas 31 Creatine Cube Formulas anni idiota 34 Creatine an Excel Works 39 Selcctinea Pase Member to Viewin Excel id di 40 Entering Data rom wien EX Cel assein a i a a ninahina 41 Detmine Cube R Er ences ia 43 lomo aD DASE a aaa 47 Summary of Quick Start EXeFCISes iii 48 ADOUCOLAP EXCHan ge Sii A 49 About POWerOLAP Services siniiiliiiolealciiciciiiado 56 Power OLAP Quick Start Manual A PLEASE NOTE Some exercises in this Quick Start manual and in the PowerOLAP User manual can not be performed with the Lite version of PowerOLAP client Modeler software Please contact PARIS Technologies to receive a temporary license for a full user version which will allow you to follow all exercises in these manuals PARIS Technologies Inc 200 Hyde Park Doylestown PA 18901 215 340 2890 www paristech com 12 Power OL AP Quick Start Manual Quick Start Note Some of the Features such as Analysis Services OLAP Exchange and HTTP web connectivity are activated for use only when appropriate License is used After activating the software check Edit Options Licensing to view the functionality on your system Quick Start Introduction The PowerOLAP Quick Start Manual is designed to introduce you to the fundamentals of PowerOLAP by using st
54. strate how to perform and apply higher level functions That manual explains more comprehensively PowerOLAP s end user features and functions Therefore please refer to the PowerOLAP User Manual for more detailed information on features discussed in this manual Also see the OLAP Exchange Synchronization Server or MDB User Manual for their respective features and functions and how they are applied to create superior business solutions PowerOLAP Quick Start Manual 1 Creating a PowerOLAP Database Creating a PowerOLAP database is the first step in developing an application to store and 4 model your data The PowerOLAP database file which has an olp file extension will C aE e contain all the components of your model As you will see these components include New Dimensions and their Members Cubes Cube Formulas and Slices which display your data Database Let s start by creating a new database which you will name QS Database short for Quick Start gt From the Start Menu select Start Programs Power OLAP The PowerOLAP main application window appears File Edit View Help oslas ee 3 0 mlizi 2 W For Help press F1 ocal I Select File New Database or click on the New Database button on the toolbar The following New Database dialog box is displayed Mew Database Databaze Database Name File Name Browse I Secure Database W Allow Reserved Characters SPH
55. t Excel templates for key Forecast sales assumptions You have already performed these steps in this manual with Cubes created via OLAP Exchange you can truly put to use all those capabilities to create a unique powerful sophisticated solution for your company Power OLAP Quick Start Manual 49 The following is an overview of the steps to follow when creating a Cube via OLAP Exchange again these steps are explained fully in the OLAP Exchange manual To build a Cube from a relational source you can begin by creating a new PowerOLAP database or you can create a Cube in an existing database proceed next by selecting OLAP Exchange as follows Select Data OLAP Exchange Create Cube Data Tools Window Help Import b R La Export gt Transfer Cube Persistent Calculations gt OLAP Exchange Create Cube Cube Properties Modify Relational Dimension Rebuild Cube Update Cube The Create Cube From Relational Source dialog appears In addition to naming the Cube and specifying security to access the Cube in this dialog you will configure the OLAP Exchange Data Source click on Configure using Data Source naming conventions corresponding to the Driver to the source relational database The following image shows a completed dialog with SQL Server indicated as the data source x Data Source 5 Driver SQL Server Configure Name OLAPExchDemo User Password a m Cube mM Incremen
56. tabase In either case all open Slices will be closed along with the database As noted earlier you can save and close any dynamically connected worksheet as a normal XLS file Upon opening such a worksheet when you press F9 PowerOLAP launches and a spreadsheet system with OLAP cubes behind it is ready for online optimized planning analysis reporting PowerOLAP Quick Start Manual 47 Summary of Quick Start Exercises In the preceding pages you very quickly learned important basic concepts and fundamental functions of Power OLAP including Creating a Power OLAP database the first step in building a Cube to model multidimensional data Creating Dimensions adding Members to those Dimensions establishing a Hierarchy among Members whether Detail or Aggregate and assigning an Aggregate Weight to a Child member Creating a Cube from Dimensions and their respective Members Creating a Slice arranging Slice dimensions selecting Page members to view and changing the layout of the grid within a Slice Setting general and formatting preferences from the Edit Options Menu Entering data in a Slice and seeing how PowerOLAP automatically recalculates Aggregate members to reflect changes in value Then saving those changes to a database Importing data from a file into a Cube Creating Cube formulas Creating a fully functional Excel worksheet from a Slice and defining database reference formulas Saving
57. tabase options Select Edit Options The Options dialog box is opened with the General tab on current settings PowerOLAP Quick Start Manual 23 General Format Licensing Portal Calculation Mode Create Excel Worksheet Using OLAP Automatic C Manual Readwrite Table Pivot Database Files Automatically save Database every fi 5 Minutes Automatically open Previous database on application launch IV Allow Reserved Characters Export Format ANSI 7 m Excel Connection M Use Default Directory Use Excel Language Wersion Use English v m Caching a Low Cancel Help Calculation Mode As discussed previously the radio buttons will allow you to show Slice changes and data entry calculations upon entry Automatic or upon pressing F9 similar to your experience with Excel 24 Power OLAP Quick Start Manual Create Excel Worksheet Using OLAP Read Write Table or Pivot These concern the data connection function used to create Excel spreadsheets from PowerOLAP slices You will create an Excel worksheet momentarily these functions are discussed in detail in the PowerOLAP User Manual Database Files When Automatically save Database every is selected you can save an active database periodically every X minutes by adjusting the spinner scrollbars or entering a number directly in the text box When Automatically open Previous database
58. tal Updating Name 4 Support Incremental Updating Sales Analysis z Periodic every 5 minutes M Numeric Data Only FT Multi Dimensional Editing Allowed Allow View Tables Live On Demand Cancel Help Note some of the other selections in this dialog including the bottom right area concerning Incremental Updating This feature enables OLAP Exchange to integrate only the incremental data that is recorded in an underlying relational database rather than needing to rebuild a Cube for each update Note that Incremental Updating can be done periodically live or as shown in the figure on demand Click OK You will next see the Dimensions dialog blank since you have just started building the Cube Click Add in the Dimensions dialog In the Select Dimension dialog the Measures Dimension radio button will be selected the Measures dimension is the first Dimension you will build This Dimension will be based on the table in the underlying database containing most typically the Fact Data values that you want to analyze and report on and which you may use as the basis for further modeling e g for budgeting forecasting Click Next in the Measures Dimension dialog The Select Measures Table dialog appears 50 PowerOLAP Quick Start Manual Here you see for the first time Power OLAP reaching through from the multidimensional world OLAP to the relational word all the underl
59. the Dimension text box at the top of the dialog Click the Add button The dialog appears as in the following figure wS 8 Delete Rename Type Accounts in the Dimensions text box Click the Add button O O Type Regions in the Dimensions text box 6 Power OLAP Quick Start Manual Click the Add button You can use the Enter key in place of clicking the Add button to add Dimensions to the database Once you have entered the all of the Dimensions above the list box will appear as in the following figure Dimensions Add Delete E Accounts p 8 Rename Click OK You are returned to the PowerOLAP main application window PowerOLAP Quick Start Manual 7 Create New Member Create New Member Adding Members to Dimensions Dimensions are composed of Detail and Aggregate member types Detail members add up to Ageregate members For example in the Months dimension you would make January February March all Detail members add up to 1s Quarter Aggregate member To add Members to a Dimension Select Model Dimensions You are returned to the Dimensions dialog Double click on Months or select Months then click the Edit button Months Hierarchy o The Months dimension is currently selected for editing as indicated in the Dimension Hierarchy dialog title bar Months Hierarchy Select the Create New Member icon ot right click wit
60. ved for the Dimension The functions listed above are described more fully in the PowerOLAP User Manual though we will now discuss Aggregate weights in order to complete our QS Database dimension set up Aggregate Weights Aggregate weights are used when a Member must be assigned a multiplied value in an aggregation in order for the data to be properly represented For example in the Accounts hierarchy Cost of Sales should be handled as a negative number in the Gross Profit aggregation because it will represent an amount subtracted from Gross Profit 1 e Gross Profit Net Sales Cost of Sales To designate Cost of Sales as a negative number in the Gross Profit aggregation we can assign it a weight of 1 This means that the data included in Cost of Sales will always be multiplied by 1 when it is rolled up in the Hierarchy PowerOLAP Quick Start Manual 13 Properties Assign Members a property for annotation purposes A Member s properties To weight Cost of Sales complete the following process Return to Accounts Hierarchy dialog box double click on Accounts in the Dimensions dialog and double click Cost of Sales in the Hierarchy Definition box on the right Note that to the right of Cost of Sales a box appears where 1 is highlighted and where you can enter a welght for the Member Enter 1 in the box so that it appears as follows gt Accounts Hierarchy aj Subset
61. ying tables are shown in the left hand pane Define Measures Table Source Tables E Categories EJ CustomerCustomerDemo E CustomerDemographics E Customers EJ Employees j EmployeeT erritories 1 Order Details E Orders EJ Products EJ Region E Shippers E Suppliers EJ Territories Dimension Name Order Details coca to By selecting table on the right you will see the fields from that table in the right hand pane you can even click on the Preview button to see the first few rows of the table and its fields This will enable you to determine if in contains the Fact Data in question gt Click Next after determining the Measures table The Define Members dialog appears PowerOLAP Quick Start Manual 51 Define Members x Fields for Order Details Members OrderlD int UnitPrice money T Quantity smallint Discount real F Allow Duplicate F Define Rollup F Partial Member Define Drill Through I Conditional Retrieval J Define Order coca to The Define Members dialog is where you will make the selection of fields that supply the Fact Data points for the Cube for example in the figure above Unit Price Quantity and Discount Note also the options at the bottom right these enable you to create an on the fly Conditional Retrieval statement for bringing over the Fact Data e g Unit Price gt 1 and to indicate what fields you w

Quick Start Manual

Contents

Download Pdf Manuals

Related Search

Related Contents