Caching & Auditing

This features is so cool that I am pretending that I don’t have
insufficient privilege: Not authorized
for my test user 🙂

Caching
I have the following query which uses calculated view “_SYS_BIC”.”s0014459590trial.inst1/SO3″

select s.”SalesOrg”, s.”Name”, so.”Quarter”, s.”Cnt”, s.”Quantity”,
s.”NetValueHRK”
from “_SYS_BIC”.”s0014459590trial.inst1/SO3″ s
where s.”SalesOrg”=’1000′
and s.”Name” in ( select “Name” from
                                                   ( select “Name”,
rank() over(order by “NetValueHRK” desc) as rank
                                                       from “_SYS_BIC”.”s0014459590trial.inst1/SO3″
where “SalesOrg”=s.”SalesOrg” )

                                            where
rank <= 20
);

We have only a few different values  for “SalesOrg” attribute, so we can say that it is low cardinality attribute. A huge number of data rows and attributes with normal or low cardinality – this is a school example in which columnar storage shows all advantages over traditional row store databases.

When data is stored in a columnar table, the repeating data has a greater likelihood to be only stored once using run-length encoding. With this method, the values are sorted and the repeating values have a greater likelihood of being sorted together as run-length encoding counts the number of consecutive column elements with the same values. If the values are the same values, only one instance is stored.

We can additionaly speed up that query even more by using a cache mechanism.
Only one value of the “SalesOrg” attribute (“SalesOrg”=’1000’) is contained in more than 80% of data,  so we will cache query that contains that value for an hour.

ALTER VIEW _sys_bic.”_SYS_BIC”.”s0014459590trial.inst1/SO3″
ADD CACHE RETENTION 60 FILTER “SalesOrg”=’1000′;

The retention paraneter is measured in minutes. We reduce the cached data by specifying filter condition. Only filtered results will be cached.

Auditing
Auditing provides you with visibility on who did what in the SAP HANA database (or tried to do what) and when. Auditing allows you to monitor and record selected actions performed in the SAP HANA database.

An audit policy defines the actions to be audited, as well as the conditions under which the action must be performed to be relevant for auditing. When an action occurs, the policy is triggered and an audit event is written to the audit trail.

Auditing can be enabled individually for every database in a multiple-container system. For tenant databases, the relevant system property ([auditing configuration] global_auditing_state) is set in the database’s own global.ini file. For the system database, it is set in the nameserver.ini file.

Putting a low performance impact on the running system is probably the most prominent quality of the auditing infrastructure. If auditing is enabled for a certain system, a lookup for qualifying audit policies has to be done for every incoming query. This lookup might be quite complex since it has to determine all underlying object accessed by a certain composite object (e.g. a view or a procedure). However, that call is in most cases a non-blocking call, because its outcome does not influence the query execution.

CREATE AUDIT POLICY SCHEMA_AUDIT
AUDITING
ALL
SELECT ON _SYS_BIC.s0014459590trial.inst1/*
LEVEL INFO;

Oglasi
Ovaj unos je objavljen u Nekategorizirano. Bookmarkirajte stalnu vezu.

Komentiraj

Popunite niže tražene podatke ili kliknite na neku od ikona za prijavu:

WordPress.com Logo

Ovaj komentar pišete koristeći vaš WordPress.com račun. Odjava / Izmijeni )

Twitter picture

Ovaj komentar pišete koristeći vaš Twitter račun. Odjava / Izmijeni )

Facebook slika

Ovaj komentar pišete koristeći vaš Facebook račun. Odjava / Izmijeni )

Google+ photo

Ovaj komentar pišete koristeći vaš Google+ račun. Odjava / Izmijeni )

Spajanje na %s