İş Zekası ve Raporlama Süreçlerinde Kur Tablosu

Merhabalar, bir önceki yazımızda Azure Data Factory kullanarak Merkez Bankası EVDS web servisinden kur bilgilerini nasıl edinebileceğimizi kaleme almıştık. Ancak elde ettiğimiz sonucu ham veri olarak değerlendirmiştik. Bu yazımızda ise elde ettiğimiz ham veriyi iş zekası ve raporlama süreçlerinde sağlıklı bir şekilde nasıl kullanabileceğimize değineceğiz.

Öncelikle neden bir kur tablosuna ihtiyacımız olabilir, bundan bahsedelim. Şirketimiz birden fazla ülkede faaliyet gösteren ya da yurt dışındaki müşterilerine farklı para birimlerinden fatura kesen bir firma olabilir. Bu durumda raporlamalarımızı hem TRY hem de yerel para biriminden, hatta bunlara ek olarak USD, EUR olarak da yapmak isteyebiliriz. İşte bu senaryoda kur tablosu bizim için gerekli olacaktır.

Peki bu kur tablomuz nasıl bir tasarıma sahip olmalıdır ? Veri ambarlarında kur tabloları genellikle aşağıdaki sütunlardan oluşur. (Ana para birimi olarak TRY kabul ediyoruz.)

1. Tarih : Kur bilgisinin geçerli olduğu tarihi,
2. Para Birimi : Kur dönüşümü yapılacak döviz türünü (USD,EUR vb.),
3. Alış Fiyatı : Alış kur değerini,
4. Satış Fiyatı : Satış kur değerini ifade eder.

Yukarıdaki değer alanları (alış-satış fiyatı) ihtiyaca göre ortalama, açılış, kapanış, en düşük veya en yüksek olarak çeşitlendirilebilir.

Şimdi elimizde bulunan gün detaylı ve her bir para biriminin alış veya satış kur bilgisini ayrı sütunlarda gösteren tablomuzu istediğimiz formata nasıl getirebileceğimizi konuşalım. Geçen yazımızda oluşturduğumuz tabloyu bir hatırlayalım.

Yukarıda da görüldüğü gibi Merkez Bankası’ndan çektiğimiz verilerde piyasanın kapalı olduğu günlere ait değerler NULL olarak geliyor. Bu günler için bir kabul ile ilerleyelim. Kabulümüz kur tablomuzu oluştururken NULL değer gelen günlere, o günden önce piyasanın açık olduğu son güne ait kur bilgisini tanımlamak olsun.

Yazımızın bundan sonraki kısmında verilerimizi istediğimiz formata getirecek SQL sorgularını yazacağız. Artık geliştirmelere başlayabiliriz.

Adım 1 : İlk olarak günleri, NULL kayıt gelen günlerde artmayacak şekilde sıralayalım. Aşağıda yazdığımız sorgu ile bu işlemi yapabiliriz. Örnek verecek olursak bu sorgu ile 4. ve 5. satırdaki kur bilgileri NULL geldiği için bu satırlara karşılık gelen Date_Index alanını 3 olarak yazdırmış olduk.

Adım 2 : Oluşturduğumuz Date_Indexleri referans alarak, first_value fonksiyonuyla NULL gelen günleri kabulümüz doğrultusunda dolduralım.

Adım 3 : Yukarıda her bir para birimi için ayrı kolonlarda bulunan değerleri unpivot table işlemi yaparak satır bazlı gösterelim. Aşağıdaki sorgu ile tarih ve para birimi detayında alış-satış kur değerlerini yazdırabiliriz.

Adım 4 : Son olarak da TRY için kur değerlerini 1 olacak şekilde tablomuza ekleyelim.

Böylelikle istediğimiz formatta bir kur tablosu oluşturmuş olduk. Bu tabloyu çeşitli sorgularda DateId ve CurrencyCode eşleşmesiyle kullanarak hem yerel hem de TRY türünden raporlamalar yapabiliriz.

Yazımızda kullandığımız sql sorgumuz da aşağıdaki gibidir:

DROP TABLE IF EXISTS #CentralBank
SELECT CONVERT(DATE,CONVERT(VARCHAR(10), CONVERT(date, [Date], 105), 23)) AS  [Date]
 ,SUM(CASE WHEN TP_DK_USD_A IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY CONVERT(DATE,CONVERT(VARCHAR(10), CONVERT(date, [Date], 105), 23))) AS Date_Index
 ,TP_DK_USD_A
 ,TP_DK_USD_S
 ,TP_DK_EUR_A
 ,TP_DK_EUR_S
 ,TP_DK_GBP_A
 ,TP_DK_GBP_S
INTO #CentralBank
FROM dbo.CentralBank (NOLOCK)

DROP TABLE IF EXISTS #CleanData
SELECT [Date]
 ,FIRST_VALUE(TP_DK_USD_A) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Buying_USD
 ,FIRST_VALUE(TP_DK_USD_S) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Selling_USD

 ,FIRST_VALUE(TP_DK_EUR_A) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Buying_EUR
 ,FIRST_VALUE(TP_DK_EUR_S) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Selling_EUR

 ,FIRST_VALUE(TP_DK_GBP_A) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Buying_GBP
 ,FIRST_VALUE(TP_DK_GBP_S) OVER (PARTITION BY Date_Index ORDER BY [Date]) AS Selling_GBP
INTO #CleanData
FROM #CentralBank

DROP TABLE IF EXISTS #DailyExchangeRate
SELECT CAST(CONVERT(nvarchar,[Date],112) AS int) AS [DateId]
 ,CurrencyCode
 ,MAX(BuyingExchangeRate) AS BuyingExchangeRate
 ,MAX(SellingExchangeRate) AS SellingExchangeRate
INTO #DailyExchangeRate
FROM (
 SELECT Date, RIGHT(CurrencyCode,3) AS CurrencyCode, BuyingExchangeRate,NULL AS SellingExchangeRate
 FROM   
    (SELECT Date, Buying_USD, Buying_EUR, Buying_GBP
    FROM #CleanData) p  
 UNPIVOT  
    (BuyingExchangeRate FOR CurrencyCode IN   
    (Buying_USD, Buying_EUR, Buying_GBP)  
  )AS unpvt_buying
 UNION
 SELECT Date, RIGHT(CurrencyCode,3) AS CurrencyCode,NULL AS BuyingExchangeRate,SellingExchangeRate
 FROM   
    (SELECT Date, Selling_USD, Selling_EUR, Selling_GBP
    FROM #CleanData) p  
 UNPIVOT  
    (SellingExchangeRate FOR CurrencyCode IN   
    (Selling_USD, Selling_EUR, Selling_GBP)  
  )AS unpvt_selling
 ) AS unpvt
GROUP BY [Date],CurrencyCode

INSERT INTO #DailyExchangeRate
SELECT DISTINCT DateId,'TRY' AS CurrencyCode,1 AS BuyingExchangeRate,1 AS SellingExchangeRate
FROM #DailyExchangeRate

SELECT *
FROM #DailyExchangeRate
ORDER BY DateId,CurrencyCode

Bir sonraki yazıda görüşmek üzere …