Virtuelle Data Marts

Verschiedene Nutzeranforderungen abdecken

Mit einem vollständig normalisierten Datenbankmodell zu arbeiten, ist für viele Benutzer zu umständlich oder auch nicht notwendig - auch wenn es für den Datenbankentwickler erhebliche Vorteile mit sich bringt.

Die Lösung ist für EXASolution recht einfach: Unabhängig von der Komplexität des zugrundeliegenden Schemas können Views angelegt werden, die auf die Bedürfnisse der jeweiligen Zielgruppe zugeschnitten sind.

Betrachten wir drei Abteilungen, die individuelle Bedürfnisse haben:

Customer Relations
Buchhaltung
Product Management

Im folgenden legen wir Views an, die diesen Abteilungen ein effizientes Arbeiten ermöglichen.

Kundenbewertung

Für Marketingzwecke der Customer Relations Abteilung ist es wichtig zu wissen, welche Kunden “gute Kunden” sind. Für die tägliche Arbeit wollen wir den Mitarbeitern eine View zur Verfügung stellen, die eine solche Bewertung vornimmt.

Das folgende Beispiel bewertet die Kunden nach einem sehr vereinfachtem RFM-Modell

Recency: Kunden, die erst kürzlich etwas gekauft haben, kaufen vermutlich nochmals.
Frequency: Kunden, die häufig etwas gekauft haben, kaufen vermutlich auch weiterhin.
Monetary values: Kunden, die viel Geld ausgegeben haben, tun das vermutlich auch weiterhin.

Wir berechnen die recency als die Anzahl an Tagen seit der letzten Bestellung, die frequency als Gesamtzahl der Bestellungen und den Geldwert als Summe der bisherigen Bestellungen abzüglich der zurückgeschickten Werte. Die Gewichtung der drei Parameter untereinander hängt vom Umfeld ab, im folgenden Beispiel bevorzugen wir den Geldwert gegenüber recency und frequency.

CREATE OR REPLACE VIEW v_customer_rating AS
SELECT 
   c.customer_no
   , c.last_name customer
   , c.city
   , c.country
   , NVL(DAYS_BETWEEN(CURRENT_DATE, last_order), 100) "R"
   , order_count "F"
   , - ordered - NVL(returned, 0)  "M"
   , DENSE_RANK() OVER (ORDER BY NVL(- ordered - NVL(returned, 0), 0) DESC, NVL(DAYS_BETWEEN(CURRENT_DATE, last_order), 100) ASC, NVL(order_count, 0) DESC NULLS LAST) score
FROM 
   v_customers c
   LEFT OUTER JOIN
   (
     SELECT 
        customer_no
        , SUM(transaction_sum) ordered
        , MAX(transaction_date) last_order
        , SUM(MONTHS_BETWEEN(CURRENT_DATE, transaction_date) <= 3) order_count
     FROM v_orders
     GROUP BY customer_no
    ) o
    ON (c.customer_no = o.customer_no)
    LEFT OUTER JOIN
    (
      SELECT 
         customer_no
         , SUM(transaction_sum) returned
      FROM v_returns
      GROUP BY customer_no
    ) r
    ON (o.customer_no = r.customer_no)
    ORDER BY score ASC
    ;

Der View kann auch von Mitarbeitern im Callcenter benutzt werden, die dann über zusätzliche Filter zum Beispiel einen einzelnen Kunden abfragen können, oder eine Liste aller Kunden mit bestimmter Bewertung.

CUS CUSTOMER     CITY         COUNTRY    R   F   M        SCO
--- ------------ ------------ ---------- --- --- -------- ---
  3 Jones        New York     USA         42   3   100.70   1
  2 Adams        New York     USA         27   5    81.65   2
  4 Brown        New York     USA         31   3    63.66   3
  8 Green        New York     USA         43   2    51.85   4
  7 Black        Newark       USA         40   2    38.20   5
 10 Young        New Orleans  USA         71   1    20.50   6
  6 Marsch       Omaha        USA         32   1    14.37   7
  5 Peters       Newark       USA         47   1     8.97   8
  1 Smith        Arkansas     USA         ##                9
 11 Rice         New Orleans  USA         ##                9
 12 Meier        San Diego    USA         ##                9
  9 White        Newberry     USA         ##                9

Buchhaltung

Es ist sehr sinnvoll, für die Abrechnungszwecke Views zu erstellen, die Bestellungen, Retouren und Einzahlungen anzeigt. Die View V_ORDERS versteckt unnötige Einzelheiten wie z.B. "welche Produkte wurden verkauft" und zeigt stattdessen die Gesamtsumme pro Bestellung sowie einige weitere Informationen über den Kunden selbst.

CREATE OR REPLACE VIEW v_orders AS 
SELECT
   customers.customer_no
   , customers.last_name customer
   , orders.order_no transaction_num
   , 'ORDER' transaction_type
   , orders.order_date transaction_date
   , SUM(-items.quantity*items.item_price) transaction_sum
   , COUNT(DISTINCT items.product_no) diff_items
FROM 
   customers
INNER JOIN
   orders
ON orders.customer_no = customers.customer_no
INNER JOIN
   items
ON items.order_no = orders.order_no
GROUP BY customers.customer_no, customers.last_name, orders.order_no, orders.order_date;
 
CREATE OR REPLACE VIEW v_returns AS 
SELECT
   customers.customer_no
   , customers.last_name customer
   , orders.order_no transaction_num
   , 'RETURN' transaction_type
   , returned_items.return_date transaction_date
   , SUM(returned_items.quantity*returned_items.item_price) transaction_sum
   , COUNT(DISTINCT returned_items.product_no) diff_items
FROM 
   customers
INNER JOIN
   orders
ON orders.customer_no = customers.customer_no
INNER JOIN
   returned_items
ON returned_items.order_no = orders.order_no
GROUP BY customers.customer_no, customers.last_name, orders.order_no, returned_items.return_date;
 
CREATE OR REPLACE VIEW v_payments AS 
SELECT
   customers.customer_no
   , customers.last_name customer
   , ROW_NUMBER() OVER (PARTITION BY customers.customer_no ORDER BY payments.payment_date) transaction_num
   , 'PAYMENT' transaction_type
   , payments.payment_date transaction_date
   , payments.payment transaction_sum
FROM 
   customers
INNER JOIN
   payments
ON payments.customer_no = customers.customer_no
;
 
CREATE OR REPLACE VIEW v_transactions AS 
SELECT
   customer_no
   , customer
   , transaction_num
   , transaction_type
   , transaction_date
   , transaction_sum
FROM 
   v_orders 
UNION ALL 
SELECT
   customer_no
   , customer
   , transaction_num
   , transaction_type
   , transaction_date
   , transaction_sum
FROM 
   v_returns UNION ALL 
SELECT
   customer_no
   , customer
   , transaction_num
   , transaction_type
   , transaction_date
   , transaction_sum
FROM 
 v_payments
;

Die View V_TRANSACTION vereinigt alle kundenbezogenen Transaktionen und kann als Basis für "Kontoauszüge" verwendet werden. Die nachfolgende Query berechnet den laufenden Kontostand jedes Kunden, Transaktionen sind nach Datum geordnet. Zu Referenzzwecken ist in jeder Zeile auch der Gesamtstand angegeben.

Eine solche View können Sie auch Ihren Endkunden zur Verfügung stellen, indem Sie einen entsprechenden Filter auf die Kundennummer legen.

SELECT 
   customer_no id
   , last_name
   , ROW_NUMBER() OVER (PARTITION BY customer_no ORDER BY transaction_date) num
   , transaction_date "DATE"
   , transaction_type TYPE
   , transaction_sum SUM
   , SUM(transaction_sum) OVER (PARTITION BY customer_no ORDER BY transaction_date) balance
   , SUM(transaction_sum) OVER (PARTITION BY customer_no) total
FROM
   v_transactions
ORDER BY last_name, id, num;

ID  LAST_NAME    NUM DATE       TYPE    SUM      BALANCE  TOTAL   
--- ------------ --- ---------- ------- -------- -------- --------
  2 Adams          1 2008-10-11 ORDER     -31.32   -31.32     0.00
  2 Adams          2 2008-10-25 ORDER     -31.89   -63.21     0.00
  2 Adams          3 2008-10-25 RETURN     12.64   -50.57     0.00
  2 Adams          4 2008-10-27 PAYMENT     6.04   -44.53     0.00
  2 Adams          5 2008-11-02 ORDER      -8.97   -53.50     0.00
  2 Adams          6 2008-11-03 ORDER     -19.25   -72.75     0.00
  2 Adams          7 2008-11-04 PAYMENT    31.89   -40.86     0.00
  2 Adams          8 2008-11-10 RETURN      8.97   -31.89     0.00
  2 Adams          9 2008-11-25 ORDER     -11.83   -43.72     0.00
  2 Adams         10 2008-11-30 PAYMENT    43.72     0.00     0.00
  7 Black          1 2008-10-20 ORDER     -21.17   -21.17     0.00
  7 Black          2 2008-11-04 PAYMENT    21.17     0.00     0.00
  7 Black          3 2008-11-12 ORDER     -17.03   -17.03     0.00
  7 Black          4 2008-11-17 PAYMENT    17.03     0.00     0.00
  4 Brown          1 2008-10-19 ORDER     -27.49   -27.49     0.00
  4 Brown          2 2008-10-25 ORDER     -15.13   -42.62     0.00
  4 Brown          3 2008-11-03 PAYMENT    27.49   -15.13     0.00
  4 Brown          4 2008-11-21 ORDER     -21.04   -36.17     0.00
  4 Brown          5 2008-11-30 PAYMENT    36.17     0.00     0.00
  8 Green          1 2008-10-14 ORDER     -16.48   -16.48   -43.61
  8 Green          2 2008-11-03 RETURN      8.24    -8.24   -43.61
  8 Green          3 2008-11-03 PAYMENT     8.24     0.00   -43.61
  8 Green          4 2008-11-09 ORDER     -43.61   -43.61   -43.61
  3 Jones          1 2008-10-11 ORDER     -42.02   -42.02     0.00
  3 Jones          2 2008-10-25 ORDER     -34.38   -76.40     0.00
  3 Jones          3 2008-11-03 PAYMENT    26.84   -49.56     0.00
  3 Jones          4 2008-11-03 RETURN      7.59   -41.97     0.00
  3 Jones          5 2008-11-10 ORDER     -31.89   -73.86     0.00
  3 Jones          6 2008-11-15 PAYMENT    73.86     0.00     0.00
  6 Marsch         1 2008-11-20 ORDER     -14.37   -14.37     0.00
  6 Marsch         2 2008-12-01 PAYMENT    14.37     0.00     0.00
  5 Peters         1 2008-11-05 ORDER      -8.97    -8.97     0.00
  5 Peters         2 2008-11-20 PAYMENT     8.97     0.00     0.00
 10 Young          1 2008-10-12 ORDER     -20.50   -20.50     0.00
 10 Young          2 2008-10-20 PAYMENT    20.50     0.00     0.00

Product Management

Eine der typischen Aufgaben im Produktmangement ist es, den Produktverkauf zu beobachten und mit bestimmten Aktionen zu beeinflussen. Im folgenden finden Sie eine View, die Produkte basierend auf der Anzahl der verkauften Artikel und dem zugehörigen Umsatz einordnet.

CREATE OR REPLACE VIEW v_product_rating_monthly AS
SELECT 
     product_no
   , product_name
   , order_month
   , sold_items
   , revenue
   , DENSE_RANK() OVER( PARTITION BY order_month ORDER BY revenue DESC NULLS LAST) revenue_rating
   , DENSE_RANK() OVER( PARTITION BY order_month ORDER BY sold_items DESC NULLS LAST) count_rating
   , DENSE_RANK() OVER( PARTITION BY order_month ORDER BY sold_items DESC, revenue DESC NULLS LAST) overall_rating
FROM
(
   SELECT 
       product_no
     , product_name
     , TRUNC(order_date, 'MM') order_month
     , SUM(COUNT) sold_items
     , SUM(SUM) revenue
   FROM V_PRODUCT_ORDERING
   GROUP BY  product_no, product_name, TRUNC(order_date, 'MM') HAVING SUM(COUNT) > 0
)
;

Die View Berechnet faktisch 3 Ränge:

count_rating anhand der Anzahl der verkauften Artikel
revenue_rating anhand des Umsatzes für jedes Produkt
overall_rating bevorzugt Produkte, die bei der gleichen Anzahl der verkauften Artikel mehr Umsatz bringen

PRO PRODUCT_NAME                        ORDER_MONT SEL REVENUE  REV COU OVE
--- ----------------------------------- ---------- --- -------- --- --- ---
  8 The Love Dare                       2008-10-01   4    46.74   2   1   1
  7 Barefoot Contessa Back to Basics    2008-10-01   3    57.75   1   2   2
  5 The Tales of Beedle the Bard        2008-10-01   3    22.77   5   2   3
  3 The Eclipse                         2008-10-01   2    25.28   3   3   4
  2 Breaking Dawn                       2008-10-01   2     24.9   4   3   5
  1 The Shack                           2008-10-01   2    16.48   7   3   6
 10 The Snowball                        2008-10-01   1    19.25   6   4   7
  9 Brisingr                            2008-10-01   1    15.13   8   4   8
  6 Twilight The Twilight               2008-10-01   1     6.04   9   4   9
  4 New Moon                            2008-10-01   1     6.04   9   4   9
 17 Dreams from My Father               2008-11-01   4    35.88   2   1   1
  5 The Tales of Beedle the Bard        2008-11-01   4    23.16   4   1   2
 10 The Snowball                        2008-11-01   2     38.5   1   2   3
 12 Outliers                            2008-11-01   2    30.78   3   2   4
  8 The Love Dare                       2008-11-01   2    16.48   6   2   5
 13 American Lion                       2008-11-01   1     16.5   5   3   6
 16 A Mercy                             2008-11-01   1    14.37   7   3   7
 15 The Last Lecture                    2008-11-01   1    12.07   8   3   8
  3 The Eclipse                         2008-11-01   1    10.99   9   3   9
  6 Twilight The Twilight               2008-11-01   1     6.04  10   3  10
  4 New Moon                            2008-11-01   1     6.04  10   3  10

Basierend auf der View kann man z.B. Bestseller der letzen 3 Monate vergleichen:

SELECT 
 product_no, product_name
, MAX(CASE WHEN order_month = add_month((SELECT MAX(order_month) FROM v_product_rating_monthly), -2) THEN overall_rating ELSE NULL END) "MONTH BEFORE PREVIOUS"
, MAX(CASE WHEN order_month = add_month((SELECT MAX(order_month) FROM v_product_rating_monthly), -1) THEN overall_rating ELSE NULL END) "PREVIOUS MONTH"
, MAX(CASE WHEN order_month = add_month((SELECT MAX(order_month) FROM v_product_rating_monthly), 0) THEN overall_rating ELSE NULL END) "CURRENT MONTH"
FROM v_product_rating_monthly
WHERE 
order_month >= add_month((SELECT MAX(order_month) FROM v_product_rating_monthly), -3)
GROUP BY product_no, product_name
ORDER BY  "CURRENT MONTH" ASC
;

PRODUCT_NO          PRODUCT_NAME                        BEFOR PREVI CURRE
------------------- ----------------------------------- ----- ----- -----
                 17 Dreams from My Father                               1
                  5 The Tales of Beedle the Bard                  3     2
                 10 The Snowball                                  7     3
                 12 Outliers                                            4
                  8 The Love Dare                                 1     5
                 13 American Lion                                       6
                 16 A Mercy                                             7
                 15 The Last Lecture                                    8
                  3 The Eclipse                                   4     9
                  6 Twilight The Twilight                         9    10
                  4 New Moon                                      9    10
                  1 The Shack                                     6      
                  9 Brisingr                                      8      
                  7 Barefoot Contessa Back to Basics              2      
                  2 Breaking Dawn                                 5

Branchen	Anwendungsgebiete	EXASolution	Services
New Media	Business Intelligence	Vorteile	Support
Marktforschung	Data Warehouse	Technische Details	Trainings
Industrie	Data Mining	Dokumentation	Betrieb
Finanzdienstleistungen	Big Data	EXASolution 4.0	Consulting
Telekommunikation	Webanalytics	EXASolution Appliance	Testsystem
Energie & Versorgung	Cloud Computing	EXACloud	Demo-System
Handel & Lebensmittel		Xtract EXA for SAP	Webinar
Logistik & Transport		Real-Time OLAP
Pharma & Chemie
Gesundheitswesen

© Copyright 2012 EXASOL AG Neumeyerstraße 48 90411 Nürnberg	Sitemap \| Impressum	EXASOL auf:

Virtuelle Data Marts

Verschiedene Nutzeranforderungen abdecken

Kundenbewertung

Buchhaltung

Product Management

Branchen

Anwendungsgebiete

EXASolution

Services