Business Data Warehousing - Exam II

Philosophy of the dashboard and scorecard user

" Tell me what I need to know - just a little information so that I know I'm on the right course - and please don't make me work too hard to get it".

Two Different Types of Selection and Extraction Services

1. Get them all and sort them out later: grabs all data elements, regardless if it was previously loaded 2. Change-oriented: find and extract only the data elements that have been newly added or edited

Slicing

A way to filter a large dataset to smaller data sets of interest.

Dimension Table (SAP BW Schema)

Acts as a link between Master data and Fact table records

Bind the data (Dashboard)

After the dashboard structure and components are laid out, each component has to be bound to a data source. As first discussed in Chapter 6, binding is the task of attaching a component to a data source so that the component can display data from that source. This process can be performed either manually or programmatically. The programming of the dashboard can dynamically change the binding of a component so that the dashboard can display different data in response to user input.

End-user Prompts

Allows users to decide the parameters of the report

Design Panel Views in SAP Analysis

Analysis Information Components

What to do when Data Quality Assurance finds problems?

Apply automatic correction rule: I.e. lookup speaking in a master table and make the change Side aside the record for a team member to analyze and correct later Cool your jets: if enough problems that are serious or require research, then halt the entire process until fixed

Business Intelligence Categories

Basic querying and reporting Business analysis (OLAP) Data Mining Dashboard and scorecards

Discovery-oriented (Data Mining)

Both the basic querying-and-reporting and business analysis/OLAP categories of business intelligence tools provide business intelligence based on either questions users explicitly ask or "institutionalized" question that members of the organization regularly ask in the form of regular reports (or both). The key word is question: If no question are asked, no answers are forthcoming.

How can dashboards be categorized?

By the presence or absence of interactivity

Data Movement (Middleware Service)

Can move data to data warehouse if hosted on a separate platform then the data source. It takes the QA'd data into the environment in which you plan to make additional transformation. (Can be a simple file transfer program) The two proceeding steps (selection and extraction, and quality assurance) take place on the same platform.

Display Only Dashboards

Dashboards that do not allow for user interaction These dashboards display the latest summarized data from the data source to the viewer.

Data to Decision Flow (Steps)

Data -> Information -> Knowledge -> Wisdom -> Decision

Predictive (Data Mining)

Data mining tools and capabilities search through large volumes of data, look for patterns and other aspects of the data in accordance with the techniques being used, and try to tell you what might happen based on the information that the data analysis found

What are some middleware services?

Data selection and extraction Data Quality Assurance Data Movement Data mapping and transformation Data quality assurance Data movement Data loading

Dicing

Dicing the sliced data set creates an even more granularly defined data set.

Hierarchies

Drill Down and Roll Up

The Dashboarding Process

Identify end user needs Specify the function of the dashboard Identify Data Sources Formulate the logic Create the user interface Bind the data Apply themes Add interactivity Test and Deploy

Report Authorizing Process

Identifying the needs of the report user Identifying data sources Building layout for readability Binding analytical components to data sources Report structure Adding prompts for end-users Deployment

Slice and Dice Mistakes

Incorrect Aggregations (ex. adding together ending inventories from each month) Treating NULL as 0 Errors in data acquisition Errors in calculations

The main objects supporting data modeling in SAP BW/4 HANA

InfoObjects, Open ODS views, Advanced DSOs, and CompositeProviders.

SAP BW Schema

Master Data Fact Table Dimension Table InfoCube

Data Quality Assurance (Middleware Service)

Middleware service that looks for: Values in data elements that exceed reasonable range Values in data elements that don't fi the official and complete lit of permissible values Cross-table inconsistencies Cross-field inconsistencies •Missing values •Data gaps •Incomplete data •Violation of business rules •Data corruption since the last extract •Spelling Inconsistencies

Queries

Prefilters Presorts Restricted key figures (ex. Revenue in 2014) Calculated Key Figures (ex. change in revenue) Conditions

Data Selection and Extractions (Middleware Service)

Primary purpose is to select from (find in) a data source the at a that you want to move into the data warehouse and then extract (pull out) that data into a form that can be readied for quality assurance services.

What does middleware do in data warehousing?

Pull data from the source Make sure that the data is correct Move the data around the environment from platform to platform, as necessary Handle any necessary data transformations Load the data into the data warehoue

What are some types of middleware?

Security Transaction Management Message Queues Web server Application Server

Slice & Dice Techniques

Sorting Filtering Ranking (top N, top/bottom %) Aggregations (SUM, AVG, COUNT, etc)

Data Flow in Reporting

Source Systems to Semantic Layer to Authoring to Report Server to End User

Types of InfoCubes

Standard Cube Virtual Cubes (Virtual Provider)

Multidimensional Modeling - Snowflake Schema

The dimension tables are split into more tables.

Data Mapping and Transformation (Middleware Service)

The main point to remember about mapping and transformation service is that you should have , at its conclusion, a unified set of data that's ready to load into the data warehouse.

Data Modeling

To explain data modeling with SAP BW/4 HANA in three words: Less is more. Less because a reduced number of modeling objects is offered in SAP BW/4 HANA compared to the SAP Netweaver based SAP BW application

Identify end user needs (Dashboard)

Understanding the users' role and information needs should be clear before beginning to design the dashboard. As with reporting and charting, failure to consider the requirements of the end-user(s) in the dashboard design can result in an ineffective and useless dashboard. Also, many information systems will limit who has access to what data, and it is a good idea to know beforehand what information the ultimate user of the dashboard will (a) need, (b) have access to, and (c) want.

What is Middleware?

a set of services that perform various functions in a distributed computing environment, across a wide set of server and client systems.

RSA1

accesses the SAP BW Administrator workbench, the main UI performing tasks in data warehousing such as data modeling function, as well as data loading control, monitoring, transport management, and maintenance processes.

Interactive Dashboards

allow users to choose inputs that modify the visualization to meet their specific needs

InfoCubes

central objects of the multi-dimensional model in SAP BW •Reports and analyses are based on these InfoCubes is a self-enclosed dataset for a business area from a reporting viewpoint •Queries can be defined and executed on the InfoCube •InfoCube consists of a number of relational tables arranged together

Design Panel

main panel where the output of the query is controlled

Main Purpose of Each Element (Data Modeling)

master data storage, virtualization, data persistency, and logical layer, respectively.

Dashboard

presents current information on your operational performance.

Scorecards

shows your performance against a plan or set of objectives.

Scorecards or balanced scorecards (BSC)

specialized types of dashboards used to display key performance indicators (KPIs) that are linked to corporate objectives and goals

Surrogate ID (SID) tables

used to convert textual keys to numeric keys to create links and provide for faster queries

Business Analysis

visualizing data in a multidimensional manner.

When is a dimension snowflaked?

when the low-cardinality columns in the dimension have been removed to separate normalized tables that then link back into the original dimension table.

What is SAP BusinessObjects Analysis, edition for Microsoft office

•A Microsoft Office Add-In that allows multidimensional analysis of OLAP sources, MS Excel workbook application design and creation of analytics presentations with PowerPoint •BEx Queries, BEx Query views, BW InfoProviders can be used as data sources •The data is displayed in the workbook in crosstabs •Design panel is like the Excel Pivot Table feature using "drag and drop" functionality •Advanced functionality includes API functions to use with Visual Basic Editor •Must be installed on your local machine •Two editions •Edition for Microsoft Excel •Edition for Microsoft PowerPoint

Analysis Functionality

•Add and remove dimensions and measures to be displayed easily with drag and drop •Refine analysis using •Conditional formatting •Filter •Ranking •Sorting (by dimension, by measure) •Prompting •Calculations •Display hierarchies •Add charts •Currency conversion •Convert to PowerPoint

Identify the data sources (Dashboard)

•After the functionality is determined, the developer identifies the relevant data sources. Most dashboards rely on dynamic data to extract the most current datasets. Examples of data sources are relational databases, data warehouses, web data (XML), spreadsheets and other flat files, and sensor data. The dashboard must have the appropriate drivers to connect to the data source. For more information on data sources and data acquisition.

SAP BusinessObjects Analysis

•Analysis is a powerful, in-depth, OLAP analysis tool that provides •Capability to create predefined workbooks that included guided navigation •Ad Hoc OLAP analysis •Hierarchical awareness •Data visualization •Best suited for power users such as a business analysts

Data Source

•BW Query or InfoProvider

Specify the function of the dashboard

•Based on the purpose of the dashboard, the end-user requirements, and the access to relevant data sources, the next step is to specify the overall function of the dashboard. This step is refined to more granular levels of detail as the dashboard functionality is developed and defined. Often this is an iterative process in which users provide input during the development process.

Binding the Report Components to the Data Source

•Binding is the action of connecting a report component (e.g. chart) to its data source (e.g. cube, flat file) •Binding links the components to the data so that the report can be refreshed when the source data changes, and/or when the report is reopened/rerun.

Business Analysis Using OLAP

•Business analysis allows the user to plot data in row and column coordinates to further understand the intersecting points. In essence, this visualization of OLAP is most often utilized with an OLAP specialty database or data warehouse. Although the business analysis can be done without such a database. Most common used in Pivot Table

Exporting to PowerPoint

•Can export analysis from Excel to PowerPoint or •Insert BEx query results directly

Dimension

•Characteristic e.g. customer

Fact Table (SAP BW Schema)

•Consists of Dimensional Keys and Key Figures (facts) •Values that can be added, subtracted, divided, multiplied, etc.

InfoCube (SAP BW Schema)

•Consists of Fact and Dimensional tables

Data Mining

•Data mining tools provide a degree of technical analysis that requires a base understanding in statistical algorithm to be successful in their use. •Data mining as a technique has either Predictive or Discovery Oriented

Elements you can create (Data Modeling)

•DataStore Objects (Advanced) •InfoSource •InfoObject •Open ODS view •InfoArea •Semantic Group •CompositeProvider •Open Hub Destination •Data Flow The elements of interest for data modeling in SAP BW/4 HANA are InfoObjects, Open ODS views, Advanced DSOs, and CompositeProviders.

What to focus on for reports?

•Define the audience •Define the decision to be made or question to be answered •Define the type of data the report is intended to convey •Consider the report medium

Four Types of Data Analytics

•Descriptive Analytics •Diagnostic Analytics •Predictive Analytics •Prescriptive Analytics

Edition for Microsoft office - features

•Design/Task/Navigation Panel like Excel "Pivot" table •Drag and Drop •Undo button •PowerPoint Integration •From Excel to PowerPoint, or a new analysis within PowerPoint •Easy insertion of multiple data sources •Pause/Refresh button •Runs any BW Query •Add multiple queries to one workbook

Shortcomings of Star Schema

•Duplication of dimensional data •No support for multiple languages •Slow joins due to alphanumeric keys •No historization (changing dimensional data) •Hierarchies can cause anomalies

Data Analytics Process

•Gathering data that are sometimes not in a usable form •Cleaning up the data to make them usable •Loading the data into storage models •Manipulating them to discover the information

Information View (SAP Analysis)

•General information on data source/workbook •Information on filters, variables in one place

Analysis Ribbon

•In Excel 2007/2010/2013, a separate ribbon (menu) appears in Excel with various buttons and menus

Multidimensional Modeling - Star Schema

•Includes 1 fact table and multiple dimension tables

Components of Interactive Dashboard design

•Interactive dashboards are great for "what-if" analysis because they allow the user to change input variables or other assumptions •An interactive dashboard, for instance can be used to help the company CFO see how a change in an interest rate will affect the company's cash flows by using one of the chart components (perhaps a dial or a slider as described below) to change the interest rate value on the dashboard •Interactive chart components or selectors are standard user interface mechanisms that you are probably already familiar with. The availability of all of these components gives the user the ability to choose what is displayed in the dashboard and how it is displayed

Two Types of Data in SAP BW InfoCube

•Key Figures (transactional data) •Characteristics 1 Fact Table and 16 Dimension Tables

Querying and Reporting

•Make a data request •Retrieve the data •Manipulate the data slightly Summarize or reorganize, for example, if necessary •Format the data •Present the data

Slice and Dice Tools

•Microsoft Excel tables and pivot tables •IBM Cognos •SAP Analysis, Edition for Microsoft Office •Oracle Hyperion •Tableau •Microsoft PowerPivot

Advantages of Slicing and Dicing

•Multi-dimensional data examined from many angles •Larger data sets filtered down to areas of interest Trends and outliers identified via sorting

Connecting to cube via query

•Multidimensional expressions query (MDX) •SQL can only process two dimensions at a time (rows & columns) OLAP Tool -> MDX Query -> Data Cube

Analysis View (SAP Analysis)

•Navigation - Drag and drop dimensions into rows or columns (or remove) •Filter a dimension by specific member •Slicing and dicing

Virtual Cubes (Virtual Provider)

•Only represent logical views of a dataset •Data resides in data source (not the virtual cube)

Standard Cubes

•Physically contains data in the database •They are data targets (Data can be loaded into cubes.) •A type of InfoProvider •BW objects are called InfoProviders when queries can be executed based on them •Standard InfoCubes are technically optimized for read access.

What is a Context Menu

•Popup menu that appears when right-clicking a field in the output of Analysis

Advantages of Snowflake Schema

•Reduces data redundancy •Supports multiple languages, currencies & UoM •Quicker joins via conversion of text keys to numbers •Supports historization •Support cube administration

Master Data (SAP BW Schema)

•Represents Dimensional data •Independent of any Fact table •Reusable in multiple Fact tables •Designed to support multi lingual systems •Designed to support time dependent data. •Designed to improve query performance

Modeling Perspectives

•SAP BW modelers are very familiar with transaction RSA1, which has been central to SAP BW since its inception.

SAP BW - Snowflake Model

•SAP BW uses a snowflake model instead of the star schema •Further normalization and expansion of the dimension tables in a star schema results in a snowflake design. •This reduces data redundancy which reduces storage space but increases the number of joins.

Fact table

•Stores measures / key figures •Represents Transactional data

Components View (SAP Analysis)

•Structure information view from data source / worksheet perspective

Components of Reports

•Tables •Text •Subreports •Geometric Shapes •Images •Charts •Maps

Pivot Chart

•The chart changes when the data is pivoted •Field controls allow for filters and sorts

When do you use Snowflake ?

•The dimension table consists of two or more sets of attributes which define information at different grains (detail) •The sets of attributes of the same dimension table are being populated by different source systems (very common in data warehouses)

Formulate the logic (Dashboard)

•The functionality of the dashboard is then mapped to a logic diagram to describe how controls, components, interactions, and display(s) will all work together. The logic must take into account all possible combinations of data values, end-user input, and the device(s) where the dashboard will be displayed. The logic has to be especially robust so as not to compromise the security and privacy of both the users and the data sources. Other considerations include handling NULL values, unexpected errors, outliers, and input devices such as a touch screen and a mouse.

Create the user interface (Dashboard)

•The next step entails building the dashboard-user interface. Digital design has moved from simply designing user interfaces (UI) to designing for the best user experience (UX). UX is a person's response and reaction to interacting with a system or service. Both users and their devices are more sophisticated and more demanding than they were in the past. The focus is on self-service and intuitive, clean, consistent design. Any dashboard that requires a manual to explain how to use it will soon fall out of favor with end-users. Instead, users expect the use and output of the dashboard to be self-explanatory. Therefore, designing for this type of user experience is paramount in dashboard creation.

Crosstab

•The rows and columns output that appears in Excel. An Excel workbook can contain several Analysis crosstabs.

Semantic Layer

•The semantic layer converts the source labels into business language ex PROD_REV into Revenue by Product

3 Dimensions predefined by SAP

•Time •Unit •Data Package

Report Structure

•Title •Headers and Footers •Report level •Page level •Page numbers •Sorting •Grouping •Aggregating •Currencies •Alerts

Attributes of a Well-designed Report

•Understandable •Timely •Accessible •Reliable •Complete •Relevant

Dashboard

•a graphical representation of summarized data that shows key performance indicators at a glance.

What is a dimension inside SAP BW?

•a group of characteristics which belong to the same business object (semantically). •Attributes may be characterized as display OR navigational AND time-dependent OR time-independent

Pivot Table

•creates what is called a cross tabulated structure or crosstab. •The crosstab displays aggregated and summarized data based on which way you sort the columns and rows.

Dimension tables

•represent master data •Provides a more detailed view of the fact •Time dimension •Customer dimension •Product dimension •Sales Org dimension •Region dimension •Unit dimension •Data package dimension

SAP BW Schema

•the multi-dimensional model in SAP BW is based on the SAP BW schema, which was developed as an extended (snowflake) star schema as a response to problems experienced with the classic star schema. •The enhancement comes from the fact that the dimension tables do not contain master data information! •Master data is stored in separate tables, called master data tables (outside the dimension tables). •Master data tables can be used in multiple InfoCubes