Business Intelligence/Data Warehouse SIG Day 2013

Business Intelligence / Data Warehouse

Özel İlgi Grubu Etkinliği 2013

21 Haziran 2013, Cuma – İTÜ ARI Teknokent 

İlki geçen yıl düzenlenen BI/DW SIG etkinliğimiz bu yıl İTÜ ARI Teknokent’te.

Yine uzman geliştirici ve danışman deneyimleri içeren konular, 

farklı çözümönerileri ile dolu sunumlar sizi bekliyor.

Etkinliğimizde sizleri de aramızda görmekten mutluluk duyacağız.

Saygılarımızla,

TROUG

Program
09:00-09:30	Kayıt ve Açılış
	Hasan Tonguç Yılmaz
09:30–10:15	Oracle Big Data Appliance & Hadoop Introduction
	Ersin İhsan Ünkar
10:30–11:15	Exadata
	Ferhat Şengönül
11:30–12:15	Veri Ambarı Ad-Hoc Performansında Yeni Bir Seçenek
	Ahmet Gök / Principal Consultant (Intellica-Sponsor Sunumu)
12:30-13:30	Öğlen Arası
13:30–14:15	OBIEE 11g Design Tips & Best Practices
	Ahmet Selahattin Güngörmüş
14:30-15:15	ODI & Oracle Big Data Connectors
	Sanem Seren Sever
15:30–16:15	Oracle DW SQL Tuning
	Özgür Macit
16:30–17:15	Oracle Data Mining: Powering Next-Generation Predictive Applications
	Onur Sarkan
17:15-17:20	Kapanış

PIVOT and UNPIVOT Feature with 11g

The pivot_clause of the SELECT statement lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. Pivoting is a key technique in data warehouses.

Syntax:
SELECT .... FROM
PIVOT (
       aggregate-function()
       FOR
       IN
       (, ,..., )
         ) AS
WHERE .....

examples:

-- PIVOT 1
SELECT *
  FROM (
            SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
            ) A
  PIVOT(
            SUM(SALARY)
             FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW)
            )

--PIVOT2
SELECT *
FROM (
          SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
          ) A
PIVOT(
          SUM(SALARY) AS SUM_SAL,
          AVG(SALARY) AS AVG_SAL
          FOR (HML_SALARY_IND)
           IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW, '*' AS UNKNOWN)
          )

changing Department ID to Department Name:

--PIVOT 3           
  SELECT *
  FROM (
            SELECT D.DEPARTMENT_NAME, HML_SALARY_IND, SALARY
            FROM EMPLOYEES2 E, DEPARTMENTS D
            WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID            
            ) A
  PIVOT(
            SUM(SALARY) AS SUM_SAL,
            AVG(SALARY) AS AVG_SAL
            FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW, '*' AS UNKNOWN)
            )

Unpivot Operator

An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

--create a pivot table

CREATE TABLE PIVOT1 AS
  SELECT *
  FROM (
            SELECT DEPARTMENT_ID, HML_SALARY_IND, SALARY FROM EMPLOYEES2            
            ) A
  PIVOT(
            SUM(SALARY)
             FOR (HML_SALARY_IND)
             IN ('H' AS HIGH, 'M' AS MEDIUM, 'L' AS LOW)
            )

SELECT * FROM PIVOT1

--UNPIVOT1
SELECT * FROM PIVOT1
UNPIVOT INCLUDE NULLS
(
SUM_SAL FOR HML_SALARY_IND IN(HIGH AS 'H', MEDIUM AS 'M' , LOW AS 'L')
)

--UNPIVOT EXLUDE NULLS
SELECT * FROM PIVOT1
UNPIVOT
(
SUM_SAL FOR HML_SALARY_IND IN(HIGH AS 'H', MEDIUM AS 'M' , LOW AS 'L')
)

Performance is a Feature Webinar

Weds July 31st, 2013
Central Europe: 17:00 UK: 16:00 East USA: 11.00 Central USA: 10.00 Pacific USA: 08.00

Register now

This webinar is totally free and you'll be notified when the recording of the session is available afterwards.

Summary

To many people who build and run software, "performance" is a side-effect, an afterthought of designing and building "proper" features like buttons that book orders and reports that explain profitability. But great performance at scale doesn't happen by accident. The first step to great performance is to remember what performance is: it is the answer to the question, "What have people been experiencing?" Knowing what people experience when they use your software is possible only if you treat performance as a proper feature–a feature you analyze, design, build, test, and maintain.

This session for system owners, users, designers, developers, and DBAs discusses performance in terms that everyone can understand. It explains why so many in the Oracle ecosystem have lost sight of the basic notion that performance is an attribute of the end-user experience. It explains how performance should really be measured, and it explains the steps that will get you started.

A live Q&A session with Cary Millsap will follow the presentation.

---

Cary Millsap

Oracle ACE Director, former VP of Performance at Oracle, Oracle Magazine's 2004 Author of the Year, Co-founder of the OakTable Network, Oracle Development Tools User Group "Editor's Choice" 2010 award.