BusinessObjects Analysis



Business Intelligence/ANALYSIS with SAP BusinessObjects Analysis, EDITION for microsoft officeNitin Kalé, USC (revised: B. Travica, um)Ver. 4.3.2, March, 2014ObjectiveThe objective of this lab is to practice using an SAP datawarehouse and a cube built form it. In other words, students will practice to derive business intelligence from a multidimensional source – SAP BW InfoCube. This includes creating queries in SAP Business Explorer (BEx) Query Designer, and analyzing query outputs with Excel augmented by SAP BusinessObjects Analysis 1.3.ActivitiesUsing dimensions and measuresOLAP techniques, crosstabs, pivoting, sorting, filtering, hierarchiesConditional FormattingCharting and presentationsTrend discoveryScenarioAs a business analyst for Global Bike Company (GBI), you have been given the task of exploring the GBI data warehouse by creating queries that discover critical business information that can be used for decision making. With BusinessObjects Analysis, you will use powerful multidimensional OLAP techniques to perform data analysis and visualization. Historical sales data for GBI has been modeled and loaded into an InfoCube within SAP Netweaver Business Warehouse. This will be the data source for the queries you will create. Software PrerequisitesSKIP TO THE NEXT SECTION!Please refer to the software requirements within the curriculum folder for SAP BusinessObjects Analysis (posted on UAC website). From the Chico 2012 workshop manual:Windows XP or higherMicrosoft Excel 2007 or 2010Install SAPGUI 7.20Go to Username: & password: provided by instructor Install SAPGUI 7.20 for Windows OS. Choose between SAP GUI 7.20 C1 P6 Automated Self-Extracting Installer or SAP GUI 7.20 C1 P7 Manual Install Enable the option to install SAP Business Explorer Query Designer (Windows only)Install the BI AddOn Patch for GUI 7.20 (from the same website)Test: - SAP logon should be on the screen and a SAP Front End folder in Start programs. - A new folder Business Explorer appears in Start/All Programs, containing Analyser, Query Designer…Install SAP Business Objects 1.3, including Analysis with Excel and PowerPointGo to ? User: sap, Password: sapgui4meUnzip the folder. Install SAP BusinessObjects Analysis 1.3 (choose the version appropriate for your computer setup. Note: use the 64-bit installer only if MS Office 2010 is 64-bit (i.e., if you run 32-bit Office on Windows 64-bit platform, use the 32-bit installer).During installation, expand Analysis, edition for Microsoft Office and enable all features. 1.1, 1.2, 1.3Test: A new folder SAP BusinessObjects appears Start/All Programs, containing Analysis for MS Excel & PowerPoint. When started, MS Excel will have a new tab Analysis (depending on a LAN setup of MS Office, the user may need to start a standard Excel first, enter his/her name via File/Options for the session, then re-start Analysis for Excel.)1856105112204500CREATING CONNECTION TO DATAWAREHOUSE AND LOGon You first need to create a new connection to a server called Magdeburg, which runs the SAP datawarehouse and the cube that was created for this laboratory exercise. Click on SAP Logon icon. In the menu across the top of the logon form, click the New button (looks like a piece of paper). On the next form Create New System Entry, double-click User specified system. On the expanded same form, make these entries:Description: Belfast BWApplication Server: belfast.cob.csuchico.eduInstance Number: 90System ID: BELClick the Finish button.To log into the server Belfast BW make the following entries in the screen as shown below:Client: 800 User: UOFM-xxx (Replace xxx by a number associated with your name in the table below; e.g., UOFM-001)001Bain, Kyle H.002Cabral, Steven003Carter, Bryce A.004Dang, Nathan005Fidler, Scott C.006Fiks, Amanda K.007Gauthier, Charles008Guzzo, Roman E.009Hekle, Alan J.010Murray, Daniel E.011Oliveira, Sarah d.012Teschuk, Cameron F.013Yakubu, Maimuna U.Password: SAP4US (case sensitive)Language: ENClick the Enter button (green checkmark).Once in the SAP system (data warehouse that SAP calls Business Warehouse), you want to access a data cube that was created by your instructor. Follow the steps below.When you are logged in the SAP data warehouse server, click on the SAP Menu button. 624205-132715000Select Modeling Data Warehousing Workbench: Modeling. (Answer Yes or No to the question on navigation tracking that pop us.)Note: Menu navigation is by clicking the arrowhead preceding a menu item. If there is no arrowhead, the displayed item should be double-clicked.Focus on the InfoProvider part of the screen and find UofM. If you do not see InfoProvider part of the screen on the first logon, you may need to navigate to it. Here is an example of what the path may be like. You should find an item called GBI Reporting Master associated with the name of your university.33528079502100112014044450000 Right click the GBI Reporting Master (InfoCube), and choose Display. You should see the content of the InfoCube, such as Key Figures and Dimensions. Expand the dimensions – Data Package, Time, Unit, Product and Customer. Within the dimensions, you see various characteristics. Expand the key figures and the Navigation Attributes. 760730387096000715010165608000Log off the SAP BW by clicking in the uppermost menu System/Log off. (Answer Yes to the question about losing data.)Designing Queries in BEx Query DesignerThe sales data within this InfoCube (cube) may inform decision making in marketing, product development, customer service, etc. Discovery of hidden trends, associations, behavior, and patterns are the main objectives of the following analysis. To query the cube, you need to create a query by using modules of SAP analytics. You will use SAP Business Explorer (BEx) Query Designer that runs on your computer. Once saved, the query will be stored in the SAP BW for future use.To open BEx Query Designer, go to Start Programs Business Explorer Query Designer. The SAP logon form pops up. Log into the server Belfast BW as before. (This step could take a while. A forms inscribed with ”SAP NetWeaver” may show up.)Get familiar with the entry screen of BEx Query Designer. InfoProvider is your cube. Click on Query/New in the menu at top of screen.In the Search in drop-down list close to top of the form, choose Find). Focus at the part to this form entitled “Search Method,” and in the search cell type MU40000 (that’s 4 zeroes). Check the option “Search in Technical Name.” Click button Find. The output should be the label GBI Reporting Master displayed in the lower part of the form. Click GBI Reporting Master and the button Open. You are back in the query designer. You see the key figures and dimensions of the GBI InfoCube in the InfoProvider panel. Expand the dimensions by clicking the plus signs. You see the characteristics that make up the dimensions.At the bottom of the screen, click Rows/pare your screen with the figure below. You should see the InfoProvider section populated, while the sections on the right (rows, columns, etc.) are not populated yet.43510208890Properties Panel00Properties Panel17754608890Free characteristics Panel00Free characteristics Panel-1143008890Cube InfoProvider00Cube InfoProvider48768002038350Preview Panel00Preview Panel-1143002914650Characteristics00Characteristics-2438401969770Dimensions00Dimensions-297180560070Key Figures00Key Figures30251404491990Rows/Columns Panel00Rows/Columns Panel12496804491990Filter Panel00Filter PanelThe query designer offers an easy ‘drag-and-drop’ user interface for building queries. You can edit, copy, reuse, and delete queries.Designing a QueryOpen the Rows and Columns panel (if not already opened)Expand the Product dimension, and drag the characteristic Material (the label used for products) into the section Rows. In other words, click on Material, keep the button down, move cursor to Rows, and release mouse button; this drag-and-drop procedure applies to the entire exercise. From Customer dimension, move characteristics Customer and Sales Organization to Rows.Note: If you move an item to a wrong place, right-click it and choose Remove in the pop-up menu.From the Key Figures folder, drag all of the key figures (CoGM, Discount, Net Sales, Revenue and Sales Quantity) into columns (MS Windows methods for multiple selection should work: while keeping the key Return pressed, click each item to be dragged).Observe the Preview panel (section) to see the layout of key figures and characteristics (order) as they will be displayed when you run the query in Analysis.Move the following characteristics into Free characteristics: From Product dimension, move Division and Product Category; from Customer move Country; and from Time move Calendar Year/Month, Calendar Year, and Calendar Month. Free characteristics are those available to be included in query results but are not displayed in the results when you first run the query. Saving the queryChoose Query/Save Read this whole step before deciding how to name your query!In the form that showed up, choose Favorites. In Description type the name of the query following this format: <course-id> <user-id> GBI Query <your name or some other identifying detail>. course-id is MU40user-id is your ID listed in the table above (e.g., 001)GBI Query indicates the type of object your name or other detail helps distinguish your query from others. Example: the query name with these specs is: MU400001 GBI Query Kyle Bainc. Click Save. (The bottom of the screen should have a space for messages and report that the query was saved.)Close Query Designer (click Query/Exit)analyzing data using sap businessojbects analysisYou are now ready to use the query you had designed to get data from our data source (InfoCube) and then analyze it using SAP BusinessObjects Analysis. In these exercises, you will use SAP front-end module for analytics called SAP BusinessObjects. It works with MS Excel (running on your computer), and your query and the data cube (running on the Belfast BW server).Analyzing data using SAP BusinessObjects Analysis, Edition for Microsoft Office, ExcelGo to Start Programs SAP BusinessObjects Analysis for Microsoft ExcelMicrosoft Excel should start up now and you should see an additional Tab labeled Analysis as part of the top level menu (along with File, Home, Insert...). 30924508636000If you do not see the tab Analysis, do this:- Close the current instance of Excel- Start standard Excel - Follow the path: File/Options/General - In the textbox User enter initials of your name and click OK- Try again: Start Programs SAP BusinessObjects Analysis for Microsoft Excel- If you still do not see the tab Analysis, try: File/Options/Add-ins/Manage/COM Ad-ins/Go. Analysis should be listed; check it and click OK. Click the Analysis tab. Click in Cell A1Click on Insert and choose Select Data Source…On the next communication form Logon to SAP BusinessObjects…click Skip (note: skipping BI platform means you can store the workbooks locally on your computer or you can store them in SAP BW).In the Select Data Source screen, choose your server Belfast BW. On the next form, for Client enter 800 and type in your login credentials. Click OK. Once you get the form Select data Source, there are two tabs for finding your query. Use one of the following two methods:In the Search tab, search for <course-id><user-id> * to list your query. Select your query and press Enter on the keyboard. (Searching may take a while.)In the Folders tab, change the View filter to InfoAreas. Then find your <course-id><user-id> GBI Query. Select your query and click OK.Once you see your query, mark it and press OK, or double-click it. Optionally, the query output may be displayed.You now see the results of your query in SAP BusinessObjects Analysis. Explore the various sections and the display of Analysis below (positioning of screen details may not be identical to yours).3390900189230Analysis Tab00Analysis Tab1695450189230Key Figures00Key Figures5029200189230Design Panel00Design Panel209550189230Dimensions00Dimensions52959003887470Background Filter00Background Filter34671003801745Rows –Dimensions in the Cube00Rows –Dimensions in the Cube18288003801745Columns in this sheet – Key Figures in the Cube00Columns in this sheet – Key Figures in the Cube1905003801745Available data – Dimensions & Key Figures in the Cube00Available data – Dimensions & Key Figures in the CubeSetting your own default display style for Analysis for the graphic look of your results. You can apply a new Style Set and set it as default.In the Analysis ribbon, click on Settings Styles Apply Style SetChoose SAP Tradeshow Plus and Set as Default. Click OK (you may choose another style of your choice)If you want to change the SAP Tradeshow Plus style (or any other style) go to Home Styles Cell StylesYou can also design custom styles setsExploring the Design PanelWhile the tab Analysis is active, click Design Panel (if it is not already opened) and the button Panel.Analysis section: Here you can see and change the navigation status of your query results. Drag and drop Measures and dimensions into Columns and rows as rmation sub-tab: Here you can see a description of the embedded data source or the workbook ponents sub-tab: Access to viewing and some other functions.Keep Analysis open for the Task 1.Multidimensional OLAP and Navigation Within SAP AnalysisIn order to derive business intelligence from a vast amount of data in a cube, it is essential to understand multidimensional Online Analytical Processing (OLAP) analysis. This includes navigation within the data using Design Panel and the query results area (also called Cross tab).TASK 1: Currency TranslationGBI is a global company and deals with multiple currencies. In the crosstab you notice that Cost of Goods Manufactured is displayed in USD. The other measures – discount, net sales, and revenue are in different currencies in different sales organizations. Notice that the total discount for all sales organizations is displayed as ‘*’. This is because of mixed currencies for that column. You will now do a currency translation:In the Analysis tab, click on Measures Currency Translation Choose Target Currency: USDCurrency Conversion Type: BI Curriculum: Fixed USD Click OK.Cost of Goods, Discount, Net Sales and Revenue are now is USD (check the bottom row). From this point on, you will answer business questions by querying your cube.Question 1: What is the overall Revenue in USD? (Ans.: US$ 285,250,874.29)One gets the answer just by reading the last row in the Revenue column, upon translating the currency (Net Sales + Discount).TASK 2: manipulating dimensionsQuestion 2: What is the Revenue for E-Bike Tailwind in 2010? (Ans.: US$ 2,024,435.82)Using SAP Analysis to answer business questions draws on manipulating the query elements. The Analysis tab and within it the Analysis section are used to add, remove, order, and filter the cross tab as needed to get answers to your questions. Excel techniques (manipulation of pivot tables, filtering etc.) work fine. Further instructions explain these techniques in conjunction with business questions. Since here you need to lookup a specific year, you need to include it in the query. Go to the Analysis section, Measures, find Calendar Year 2010, and drag 2010 into the Columns section. The year specification will serve as a filter. As a result of these inputs, the query output will change. Now you need to navigate a bit to get to the appropriate Excel cell. You can search the sheet on “Tailwind”. Once you get to the E-Bike Tailwind section, look for a cell that sums up the sales of this bike across customers. Year 2010 should be shown in the row underneath the label Revenue.Question 3: What is the Revenue for E-Bike Tailwind in 2009? (Ans.: US$ 0)Hint: Add the characteristic 2009 into columns.18. Changing Order of MeasuresMove Sales Quantity to the top in the section Columns. Notice the impact of changing the order in the spreadsheet.19. Ordering DimensionsAdd Calendar Year to Rows: Mark Calendar Year in Measures and drag it to drop it into the Rows area.Remove Sales Organization from the Rows area: Right-click it, and select Remove.TASK 3: SAVING WORKBOOKThere are several options for saving your workbook. You can save the workbook locally on your computer or on the SAP BW serverFor this exercise, save your workbook locally after each task.You can reopen the workbook on your computer at a later time and continue from there.TASK 4: SORTINGYou can sort, rank, and filter the query output in various ways.Sorting by DimensionsClick the label of any row or column, and in the main ribbon click Sort; choose between two ordering options. Characteristics often have both a key column and a text column. Sorting applies to whichever column you choose. Note: To know the sorting status of any characteristics, right click the column name. The current sorting status is greyed out. You may choose the other available sorting order.Question 4: What is the Sales Quantity of T-shirts in 2007? (Ans.: 30) Sorting by Measures will help you answer this questionMake sure you have active these rows: Calendar year, Material, and Customer. Also, you will need to have column Sales Quantity active.Right-click the column name Material.Click Filter by member.Undo Select All (if it is checked) and click T-Shirt.Right-click the column Sale Quantity and chose Sort Descending. This will help you to inspect quickly the quantity column in the part related to 2007 and T-shirt. Question 5: Which year had the highest Sales Quantity? (Ans.: 2007, 37,537)Upon sorting the worksheet on Sales Quantity remove rows (dimensions) Customer and Material. The result is a small table with the answer in the top row. Question 6: Which year had the lowest Revenue? (Ans.: 2009, $ 52,610,815.24)Still working with the same table, sort the Revenue column in ascending order.Note: If dollar figures do not show up in columns, you have to run currency translation; see p. 12.Question 7: In the year with the lowest Revenue, which Material had the lowest revenue? (Ans.: Fixed Gear Bike Plus, $13,475.35)a.Remove year rows, then put 2009 in Rows, and sort ascending column Revenue.Question 8: Which material had the lowest revenue in the observed period? (Ans.: Water Bottle, $83,314.79)Hint: The only row should be plex sorting Move these dimensions to the section Rows – Calendar Year, Division, Customer (remove the other dimensions)In the section Analysis and Rows, click Calendar Year. (Alternatively, click Calendar Year column (actually, it applies to rows ). In the main tab Analysis, click Sort More Sort Options.Now you can sort by Default, Member Display Type, or MeasureDefault (Dimension Members by First Display Type): In this case, this is Calendar year.Member Display Type: You can select one of the member display types (e.g., key; these options may change).Measure: Sort is applied to a select dimension (e.g. Sales Quantity).Choose Measure Net Sales, Sort Descending. Click OK.Then, choose Division and sort it in descending order of Net SalesThen, choose Customer and sort it in descending order of Net SalesWhat have you got as the output? Question 9: In the year that had the highest Net Sales, what division had the highest Net Sales? (Ans.: 2007, BI, US$ 58,293,361.95)Hint: You should have just Year and Division as rows.Question 10: For the division in Question 9, which customer did have the highest Net Sales? (Ans.: Bavaria Bikes, US$ 5,709,514.92)Hint: Customer should be in rows as well.Question 11: In the year with the highest Revenue, which division did have the highest Revenue? (Ans.: BI, US$ 60,206,607.19)The answer is readily available in the Result row at the bottom of the BI rows. Question 12: For the division in Question 11, which customer is associated with the highest Revenue? (Ans.: Bavaria Bikes, US$ 6,010,016.06)Look at the top row.Task 5: FilteRINGYou can filter the query output by dimensions (rows) or by measures (columns) to limit the data displayed and so get answers to some questions.Have these rows displayed: MaterialSales OrganisationCustomerYou want to filter Customer to the values Bavaria Bikes and Beantown Bikes.In the section Analysis, Rows, right-click CustomerUse Filter By Member as before (undo Select All and select Bavaria Bikes and Beantown Bikes). The filter icon next to the Customer dimension indicates that you have selected filter values. Only two customers are included in your query results as shown below just for one product:\sMove Customer from the Rows to the Background Filter area. What happens to the results table? Customer is removed from the table but the results of filtering are still displayed. You compressed the output. \sMove Customer back to Rows.Pausing (automatic) refreshingThe Pause Refresh button gives you the option of pausing the refresh (roundtrip to a data source) while you navigate. Doing so adjusts the refresh time until you are ready to display results.Click Pause RefreshUsing the Filter By Member again in the row Material, take out one of the value (e.g., Bavaria Bikes). Notice that the query results are not refreshed. Unclick Pause Refresh. The result of new filtering should show up.To run deeper filtering, focus on the dimensions Customer. It may display company names and an item Attributes with a plus sign next to it.Expand Attributes.Select Location and drag it in Customers in the rows area.Notice the change that inclusion of Location makes in the query output.Remove the filter on the Customer dimension to see the Location impact.Question 13: Which customer did yield the highest Net Sales for Air Pump in 2009? (Ans.: Red Light Bikes, $8,127.22)Hint: Utilize techniques described in the procedure above. Question 14: Find out the same as in 13 but for Repair Kit and identify the customer’s location.(Ans.: Silicon Valley Bikes, $2,035.89, Palo Alto)Ranking and Filter by Measure.With filtering measures, you can define rules to filter the data. You can apply one or multiple rules to a measure. Depending on where you open the Filter by measure dialog, the filter definition is applied to a selected dimension, to all dimensions or to the most detailed dimension in your analysis. The applied measure filters do not affect totals or subtotals in your analysis. Remove any filter on Calendar Year so that all years are listed. Remove the filter on Division. Also remove the attribute – Location. In Analysis, Columns, click Revenue. Them in the ribbon, click Filter Filter by MeasureYou see three options:All Dimensions Independently: The filter is applied to all dimensions in the analysis, from the outermost to the most detailed one. For example, in an analysis with three dimensions in columns A, B and C, the filter is first applied to the dimension in column A, then to the dimension in column B and finally to the dimension in column C.Most Detailed Dimension in Rows: The filter is applied to the most detailed dimension of the rows. Most Detailed Dimension in Columns: The filter is applied to the most detailed dimension of the columns. Choose All Dimensions Independently EditBased on Measure – Revenue. Definition – Top N. Enter 3. Click Add and OK.You could see an output as the partial one below showing Revenue for the top 3 products, top 3 Years, and top 3 Customers.Question 15: What are the best three years in terms of Revenue, and how much Revenue did they return? (Ans.: 2007, 2008, 2011; US$ 60,715,832.76, US$ 59,444,067.04 & US$ 56,625,742.97, respectively)Choose Revenue. Filter Filter by Measure All Dimensions Independently Reset. The top 3 filter is removed.Choose Revenue. Filter Filter by Measure Most Detailed Dimension in Rows Edit.Define a rule that lists the top 3 Revenue, Add, OK.You should now see the top three years on Revenue.Question 16: In 2011, what is the total revenue for the top three customers? The answer appears below. Sum function used instead of the initial result.Keep the workbook open for the next task.> <Task 6: Totals and sub-totalsRemove the top 3 filter applied above (on Revenue)We would like to change the aggregation for measures from the default sum (set in SAP BW) to one of our choice.In the spreadsheet, click Revenue – the name of the column. Then, in the Analysis ribbon select Totals Calculate Total As Average.Question 17: What is the overall average annual revenue per customer? (Ans.: 2,397,066.17)Hint: Have Calendar Year and Customer in Rows and display Revenue as Average. Then look at the intersection of the last row and the Revenue column.Save. Keep the workbook open for the next task.Task 7: Advanced SORtiNG AND FilteringEfficient sorting and filtering is essential for getting quickly to needed answers.We would like to list only the top 2 Customers (in terms of Revenue) for each Calendar Year and Material combination. Drag dimensions Calendar Year, Material, Customer into the Rows section.In the spreadsheet, click Revenue – the name of the column. Then, in the Analysis ribbon select Totals Calculate Total As Sum.Click Revenue and then Filter Filter by Measure Most Detailed Dimension in Rows. Edit.In Based on Measure, choose Revenue. In Definition, choose top N. Enter 2. Click Add, and OK.You should see the top 2 Customers for each Material within each Calendar Year.(A partial output is below. If Customer Attributes are active, your view will be more detailed.)???RevenueCalendar yearMaterialCustomer$2007Air PumpBavaria Bikes11,270.77??Silicon Valley Bikes15,064.00??Result26,334.77?City MaxRed Light Bikes9,900.01??Silicon Valley Bikes21,000.00??Result30,900.01?Deluxe Touring Bike-BlackBavaria Bikes250,800.17??Silicon Valley Bikes231,000.00??Result481,800.17?Deluxe Touring Bike-RedBavaria Bikes260,700.17Now, you would like to list the top 2 Customers within the whole 2007-11 period.Remove the filter by right clicking Revenue Filter by Measure Most Detailed Dimension in Rows. ResetClick Revenue. Then select Sort More Sort Options. Sort Descending. Select Break Hierarchies,* and OK.* In the default setting, data is sorted in the hierarchy levels of the crosstab sheet (the query output) - parent members are sorted in order, and child members are sorted below each parent member in their own order. Break Hierarchies disables such a view.Now you see that the Customers are listed in descending order of Revenue with their respective Years and products they purchased (Materials).Right click Revenue, and then Filter by Measure Most Detailed Dimension in Rows. EditChoose Revenue for Based on Measure. In Definition, choose top N. Enter 2, Add, OK. The output below reveals Bavaria Bikes as the best customer along with the best selling product to this customer ???RevenueCalendar yearMaterialCustomer$2011Men's Off Road Bike FullyBavaria Bikes1,608,347.532007Men's Off Road Bike FullyBavaria Bikes1,436,160.96Overall Result??3,044,508.49Check: Build a new query out of Material, Calendar Year, and Customer. Then set filters so that year are only 2011 and 2007, customer is Bavaria Bikes, and Material is Men's Off Road Bike Fully.You may need to set Revenue to show Sum to get an output equivalent to the one above. To ensure that the shown product is correctly found, remove the filter from Material to show all products. Task 8: Conditional FormattingConditional formatting is used to highlight important values or unexpected results in your data. With conditional formatting, you define rules to select different or critical values in your query results. Results that fall outside a set of predetermined threshold values are highlighted in color or designated with symbols. This enables you to identify immediately any results that deviate from the expected results. You can add one or more rules to a conditional format and prioritize the application of the rules.We will reset some of the sorting and filtering from the previous taskStart by removing the top 2 filter on RevenueSort Calendar Year in ascending orderSort Material in ascending orderSort Customer in ascending orderConditional FormattingIn the Analysis ribbon, click the button Conditional Formatting, and then New.A form titled New Conditional Formatting shows up. Expand it a bit to see tabs Definition, Selection, and Display.Make entries as shown below. Here is an example procedure to enter the brown arrow pointing up and 9, Less Than 100000:In Definition tab, click the green down-pointing arrow and then click enter the brown arrow pointing up and 9;In the middle drop-down list, select Greater Than;In the adjacent cell displaying a 0, type 100000;Click button Add; all entries should appear in the Rules section in the lower part of the form;Set the other two rules for range. The Revenue measure is conditionally formatted using the rules you have created.This procedure inserts trend arrows in Revenue cell, so that a simple visual inspection of the sheet can quickly inform the user on customer performance. A partial output appears below.?Velodrom01524000 63,045.04?Windy City Bikes01524000 66,865.89Deluxe Touring Bike-SilverAirport Bikes01524000 266,190.18?Alster Cycling01524000 248,677.67?Bavaria Bikes01524000 595,425.40?Beantown Bikes01524000 273,831.74?Big Apple Bikes01524000 210,149.94?Capital Bikes01524000 343,245.23Save. Keep the workbook open for the next task.Task 9: ChartingYou will now create a chart based on the query resultsFirst remove the conditional formatting from the previous task by clicking Conditional Formatting in the ribbon Analysis and then choosing Delete. Remove the totals data from all columns by right-clicking Revenue, and then selecting Totals and Hide. Then do:Remove Customer from Rows.Remove Material from Rows, thus leaving just Calendar Year in Rows.Mark the are of spredsheet to be charted.In the ribbon Analysis, click on Chart.Expand the image and move it where you wish.Double click the chart to choose its properties. This will open the ribbon Design.In the ribbon Design, click Change Chart Type to select a different charting method and Chart Style.To get a country breakdown per year, move the Country dimension to Rows.You should now see the revenue for DE by year, US by year and Overall by Year.TAsk 10: Creating a PowerPoint slidePart of SAP Analysis is PowerPoint with the tab Analysis. It is accessible from the ribbon area.Another option is to launch PowerPoint from the SAP BusinessObjects menu in the Windows start menu. According to SAP sources, data analysis that is performed in Excel with SAP Analysis should be possible to perform from within PowerPoint as well. This allows for using PowerPoint functionality in the analysis process and having a presentation created along the way.To use PowerPoint with SAP Analysis you would need to have a data source stored on the data warehouse server. The data source can be a query you previously created using Analysis for Excel. Once you are logged into the server, file upload is available via the Favorites menu item. After starting Analysis for PowerPoint, you can access your data source in the same way you did that for your initial query, as explained in the instructions above.TAsk 11: Trend analysisThese are open ended questions whose objective is to explore, exploit and discover some of the trends in the sales data. Some have been answered via the previous queries. Use charts to visualize trends, identify outliers, and discover sudden changes.Question 20: What Year had the highest revenue? What was the revenue?Question 21: What material had the highest revenue? What was the revenue? Question 22: Are the historical (year by year) revenue trends for the US and DE similar or dissimilar?Question 23: Did GBI ever gain or lose a customer? ExplainQuestion 24: Is there seasonality in revenue during the year? If so, what month has the highest revenue? Is the seasonality similar from year to year?Question 25: What Year and Material did the highest Revenue from a single customer occur in? Question 26: Are there any products that show dramatic change in revenue over time (years)? Does it have the same change by country? Question 27: Is there any material that does not display significant seasonality? Question 28: What customer has the highest percentage contribution? What has been the trend of that customer’s percentage contribution over the years? Hint: Use Calculations.018732500 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download