Rebuilding SAP Indexes

When an indexed field in a table is changed repeatedly, the data is deleted from the index and re-inserted at a new block in the index. The new entries are always added to the right side of the index tree and therefor deletion is relatively to the left.

When the index elements are deleted and added repeatedly, the left side of the index tree tends to be sparse and the right side keeps growing. Depending on the size of index, there might be thousands of sparse blocks, which are being checked during execution.

Fragmented index results in increased usage of database space and more blocks being read into the buffer. This can be avoided by rebuilding the index.

One can measure index fragmentation using the report RSORATAD or using DB02 --> Detailed Analysis --> Enter Index --> Detailed Analysis --> Analyze Index --> Storage Quality.

If storage quality is less than 50% you may need to reorg the index. If you wish to run an analysis on all the indexes, run the report RSORAISQN.

Check SAP note 970538 for more details on the restriction with using this report. Do not run the report without reading the note.

You can also get an idea on the amount of fragmentation by comparing the size of the table and the index. If the size of the index is larger than that of the table, the index is heavily fragmented.

To rebuild index, you can use one the following methods:
1. brspace -f idrebuild -i <index_list>
2. Using DB02
DB02 --> Detailed Analysis (in the "Tables and Indexes" section) --> Object Name: <index> --> Detailed Analysis --> Alter Index --> Coalesce / Rebuild
3. Using RSORAISQ report
   Call SAP transaction SE38 and execute the report RSORAISQ.
   Input the tablespace name and the index name in Object Selection section.
   Check rebuild option in Function Code section.
   A report will be generated with details on the index.
   Click on the Index Rebuild button.
   You will be prompted to feed the name of the index and tablespace again, enter the details and execute.
4. Rebuild indexes offline using the report RSANAORA
5. Mass rebuild of indexes using the report RSORAISQN



If you find this useful, please consider sharing the post with your friends and colleagues on facebook,twittergoogle+linkedin (or any other medium you like!)

Would you like to read more on indexes? The linked post discusses guidelines to creating an Oracle index based on table size.

Monitoring Table and Index Fragmentation (Oracle)

Fragmentation of tables and indexes may reduce performance, depending on the way data is accessed. Fragmentation also leads to greater overall storage space usage. This problem can be eliminated by reorganizing the particular object. However you should bear in mind that this process is very expensive and that the system is not available during a reorganization. It is often not advisable to immediately start a reorganization to eliminate fragmentation. For more information on when to perform a reorganization, see Reorganization.

Table Fragmentation

Table fragmentation will result in longer query times when a full table scan is performed. Since data is not as evenly packed in the data blocks, many blocks may have to be read during a scan to satisfy the query. These blocks may be distributed on various extents. In this case, Oracle must issue recursive calls to locate the address of the next extent in the table to scan.
Recent studies have shown that table fragmentation has hardly any effect on the performance of the database system. This is mainly because full table scans are somewhat rare in an SAP system since data is accessed using an index. Reorganizing table data is generally not as beneficial to performance as previously thought. For more information on how to perform a reorganization, see Reorganizing Tables with BR*Tools

Index Fragmentation

Index fragmentation may bring a higher penalty to application performance. When accessing data through an index and an index range scan (common in SAP systems), Oracle must read each block in the specified range to retrieve the indexed values. If the index is highly fragmented, Oracle may have to search many more blocks, and possibly levels, to get this information. To eliminate index fragmentation, you must rebuild the index.
In both cases – table fragmentation and index fragmentation –always make sure that the soft and hard limits for the number of extents (MAXEXTENTS parameter) are not reached. If this happens, you must intervene.

See also:

SAP HANA Cloud Integration for Data Services – an ASUG Webcast

Ben Hofmans, HANA Cloud Integration product owner, more specifically for Data Services provided this ASUG webcast.   As background you can  read Dick Hirsch’s blog and comments What if HANA Cloud Integration was already productive and nobody knew (or cared)


HANA Cloud Integration is an on demand version of Data Services.  The usual disclaimer applies – things discussed below are subject to change.  Please note these are my notes and I am not a subject matter expert in this area.

1fig.png
Figure 1: Source

Figure 1 shows that there different kinds of clouds

Integration is important in all cloud types

On the left of Figure 1 is the Managed Cloud where SAP provides hosted version of software  - HANA Enterprise Cloud is an example – you get a HANA box – it is not in your network, you don’t acquire hardware or maintain.  It is by SAP in the managed cloud.

In the middle blue box – LOB applications – SuccessFactors, ByDesign, Sales & Operations Planning, Travel on Demand – you don’t have access to HANA database but the UI’s to connect to cloud applications.

Third area is Marketplace – launched at SAPPHIRE – you buy SAP applications – mainly Amazon and other partner clouds provided later

There are many clouds. Integration is an important service, says Ben.

2fig.png
Figure 2: Source: SAP

LOB (“line of business) users is on the left and the right is IT with their requirements – for integration between on premise and in cloud

LOB users want a single source of truth and master data synchronization – one master system for employees, customers .

LOB needs more real-time business process integration

IT's main concern is data security.

3fig.png
Figure 3: Source: SAP

On premise in the cloud and on premise systems and third party solutions

SAP HANA Cloud integration includes process integration and data integration

NetWeaver PI & Data Serivces for data integration - the Cloud brings these two together, with real time process integration.

It has pre-packaged integration flows from SAP system on premise, with a  place to share content

SAP HANA Cloud Integration (Data Services)


The same development team who works on premise works on the cloud offering.

This is Data Services in SAP HANA Cloud, which is a new solution, new architecture, for multi-tenant cloud environment

4fig.png
Figure 4: Source: SAP

SOP is Sales & Operations Planning and since last December is live and GA

It can move data from on premise (ERP APO) and load to HANA hosted in cloud – where SOP planning is built.

SOP  enables organizations to align supply & demand in real-time

5fig.png
Figure 5: Source: SAP

The SuccessFactors application is used to manage employee performance

For business execution use BizX

You can get data from on premise or the other way around, as shown in Figure 5.

6fig.png
Figure 6: Source: SAP

You can access SAP HANA box and SAP HANA marketplace, with your HANA database, data services

You can use this to extract data from on-premise (Business Suite, or data bases) and load to Hana database

7fig.png
Figure 7: Source: SAP

Figure 7 shows Data Services on Demand, available today.  It provides a role based user interface

Data Services extractors that work for on premise also work for Cloud, said Ben.

8fig.png
Figure 8: Source: SAP

Figure 8 shows that there is a lightweight installation inside – where to install, which user to install

The agent packages and sends through https to the cloud

You do not need to open firewall  - the request is from on premise to the cloud

9fig.png
Figure 9: Source: SAP

Ben said you do not need a hard core development.  It offers an HTML5 interface as shown in Figure 9.

You can drag and drop to model data flows from on premise to the cloud or the other way

During this webcast, Ben provided a demo of integration.ondemand.com

10fig.png
Figure 10: Source: SAP

Later this year they plan to offer a license not tied to an SAP cloud application

Limited ETL – only focused on what cloud applications needed (this does not include unstructured)

11fig.png
Figure 11: Source: SAP

Ben said Figure 11 was presented at SAPPHIRE.

SAP Lumira is the new BI on demand solution – need to load to cloud – Hana cloud will play a role for delta loads

Subset of Question & Answer

Q:  Is "Data Services On Demand" only applicable when loading data onto the SAP cloud, or could be used to load data onto an on premise SAP system ?
  A:  For the moment, the Cloud needs to be one end of it. It can load into the on premise system.
________________________________________________________________

Q:  Does this provide integration for SAP BW and SF Workforce Analytics?
A:  Workforce Analytics is in pilot. Not GA yet.
A:  Currently it is not available yet for SAP BW
________________________________________________________________

Q:  SAP Cloud for Travel - we are very close to purchasing Travel On Demand - should we ask our SAP sales person about whether HCI would be the way to integrate with on-premise ERP?  I assume it would be included in ToD pricing?
A:  Definitely worth enquiring.
________________________________________________________________

Q:  How is performance of the data loading ensured?
A:  There are 3 parts that influence performance: (1) extraction, (2) data transfer to the cloud and (3) inserts in the HANA target database.

The onPremise agent is based on the SAP Data Services engine, so has a long history of optimizing extraction from various sources through native protocols (RFC, business content extractors and ABAP generation, as well as native database access for non-SAP systems).
For the data transfer to the cloud, the onPremise agent will compress the data before sending it to the cloud. This is also the part of the overall loading time where bandwidth between the customer’s network and the cloud plays a major role.

Once the data is received by the HCI server component, a dedicated HANA loader will take care of high performance inserts/updates in the HANA database in the cloud.
________________________________________________________________

Q:  In which country is the cloud data stored?
A:  SAP has Data Centers across the world.
________________________________________________________________

Q:  I assume Hana Cloud Integration can schedule tasks, can one task call another task? Resulting in the triggering of Tasks, based on other task completion.

A: Yes, HCI can schedule tasks.
Scheduling is currently time-based only, it is not possible to set dependencies between tasks, or launch one task from another task.

Official Product Tutorials – BI for SAP HANA

The following tutorials have been developed to help you get started using Business Intelligence products. New content is added as it becomes available, so check back on a regular basis.

Looking for more tutorials for the Business Intelligence Suite? Go to sap.com/LearnBI.

See the BI for SAP HANA overview or click a BI tool in the diagram to see tutorials that will help you start using the tool with SAP HANA.

HANA_map_gradientBG.jpg


Information design tool

Within the information design tool (IDT), you can create a relational connection to an SAP HANA view or table using JDBC or ODBC drivers, and build a universe using this connection to provide access via other client tools such as Dashboards and Web Intelligence. Alternatively, you can use IDT to create a relational connection directly in the BI platform repository, where it can be used to connect to SAP HANA without a universe for client tools such as Crystal Reports for Enterprise.

4.0
  • Create a relational connection to SAP HANA (2:54)    interactive / video
  • Create a data foundation based on an SAP HANA view (3:45)    interactive / video
  • Create a business layer for a data foundation based on an SAP HANA view (6:45)    interactive / video
  • Create a data foundation based on SAP HANA tables (5:33)    interactive / video
  • Create a business layer for a data foundation based on SAP HANA tables (4:43)    interactive / video
  • Modify parameters to optimize an SAP HANA universe (2:57)    interactive / video
  • Create a relational connection to SAP HANA in the repository (3:22)    interactive / video

4.0 FP3
  • Create a relational connection to SAP HANA (2:34)    interactive / video
  • Create a data foundation based on an SAP HANA view (3:28)    interactive / video
  • Create a business layer for a data foundation based on an SAP HANA view (4:42)    interactive / video
  • Create a data foundation based on SAP HANA tables (4:52)    interactive / video
  • Create a business layer for a data foundation based on SAP HANA tables (4:55)    interactive / video
  • Modify parameters to optimize an SAP HANA universe (2:33)    interactive / video
  • Create a relational connection to SAP HANA in the repository (3:09)    interactive / video

4.1
  • Create an OLAP connection to an SAP HANA view (1:11)    interactive / video

4.1 SP4
  • Create a relational connection, universe, and Web Intelligence document that consume HANA variables and input parameters (7:03)    interactive / video



Crystal Reports 2011

You can create a direct connection to SAP HANA using JDBC or ODBC drivers.

  • Create a connection to SAP HANA using ODBC drivers (2:43)    interactive / video



Crystal Reports for enterprise

In Crystal Reports for Enterprise, you can access SAP HANA data by using an existing relational connection created using the information design tool. You can also connect to SAP HANA using an OLAP connection created using information design tool or CMC.

4.0
  • Connect to SAP HANA using a relational connection (1:53)    interactive / video

4.1
  • Create an OLAP connection to an SAP HANA view (1:14)    interactive / video

4.1 SP4



Design Studio

Design Studio can access SAP HANA data by using an existing olap connection created using the information design tool or CMC. It can also access SAP HANA outside of SAP BI Platform using ODBC data source. Customers should adopt SAP BusinessObjects Design Studio as their primary tool for creating dashboards and applications, both on the Web and on mobile devices, on top of SAP HANA.

1.0
  • Connect to SAP HANA using ODBC data source. (2:13)    interactive / video

1.4
  • Refresh an SAP BusinessObjects Design Studio document (based on SAP HANA) in a web or mobile client     process flow



Dashboards

Dashboards can connect to SAP HANA only through a relational universe. Customers using Dashboards on top of SAP HANA should strongly consider building their new dashboards with Design Studio.




Web Intelligence

Web Intelligence can connect to SAP HANA only through a relaional universe.


4.1
  • Setup query stripping for SAP HANA datasource (3:43)    interactive / video

4.1 SP4
  • Create a relational connection, universe, and Web Intelligence document that consume HANA variables and input parameters (7:03)    interactive / video



Lumira (previously known as Visual Intelligence)

Lumira can connect directly outside of SAP BI Platform to SAP HANA analytic and calculation views. It can also connect to SAP HANA through SAP BI Platform using a relational universe.


1.0.1
1.0.8
1.17
1.17
  • Acquire a dataset from an SAP HANA analytic view  (1:27)    interactive / video


Analysis, edition for Microsoft Office

Using ODBC drivers, you can create a local connection to SAP HANA that enables you to analyze the data in Analysis, edition for Microsoft Office.

1.1
  • Insert a data source using a local connection to SAP HANA (3:08)    interactive / video



Analysis, edition for OLAP

In Analysis, edition for OLAP, you can connect to SAP HANA using an OLAP connection defined in the Central Management Console or in the information design tool.

4.0
  • Insert a data source using an OLAP connection to SAP HANA (2:48)    interactive / video

4.1



Explorer

You can create an information space based on an SAP HANA view using JDBC drivers.


4.0 FP3
  • Create an information space based on an SAP HANA InfoCube (7:43)    interactive / video