Friday, December 30, 2016

SAP Replication Server/Sybase H100

SAP Replication Server:

·         Log based Database replication tool

SAP Sybase Replication server Intro:
·         Transactional data movement product that moves and synchronizes data across the enterprise
·         Real-time business intelligence
·         Zero operational downtime
·         Log based replication process
·         Non-intrusive
·         Very high performance
·         Improve recovery, resumption times and minimize downtime
·         Bi-directional replication
·         Standby DB is always available and can be used for read-only report server
·         Fresh data to enable timelier decision
·         Run resources intensive reports on reporting servers without implacting OLTP systems
·         Reduces info latency for reporting and optimize batch reporting
·         RT data sharing and synchronization
·         Facilitates decentralized business operations
·         Enables remote application to access data locally for improved performance

Relies on log based replication techniques:
·          Changed Data Capture
·         not done against the data volume of the source database
·         reads directly the database log
·         reduces the workload that the replication process brings to the source db
·         Data distribution and migration
·         Distribute: move centralized data to ops apps
·         Share data between ops apps
·         Synchronize: maintain consistency in overlapping data values
·         Migrate: move from older versions of database platform to newer
·         Real time decision support
·         Create ops data store
·         RT loading of DW (HANA< SAP IQ<SAP ASE, Oracle, Microsoft, IBM)
·         RT data sharing and synchronization
·         Enable bzn continuity in case of site wide disaster
·         Maintain app availability during planned/unplanned downtime

Source system = Primary DB
Target = Replicate DB


SAP replication server:
·          Heterogenous materialization( initial load)
·         Pre-req before you can replicate transactions
·         Optimized for numerous tables and big data volumes
·         Leverages native HANA ODBC driver

·         Strong bulk insert/delete capabilities







Qs:11 Your customer requires real-time replication. Where do you select the tables to be replicated to SAP HANA?

A. in transaction LTR in SAP Landscape Transformation (SLT) 
B. in the Import dialog of the Quick Launch of SAP HANA studio 
C. in transaction SAP ERP Tables and Indexes Monitor (DB02) 
D. in the Data Provisioning dialog of SAP HANA studio



2)  SAP HANA Appliance Software :
        a)  SAP HANA Studio                   = SAP HANA Load Controller
b)  Sybase Replication Server    = SAP Host Agent
        c)  SAP HANA Database Clients = LM Structure Files

        d)  SAP HANA Database              = SAPCAR


3)  SAP HANA Enterprise extended  edition : (For Log based replication)
i)  Sybase Replication Agent
              ii)  Sybase Replication Server

             iii)  SAP HANA Load Controller (R/3 Load in Source + R/3 Load in SAP HANA)

5)   Replication Methods Overview :
       a)  Source System-SAP ERP (Application Layer) à Trigger Based Replication à SAP In-Memory Database
       b)  Source System-SAP ERP (Application Layer) à    ETL Based Replication  à SAP In-Memory Database
       c)  Source System-Database (Log File)                 à   Log Based Replication  à SAP In-Memory Database

  RFC connection is required for connection from Replication server to HANA Database : False


  • HANA is connected to ERP systems, Frontend modeling studio can be used for load control and replication server management 
  • HANA Supports Sybase Replication Server – Sybase Replication Server can be used for real time synchronization of data between ERP and HANA 
  • Replication servers create tables in column store per default
  • A “What-if” scenario is the strength of SAP BO Dashboards.
    26)  a) SAP ERP à SLT (Replication Server) ----(Real Time Replication) à SAP HANA à SAP BO  à Presentation

C) SAP HANA Appliance (SLES 11 SP1)
                              i. SAP HANA Database – Clients Studio
                             ii. SAP HANA Load Controller
                            iii. SAP Host Agent 7.20
                            iv. Sybase Replication Server 15.5 + ECDA

Calculation View H100

Calculation Views:

·         Visible to reporting tools
·         When the view is accessed, a function is implicitly executed
·         Function is defined in the HANA-specific Language “SQL Script”
·         Functions contain SQL commands
·         SQL in functions must be ‘read only’(no insert, update, delete, drop,…)
·         Functions can call other functions

·         Composite Views, re-uses Analytical and Attribute views
·         SQL/SQL Script/Custom Functions




  • Calculation View – With custom functions and calculations
  • Calculation Views – Similar to Virtual provider concept in BW
  • Information Models – Attribute, Analytic, Calculation Views and Analytic  Privilege
  • Calculation View:

    • Define table output Structure
    • Write SQL statement
      • Ensure the selected fields corresponds to previously defined output structures
    • SQL Scripts unlike SQL procedure can't change any data they are read only

    Graphical:
    ·         3 types of calculated Views
    ·          Dimension
    ·         Used to build the equivalent of an attribute view.  No multidimensional reporting is enabled, and the last node is a projection node
    ·         Cube
    ·         Final node is an aggregation node.  Multidimensional reporting is possible
    ·         Cube with Star Join
    ·         Allows modeler to join one or several calculated views of type dimension in the very last stage of the model calculation

    Union: used to combine the result of 2 or more data sources
    ·         Know all the names of all the employees of a store with different branches maintaining their own employee table

    Join: used to query data from 2 or more data sources, based on some condition
    ·         Retrieve the sales of 2 stores maintaining the individual tables for sales based on the customer id

    Projection: Used to filter or create a subset with only the columns of a table or view that are required for you model.
    ·         Selecting the employee name and sales quantity from a table that includes many more columns

    Aggregation: Used to summarize the data of a group of rows by calculating values in a column.
    ·         To retrieve total sales of some product in a month.  The supported aggregation types are sum, min, and max

    Star Join:  Used only in Calculation Views of Type Cube with Star Join, and only as the last calculation step ( just before the Semantics node)

    SQLScript pushes data intensive application logic into the database

    ·         Define Function (with input and output parameters)
    ·         Can be a sclar value to pass parameters from the front-end tools in order to filter the results
    ·         Can be a table type -> to pass results from one function into another
    ·         The output parameter is mandatory
    ·         Can be locally defined table type

    Creating the database object for the calculation view:
    ·         Metadata(the calculation view coding0 has to be translated into run-time objects)
    ·         Executes the coding
    ·         Defined table type is dropped and created
    ·         SQL script function is created
    ·         Column views are created in the chosen output schema

    ·         Select the attributes and measures for the output node.  This will represent the definition of the column view that the front end tools will query against

    ·         Final Step:  Save and activate




    Qs:12 Which of the following can you use as a source for a graphical calculation view? (Choose three)

    A. Calculation view 
    B. SQL view 
    C. Decision table 
    D. Table 
    E. Procedure

    Qs:1 Which of the following nodes can you use when you create a calculation view with the SAP HANA studio graphical interface? (Choose two)


    A. Data Foundation 
    B. Aggregation 
    C. Join 
    D. Analytic Privilege


    Qs:19 Which of the following automatically aggregates data? (Choose two)

    A. Analytic view 
    B. Calculation view 
    C. Column table 

    D. Attribute view


    Qs:18 Where is the physical data stored, on which a calculation view is based?

    A. In the _SYS_BI schema 
    B. In a schema 
    C. In the _SYS_BIC schema 

    D. In a package

    Qs:43 Which of the following are required to build a SQLScript calculation view? (Choose two)

    A. Define a data foundation. 
    B. Define a measure. 
    C. Set the MultiDimensional Reporting property to false. 

    D. Add columns to the var_out output parameter.

    Qs:48 How can you create a computed column in an information model? (Choose two)

    A. Create a calculated attribute within a calculation view. 
    B. Create a counter in an analytic view. 
    C. Create a restricted measure in a calculation view. 
    D. Create a restricted measure in an analytic view.

    Qs:49 You create a graphical calculation view that is based on analytic views. What do you use to combine the data from the analytic views?

    A. Referential join 
    B. Projection node 
    C. Aggregation node 

    D. Union



    On calculation view 3 questions for example: In what situation the calculation view is required?

    Ans:  When we require complex logic that cant be delivered by Attribute and Analytical Views.


     How can you evaluate the actual speed of a calculation within HANA DB, Ans: (3 answers needed)
    a)  Write and execute SQL statement
    b)  Use data preview
    c)  Execute WEBI 

    d)  Execute information space view, e0 trace using python admin tool within TREX.

    What are the reasons for building a calculation view with SQL script rather than the graphical interface
    a)     To reuse standard SQL functions not provided within the modeler
    b)    To create custom reusable calculation functions

    3.     Which tool can you use to preview calculation view data?
    a)     Data Preview
    b)    SQL Editor


    1.  Calculation View can contain the following:
    a.     Database Tables
    b.    Attribute Views
    c.     Analytic Views

    d.    Calculation Views



      Without Analytic Privilege, no data can be viewed from
    a.     Attribute Views
    b.    Analytic Views
    c.     Calculation Views



    62.  Calculation engine performs the following:
    ·         Create Joins at run-time
    ·         Create Calculated Measures and Calculated Attributes

    ·         Compute and Execute Calculation Views



    76.  Calculation views can also be used as business reporting view ? True



    5.     How many result tables are generated from one calculation view per session,

    Ans: 1, other options 2,3,4

    6.     In a calculation view which node is used
    Ans: Projection, Union, there were other options

    10.  How can you evaluate the actual speed of a calculation within a DB,
     Ans: (3 answers needed) a) write and execute SQL statement, b) Use data preview, c) execute Webi, d) Execute information space view, e0 trace using python admin tool within TREX (don't ask me what I picked!)


             What is SQL Script ?

                SQL script is used for calculation view and is a proprietary language.











    -

    Analytical View HA100

    Analytic View:

    ·         Regarded as “cube”
    ·         Multidimensional reporting model
    ·         Fact Table joined against modeled dimensions(attribute Views)

      • Similar to Cube
      • Analytic Views does not store any data. The data is stored in column store table or view based on Analytical View structure
      • Attributes and Measures – Like key figures
    • Data Preview – Similar to list cube functionality

    ·         Data is read from the joined db tables
    ·         Joined and calculated measures are evaluated at run time
    ·         Master data for MDX/BICS are stored in system tables

    Step 1) Set Analytic View Parameters
    Step 2) Select table
    ·         Data Foundation View
    ·         Create a fact table by adding and joining the tables
    ·         Logical Join view
    ·         Join attributes to these tables in the logical join node


    ·         Semantics View
    ·         Classify the columns and calculated columns as attributes and measures.
    ·         Create variables/input parameters, and assign variables to the columns in the Semantic node
    ·         Analytic View Editor
    ·         Create a fact table by adding the tables in the data foundation node.
    ·         Join attribute views to tables in the Logical join node
    ·         Output Pane
    ·         Models view elements:
    ·         Column
    ·         Calculated Columns
    ·         Restricted Columns
    ·         Input Parameters
    ·         Semantics node
    ·         Classify
    ·         Columns as attributes
    ·         Calculated columns as measures

    Step 3) Select Attribute Views (optional)
    ·         Attribute Views can be added to the logical join
    ·         View Creation
    ·         Data foundation
    ·         Create the data foundation (‘Fact table’)

    Step 4) Join Attribute Views
    ·         Data foundation view: shows the physical table with all fields that can be incorporated in the final model
    ·         Logical Join views: displays the fields that you have chosen to include I the output of the data foundation node, as well as the restricted and calculated measures
    ·         Analytic view (data foundation view)
    ·         Features
    ·         Add columns
    ·         Create column filters
    ·         Create joins if more than one table


    Step 5) ?

    Step 6) Calculated Measures


    Step 7)  Create Restricted Measures
    ·         Semantics view you can classify the Columns and Calculated columns as attributes and measures
    ·         Can create variables/input parameters
    ·         Assign variables to the columns

    Step 8)  Save and activate
    ·         Creates a column view (DB view) in schema _SYS_BIC
    Step 9) Data Preview
    ·         Raw data-table format of data
    ·         Can create filters
    ·         Distinct Values -graphical and text format id unique values
    ·         Analysis- Select fields to display in graphical format or in a table

    ·         Table, selection of chart types and templates


    Qs:3 Which of the following can be part of an analytic view? (Choose three)

    A. Query transformation 
    B. Unions 
    C. Logical joins 
    D. Data foundation 
    E. Attributes


    Qs:25 You want to restrict access to SAP HANA information models based on user groups. The models are accessed through an SAP BusinessObjects report. In which of the following can you implement these restrictions? (Choose two)

    A. The schema mapping 
    B. The restricted measures 
    C. The analytic privileges 

    D. The universe


    Qs:37 An analytic view has two columns, SHIP_TO_COUNTRY and SHIP_FROM_COUNTRY, that contain ISO country codes. You must replace these codes with the English country names by joining to the same country attribute view twice. What must you create to achieve this?

    A. A derived attribute view 
    B. A shared attribute view 
    C. Two data foundations 

    D. An attribute view using the Copy From method.


    Qs:48 How can you create a computed column in an information model? (Choose two)

    A. Create a calculated attribute within a calculation view. 
    B. Create a counter in an analytic view. 
    C. Create a restricted measure in a calculation view. 

    D. Create a restricted measure in an analytic view.

    Qs:49 You create a graphical calculation view that is based on analytic views. What do you use to combine the data from the analytic views?

    A. Referential join 
    B. Projection node 
    C. Aggregation node 

    D. Union


    67.  Filter values can assigned at the ‘Data foundation’ in the Analytic view ? True

    77.  Attribute view provides the master data information & can be used in any analytic view ? True



    What is Private Attribute ?
    Private Attributes : Private attributes are used to model analytic views and cannot be used outside the
    view. Private attributes are required to link to the subject area or attribute views. Private attributes add  

                  more information to the model. Private Attribute selected from a Database table




    73.  Is it possible to add tables later after the Analytic view is created ? True

    • Analytic Views are like cube model where Transaction Data is connected to attribute view
    11.  Calculation View can contain the following:
    a.     Database Tables
    b.    Attribute Views
    c.     Analytic Views

    d.    Calculation Views



    Without Analytic Privilege, no data can be viewed from
    a.     Attribute Views
    b.    Analytic Views
    c.     Calculation Views


     Validation rules of Analytic View can be selected & de-selected from the ‘Manage Preferences’? True