1 / 0

SAP BW Query and Reporting

SAP University Alliances Version 3.0 Authors Tobias Hagen Klaus Freyburger. SAP BW Query and Reporting. Abstract: This chapter covers the basic aspects and concepts of query and reporting with SAP Business Information Warehouse (SAP BW). Product SAP Netweaver 7.3 BI Level

dunne
Télécharger la présentation

SAP BW Query and Reporting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAP University Alliances Version 3.0 Authors Tobias Hagen Klaus Freyburger

    SAP BW Query and Reporting

    Abstract: This chapter covers the basic aspects and concepts of query and reporting with SAP Business Information Warehouse (SAP BW). Product SAP Netweaver7.3 BI Level Undergraduate Beginner
  2. Learning objectives Understandtheconceptof a query in SAP BW Knowthemostimportantelementsof a query Learnhowtobuild a query
  3. Agenda Overview Queries and SAP BEx Query Designer Query Basics SAP BEx Query Designer Calculated measures Currency Conversion Navigational and Display Attributes Advanced Features of Queries Hierarchical Display Selections Exceptions Conditions Variables (Report-Report-Interface) * (Authorization) * * Not part of the case studies
  4. SAP Business IntelligencePlatform Information Access SAP NetWeaver Portal Any Portal, Any Destination SAP BEx Explorer Crystal Reports Web Intelligence Dashboard Analysis SAPWAD SAP Business Intelligence - BI 4.0 SAP Integration Data Federator Data Quality Mgmt Data Quality Mgmt Master Data Management SAP NetWeaver BW on HANA ERP Financials Operations Human Resources Corporate Services Metadata Management Data Foundation SAP NetWeaver BW / BW-IP CRM SCM Data Services Data Integrator Data Quality Management TextAnalysis SAP Business Suite SRM PLM 3rd-Party Databases and Applications
  5. Agenda Overview Queries and SAP BEx Query Designer Query Basics SAP BEx Query Designer Calculated measures Currency Conversion Navigational and Display Attributes Advanced Features of Queries Hierarchical Display Selections Exceptions Conditions Variables (Report-Report-Interface) * (Authorization) * * Not part of the case studies
  6. Query Facts Information accessto BW dataalwaysbymeansof a Query A Query does not containdata MultidimensionalView A Query isalwaysdefined on an InfoProvider A Query restictsthenavigationsubspaceavailablefor a user A Query hidescomplexityofInfoProvider QueriesarecreatedwithBEx Query Designer SAP BEx Analysis Dashboard … SAPWAD Information Access via Queries SAP BW OperationalData Store Data Marts Data Warehouse (Persistent) Staging Area Source Systems
  7. InfoProviders “Provide” Access to Data Records InfoProviders: Provide common access interface to data records in BW May storedatarecordsphysically (persistent) May beusedtoaccessdatastored outside SAP BW: ERP, SAP HANA, … InfoProviderswith physical data: InfoCubes, DataStore Objects,… InfoProviders without physical data: MultiProviders, Virtual Providers,… (formoredetailsseemodule BI2M1)
  8. Query Fundamentals Create a querytoselectdatafrom an InfoProvider Use a desktoptooltodisplaythedatato end usersas a report 2 Dimension Product 1 All Materials fromdivision „Bicycles“ All months in year „2012“ Dimension Time All salesorganisations In country„US“ Dimension Customer
  9. Elements of a Query A Query consistsofthefollowingelements A subsetofcharacteristicsandkeyfiguresoftheInfoProvider Filterforcharacteristicvalues Free characteristics Characteristicsthatcanbeusedfor OLAP navigationatqueryrun time RowsandColumnscontainingcharacteristicvaluesandkeyfigures startviewdisplayedwhenqueryisexecuted Manysettingsandpropertiesforthekeyfiguresandcharacterics Advancedfeatures Exceptions Conditions Selections Calculatedkeyfigures / formulas … Everythingisdesigned in BEx Query Designer andstoredatthe BW Server
  10. Create a Query New Query Dialog: Select an InfoProviderforyour Query History: Displays yourrecentInfoProviders Find:AllowsyoutosearchforInfoProvider InfoAreas: All InfoAreas Tip:UseFindforbestperformance
  11. Query Designer: Overview Dimensionswithcharacteristicsandkeyfigures Properties ofselectedelement Free Characteristics Columns Preview Rows
  12. Query Designer: InfoProvider ChoosekeyfiguresanddimensionelementsofInfoProviderusedforquery Dimensionscontaincharacteristicsandattributes Usage: Select elementofInfoProvideranddragittothetargetarea (rows/columnsorfiltertab)
  13. Query Designer: Rows, Columns, Free Characteristics Key figuresandcharacteristicsofthequerythatarepartofcolumnsorrows definestartviewofthequery Free characteristics Free characteristicsare invisible in startview Can beusedfornavigation  display in roworcolumnoruse in filter All otherelementsofInfoProviderare NOT availableforthe end user!
  14. Query Designer: Properties All elementsofthequeryhavepropertiesthatcanbeset in propertieswindow Key figures: Description, numberofdecimals, currencyconversion, aggregationbehaviour, … Characteristics: Description, displayas…, sorting, result, … Overall propertiesofthequery: description Note: Manyofpropertiesset in Query Designer canbechangedby end useratrun time
  15. Query Designer: Filter A Filterrestrictsthe Query resultstocertaincharacteristicvalues Usedforsettingslicesordices Single values, ranges, orcomplexcombinationspossible FromInfoProviderareachooseeitherfixedcharacteristicvaluesorvariables ( seebelow) Note: Filters based on keyfigurevalues (Revenue < 1 000 000 USD) arecalledConditions in SAP BW! ( seebelow)
  16. Testingof a Query Youcantestyourquerybyopeningitwith SAP BO Advanced Analysis for Office ordirectlyfrom Query Designer. Web Browser isopenedandqueryresultisdisplayedas a BEx Web Application. All OLAP operationsaresupportedhere.
  17. Display and Navigational Attributes InfoObjectscanhaveattributes Someattributesareinterestingfor end users but may not beusedfordataanalysis Examples Color, size House number, Email address AnalysesusingtheseInfoObjectswouldprobably not make sense. „I wanttoseethenetsalesfrom Germany orderedbyhousenumber.“ „I wanttoseetherevenue in November groupedbycolor.“ Solution in SAP BW toreducecomplexity: Therearetwotypesofattributes Display Attributes haveno OLAP functionality. Thycanbe just displayedbeneaththeInfoObject Navigational Attributes canbeusedfor OLAP navigationandlikeordinarydimensionelements in a InfoProvider.
  18. Definition ofCharacteristics Attributes Toggle Display or Navigational Attribute
  19. CalculatedMeasures Create new measures/calculations that are calculated on-the-fly when query is processed  Values are not stored in InfoProvider Local calculations can be created in Query Designer New Formula  Formula is part of the Query definition, no reuse New Calculated Key Figure Formula is part of the InfoProvider, all queries based on InfoProvider may reuse it Local calculations can also be done in various BI clients Advanced Analysis for Excel BEx Analyzer and BEx Web Applications BO Web Intelligence
  20. Formulas/Calculated Key Figures
  21. Currency Conversion/ Units ofmeasurement Amountscanbeconvertedto a different currency The businessrulesforconversion (conversionrates, possibletargetcurrency) aredefined in a Conversion Typeobject, definedat BW server Complexityishidden & rulesarestandardized ConversionTypescan also beused in transformations in ETL process IdenticalconceptisusedforconversionsofUnits ofmeasurement(UOM) Convertwithin a dimension (e.g. netweightfrom kg to g) Convertbetweendimensions (e.g. from PC topalettesorvolume in m3)
  22. Agenda Overview Queries and SAP BEx Query Designer Query Basics SAP BEx Query Designer Calculated measures Currency Conversion Navigational and Display Attributes Advanced Features of Queries Hierarchical Display Selections Exceptions Conditions Variables (Report-Report-Interface) * (Authorization) * * Not part of the case studies
  23. Display Characteristics as a Hierarchy You can display characteristics hierarchically as a tree. You can specify for each axis whether all objects on an axis (structures, characteristics with or without display hierarchies) are displayed as individual hierarchies. Choose the hierarchy level for drill down in the initial view.
  24. Selections/Restricted Key Figures A Selectioncombines a keyfigurewith a setoffiltersforcharacteristicvalues Example: Insteadofdisplayingkeyfigure „Revenue“, display„planned Revenue in Q1 ofthisyear“ Usecase: comparisonsof a singlekeyfigurehaving different filters (plan-actual, previousperiods, etc.) Note: Selectionsareindependenttocharacteristicvalues on theaxisofthequery Two ways to define in Query Designer New Selection  Selection is part of the Query definition, no reuse New Restricted Key Figure  Selection is part of the InfoProvider, all queries based on InfoProvider may reuse it
  25. Selections/Restricted Key Figures
  26. Selections An Selectioncombines a keyfigurewith a setoffiltersforcharacteristicvalues Example: Insteadofdisplayingkeyfigure „Revenue“, display„planned Revenue in Q1 ofthisyear“ Usecase: comparisonsof a singlekeyfigurehaving different filters (plan-actual, previousperiods, etc.) Note: Selectionsareindependenttocharacteristicvalues on theaxisofthequery Two ways to define in Query Designer New Selection  Selection is part of the Query definition, no reuse New Restricted Key Figure  Selection is part of the InfoProvider, all queries based on InfoProvider may reuse it
  27. Exception Reporting  In exception reporting, you highlight objects that are in some way different or critical based on business rules (c.f. Conditional formatting in Excel) Example: Increase in Sales > 10% is good, Sales increase < 2% is bad. Good values are highlighted green, bad values are highlighted red Identify immediately any exceptional/critical results in large amounts of data Use it to visualize deviations of actual from plan or targets (in scorecards)
  28. BEx Broadcaster & Exception Reporting  Exceptions can be used as trigger for follow up actions with BEx Broadcaster. Example: Query with latest weekly sales data is scheduled to run in background every Monday System checks data against exception rules No Exception there is no need to read the report Exception occurs  System notifies responsible manager via email  Automated reporting process Reduced “Information overload”
  29. Exception Definition You define exceptions in the Exceptions screen area in Query Designer. Choose New Exception in the context menu. Set the required alert level and specify the relevant threshold values and operators in the Define Exception Values pane. Choose from the following alert levels: Good 1, …, 3 Critical 1, …, 3 Bad 1, …, 3 You can then choose from the different operators. You can either specify values or variables (for user entry of values).
  30. Conditions A Conditionislike a filterbased on keyfigurevalues: Onlyrecordsthatmeettheconditionarepartthequeryresult Example1: Display onlycustomerswithrevenue > 100 000 USD Example 2: Top N list: Display onlythe Top 5 customersbased on revenue Note: Resultcellscanbecalculatedover all valuesorvisiblerecordsonly! Nocondition Top 5
  31. Conditions Definition Define Conditions in the Conditions screen area in Query Designer. Choose New Condition. Choose Button New. Select key figure, operator, and value. You can either specify values or variables (for user entry of values).
  32. Variables Variables are parameters of a query that are replaced with values when before the query is executed Variables can fill characteristic values (e.g. in query filter, selections) Variables can fill numerical values (e.g. in formulas, exceptions, conditions) Variables can be replaced with values By direct user input By a program (Exit) By user authorization Examples Actual period/quarter/year/…, previous period/quarter/year/… Sales regions a manager is responsible for (= is authorized) Reducenumberofqueries Reducequerymaintenance PersonalizeQuerieswithparametersbased on user
  33. Using Variables Variables are parameters of a query that Are defined in Query Designer. Are filled with values when you execute the query or Web application. Value selections can be saved as variants for end users’ needs.
  34. Defining Variables A variable editor is available in Query Designer for designing and changing variables.
  35. Agenda Overview Queries and SAP BEx Query Designer Query Basics SAP BEx Query Designer Calculated measures Currency Conversion Navigational and Display Attributes Advanced Features of Queries Hierarchical Display Selections Exceptions Conditions Variables (Report-Report-Interface) * (Authorization) * * Not part of the case studies
  36. Report-to-Report Interface The report-to-report interface provides a number of report types as jump targets (receiver) of an SAP BW BEx query (sender). Other SAP BEx queries An SAP ERP transaction A web address … There can be parameters passed in this standard interface. An example: A query has the characteristic Months in its rows and the two key figures Revenue und Quantity in its columns. You want to see the particular record items for all days of a particular month. The daily record items are not part of the InfoCube but reside in the DSO. Upon jumping, the new report type should cause the Months to be drilled down in the daily data of the rows.
  37. Report-to-Report Assignments The Sender-Receiver Assignments to allow navigation to other reports/queries/websites…
  38. Report-to-Report Assignment Creation Add the navigation target (receiver) to a query (sender)
  39. Report-to-Report Interface Usage In the context menu in BEx Analyzer, you will find the new query in the Goto function area.
  40. Authorization levels Authorizations can be defined on the following levels InfoCube Characteristic Characteristic value Key Figure Hierarchy node Source: Deng, J. & Uhle, R. (2006). SAP NetWeaver 2004s: Enterprise Data Warehousing. Retrieved from https://www.sdn.sap.com
  41. Activating Authorizations for Characteristics To activate authorizations for a characteristic, check the appropriate flag
  42. Authorization Maintenance (1/2)
  43. Authorization Maintenance (2/2) Authorization:Access limitedto Sales-Orgs.in Germany(From AUS1 to AUS3)
  44. Special Characteristics Three characteristics should be included in each authorization (not mandatory for each – but must be assigned to a user in at least one authorization) Activity (0TCAACTVT): e.g. reading (03) InfoProvider (0TCAIPROV): e.g. MU1999 (Bike Company) Validity for time period (0TCAVALID): e.g. 2008 Insert specialcharacteristics
  45. Authorization Check Source: Deng, J. & Uhle, R. (2006). SAP NetWeaver 2004s: Enterprise Data Warehousing. Retrieved from https://www.sdn.sap.com
  46. Summary Queriesprovideinformationaccessto BW data QueriesenrichBW datawithmetadataprovidingflexibilityandbusinesssemantics Queriesareusedby all BI frontendtoolsavailablefor BW tovisualizedatafor end users BI designerscreatequerieswithBEx Query Designer SAP BEx Analysis Dashboard … Information Access via Queries SAPWAD SAP BW OperationalData Store Data Marts Data Warehouse Persistent Staging Layer Source Systems
More Related